January 14, 2009 at 2:32 pm
I have a query that runs fine if I do not include Non-Clustered Indexed column in WHERE, but if I include this column, it take 5 times more time to execute.
If I use this column, which has Non-Clustered Index, Query Execution Plan shows that the Non-Clustered Index Scan take 20% cost and just after this Bookmark Lookup Cost is 60%.
I have to use this column in WHERE to limit result according to user provided input, but can’t afford the long execution time.
My looks like:
SELECT DISTINCT
M.idt1, M.nme1, M.nmeP,
R.varC, R.var_CD, O.nme_O
FROM
fact_M M INNER JOIN
dim_R R ON M.sur_id_M = R.sur_id_M INNER JOIN
fact_I I ON M.sur_id_M = I.sur_id_M LEFT OUTER JOIN
dim_O O ON M.dim_id_O = O.dim_id_O
WHERE
(R.ind_P = 1)
AND (M.idt_P = @varP OR @varPIC IS NULL)
AND (M.var_ Status = @varStatus OR @varStatus IS NULL)
AND (I.dte_A BETWEEN @varBegin_Date AND @varEnd_Date)
ORDER BY
R.varC, M.idt1
“M.idt_P” has Non-Cluster Index on it, if I remove this from WHERE, query runs fine, otherwise takes 5 times longer. I have tried couple of options but no use.
Please let me know what Am I doing wrong? Or how can I make it faster?
Thanks
January 14, 2009 at 3:17 pm
It's probably faster for SQL to scan the entire table than scanning the index + bookmark lookup...
Is it a big table?
Have you tried creating a covering index?
January 14, 2009 at 5:46 pm
Can you post the execution plans?
It does sound like you need a covering index. A key lookup can be very expensive.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2009 at 7:11 am
Did you try doing a derived table?
Something like this.
Select idt1,nme1,nmeP,varC, var_CD,nme_O from
(SELECT DISTINCT
M.idt1, M.nme1, M.nmeP,
R.varC, R.var_CD, O.nme_O, M.idt_P
FROM
fact_M M INNER JOIN
dim_R R ON M.sur_id_M = R.sur_id_M INNER JOIN
fact_I I ON M.sur_id_M = I.sur_id_M LEFT OUTER JOIN
dim_O O ON M.dim_id_O = O.dim_id_O
WHERE
(R.ind_P = 1)
AND (M.var_ Status = @varStatus OR @varStatus IS NULL)
AND (I.dte_A BETWEEN @varBegin_Date AND @varEnd_Date)) as x
AND (M.idt_P = @varP OR @varPIC IS NULL)
ORDER BY
varC, idt1
I am not sure if this work or not. But you can try.
-Roy
January 15, 2009 at 9:11 am
I tried both Covering Index as well as Derived Table options, but no use :crying:
Here is my actuall query:
DECLARE @varBegin_Date datetime
DECLARE @varEnd_Date datetime
DECLARE @varCoSub varchar(100)
DECLARE @varPIC int
DECLARE @varStatus varchar(10)
DECLARE @varInvoice_Status varchar(10)
DECLARE @varTeam int
set @varBegin_Date ='01/01/2004'
set @varEnd_Date ='01/01/2005'
set @varCoSub =null
set @varPIC =null
set @varStatus =null
set @varInvoice_Status ='paid'
set @varTeam = 33
SELECT DISTINCT
M.idt_Matter, M.nme_Matter, M.nme_PIC,
R.var_CoSub, R.var_CoSub_Desc, R.var_RC, R.var_RC_Desc, R.var_GL,
O.nme_Organization
FROM
fact_Matter M INNER JOIN
dim_Rollup R ON M.sur_id_Matter = R.sur_id_Matter LEFT OUTER JOIN
dim_Organization O ON M.dim_id_Organization = O.dim_id_Organization
WHERE
(R.ind_Primary = 1)
AND (M.idt_PIC = @varPIC OR @varPIC IS NULL)
AND (M.var_Matter_Status = @varStatus OR @varStatus IS NULL)
AND ((M.idt_Team = @varTeam OR M.idt_Practice_Group_Id = @varTeam ) OR @varTeam IS NULL)
ORDER BY
R.var_CoSub, M.idt_Matter,M.nme_PIC
And here is its execution plan:
StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
SET STATISTICS PROFILE ON15101SETSTATON0
(1 row(s) affected)
StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
SET SHOWPLAN_ALL ON16101SETON0
(1 row(s) affected)
StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
DECLARE @varBegin_Date datetime
DECLARE @varEnd_Date datetime
DECLARE @varCoSub varchar(100)
DECLARE @varPIC int
DECLARE @varStatus varchar(10)
DECLARE @varInvoice_Status varchar(10)
DECLARE @varTeam int
set @varBegin_Date ='01/01/2004'17102SELECT0
set @varEnd_Date ='01/01/2005'18203SELECT0
set @varCoSub =null19304SELECT0
set @varPIC =null20405SELECT0
set @varStatus =null21506SELECT0
set @varInvoice_Status ='paid'22607SELECT0
set @varTeam = 3323708SELECT0
SELECT DISTINCT
M.idt_Matter, M.nme_Matter, M.nme_PIC,
R.var_CoSub, R.var_CoSub_Desc, R.var_RC, R.var_RC_Desc, R.var_GL,
O.nme_Organization
FROM
fact_Matter M INNER JOIN
dim_Rollup R ON M.sur_id_Matter = R.sur_id_Matter LEFT OUTER JOIN
dim_Organization O ON M.dim_id_Organization = O.dim_id_Organization
WHERE
(R.ind_Primary = 1)
AND (M.idt_PIC = @varPIC OR @varPIC IS NULL)
AND (M.var_Matter_Status = @varStatus OR @varStatus IS NULL)
AND ((M.idt_Team = @varTeam OR M.idt_Practice_Group_Id = @varTeam ) OR @varTeam IS NULL)
ORDER BY
R.var_CoSub, M.idt_Matter,M.nme_PIC24809194.532915.0178246SELECT0
|--Sort(DISTINCT ORDER BY:([R].[var_CoSub] ASC, [M].[idt_Matter] ASC, [M].[nme_PIC] ASC, [M].[nme_Matter] ASC, [R].[var_CoSub_Desc] ASC, [R].[var_RC] ASC, [R].[var_RC_Desc] ASC, [R].[var_GL] ASC, [O].[nme_Organization] ASC))2498SortDistinct SortDISTINCT ORDER BY:([R].[var_CoSub] ASC, [M].[idt_Matter] ASC, [M].[nme_PIC] ASC, [M].[nme_Matter] ASC, [R].[var_CoSub_Desc] ASC, [R].[var_RC] ASC, [R].[var_RC_Desc] ASC, [R].[var_GL] ASC, [O].[nme_Organization] ASC)194.532911.1261261E-22.4077187E-31625.0178246[M].[idt_Matter], [M].[nme_Matter], [M].[nme_PIC], [R].[var_CoSub], [R].[var_CoSub_Desc], [R].[var_RC], [R].[var_RC_Desc], [R].[var_GL], [O].[nme_Organization]PLAN_ROW01.0
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([M].[dim_id_Organization]) WITH PREFETCH)24109Nested LoopsLeft Outer JoinOUTER REFERENCES:([M].[dim_id_Organization]) WITH PREFETCH194.532910.04.0657379E-440324.9725127[M].[idt_Matter], [M].[nme_Matter], [M].[nme_PIC], [R].[var_CoSub], [R].[var_CoSub_Desc], [R].[var_RC], [R].[var_RC_Desc], [R].[var_GL], [O].[nme_Organization]PLAN_ROW01.0
|--Filter(WHERE:(Convert([R].[ind_Primary])=1))241210FilterFilterWHERE:(Convert([R].[ind_Primary])=1)194.532910.05.6839617E-533724.5433788[M].[dim_id_Organization], [M].[idt_Matter], [M].[nme_Matter], [M].[nme_PIC], [R].[var_CoSub], [R].[var_CoSub_Desc], [R].[var_RC], [R].[var_RC_Desc], [R].[var_GL]PLAN_ROW01.0
| |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([CLD_Warehouse].[dbo].[dim_Rollup] AS [R]))241312Bookmark LookupBookmark LookupBOOKMARK:([Bmk1001]), OBJECT:([CLD_Warehouse].[dbo].[dim_Rollup] AS [R])[R].[ind_Primary], [R].[var_CoSub], [R].[var_CoSub_Desc], [R].[var_RC], [R].[var_RC_Desc], [R].[var_GL]195.998670.60936041.0779927E-433724.5433221[M].[dim_id_Organization], [M].[idt_Matter], [M].[nme_Matter], [M].[nme_PIC], [R].[ind_Primary], [R].[var_CoSub], [R].[var_CoSub_Desc], [R].[var_RC], [R].[var_RC_Desc], [R].[var_GL]PLAN_ROW01.0
| |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[sur_id_Matter]) WITH PREFETCH)241513Nested LoopsInner JoinOUTER REFERENCES:([M].[sur_id_Matter]) WITH PREFETCH195.998670.04.0963723E-431323.9338536[M].[dim_id_Organization], [M].[idt_Matter], [M].[nme_Matter], [M].[nme_PIC], [Bmk1001]PLAN_ROW01.0
| |--Filter(WHERE:((([M].[idt_Team]=[@varTeam] OR [M].[idt_Practice_Group_ID]=[@varTeam]) OR [@varTeam]=NULL) AND ([M].[var_Matter_Status]=[@varStatus] OR [@varStatus]=NULL)))241715FilterFilterWHERE:((([M].[idt_Team]=[@varTeam] OR [M].[idt_Practice_Group_ID]=[@varTeam]) OR [@varTeam]=NULL) AND ([M].[var_Matter_Status]=[@varStatus] OR [@varStatus]=NULL))163.67410.08.9831237E-431043.5071378[M].[sur_id_Matter], [M].[dim_id_Organization], [M].[idt_Matter], [M].[nme_Matter], [M].[nme_PIC]PLAN_ROW01.0
| | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([CLD_Warehouse].[dbo].[fact_Matter] AS [M]))241817Bookmark LookupBookmark LookupBOOKMARK:([Bmk1000]), OBJECT:([CLD_Warehouse].[dbo].[fact_Matter] AS [M])[M].[sur_id_Matter], [M].[dim_id_Organization], [M].[idt_Practice_Group_ID], [M].[idt_Team], [M].[var_Matter_Status], [M].[idt_Matter], [M].[nme_Matter], [M].[nme_PIC]1137.10422.79368316.2540732E-431043.5062394[M].[sur_id_Matter], [M].[dim_id_Organization], [M].[idt_Practice_Group_ID], [M].[idt_Team], [M].[var_Matter_Status], [M].[idt_Matter], [M].[nme_Matter], [M].[nme_PIC]PLAN_ROW01.0
| | |--Index Scan(OBJECT:([CLD_Warehouse].[dbo].[fact_Matter].[idx_PIC] AS [M]), WHERE:([M].[idt_PIC]=[@varPIC] OR [@varPIC]=NULL))242018Index ScanIndex ScanOBJECT:([CLD_Warehouse].[dbo].[fact_Matter].[idx_PIC] AS [M]), WHERE:([M].[idt_PIC]=[@varPIC] OR [@varPIC]=NULL)[Bmk1000], [M].[idt_PIC]1137.10420.435356290.17093195330.60628825[Bmk1000], [M].[idt_PIC]PLAN_ROW01.0
| |--Index Seek(OBJECT:([CLD_Warehouse].[dbo].[dim_Rollup].[dim_Rollup19] AS [R]), SEEK:([R].[sur_id_Matter]=[M].[sur_id_Matter]) ORDERED FORWARD)243615Index SeekIndex SeekOBJECT:([CLD_Warehouse].[dbo].[dim_Rollup].[dim_Rollup19] AS [R]), SEEK:([R].[sur_id_Matter]=[M].[sur_id_Matter]) ORDERED FORWARD[Bmk1001]1.19749363.2034251E-37.9820835E-5370.42630643[Bmk1001]PLAN_ROW0163.6741
|--Clustered Index Seek(OBJECT:([CLD_Warehouse].[dbo].[dim_Organization].[PK_dim_Organization] AS [O]), SEEK:([O].[dim_id_Organization]=[M].[dim_id_Organization]) ORDERED FORWARD)244110Clustered Index SeekClustered Index SeekOBJECT:([CLD_Warehouse].[dbo].[dim_Organization].[PK_dim_Organization] AS [O]), SEEK:([O].[dim_id_Organization]=[M].[dim_id_Organization]) ORDERED FORWARD[O].[nme_Organization]1.03.2034251E-37.9603E-56680.42872724[O].[nme_Organization]PLAN_ROW0194.53291
(18 row(s) affected)
StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
SET STATISTICS PROFILE OFF25101SETSTATON0
(1 row(s) affected)
The Column in question is M.idt_PIC and the Non-Clustered Index in question is idx_PIC.
January 15, 2009 at 10:13 am
You didn't mention that you're using SQL 2000. Please post SQL 2000-related questions in the SQL 2000 forums. If you post in the 2005 forums, you're very likely to get 2005-specific solutions.
The plan, as you've pasted it is unreadable. Please run the query with the showplan_all option on and results to grid. Copy the grid and paste in Excel so that the various pieces of info are in separate cells. Save, zip and attach the excel spreadsheet.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2009 at 12:11 pm
“M.idt_P” has Non-Cluster Index on it, if I remove this from WHERE, query runs fine, otherwise takes 5 times longer. I have tried couple of options but no use.
Please let me know what Am I doing wrong? Or how can I make it faster?
Thanks
The plan is very tough to read but if what you say here is true that removing this piece from the where clause makes everything "fine" then as a quick fix you can remove it from the where clause, stick those results into a temp table and then run the select on the temp table with that filter included. Obviously not necessarily ideal but if you want a quick fix that would surely be it.
January 16, 2009 at 2:44 am
Syed Asim Rizvi (1/14/2009)
I have a query that runs fine if I do not include Non-Clustered Indexed column in WHERE, but if I include this column, it take 5 times more time to execute.If I use this column, which has Non-Clustered Index, Query Execution Plan shows that the Non-Clustered Index Scan take 20% cost and just after this Bookmark Lookup Cost is 60%.
I have to use this column in WHERE to limit result according to user provided input, but can’t afford the long execution time.
My looks like:
SELECT DISTINCT
M.idt1, M.nme1, M.nmeP,
R.varC, R.var_CD, O.nme_O
FROM
fact_M M INNER JOIN
dim_R R ON M.sur_id_M = R.sur_id_M INNER JOIN
fact_I I ON M.sur_id_M = I.sur_id_M LEFT OUTER JOIN
dim_O O ON M.dim_id_O = O.dim_id_O
WHERE
(R.ind_P = 1)
AND (M.idt_P = @varP OR @varPIC IS NULL)
AND (M.var_ Status = @varStatus OR @varStatus IS NULL)
AND (I.dte_A BETWEEN @varBegin_Date AND @varEnd_Date)
ORDER BY
R.varC, M.idt1
“M.idt_P” has Non-Cluster Index on it, if I remove this from WHERE, query runs fine, otherwise takes 5 times longer. I have tried couple of options but no use.
Please let me know what Am I doing wrong? Or how can I make it faster?
Thanks
Check the colunm which in where condition has how many distincat value..if more then create index
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply