Any way to speed this up?? - 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)

Any way to speed this up??

Author

Topic

cesopk
Starting Member

1 Post

Posted - 2007-08-21 : 13:55:10

Hi.
I am trying to find a way to speed this up. Currently it takes over 25 minutes and I have to kill it. Thanks in advance.

SELECT DISTINCT
DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT) AS DATERANGE
, TBLDISPATCHGROUP.NAME
, MGS=(SELECT COUNT(fromname)
FROM TBLMESSAGES T
WHERE DATENAME(MM, T.DTSENT) + ' ' + DATENAME(DD, T.DTSENT)=DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT)
and FROMNAME IN (SELECT TRUCKNAME FROM TBLTRUCKS WHERE TBLTRUCKS.CURRENTDISPATCHER = TBLDISPATCHGROUP.SN))
, MGSERRORS=(SELECT COUNT(*)
FROM XX_TMERRORS X
WHERE DATENAME(MM, X.Date) + ' ' + DATENAME(DD, X.Date)=DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT)
and MSGFROM IN (SELECT TRUCKNAME FROM TBLTRUCKS WHERE TBLTRUCKS.CURRENTDISPATCHER = TBLDISPATCHGROUP.SN))
FROM TBLTRUCKS
,TBLDISPATCHGROUP
,TBLMESSAGES
WHERE TBLTRUCKS.CURRENTDISPATCHER = TBLDISPATCHGROUP.SN
AND TBLTRUCKS.TRUCKNAME = TBLMESSAGES.FROMNAME

ORDER BY DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT)
, TBLDISPATCHGROUP.NAME

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-21 : 13:57:33

Post the DDL with all of the Indexes, and tell us how many rows of data

But I can tell you right now, your predicate is going to cause a scan

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-21 : 22:06:31

Double check execution plan, and ensure it's not blocked.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-22 : 08:34:43

There's no way I could test this for performance because I don't have the data available so I haven't tried a rewrite for you, but I can tell you this... as well as the WHERE clauses NOT being sargable (able to use indexes correctly), you have double nested correlated subqueries in the SELECT list. That means that each subquery will be executed once for every row you select in the outer query. You need to rewrite this to use proper joins on, perhaps, some derived tables so that each row is only touched once instead of the thousands of times that the correlated sub-queries will cause. If you reformat your code with proper indentation, you'll see exactly what I'm talking about...

 SELECT DISTINCT 
        DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT) AS DATERANGE
        , TBLDISPATCHGROUP.NAME
        , MGS=(SELECT COUNT(fromname) 
                 FROM TBLMESSAGES T 
                WHERE DATENAME(MM, T.DTSENT) + ' ' + DATENAME(DD, T.DTSENT)=DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT) 
                  and FROMNAME IN (SELECT TRUCKNAME 
                                     FROM TBLTRUCKS 
                                    WHERE TBLTRUCKS.CURRENTDISPATCHER = TBLDISPATCHGROUP.SN))
        , MGSERRORS=(SELECT COUNT(*) 
                       FROM XX_TMERRORS X
                      WHERE DATENAME(MM, X.Date) + ' ' + DATENAME(DD, X.Date)=DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT) 
                        and MSGFROM IN (SELECT TRUCKNAME 
                                          FROM TBLTRUCKS 
                                         WHERE TBLTRUCKS.CURRENTDISPATCHER = TBLDISPATCHGROUP.SN))
   FROM TBLTRUCKS
        ,TBLDISPATCHGROUP
        ,TBLMESSAGES
  WHERE TBLTRUCKS.CURRENTDISPATCHER = TBLDISPATCHGROUP.SN
    AND TBLTRUCKS.TRUCKNAME = TBLMESSAGES.FROMNAME
  ORDER BY DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT)
        , TBLDISPATCHGROUP.NAME

Another major problem with the code is that you're using a lot of character based stuff and concatenation to figure out the rows that are all in the same month... try/study the following formula and lookup DATEDIFF and DATEADD in Books Online to find a better way...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) --Change GETDATE() to other dates to see what happens

Last, but not least, as you are rebuilding your code, remember to NOT format intermediate results for display... it will only slow the code to a crawl. If you are using this in conjunction with a GUI, you shouldn't be formatting dates at all in the SQL... do it in the GUI, instead.

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 08:54:36

[code]SELECT DISTINCT DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT) AS DATERANGE,
TBLDISPATCHGROUP.NAME,
COUNT(*) OVER (PARTITION BY CONVERT(VARCHAR(6), TBLMESSAGES.DTSENT, 107), TBLTRUCKS.CURRENTDISPATCHER) AS MGS,
ISNULL(d.p, 0) AS MGSERRORS
FROM TBLTRUCKS
INNER JOIN TBLDISPATCHGROUP ON TBLDISPATCHGROUP.SN = TBLTRUCKS.CURRENTDISPATCHER
INNER JOIN TBLMESSAGES ON TBLMESSAGES.FROMNAME = TBLTRUCKS.TRUCKNAME
LEFT JOIN (
SELECT X.MSGFROM,
DATENAME(MM, X.Date) + ' ' + DATENAME(DD, X.Date) AS Y,
COUNT(*) AS p
FROM XX_TMERRORS X
GROUP BY X.MSGFROM,
DATENAME(MM, X.Date) + ' ' + DATENAME(DD, X.Date)
) AS d ON d.MSGFROM = TBLTRUCKS.CURRENTDISPATCHER AND d.Y = DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT)
ORDER BY DATENAME(MM, TBLMESSAGES.DTSENT) + ' ' + DATENAME(DD, TBLMESSAGES.DTSENT),
TBLDISPATCHGROUP.NAME[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources