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 2008 Forums
 Transact-SQL (2008)
 Need help with query logic time in time out labor

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-04-26 : 15:50:49
I have this query used within a view, this is taking horrible amount of time, whe query select top 100 rows, it is taking almost 7 minutes, there are 10 mill rows in this table:

StartDTM and ENDDTM both are datetime datatype fields: is this logic the efficient?
Please need help:

CASE
WHEN DATEPART(Hour, StartDTM) = 1 THEN
CASE
WHEN DATEPART(Hour, EndDTM) = 1 THEN
CAST(DATEPART(minute, EndDTM) - DATEPART(minute, StartDTM) AS FLOAT)/60
ELSE
CAST(60 - DATEPART(minute, StartDTM) AS FLOAT)/60
END
WHEN DATEPART(Hour, EndDTM) = 1 THEN
CAST(DATEPART(minute, EndDTM) AS FLOAT)/60
WHEN 1 BETWEEN DATEPART(Hour, StartDTM) AND DATEPART(Hour, EndDTM) THEN 1
ELSE 0
END AS '1AM',

CASE
WHEN DATEPART(Hour, StartDTM) = 2 THEN
CASE
WHEN DATEPART(Hour, EndDTM) = 2 THEN
CAST(DATEPART(minute, EndDTM) - DATEPART(minute, StartDTM) AS FLOAT)/60
ELSE
CAST(60 - DATEPART(minute, StartDTM) AS FLOAT)/60
END
WHEN DATEPART(Hour, EndDTM) = 2 THEN
CAST(DATEPART(minute, EndDTM) AS FLOAT)/60
WHEN 2 BETWEEN DATEPART(Hour, StartDTM) AND DATEPART(Hour, EndDTM) THEN 1
ELSE 0
END AS '2AM',


I only pasted for 1Am and 2AM hours using datepart.

Thank you very much for the helpful info.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 17:23:18
More often than not, resources spent on figuring out the arithmetic that you have in you select statement are neglible compared to the resources spend on retrieving, and sorting the data. So look at your tables and the join clauses and the columns that participate in the WHERE clauses. Those are that are the bottle necks.

You should run the TOP 100 query after enabling the query plan (control-m) and then look at the query plan. Look where it is taking the bulk of the time. Once you know what they are, those together with your joins/where clauses should help you figure out how to speed it up.

If you like post your query including the where clauses and joins. Also, look up and post what indexes you have on the tables.

Another thing you should make sure is that the statistics are updated regularly. Also, you would need to look at index fragmentation. A severely fragmented index can add to performance problems.
Go to Top of Page
   

- Advertisement -