SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with query logic time in time out labor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

536 Posts

Posted - 04/26/2013 :  15:50:49  Show Profile  Reply with Quote
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.

Edited by - cplusplus on 04/26/2013 15:51:46

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 04/26/2013 :  17:23:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000