select query runs forever - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select query runs forever

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-27 : 10:38:59

Hi,
The following query used to take about 2 seconds to return data.
Now, it just runs forever to complete.
Not sure what/if anything has changed.
Where do I start investigating into this please?

select
ROW_NUMBER() over (partition by ck.KeyID order by cd.TDateTime desc) as RowNum,
ck.KeyID,
ck.KeyName,
cd.TDateTime
from
tblKey as ck
inner join tblData as cd on ck.KeyID = cd.KeyID
where
Enum = 3
and cd.TDateTime > dateadd(month, -1, GetDate())

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 10:53:34

Use sp_who2 to check for block and that it is doing io and not stuck.
Check the query plan and look for table scans or loops.
Try updating statistics.

Is KeyID unique in both tables?

take out the rownumber and select the top 10.
how many rows does it return in total

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-27 : 11:11:44

Hi,
sp_who2 only shows my login as runnable.
There are no table scans.
I do not have permission to run update statistics tablename.

KeyID is unique in both tables.
after taking out the rownumber, it does return 10 records within a few seconds.
What should I do next?
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 11:19:51

try
;with cte as
(
select
ck.KeyID,
ck.KeyName,
cd.TDateTime
from
tblKey as ck
inner join tblData as cd on ck.KeyID = cd.KeyID
where
Enum = 3
and cd.TDateTime > dateadd(month, -1, GetDate())
)
select
ROW_NUMBER() over (partition by KeyID order by TDateTime desc) as RowNum,
KeyID,
KeyName,
TDateTime
from cte

if that doesn't work then select the cte bit into a temp table and then run the last select on that.

Actually if keyid is unique and that's your partitioning column doesn't it mean that rownum is always 1?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-27 : 11:33:39

Where are your indexes?

SELECT		ROW_NUMBER() OVER (PARTITION BY ck.KeyID ORDER BY cd.TDateTime DESC) AS RowNum,
		ck.KeyID,
		ck.KeyName,
		cd.TDateTime
FROM		dbo.tblKey AS ck
INNER JOIN	dbo.tblData AS cd ON cd.KeyID = ck.KeyID
			AND cd.TDateTime > DATEADD(MONTH, -1, GETDATE())
WHERE		Enum = 3

N 56°04'39.26"
E 12°55'05.63"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-27 : 11:34:17

Hello again,
Everything seems to be working ok as before.
Not sure what was holding this.
Thanks anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources