Running SQL query - getting timeout

Question

SELECT TOP 1 * 
FROM URLForPosting WITH(nolock) 
WHERE status = 0 
ORDER BY newid()

This is the query when I run in code I get timeout, even when I run it at SQL Server I get timeout.

However when I do this

SELECT TOP 1 * 
FROM URLForPosting WITH(nolock) 
WHERE status = 0

It runs perfectly fine.

Also the first query was running fine until the records for the first 6 lacks records its has total 8 lack now it gives time out? I have created index on status.

Any suggestions?

The Order by newid() is a no-go. You sort by a value that is completely random and can't be indexed! What are you trying to achieve? — Yves M.
– Yves M., Commented Nov 1, 2010 at 9:33
i am selecting links from the database and i want to get them randomly and when after completing my task i set there status to 1... — Ehsan Kayani
– Ehsan Kayani, Commented Nov 1, 2010 at 9:36
What do you suggest i do if i want to randomly pick values from the database not in a order — Ehsan Kayani
– Ehsan Kayani, Commented Nov 1, 2010 at 9:37
what do you mean by newid()? You should have column names in order by clause. — pavanred
– pavanred, Commented Nov 1, 2010 at 9:38

shA.t · Accepted Answer · 2015-05-06 04:38:07Z

5

An alternative way to return a random record is to use TABLESAMPLE. See how this performs:

SELECT TOP 1 * 
FROM URLForPosting TABLESAMPLE(1) WITH(nolock) 
WHERE status=0 
ORDER BY newid()

TABLESAMPLE is available in SQL Server 2005 and later versions.

edited May 6, 2015 at 4:38

shA.t

17k5 gold badges59 silver badges121 bronze badges

answered Nov 1, 2010 at 9:54

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

egrunin Over a year ago

+1 because I never knew about this, but: If you specify a small number, such as 5, you might not receive results in the sample.

Ehsan Kayani Over a year ago

well i used this instead of newid() and it solved the problem SELECT top 1 * FROM URLForPosting WHERE status=0 and (ABS(CAST( (BINARY_CHECKSUM(*) * RAND()) as int)) % 100) < 1

Zephyr · Accepted Answer · 2010-11-01 10:02:09Z

0

Since it was running fine for when you had 600K records and is hanging at 800K it is likely that the inserts have caused your indexes to become fragmented. Get your DBA (or someone with db_owner rights) to run DBCC DBREINDEX(URLForPosting).

Or for versions 2005 upwards you can use ALTER INDEX REBUID.

answered Nov 1, 2010 at 10:02

Zephyr

7,8634 gold badges40 silver badges42 bronze badges

1 Comment

Ehsan Kayani Over a year ago

well i used this instead of newid() and it solved the problem SELECT top 1 * FROM URLForPosting WHERE status=0 and (ABS(CAST( (BINARY_CHECKSUM(*) * RAND()) as int)) % 100) < 1

Collectives™ on Stack Overflow

Running SQL query - getting timeout

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related