February 4, 2007 at 10:03 pm
Hello All-
I have this query and it times out thro' application on the first time run. But, 2nd and 3rd runs go fine and quick. I need your expert advise and what else can I do on this query.
Query:
SELECT CustomerNameNm = CASE WHEN BE.EmpFamiliarNm IS NULL
OR BE.EmpFamiliarNm = '' THEN BE.EmpLastNm + ', ' + BE.EmpFirstNm
ELSE BE.EmpLastNm + ', ' + BE.EmpFirstNm + ' (' + BE.EmpFamiliarNm + ')' END,
CF.CustFileId,
CF.AuthCreateDt,
LEFT(ISNULL(NULLIF(RTRIM(LTRIM(CF.DeptrCityNm)) + ',', ','), '') + ISNULL(NULLIF(RTRIM(LTRIM(CF.DeptrStProvNm)) + ',', ','), '') + ISNULL(NULLIF(RTRIM(LTRIM(CF.DeptrCountryNm)) + ',', ','), ''), NULLIF(LEN(ISNULL(NULLIF(RTRIM(LTRIM(CF.DeptrCityNm)) + ',', ','), '') + ISNULL(NULLIF(RTRIM(LTRIM(CF.DeptrStProvNm)) + ',', ','), '') + ISNULL(NULLIF(RTRIM(LTRIM(CF.DeptrCountryNm)) + ',', ','), '')) - 1, -1)) AS DepartureNm,
LEFT(ISNULL(NULLIF(RTRIM(LTRIM(CF.DestCityNm)) + ',', ','), '') + ISNULL(NULLIF(RTRIM(LTRIM(CF.DestStProvNm)) + ',', ','), '') + ISNULL(NULLIF(RTRIM(LTRIM(CF.DestCountryNm)) + ',', ','), ''), NULLIF(LEN(ISNULL(NULLIF(RTRIM(LTRIM(CF.DestCityNm)) + ',', ','), '') + ISNULL(NULLIF(RTRIM(LTRIM(CF.DestStProvNm)) + ',', ','), '') + ISNULL(NULLIF(RTRIM(LTRIM(CF.DestCountryNm)) + ',', ','), '')) - 1, -1)) AS DestinationNm,
MoveTypeDesc.Descr,
CF.CustFileStatusCd,
StatusDesc.Descr
FROM dbo.tblDivBranchCustFile AS DC WITH (NOLOCK)
INNER JOIN DBO.tblDivBranch DB WITH (NOLOCK)
ON DC.DivBranchId = DB.DivBranchId
AND DB.BUSNPARTID = 1647
INNER JOIN dbo.tblCustomerFile AS CF WITH(NOLOCK)
ON DC.CustFileId = CF.CustFileId
INNER JOIN dbo.tblCode AS MoveTypeDesc WITH (NOLOCK)
ON CF.MoveTypCd = MoveTypeDesc.Cd
INNER JOIN dbo.tblCode AS StatusDesc WITH (NOLOCK)
ON CF.CustFileStatusCd = StatusDesc.Cd
INNER JOIN dbo.tblBusnPartEmp AS BE WITH (NOLOCK)
ON CF.BusnPartEmpId = BE.BusnPartEmpId
WHERE
((BE.EmpLastNm Like 's%' AND (BE.EmpFirstNm Like 's%' OR BE.EmpFamiliarNm LIKE 's%') )
OR
(BE.EmpPrevLastNm LIKE 's%' AND (BE.EmpFirstNm Like 's%' OR BE.EmpFamiliarNm LIKE 's%') ))
AND BE.EmpTypCd = 2
AND StatusDesc.Typ = 1099
AND MoveTypeDesc.Typ = 1102
AND CF.CustFileId <> 0
AND (CF.CustFileStatusCd <> 13
OR CF.CustFileStatusCd = 1 OR CF.CustFileStatusCd = 12
AND CF.CustFileStatusDt >= CONVERT(DATETIME, GETDATE() - 365, 101)
AND CF.CustFileStatusDt <= CONVERT(DATETIME, GETDATE(), 101))
There three large tables in this query, tblBusnPartEmp, tblCustomerFile and tblDivBranchCustFile.
I checked on the query optimizers plan and statistics and I found the troubled areas is the predicate like with a single letter.
Here is the index structure of the table tblBusnPartEmp, tblCustomerFile and statistics for the query.
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IDXBusnPartEmp1 nonclustered located on PRIMARY EmpNTLogin
IDXBusnPartEmp2 nonclustered located on PRIMARY EdgeUserTypeCd
IDXBusnPartEmp3 nonclustered located on PRIMARY EmpIdNo
IDXBusnPartEmp4 nonclustered located on PRIMARY EmpLegalLastNm, EmpLegalFirstNm
IDXBusnPartEmp5 nonclustered located on PRIMARY NCEZCode, NCEContractorNbr
IDXBusnPartEmp6 nonclustered, stats no recompute located on PRIMARY EmpLastNm, EmpFirstNm, EmpFamiliarNm, BusnPartEmpId
IDXBusnPartEmp7 nonclustered located on PRIMARY EmpPrevLastNm, EmpFirstNm, EmpFamiliarNm, BusnPartEmpId
XPKBusnPartEmp nonclustered, unique, primary key located on PRIMARY BusnPartEmpId
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IDXCustomerFile1 nonclustered located on PRIMARY RepaymentAgreeId
IDXCustomerFile2 nonclustered located on PRIMARY BusnPartEmpId
IDXCustomerFile3 nonclustered located on PRIMARY BusnPartTmId
IDXCustomerFile4 nonclustered located on PRIMARY GroupMoveId
IDXCustomerFile5 nonclustered located on PRIMARY TransId
IDXCustomerFile6 nonclustered located on PRIMARY ClientContactBusnPartEmpId
IDXCustomerFile7 nonclustered located on PRIMARY ClientMoveTypId
IDXCustomerFile8 nonclustered located on PRIMARY CustFileStatusCd, CustFileStatusDt
XPKCustomerFile nonclustered, unique, primary key located on PRIMARY CustFileId
statistics io result:
Table 'tblCode'. Scan count 2, logical reads 30, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblDivBranch'. Scan count 5, logical reads 451, physical reads 0, read-ahead reads 451, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblCustomerFile'. Scan count 2378, logical reads 18706, physical reads 102, read-ahead reads 1542, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBusnPartEmp'. Scan count 5, logical reads 537710, physical reads 0, read-ahead reads 1392, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblDivBranchCustFile'. Scan count 5, logical reads 2698, physical reads 0, read-ahead reads 2698, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Can someone look into this and suggest me how do I go about this?
Viking
February 5, 2007 at 3:33 am
Hello,
You could relocate
AND BE.EmpTypCd = 2
AND StatusDesc.Typ = 1099
AND MoveTypeDesc.Typ = 1102
(BE.EmpFirstNm Like 's%' OR BE.EmpFamiliarNm LIKE 's%')
AND (BE.EmpPrevLastNm LIKE 's%' or BE.EmpLastNm Like 's%' )
AND CF.CustFileStatusDt >= CONVERT(DATETIME, GETDATE() - 365, 101)
AND CF.CustFileStatusDt <= CONVERT(DATETIME, GETDATE(), 101)
from your where-clause to your join-statements (these are common). This because the first filtering happens at join-level, second at where,third at having
*can CF.CustFileId be smaller than 0? If it can't use > instead of <> 0. Using inequality prevents the use of an index (sargability)
I haven't went through your indexes yet.
*same with CustFileStatusCd. Perhaps using CustFileStatusCd between 0 and 12 OR CustFileStatusCd>13
February 5, 2007 at 6:04 am
I don't know how many rows this query returns but I figure it needs tuning. Why it times out I've no idea, proably a high cost plan / and/or loading data into cache, which subsequent runs don't have to do. Generally single column indexes are usually less than useful, you need to examine the query plan and see if all those scans and reads are really required.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 5, 2007 at 9:56 am
Something else you could try is explicitly specifying the type of join.
When there are more than 3 or 4 tables in a query I have noticed that the optimizer works out a good plan for the first few tables and then just uses nested loop joins for the remaining tables. This is often not very efficient. If you know there are indexes on the FKs used in the join you could try specifying INNER MERGE JOIN instead of INNER JOIN.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply