Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
srikanth1457
Starting Member
6 Posts |
 Posted - 2008-11-26 : 07:20:16
|
| Hi,I have a select statement with 20 joins.I execute the statement it is taking 45 mins.If i comment some of the fields for particular table in the select statements it is executed within secons.i am not able to find out the error.where should i modify in the query?.please help me in this.Thank you,srikanth |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-26 : 07:36:15
|
Please post the execution plan.It seems you are lacking a proper index, and the execution plans show some kind of bookmark lookup.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
srikanth1457
Starting Member
6 Posts |
Posted - 2008-11-26 : 09:07:42
|
| Thank you, I am not able to copy the execution plan it is disabled.Execution plan shows like this(part).I suspect the problem is here but not able to find out the solution.please help me in this.HashMatch <-------Bookmark Lookup <------------------ Index scan (inner join)| [mppdm].[dbo].[tblchangemaster][mppdm].[dbo][tblchangemaster].tblchangemaster93] cost:17% | cost:2% cost:11% | |<-----Hash Match <-------------Index scan (Aggregate) [mppdm].[dbo][tblchangemaster].tblchangemaster93] cost:47% cost:11% |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-26 : 09:17:51
|
| You can attach your plan as a screenshot if you want to. |
|
|
|
srikanth1457
Starting Member
6 Posts |
Posted - 2008-11-26 : 11:35:56
|
| I am not able to attach the screenshot.In the execution plan everything cost is 0% except the below.tblchangemaster93 is an non-clustered index with 4 columns.do i need to change this index.your swift response to this request would be greately appreciated.HashMatch <-------Bookmark Lookup <------------------ Index scan (inner join)| [mppdm].[dbo].[tblchangemaster][mppdm].[dbo][tblchangemaster].[tblchangemaster93] cost:17% | cost:2% cost:11% | |<-----Hash Match <-------------Index scan (Aggregate) [mppdm].[dbo][tblchangemaster].[tblchangemaster93] cost:47% cost:11% |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 11:43:52
|
| Create Covering index on search fields. |
|
|
|
srikanth1457
Starting Member
6 Posts |
Posted - 2008-11-27 : 07:08:18
|
| Hi Sodeep,Could you please let me know how to create covering index on search fields.Thank you,Regards,Srikanth |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-27 : 07:22:42
|
| Create a composite index, including all columns referenced in SELECT, JOIN, and WHERE clauses of your query. This is what he means by covering indexes. This will make the query just look on indexes without actually looking through the actual data pages. It will help if you can identify that your query is not performing good because of bookmark lookups. Also, Creating covering indexes can sometimes degrade performance if you have lots of columns as a part of index. More on tthe same..http://www.sql-server-performance.com/tips/covering_indexes_p1.aspx |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-27 : 10:10:17
|
| Show us code and associated index on columns so we can assist. You need Covering index to avoid bookmark lookup. Is this high OLTP table? |
|
|
|
srikanth1457
Starting Member
6 Posts |
Posted - 2008-12-01 : 06:14:48
|
| Thank you for the information.Please see the code and help me in this to where exactly i need to create a covering index.If i commented the fields(done this in the select statemnt) it is executed quickly within 30 secs otherwise taking 45 minutes.SELECT DISTINCT [DepictionID] = d.[ID], [DepictionParentID] = d.[intParentID], [LevelID] = d.[intBranchLevel], [Level] = replicate('.',(d.[intBranchLevel]-1))+CAST(d.[intBranchLevel] AS VARCHAR), [StatusID] = d.[intStatusID], [DepictionNumber] = d.[varDocumentNumber] , [ProjectID] = d.[intProjectID] , [ProjectCode] = prj.[varCode], [ProjectDescription] = prj.[varDescription], [ProjectPhaseID] = lpp.[ID], [PhaseCode] = ph.[varCode], [PhaseDescription] = ph.[varDescription], [FunctionCodeID] = d.[intFunctionCodeID] , [FunctionCode] = fc.[varCode] , [FunctionDescription] = fc.[varDescription] , [ProjectAreaID] = d.[intProjectAreaID] , [ProjectAreaCode] = pa.[varCode], [ProjectAreaDescription] = pa.[varDescription], [EngineerID] = d.[intEngineerID] , [EngineerName] = eng.[varFirstName] + ' ' + eng.[varLastName], [DesignerID] = d.[intDesignerID] , [DesignerName] = des.[varFirstName] + ' ' + des.[varLastName], [Quantity] = d.[dblQuantity] , [QuantityFactored] = d.[dblFactoredQuantity], [ServiceItem] = d.[blnServiceItem] , [PackagingCheck] = d.[blnPackagingCheck] , [DatePackagingCheck] = d.[dtePackagingCheck] , [DatePlannedRelease] = d.[dtePlannedRelease] , [DepictionComments] = d.[varComments] , [DateDepictionCreated] = d.[dteCreated], [DateDepictionLastUpdated] = d.[dteLastUpdated], [DepictionLastUpdatedBy] = d.[varLastUpdatedBy], [Part] = p.[varPartNumber], [PartRevision] = pr.[varPartNoMajorRevision] + CAST(pr.[tintPartNoMinorRevision] AS VARCHAR(50)), [PartNumber] = pr.[varPartRevisionNumber] + '(' + sfx.varSuffixCode + ')', [PartDescription] = pr.[varPartDescription], [PartSource] = ps.[varDescription], [PartSourcedBy] = sbc.[varDescription], [UnitOfMeasure] = uom.[varCode], [OptionCodeID] = oc.[ID], [OptionCode] = oc.[varCode], [OptionDescription] = oc.[varDescription], [VariantID] = COALESCE(vr.[ID], 0), [VariantCode] = COALESCE(vr.[varCode], 'N/A'), [VariantDescription] = COALESCE(vr.[varDescription], 'N/A'), [ChangeMasterID] = COALESCE(cob.[ID], 0)--, [ChangeMasterNumber] = COALESCE(cob.[varNumber], 'N/A')--, [ChangeMasterStatusID] = COALESCE(cob.[intStatusID], 1)--, [PCOID] = COALESCE(pco.[ID], 0)--, [PCONumber] = COALESCE(pco.[varNumber], 'N/A')--, [PCODescription] = COALESCE(pco.[varDescription], 'N/A')--, [PCOStatusID] = COALESCE(pco.[intStatusID], 1)--, [PCOStatus] = COALESCE(pcost.[varStatus], 'N/A'), [BuyerID] = lps.[intBuyerID] , [BuyerName] = buy.[varFirstName] + ' ' + buy.[varLastName], [Supplier] = supp.[varDescription]FROM tblDepiction d INNER JOIN lnk_Depiction_Part lnk ON d.[ID] = lnk.[intDepictionID] AND lnk.[blnInactive] = 0 INNER JOIN tblPartRevision pr1 ON lnk.[intPartID] = pr1.[ID] INNER JOIN tblPart p ON pr1.[intPartID] = p.[ID] INNER JOIN tblPartRevision pr ON p.[ID] = pr.[intPartID] INNER JOIN lkpSuffix sfx ON sfx.ID = d.tintPartNoSuffixID INNER JOIN lkpPartSource ps ON sfx.[tintPartSourceID] = ps.[ID] INNER JOIN lkpSourcedByCode sbc ON pr.[intSourcedByID] = sbc.[ID] INNER JOIN lkpUnitOfMeasure UOM ON pr.tintUnitOfMeasureID = UOM.[ID] INNER JOIN lkpProject prj ON d.[intProjectID] = prj.[ID] INNER JOIN lnk_Depiction_Phase ldp ON d.[ID] = ldp.[intDepictionID] INNER JOIN lnk_Project_Phase lpp ON ldp.[intPhaseID] = lpp.[ID] INNER JOIN lkpPhase ph ON lpp.[intPhaseID] = ph.[ID] INNER JOIN lnk_Project_ProjectArea lppa ON d.[intProjectAreaID] = lppa.[ID] INNER JOIN lnk_Project_FunctionCode lpfc ON d.[intFunctionCodeID] = lpfc.[ID] INNER JOIN lkpFunctionCode fc ON lpfc.[intFunctionCodeID] = fc.[ID] INNER JOIN lkpProjectArea pa ON lppa.[intProjectAreaID] = pa.[ID] INNER JOIN tblUser eng ON d.[intEngineerID] = eng.[ID] INNER JOIN tblUser des ON d.[intDesignerID] = des.[ID] INNER JOIN lnk_Depiction_OptionCode ldoc ON d.[ID] = ldoc.[intDepictionID] INNER JOIN lkpOptionCode oc ON ldoc.[intOptionCodeID] = oc.[ID] LEFT JOIN lnk_Variant_OptionCode voc ON oc.[ID] = voc.[intOptionCodeID] LEFT JOIN lkpVariant vr ON voc.[intVariantID] = vr.[ID] LEFT JOIN (SELECT * FROM tblChangeMaster WHERE ID IN (SELECT MAX(ID) FROM tblChangeMaster WHERE intStatusID <> 7 GROUP BY intUsageID)) AS cob ON cob.intUsageID = d.ID LEFT JOIN lnk_PCO_ChangeRecord lpcocr ON cob.[intChangeMasterID] = lpcocr.[intChangeMasterID] LEFT JOIN tblPCO pco ON lpcocr.[intPCOID] = pco.[ID] LEFT JOIN lkpPCOStatus pcost ON pco.[intStatusID] = pcost.[ID] LEFT JOIN lnk_Part_Supplier lps ON pr.[ID] = lps.[intPartID] AND lps.[blnInactive] = 0 AND lps.[ID] = (SELECT MAX([ID]) FROM lnk_Part_Supplier WHERE intPartID = pr.[ID] AND blnInactive = 0) LEFT JOIN tblUser buy ON lps.[intBuyerID] = buy.[ID] LEFT JOIN lkpSupplier supp ON lps.[intSupplierID] = supp.[ID] LEFT JOIN tblPartCost cost ON lps.[ID] = cost.[intPartSupplierID] AND cost.[blnInactive] = 0 AND (cost.[intProjectID] = d.[intProjectID] OR cost.[intProjectID] = 1) AND (cost.[intPhaseID] = ldp.[intPhaseID] OR cost.[intPhaseID] = 1) AND (lps.ID IN (SELECT intPartSupplierID FROM tblPartCost WHERE intexpendituretypeid IN (2,3))) LEFT JOIN lkpPartExpenditureType pet ON cost.[intExpenditureTypeID] = pet.[ID] LEFT JOIN lkpPartCostType pct ON cost.[intCostTypeID] = pct.[ID] LEFT JOIN lkpPartCostStatus pcs ON cost.[intCostStatusID] = pcs.[ID] LEFT JOIN lkpCurrency curr ON cost.[intCurrencyID] = curr.[ID] LEFT JOIN lkpUnitOfMeasure coum ON cost.[intCostUOMBaseID] = coum.[ID] LEFT JOIN lkpUnitOfMeasure boum ON cost.[intUnitOfMeasureID] = boum.[ID] WHERE d.[intProjectID] = 46 AND ldp.[intPhaseID] = 36 AND vr.[ID] = 25 AND d.[blnInActive] = 0 ORDER BY p.[varPartNumber], d.[varDocumentNumber], pr.[varPartNoMajorRevision] + CAST(pr.[tintPartNoMinorRevision] AS VARCHAR(50)) |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-01 : 17:23:55
|
| Can't really say by looking at this coz i dunno about indexing strategies in your table? Can you put this code in Database Tuning Wizard and see what it recommends?I am sure it recommends Covering index. Test it before you apply it. |
|
|
|
srikanth1457
Starting Member
6 Posts |
Posted - 2008-12-02 : 06:55:29
|
| Thank you for the information.for table tblchangeMaster i have 6 indexes.In the execution plan showing tblchangeMaster93 index is taking more time to execute.It is a non-clustered index with four columns(ID,intUsageID,intStatusId,dteaLstUpdated).shall i check rebuild the index(in the options tab of the index properties) will it work or what should i do.please help me in this. |
|
|
|
|
|
|
|
|
performance of SQL query