speeding up SQL query time

[David]

Consider this SQL Query:
-----------------------------------------------------------------
SELECT c.CASE_NBR, DATEDIFF(d, c.CREATE_DT, GETDATE()) AS Age,
c.AFFD_RCVD, c.PRV_CRD_ISS, x.RegE, x.Type, x.Fraud,
c.CUST_FN + ' ' + c.CUST_LN AS CustFullName,
c.ATM_CKCD_NBR, x.TotalLoss, x.Queue, x.Status,
c.QUEUE AS Expr1, x.CHECK_ACT_NBR, c.CUST_LN, c.SSN,
c.CREATE_DT
FROM (
SELECT TOP 9999999 cl.CASE_NBR, cl.SSN, cl.CREATE_DT,
SUM(cast(TRANS_AMNT AS float)) AS TotalLoss,
glQueue.REFN_NM AS Queue,
glStatus.REFN_NM AS Status,
grRegE.REFN_NM AS RegE, grType.REFN_NM AS Type,
grFraud.REFN_NM AS Fraud, cl.CHECK_ACT_NBR
FROM (
((((T_CASE_LST AS cl LEFT JOIN
T_INCIDENT_LST AS il ON cl.CASE_NBR = il.CASE_NBR)
INNER JOIN T_GNRL_REFN AS glQueue
ON cl.QUEUE = glQueue.REFN_NBR)
INNER JOIN T_GNRL_REFN AS glStatus
ON cl.STATUS_CD = glStatus.REFN_NBR)
INNER JOIN T_GNRL_REFN AS grRegE
ON cl.REGE_CD = grRegE.REFN_NBR)
INNER JOIN T_GNRL_REFN AS grType
ON cl.CASE_TYPE_CD = grType.REFN_NBR
)
INNER JOIN T_GNRL_REFN AS grFraud ON cl.FRAUD_CD =
grFraud.REFN_NBR
WHERE (
((glQueue.REFN_DESC) = 'Queue')
AND ((glStatus.REFN_DESC) = 'STATUS_CD')
AND ((grRegE.REFN_DESC) = 'YesNo')
AND ((grType.REFN_DESC) = 'Fraud_Code')
AND ((cl.STATUS_CD) = 0)
)
GROUP BY cl.CASE_NBR, glQueue.REFN_NM, glStatus.REFN_NM,
grRegE.REFN_NM, grType.REFN_NM, grFraud.REFN_NM,
grFraud.REFN_DESC, cl.CHECK_ACT_NBR,
cl.SSN, cl.CREATE_DT
HAVING (((grFraud.REFN_DESC) = 'YesNo'))
) x
LEFT OUTER JOIN T_CASE_LST c ON x.CASE_NBR = c.CASE_NBR
-----------------------------------------------------------------
1. Is there anything that can be done to speed up the query?
2. This part of the query: ... AND ((cl.STATUS_CD) = 0 ... where the 0
is actually a variable passed in via a VB application. 0 would be new
cases, and normally return around 4000 - 5000 records.
3. The SQL server, Web Server, and users, are all in different states.
4. The time to return this query where cl.STATUS_CD = 0 is about 7 -
12 seconds.
5. Is this a reasonable time for this query? What can be done to
increase the time?
6. The SQL server is indexed on T_CASE_LST.STATUS_CD and
T_INCIDENT_LST.CASE_NBR, but not on any field from T_GNRL_REFN since
T_GNRL_REFN is only a general lookup table, and contains less than 50
records.
7. I've built the query as a stored procedure, and it works, though no
measurable speed increase was obtained.
8. I have not attempted building a view to aid this, as I don't see
that helping... or will it?
9. Well: any ideas?
10. I would gladly rewrite the SQL Query if it could return the same
data faster using another method.
11. Is there a way to accomplish the joins involved with the
T_GNRL_REFN in another manner to make it quicker?
12. Is there a better way to add the values in T_INCIDENT_LST than:
.... SUM(cast(TRANS_AMNT AS float)) AS TotalLoss ... ?
13. I don't care if its pretty, I just need it faster.
14. How can I get the summing of T_INCIDENT_LST.TRANS_AMNT without a
derived table...? I know that using the derived table is slowing it
down some.
**** Any Ideas ****

David

[Gert-Jan Strik]

> 14. How can I get the summing of T_INCIDENT_LST.TRANS_AMNT without a

derived table...? I know that using the derived table is slowing it
down some.

15. Simply add another join to your main query (the subselect) for the
T_CASE_LST table. Add extrema aggregates to all column names from that
table in the Selection List. This way you won't have to add columns like
ATM_CKCD_NBR and QUEUE to the GROUP BY clause. Note: you only have one
derived table in your query (the one that is aliased as x).

16. Another way to "get the summing of T_INCIDENT_LST.TRANS_AMNT"
instead of the current LEFT JOIN is by using a scalar subquery. You
would get something like:
...
SELECT ..., cl.CREATE_DT, (
SELECT SUM(cast(TRANS_AMNT AS float))
FROM T_INCIDENT_LST AS il
WHERE cl.CASE_NBR = il.CASE_NBR
) AS TotalLoss
FROM T_CASE_LST AS cl
INNER JOIN ...

17. Are you actually trying to limit the number of rows with TOP
9999999? If not, then lose the TOP keyword, because in your query it
serves no other purpose.

18. A HAVING clause without aggregates can be moved to the WHERE clause.
This is the case here.

19. If not all columns in the list (cl.CASE_NBR, cl.SSN, cl.CREATE_DT,
glQueue.REFN_NM, glStatus.REFN_NM, grRegE.REFN_NM, grType.REFN_NM,
grFraud.REFN_NM, cl.CHECK_ACT_NBR) actively contribute to the key of the
group, then remove them from the GROUP BY clause and simply add an
extrema aggregate in the Selection List. For example, if cl.SSN is
always the same for a particular cl.CASE_NBR, then remove cl.SSN from
the GROUP BY clause, and in the Selection List change "cl.SSN" in
"MAX(cl.SSN) AS SSN".

20. Make sure all foreign keys are indexed. Play around to see which
index should be clustered to get the best performance.

21. Add an alias to TRANS_AMNT so one can see from which table it
originates.

22. BTW: maybe all you care about is speed, but the poor slub that has
to maintain this query may have a different opinion. Make sure you
document this query thoroughly. Currently, documentation seems
non-existing. There is no explanation what the query does, nor is there
any explanation why you are using a derived table, why you use
inconsistent indentation with respect to the joins, etc.

Good luck,
Gert-Jan

[Gert-Jan Strik]

> 14. I'm going to work with indexing the server now.

Well, that explains a lot... It is pretty useless to try to optimize a
query if the proper indexes are not yet in place. With so many joins in
the original query and no proper indexes you will see hash joins all
over the place. What a waste of time (mostly your time).

On the other hand, you have cleaned up the query quite nicely. It is now
easy to read, and thus easy to maintain.

AFTER you added the proper indexes, you might want to experiment with
the SUM calculation as a JOIN with a GROUP BY clause instead of the
scalar subquery. One method might be faster than the other.

Gert-Jan

[Erland Sommarskog]

David (da*********@wdsrc.com) writes:

SELECT cl.CASE_NBR,
DATEDIFF(d, cl.CREATE_DT, GETDATE()) AS Age,
CASE WHEN cl.REGE_CD = 0 THEN 'No' ELSE 'Yes' END AS RegE,
CASE WHEN cl.CASE_TYPE_CD = 0 THEN 'VISA Courtesy'
WHEN cl.CASE_TYPE_CD = 1 THEN 'CKCD' ELSE 'PIN' END AS Type,
CASE WHEN cl.FRAUD_CD = 0 THEN 'No' ELSE 'Yes' END AS Fraud,
(SELECT SUM(cast(il.TRANS_AMNT AS float))
FROM T_INCIDENT_LST AS il
WHERE cl.CASE_NBR = il.CASE_NBR AND il.QUEUE < 51
)AS TotalLoss,
cl.CUST_FN + ' ' + cl.CUST_LN AS CustFullName,
cl.QUEUE AS Queue, cl.STATUS_CD AS Status, cl.QUEUE AS Expr1,
cl.CHECK_ACT_NBR, cl.AFFD_RCVD, cl.PRV_CRD_ISS,
cl.CHECK_ACT_NBR, cl.CUST_LN, cl.SSN, cl.CREATE_DT
FROM T_CASE_LST cl
WHERE cl.STATUS_CD = 0

Whoah! That's a whole lot nicer query than the first you posted.
Good job!

Here is a variation:

SELECT cl.CASE_NBR,
DATEDIFF(d, cl.CREATE_DT, GETDATE()) AS Age,
CASE WHEN cl.REGE_CD = 0 THEN 'No' ELSE 'Yes' END AS RegE,
CASE WHEN cl.CASE_TYPE_CD = 0 THEN 'VISA Courtesy'
WHEN cl.CASE_TYPE_CD = 1 THEN 'CKCD' ELSE 'PIN' END AS Type,
CASE WHEN cl.FRAUD_CD = 0 THEN 'No' ELSE 'Yes' END AS Fraud,
il.amt AS TotalLoss,
cl.CUST_FN + ' ' + cl.CUST_LN AS CustFullName,
cl.QUEUE AS Queue, cl.STATUS_CD AS Status, cl.QUEUE AS Expr1,
cl.CHECK_ACT_NBR, cl.AFFD_RCVD, cl.PRV_CRD_ISS,
cl.CHECK_ACT_NBR, cl.CUST_LN, cl.SSN, cl.CREATE_DT
FROM T_CASE_LST cl
JOIN (SELECT il.CASE_NBR, amt = SUM(il.TRANS_AMNT)
FROM T_INCIDENT_LST
WHERE il.QUEUE < 51) AS il ON il.CASE_NBR = cl.CASE_NBR
WHERE cl.STATUS_CD = 0

I don't know if it will actually run faster, but it is worth to
give it a shot. I also took out the CAST, but if you have a good
reason for it, put it back. It is not the CAST that kills you
anyway.

By now the query is so simple, so it is difficult find suggestions
to speed it up. But two things to check:

o You have clustered index on STATUS_CD.
o CASE_NBR have the same data type in both tables.

And, oh, if there are many columns in the incident table, add a
a non-clustered index on (CASE_NBR, QUEUE, TRANS_AMNT). Also try
(QUEUE, CASE_NBR, TRANS_AMNT). If the table is narrow (that is,
there are no other columns, these are best fit for the clustered
index.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp