How can I optimize my SQL query to run faster?

Question

SELECT 
   b.User_Id
   ,(CONVERT(varchar, DATEADD(hh, - 7, b.callstartdt), 101))as 'Dt'
   ,(COUNT(distinct b.SeqNum ) + Count(distinct c.SeqNum) + count(distinct d.seqnum)) as 'TotalCalls'
   ,COUNT(distinct b.SeqNum )as 'ACD'
   ,COUNT(distinct c.SeqNum)as 'AOD'
   ,COUNT(distinct d.seqnum)  as 'Manual'
   ,COUNT(distinct e.SeqNum)as 'Contacts'
   ,COUNT (distinct es.seqnum) as 'Success'
FROM 
   [detail_epro].[dbo].[ACDCallDetail]as b 
LEFT JOIN 
   [detail_epro].[dbo].[AODCallDetail]as c on c.User_Id = b.User_Id   
LEFT JOIN
   [detail_epro].[dbo].[manualCallDetail]as d on d.User_Id = b.User_Id 
LEFT JOIN
   (SELECT 
       USER_ID, CallStartDt, SeqNum
    FROM 
       [detail_epro].[dbo].[AgentDispoDetail]
    WHERE 
        Disp_Id IN 
                (100000150, 100000126,  100000137,  100000093,  100000133,  
                100000123,  100000094,  100000161,  100000162,  100000085,  
                100000084,  100000086,  100000096,  100000087,  100000157,  
                100000088,  100000097,  100000154,  100000148,  100000134,  
                100000131,  100000160,  100000156,  100000165,  100000166,  
                100000122,  100000121,  100000138,  100000130,  100000144,  
                100000132,  100000158,  100000098,  100000147,  100000100,  
                100000153,  100000139,  100000145,  100000101,  100000140,  
                100000102,  100000103,  100000104,  100000105,  100000106,  
                100000159,  100000112,  100000135,  100000090,  100000113,  
                100000141,  100000146,  100000115,  100000108,  100000092,  
                100000155,  100000125,  100000151,  100000136,  100000107,  
                100000142) 
   ) AS e ON e.User_Id = b.User_Id
LEFT JOIN 
   (SELECT 
        USER_ID, CallStartDt, SeqNum
    FROM 
        [detail_epro].[dbo].[AgentDispoDetail]
    WHERE Disp_Id IN 
                (100000150, 100000137,  100000093,  100000133,  100000123,  
                100000094,  100000161,  100000085,  100000086,  100000157,  
                100000088,  100000131,  100000160,  100000156,  100000165,  
                100000166,  100000122,  100000121,  100000138,  100000144,  
                100000132,  100000098,  100000100,  100000153,  100000139,  
                100000145,  100000101,  100000140,  100000102,  100000103,  
                100000105,  100000106,  100000159,  100000112,  100000135,  
                100000141,  100000146,  100000115,  100000108,  100000092,  
                100000155,  100000125,  100000151,  100000136,  100000107) 
   ) AS es ON es.User_Id = b.User_Id
WHERE
   (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101)) = (CONVERT(varchar, DATEADD(hh, - 7, c.CallStartDt), 101))
   AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101))= (CONVERT(varchar, DATEADD(hh, - 7, d.CallStartDt), 101))
   AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101))= (CONVERT(varchar, DATEADD(hh, - 7, e.CallStartDt), 101)) 
   AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101))= (CONVERT(varchar, DATEADD(hh, - 7, es.CallStartDt), 101))
   AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101)) >= '08/01/2014'
GROUP BY
   b.User_Id, b.CallStartDt

It's taking a lot longer than I would like to run this query, over a minute, I'm guessing it has a lot to do with the server but figured I would ask to see if anyone had any thoughts of making this faster

The query is to get some phone agent data, that isn't summarized by any tables

acd = inbound calls
aod = dialer calls
manual = manual calls
contacts = are based on the disposition codes an agent would use
success = is a subset of contacts

How large are the tables it's running against? I suspect the `IN` sections of the subqueries are what's making it crawl. all of the `Convert` functions probably aren't helping your cause either. — user2366842, Aug 06 '14 at 18:02
Why on earth are you converting the datetimes to `VARCHAR` and - even worse - to the `'08/01/2014'` format? — ypercubeᵀᴹ, Aug 06 '14 at 18:05
Here,look at the accepted answer to understand what the explain is http://stackoverflow.com/a/7359705/1745672 — Mihai, Aug 06 '14 at 18:06
@ypercube tables datetime functions all seem to return blanks unless i convert — lookslikeanevo, Aug 06 '14 at 18:07
ugh epro...i'm guessing there are no index's on the tables you have here. Explain plan will help identify where those index's need to be. — Twelfth, Aug 06 '14 at 18:12
your query would be returning blank without the casting because the datetimes in the database likely don't match exactly.....even if they're inserted into the database at the same time, they'll be a fraction of a second off due to the time it takes to insert one record versus the other. Is there another field that you can join against in the bottom as opposed to the date? Generally speaking it's not really a good (or even safe) thing to join where dates match... — user2366842, Aug 06 '14 at 18:12
@Twelfth ya, i wasnt ehre during the db set up, so i got to inherit a lot of this — lookslikeanevo, Aug 06 '14 at 18:15
@user2366842 I joined them at user_id but needed date to match also — lookslikeanevo, Aug 06 '14 at 18:15
If that's how it HAS to be set up, then I'd look into using DatePart instead of casting the entire date. — user2366842, Aug 06 '14 at 18:19
If it's like the majority of call centers, it's likely being used/maintained by reporting coordinators...people that were on the phones at first but got pulled off to handle reporting needs...tends to result in poor database designs as they go trial and error. There isn't much 'pure sql' that can speed this up...do some index researching (clustered vs non-clustered), look up your existing indexes (if any), record where you could use indexes (explain in SSMS will give tips where indexes could be), and then finally build some indexing for yourself. — Twelfth, Aug 06 '14 at 18:19
@Twelfth would you believe me if i told you this is how the vendor set it up? O_o — lookslikeanevo, Aug 06 '14 at 18:24
to be clear, what's happening when you're casting the dates and comparing them, is sql has to go through each date character by character and compare each separate character. Each of your date comparisons are now doing something like ten comparisons instead of just one. — user2366842, Aug 06 '14 at 18:25
@lookslikeanevo - listen to user2366842. Vendor's very rarely care about reporting afterwards...why would you possibly want that? — Twelfth, Aug 06 '14 at 18:27
Also you might want to take a look at the (currently) only answer posted. Dumping the values into a temp table will probably speed up that portion of the query. — user2366842, Aug 06 '14 at 18:30
In addition to what others threw into the ring, you have LEFT-JOINs, but then in the WHERE clause are explicitly testing equality between the respective "c", "d", "e" and "es" aliases which thus turns it into all INNER JOINs. Was that your intent? — DRapp, Aug 06 '14 at 18:46
According to http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server, @DRapp is on the right track here too. swapping out the left joins for inner joins might make it right slightly faster (since you're really only grabbing the inner joined records anyways) — user2366842, Aug 06 '14 at 18:51
http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN — HLGEM, Aug 06 '14 at 18:55
and if you really need left joins, then ,(COUNT(distinct b.SeqNum ) + Count(distinct c.SeqNum) + count(distinct d.seqnum)) as 'TotalCalls' will not work unless you handle nulls — HLGEM, Aug 06 '14 at 18:57

score 6 · Accepted Answer · edited Jul 29 '20 at 18:43

There are numerous issues with the query but the first thing I notice is the inefficient datetime conversions. So, I'd start with that part first, before examining the indexing and the execution plan.

I suppose you want to check if the various datetimes are in the same date (minus the 7 hours which is probably your timezone while the data are stored in UTC). So, lets try this, instead of that (horrible) WHERE:

CROSS APPLY
    ( SELECT dt = DATEADD(hour, -7, b.CallStartDt) ) AS x
CROSS APPLY
    ( SELECT dt = DATEADD(day, +1, x.dt) ) AS y
WHERE
       b.CallStartDt >= DATEADD(hour, +7, '20140801')
  AND  c.CallStartDt >= x.dt AND  c.CallStartDt < y.dt
  AND  d.CallStartDt >= x.dt AND  d.CallStartDt < y.dt
  AND  e.CallStartDt >= x.dt AND  e.CallStartDt < y.dt
  AND es.CallStartDt >= x.dt AND es.CallStartDt < y.dt

Explanation/notes:

the (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101)) >= '08/01/2014' is utterly wrong. Not only it uses inefficient conversions, it will also return wrong results. Because the date (both as string and as date) '08/03/2014' is after '08/01/2014' but for other examples, it's the other way around: '09/09/2011' > '08/01/2014' but obviously 2011 is before 2014.
All unnecessary calls to DATEDIFF() and CONVERT() have been removed. This way, not only several thousands calls (or million, depends on your tables sizes) to the functions will not be done but the optimizer will be able to use indexes for the various conditions, if there are indexes on these columns.
Only the (-7 hours) of b.CallStartDt has been kept as there is no way to avoid that without altering the table (adding a computed column with an index though would help.)
Use sane formats for dates and datetimes, like '20140801' ('YYYYMMDD'), which as Aaron Bertrand's blog explains is the only 100% secure format for using with dates in SQL-Server. See: Bad habits to kick : mis-handling date / range queries
With the DATEADD() function, use the long forms. hour instead of hh, day instead of dd, minute instead of mm (or is it mi?, or min?, I keep forgetting.) Less error-prone.

More to do:

The 4 above conditions (the ones regarding c, d, e and es tables) should probably be moved to the respective LEFT joins (as DRapp commented.)
Check the execution plan and whether indexes are available and used.

Minor details:

Avoid single quotes aliases. use either unquoted aliases, or double-quoted if needed. See (another Aaron Bertrand's blog post): Bad Habits to Kick : Using AS instead of = for column aliases

+1 - assumes he is on 2005 or later, but much better than converts — Twelfth, Aug 06 '14 at 19:15
@twelfth. Thnx. I guess you are talking about the `CROSS APPLY`. This can changed but then the expressions would have to be written four times. It's not a matter of efficiency, just a bit less code. — ypercubeᵀᴹ, Aug 06 '14 at 19:19
@ypercube wow! ...i aprreciate all the feedback...learning everyday! I apprecaite all the feedback and reads on best practices! — lookslikeanevo, Aug 06 '14 at 19:31

score 2 · Answer 2 · answered Aug 06 '14 at 18:14

You need to remove your subqueries and put

WHERE Disp_Id IN 
(100000150, 100000137,  100000093,  100000133,  100000123,  
100000094,  100000161,  100000085,  100000086,  100000157,  
100000088,  100000131,  100000160,  100000156,  100000165,  
100000166,  100000122,  100000121,  100000138,  100000144,  
100000132,  100000098,  100000100,  100000153,  100000139,  
100000145,  100000101,  100000140,  100000102,  100000103,  
100000105,  100000106,  100000159,  100000112,  100000135,  
100000141,  100000146,  100000115,  100000108,  100000092,  
100000155,  100000125,  100000151,  100000136,  100000107)

In a temp table and join that temp table to [detail_epro].[dbo].[AgentDispoDetail] on Disp_ID.

And as others have already posted, why are you using converts to compare the dates? Are they in a different format on each table? If they aren't remove the converts.

Also, remove the DATEADDS. You are using them in a comparison, but adding -7 to each half of the equation. That is like: X + 7 = 8 + 7. Remove the 7s and the value of x hasn't changed.

I explained what I believe to be going on with the Dates above (I'm guessing they're stored as datetime, which means they'll never match exactly) +1 for the temp table, and also cutting out the -7's on both sides of the comparison. — user2366842, Aug 06 '14 at 18:46

score 1 · Answer 3 · answered Aug 21 '14 at 23:11

1

Just as an update since i didnt post an answer, I ended up seprating a lot of the queries into smaller queries with temp tables, doing this sped up the query and the process 1000%

eventually converted to CTE and saved it as a view

Thanks all

answered Aug 21 '14 at 23:11

lookslikeanevo

566
1
5
14

score 1 · Answer 4 · answered Sep 19 '14 at 16:46

1

Not sure of your locale but a better UTC conversion you might want to consider is

    dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), b.callstartdt)

which also accounts for daylight savings, again depending on your locale.

answered Sep 19 '14 at 16:46

Scott Leas

63
1
5

How can I optimize my SQL query to run faster?

4 Answers4