September 24, 2013 at 2:25 am
Hi,
I have a query which takes more than 2 min whenver it runs.
Is there any other way to wrtie this query so that performance can be improved
SELECT A.OFFICEID,A.PROJECTID,A.PROPOSALID,A.SOLUTIONID,A.UNITID,A.PRICEITEMID,A.ESTIMATIONGROUPID,A.SRNO,A.PRICEITEMNAME,A.LOCALPURCHASETYPEID,A.PRI_PRICE,
A.Sec_Price,A.PRI_PRICEWOR,A.SEC_PRICEWOR,A.PRIMARYCURRENCYID,A.SECONDARYCURRENCYID,A.DELETEFLAG,A.DATEADDED,A.ADDEDBY,A.DateChanged,A.ChangedBy,A.Quantity,
A.SrNoDetail,A.Pri_PriceWM,A.Sec_PriceWM ,A.Code,A.MOrIType
FROM ESTIMATIONOUTPUTPRICE A WITH (NOLOCK),VW_ESTIMATIONOUTPUTPRICE_ETL_COMPANY B WITH (NOLOCK), UNIT C WITH (NOLOCK)
WHERE A.OFFICEID = B.OFFICEID and
A.PROJECTID = B.PROJECTID and
A.PROPOSALID = B.PROPOSALID and
A.SOLUTIONID = B.SOLUTIONID and
A.UNITID = B.UNITID and
B.OFFICEID = C.OFFICEID and
B.PROJECTID = C.PROJECTID and
B.PROPOSALID = C.PROPOSALID and
B.SOLUTIONID = C.SOLUTIONID and
B.UNITID = C.UNITID and
C.ISARCHIVED=0
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 24, 2013 at 2:26 am
I thought due to cross join performance issue is there so I rewrite the query in this manner:
SELECT
A.OFFICEID,
A.PROJECTID,A.PROPOSALID,A.SOLUTIONID,A.UNITID,A.PRICEITEMID,A.ESTIMATIONGROUPID,A.SRNO,A.PRICEITEMNAME,A.LOCALPURCHASETYPEID,A.PRI_PRICE,
A.Sec_Price,A.PRI_PRICEWOR,A.SEC_PRICEWOR,A.PRIMARYCURRENCYID,A.SECONDARYCURRENCYID,A.DELETEFLAG,A.DATEADDED,A.ADDEDBY,A.DateChanged,A.ChangedBy,A.Quantity,
A.SrNoDetail,A.Pri_PriceWM,A.Sec_PriceWM ,A.Code,A.MOrIType
FROM ESTIMATIONOUTPUTPRICE A WITH (NOLOCK)
inner JOIN VW_ESTIMATIONOUTPUTPRICE_ETL_COMPANY B WITH (NOLOCK) ON
A.OFFICEID = B.OFFICEID and
A.PROJECTID = B.PROJECTID and
A.PROPOSALID = B.PROPOSALID and
A.SOLUTIONID = B.SOLUTIONID and
A.UNITID = B.UNITID
INNER JOIN UNIT C WITH (NOLOCK)
ON B.OFFICEID = C.OFFICEID and
B.PROJECTID = C.PROJECTID and
B.PROPOSALID = C.PROPOSALID and
B.SOLUTIONID = C.SOLUTIONID and
B.UNITID = C.UNITID
WHERE C.ISARCHIVED=0
But still no improvement in performance
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 24, 2013 at 2:45 am
BrainDonor (9/24/2013)
Could we see the definitions of the tables involved?
Sorry I cant disclosed that but the columns using in join all have index on them
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 24, 2013 at 4:04 am
Please post table definition, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/. At absolute minimum, we need the execution plan. There's no way to guess query performance without. Those 'indexes on each column' could be completely useless for all we know.
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
September 24, 2013 at 7:15 am
Just piling on, but, no execution plan, we're just making crazy guesses that will likely have absolutely no bearing on reality.
Oh, and no_lock... that is just such a dangerous crutch to use. Please read up on it.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply