Different query times for same query.
-
Greetings all, on the same server, the same query (fairly hefty) runs infinitely in 1 database and takes only 1 minute in another (substantially larger) database. The 2 databases have the same properties settings. We have not found any corrupt data. I have scoured other threads and have tried DBCC commands, ReIndexing/Defragging, hints, comparing execution plans (only can get estimated in the database that runs infinitely), analyzing in Profiler . We are on SS2000, but the query runs alright in the database if we run it in SS2005.
Any thoughts / ideas? We're at a loss
.Thank you in advance...
MCF-
-
>>I have scoured other threads and have tried DBCC commands, ReIndexing/Defragging
Those don't have much impact if the tables don't have a clustered index and have become highly fragmented heaps. Table & index DDL along with the execution plans would be useful info to add to this thread.
A larger database size is not as relevant as you might think. The distribution of the data in the tables can have a much larger impact.
-
Does the query have any kind of looping? An unmet ending condition (data differences) would be one of my suspects for what you describe.
Have you looked at locking?
Have you run a profiler trace to see exactly what statement is running so long?
What kind of logging / checking are you doing?
-
PW - Thank you for the reply. All of our tables have a clustered index. Is there anything specifically I can look up about DISTRIBUTION in BOL or online?
BTW - The execution plan is huge, so I am going to see if there were any replies before I post it. ----
Pam Abdulla - thank you for your reply. We have already researched your first 3 suggestions. When you mention logging / checking - what specifically are you referring to?
Thanks!
M-
-
If you have done the first three, have you pinopinted troublesome query?
BY logging / checking, I mean have you tried setting up the proc with debug / logging info to see how far it gets?
-
Pam - yes, I should have included this info before. There are 3 left joins that when changed to inner joins, cause the query to run at normal time/speed.
-
Distribution of data affects the selectivity of an index. If you were to index a column containing, say, Gender, which contained only the values 'M' and 'F', the SQL optimizer would probably never use the index to seek for data, because a column with only 2 values is not selective enough and the optimizer decides it might as well just tablescan.
Some useful info here:
http://www.sql-server-performance.com/nonclustered_indexes.asp
-
Check the size of TempDB on both servers.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
could there be an open transaction on the server that runs indefinately? that or a table lock might lock up the query, even though normally it might be quick.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
post the query
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Thank you all for your replies. Here is the scrubbed query:
SELECT U.C_Id, U.XPNom, U.Pln_Nm, U.U_Num, U.APD_Id, P.SttA_Id, P.PName
, MAX(U.X7) AS X7
, MAX(U.X8) AS X8
, MIN(U.LNm) AS LNm, MIN(U.FNm) AS FNm, MIN(U.Pat_Mi) AS Pat_Mi, MAX(U.I_Nm) AS I_Nm
, MIN(P.Dt_In) AS Dt_In, MAX(P.Go_Dt) AS Go_Dt, MIN(U.O_C_Id) AS O_C_Id
, SUM(U.TotA) AS TotA, SUM(U.XRem) AS XRem
, MAX(P.PPDt) AS PPDt, MAX(P.OPDt) AS OPDt, MAX(P.PPat_D) AS PPat_D
, SUM(COALESCE(P.PriP, 0) ) AS PriP
, SUM(COALESCE(P.OPt, 0) ) AS OPt
, SUM(COALESCE(P.Patt, 0) ) AS Patt
, SUM(COALESCE(C.ExPt, 0) ) AS ExPt
, SUM(COALESCE(C.CoPay, 0) ) AS CoPay, SUM(COALESCE(C.DdCt, 0) ) AS DdCt, SUM(COALESCE(C.Oop, 0) ) AS Oop
, SUM(COALESCE(C.NCmt, 0) ) AS NCmt
, SUM(COALESCE(C.DAt, 0) ) AS DAt
, SUM(COALESCE(VA.VaJ, 0) ) AS VaJ
, (SELECT SUM(AmBl) FROM Table1
WHERE Table1.aPrimH_Num = P.aPrimH_Num
AND Table1.Pr_Id = '1' ) AS Pri_Ins_AmBl
, (SELECT SUM(AmBl) FROM Table1
WHERE Table1.aPrimH_Num = P.aPrimH_Num) AS AmBl
FROM
(
SELECT C_Id, XPNom, Pln_Nm, Pn_Cd, APD_Id, U_Num
, MAX(CASE WHEN B_Typ LIKE '7' THEN 'Y' ELSE 'N' END) AS X7
, MAX(CASE WHEN B_Typ LIKE '2' THEN 'Y' ELSE 'N' END) AS X8
, MIN(CAST(O_Clm_Id AS tinyint) ) AS O_Clm_Id, MAX(I_Num) AS I_Num
, MIN(LNm) AS LNm, MIN(FNm) AS FNm, MIN(Pat_Mi) AS Pat_Mi
, SUM(TotA) AS TotA, SUM(ChgTl) AS XRem
FROM
(
SELECT Table2.C_Id, Table8.XPNom, Table8.Pln_Nm, Table2.Pn_Cd, Table2.APD_Id
, CASE WHEN Table2.dtCd = '' THEN Table2.U_Num ELSE Table2.dtCd END AS U_Num
, Table2.UjCode, Table2.O_C_Id, Table2.I_Nm
, Table2.LNm, Table2.FNm, Table2.Pat_Mi
, Table2.TotA, Table2.ChgTl
FROM Table2
INNER JOIN Table8
ON Table2.Pn_Cd = Table8.Pn_Cd
AND Table8.XUpId = 0
WHERE Table2.DerOId = 'T'
AND EXISTS
( SELECT * FROM View1 V
WHERE V.U_Num = Table2.U_Num
AND V.Value_Q = 'I'
AND V.Post_Dt BETWEEN '2000/01/01' AND '2020/12/31' )) Clms
GROUP BY C_Id, XPNom, Pln_Nm, Pn_Cd, APD_Id, U_Num) U
INNER JOIN
( SELECT P1.U_Num, P1.aPrimH_Num, P1.SttA_Id, P1.Dt_In, P1.Go_Dt, P1.PName
, MAX(CASE WHEN P1.Value_Q = 'I' THEN P1.Date_1 ELSE NULL END) AS PPDt
, MAX(CASE WHEN P1.Value_Q = 'S' THEN P1.Date_1 ELSE NULL END) AS OPDt
, MAX(CASE WHEN P1.Value_Q = 'P' THEN P1.Date_1 ELSE NULL END) AS PPat_D
, SUM(CASE WHEN P1.Value_Q = 'I' THEN P1.Chg_1 ELSE 0 END) AS PriP
, SUM(CASE WHEN P1.Value_Q = 'S' THEN P1.Chg_1 ELSE 0 END) AS OPt
, SUM(CASE WHEN P1.Value_Q = 'P' THEN P1.Chg_1 ELSE 0 END) AS Patt
FROM
(
SELECT Table6.U_Num, Table6.aPrimH_Num, Table6.SttA_Id, Table6.Dt_In, Table6.Go_Dt
, Table6.PName, Table4.Date_1
, CASE WHEN Table4.Typ_Cd IN('P', 'R')
THEN CASE WHEN EXISTS
( SELECT * FROM Table3
WHERE Table3.Pn_Cd = 'QWERTY'
AND Table3.Opg_Cd IN ('SM')
AND Table3.Pln_Cd = Table4.TMCIS
AND Table3.FromE <= Table4.Date_1
AND Table3.ThruE >= Table4.Date_1 )
THEN 'P'
WHEN EXISTS
( SELECT * FROM Table3
WHERE Table3.Pn_Cd = U1.Pn_Cd
AND ((U1.Xd_Id = 'Z' AND Table3.Opg_Cd IN ('SM') )
OR (U1.Xd_Id = 'OPLD' AND Table3.Opg_Cd IN ('SM') )
OR (U1.Xd_Id = 'H' AND Table3.Opg_Cd IN ('SM') ))
AND Table3.Pln_Cd = Table4.TMCIS
AND Table3.FromE <= Table4.Date_1
AND Table3.ThruE >= Table4.Date_1 )
THEN 'I'
ELSE 'S'
END
ELSE CASE Table4.TMCIS WHEN '77' THEN 'Q' WHEN '58' THEN 'Y' ELSE 'O' END
END AS Value_Q
, Table4.Chg_1 AS Chg_1
FROM Table4
INNER JOIN Table6
ON Table4.aPrimH_Num = Table6.aPrimH_Num
INNER JOIN
( SELECT Pn_Cd, U_Num
, CASE WHEN COUNT(DISTINCT Xd_Id) > 1 THEN 'Z' ELSE MIN(Xd_Id) END AS Xd_Id
FROM
( SELECT DISTINCT Table8.Pn_Cd, Table2.U_Num, 'OPLD' AS Xd_Id
FROM Table2
INNER JOIN Table8
ON Table2.Pn_Cd = Table8.Pn_Cd
AND Table8.XUpId = 0
WHERE Table2.DerOId = 'T'
AND EXISTS
( SELECT * FROM View1 V
WHERE V.U_Num = Table2.U_Num
AND V.Value_Q = 'I'
AND V.Post_Dt BETWEEN '2004/09/01' AND '2020/12/31' )) U2
GROUP BY Pn_Cd, U_Num ) U1
ON Table6.U_Num = U1.U_Num
WHERE Table4.Date_1 <= '2006/06/05'
AND Table6.XUpId = 0
AND Table4.Typ_Cd = 'P') P1
GROUP BY P1.U_Num, P1.aPrimH_Num, P1.SttA_Id, P1.Dt_In, P1.Go_Dt, P1.PName) P
ON U.U_Num = P.U_Num
LEFT OUTER JOIN
( SELECT Table6.U_Num
, SUM(CASE WHEN Table5.PRTCd IN('C', 'D', 'O', 'T') THEN Table5.PRAmt ELSE 0 END) AS Expected_Patt
, SUM(CASE WHEN Table5.PRTCd = 'C' THEN Table5.PRAmt ELSE 0 END) AS CoP
, SUM(CASE WHEN Table5.PRTCd = 'T' THEN Table5.PRAmt ELSE 0 END) AS Ddc
, SUM(CASE WHEN Table5.PRTCd = 'O' THEN Table5.PRAmt ELSE 0 END) AS Op
, SUM(CASE WHEN Table5.PRTCd = 'D' THEN Table5.PRAmt ELSE 0 END) AS Non_Num
, SUM(CASE WHEN Table5.PRTCd = 'N' THEN Table5.PRAmt ELSE 0 END) AS Den_Num
FROM Table5
INNER JOIN Table6
ON Table5.aPrimH_Num = Table6.aPrimH_Num
INNER JOIN
( SELECT DISTINCT Table2.U_Num
FROM Table2
INNER JOIN Table8
ON Table2.Pn_Cd = Table8.Pn_Cd
AND Table8.XUpId = 0
WHERE Table2.DerOId = 'T'
AND EXISTS
( SELECT * FROM View1 V
WHERE V.U_Num = Table2.U_Num
AND V.Value_Q = 'I'
AND V.Post_Dt BETWEEN '2004/09/01' AND '2020/12/31' )) U2
ON Table6.U_Num = U2.U_Num
WHERE Table5.PRDt <= '2006/06/05'
AND Table5.ILvId = '1'
AND Table6.XUpId = 0
GROUP BY Table6.U_Num ) C
ON U.U_Num = C.U_Num
LEFT OUTER JOIN
( SELECT Table6.U_Num
, SUM(Table4.Chg_1) AS VaJ
FROM Table4
INNER JOIN Table6
ON Table4.aPrimH_Num = Table6.aPrimH_Num
INNER JOIN Table7
ON Table4.TMCIS = Table7.TMCIS
INNER JOIN
(SELECT DISTINCT Table2.U_Num
FROM Table2
INNER JOIN Table8
ON Table2.Pn_Cd = Table8.Pn_Cd
AND Table8.XUpId = 0
WHERE Table2.DerOId = 'T'
AND EXISTS
( SELECT * FROM View1 V
WHERE V.U_Num = Table2.U_Num
AND V.Value_Q = 'I'
AND V.Post_Dt BETWEEN '2004/09/01' AND '2020/12/31' )) U3
ON Table6.U_Num = U3.U_Num
WHERE Table7.Cat_Cd = 'VA'
AND Table7.FromE <= Table4.Date_1
AND Table7.ThruE >= Table4.Date_1
AND Table6.XUpId = 0
AND Table4.Date_1 <= '2006/06/05'
GROUP BY Table6.U_Num ) VA
ON U.U_Num = VA.U_Num
GROUP BY U.C_Id, U.XPNom, U.Pln_Nm, U.U_Num, U.APD_Id, P.aPrimH_Num, P.SttA_Id, P.PName
-
More importantly, check the location of the data files for each db, and (if these are run on separate instances) then check the individual TempDB locations.
Also, if these DBs are on separate instances, check the memory utilization for each instance. If these are on the same instance, memory and TempDB will be the same for each of the databases.
What you are looking for here:
- Are the data files and TempDB on the same drive for the slow DB?
- If using separate instances, is the memory allocation for the slow instance less than for the fast instance?
- Are there any other items that may make the first DB faster than the second DB?
-
The databases & datafiles are on the same server & drive. The settings of the 2 databases are identical as far as we can tell.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply