SQL Server Performance Forum – Threads Archive

Need a biggg help optimizing Stored procedure.

People – I have a question for you regarding optimizing an existing Stored procedure for me. This will be a long description which covers the business need and required dataset for me. Please take a little time and help me with this. Business Requirement
——————–
This stored procedure will get an XML file which has some search criteria and the stored procedure is expected to do some joins depending on the criteria and return the dataset. The new requirement is to see if the existing stored procedure can be optimized further. See if changing the table structure or modifying the stored procedure will improve performance or not. I’m sorry for really big chunk of code and table design i’m posting, but i felt this would help you guys to figure out what exactly happening.
Description of Tables used in Stored procedure
———————————————-
The only static table used in this stored procedure is MISSDenormalized and the rest of the tables local to Stored procedure. These tables are created based on the search criteria that is passed into the Stored procedure. Design of MISSDenormalized table
——————————– SupplierIntegrationID
ParentAccountName
RankQuadrant
HighBidInc
LowBidInc
SupplierBidInc
EstSpend
CBEIntegrationID
CBENumber
LotNumber
EventDate
LotItemID
UNSPSCCode
AttributeID
AttributeValue
ExcludeSupplierFromCBE
ExcludeSupplierFromAll
ExcludeCBE
SupplierIDTagExcpt
DestLat
DestLon
PenaltyStatusID
PenaltyStatus
AccountName
AddressLine1
AddressLine2
AddressLine3
City
State
Country
CountryID
Region
RegionID
Zip
AnnualRevenue
AnnualRevenueID
ISOStatusID
ISOStatus
QSStatusID
QSStatus
NumEmployees
PrimaryLanguage
SubLotInterest
SubBid
SubCostBreakdown
Incumbent
AwardedBus The description for other tables are specified in stored procedure which i will post the bottom. Working of Stored Procedure
————————— To drill down, the three main columns are UNSPSCCODE, AttributeID and AttributeValue. Sample Test Criteria
——————–
<SearchCriteria>
<Codes>
<UNSPSCCode>31161600</UNSPSCCode>
<UNSPSCCode>31161619</UNSPSCCode>
<UNSPSCCode>31161620</UNSPSCCode>
<UNSPSCCode>31160000</UNSPSCCode>
</Codes>
<SupplierProfile>
<CountryIDs>
<CountryID>173</CountryID>
<CountryText ID="173"><![CDATA[China]]></CountryText>
</CountryIDs>
<ISOCerts>
<ISOCertID>414</ISOCertID>
<ISOCertText ID="414"><![CDATA[ISO 9001 Certified]]></ISOCertText>
</ISOCerts>
<RevenueValues></RevenueValues>
</SupplierProfile>
<Attributes>
<Attribute>
<ID>25</ID>
<Value><![CDATA[Carbon Steel]]></Value>
<Required>0</Required>
<MinLat>-1000</MinLat>
<MaxLat>1000</MaxLat>
<MinLon>-1000</MinLon>
<MaxLon>1000</MaxLon>
</Attribute> </Attributes>
<AttributeCount>1</AttributeCount>
</SearchCriteria> Now, the stored procedure will load the values into the temp table VARIABLES local to stored procedure and will join those table variables with MIDD_Denormalized table to get the DataSet.
HOW TO OPTIMIZE.
————— Since, the stored procedure is very big in size, i added this in Yahoo briefcase and made it PUBLIC so that everyone can look at the stored procedure. CAN YOU PLEASE LOOK INTO THE STORED PROCEDURE AND TELL ME HOW WE CAN INCREASE THE PERFORMANCE OF THE STORED PROCEDURE? There is a while loop used to fetch the dataset when there are more than one <Attributes> node in the search criteria. And my team lead feels, if some how the while loop is skipped the there will be an increase in performance. But i’m just out of ideas. IF ANYONE CAN JUST TAKE A LOOK AT STORED PROCEDURE AND THROW IN SOME IDEA, I WILL WORK ON THAT. Link to file which has Stored procedure —>http://us.f1f.yahoofs.com/bc/2abd67d3/bc/SQL/StoredProc.sql?bf6qSEBBMQw_OeDW
Link to Sample Data (Only three fields) –>http://us.f1f.yahoofs.com/bc/2abd67d3/bc/SQL/SampleData.txt?bf6qSEBBxImhs7Kw I know that the information that i gave above is not enough toorrectly understand, So please shoot me a reply back and will reply to you guys immediately. Thanks,

If you need to improve performance with new indexs, use Query Analyzer and see execution plan.
If you need to improve performance with smart codification, I suggest try using SQL Server Tuning version 3.3 (Trial) from Quest Central. HTH Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks Martin….Will try that…If anyone has anymore ideas, please let me know.

http://www.sql-server-performance.com/stored_procedures.asp
http://vyaskn.tripod.com/analyzing_profiler_output.htm
… artciles reference to fine tune the issue. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>