Need a biggg help optimizing Stored procedure.

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need a biggg help optimizing Stored procedure.

Author

Topic

achamarthy
Starting Member

26 Posts

Posted - 2004-08-04 : 14:26:33

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 to correctly understand, So please shoot me a reply back and will
reply to you guys immediately.

Thanks,

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-08-04 : 14:38:50

Please post some CREATE TABLE and INSERT INTO statments that we can work with.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-04 : 14:39:36

And some links that work.

Tara

achamarthy
Starting Member

26 Posts

Posted - 2004-08-04 : 14:48:17

Is there any place where i can save files for public sharing?

achamarthy
Starting Member

26 Posts

Posted - 2004-08-04 : 15:29:54

Luckily my college account still works..anyway the links

I took some data and exported thru DTS to output.txt which is comma delimited...There are no insert statements becasue that table itself is loaded from miliion different places...

http://www.ent.ohiou.edu/~anil/files/StoredProc.txt
http://www.ent.ohiou.edu/~anil/files/SampleData.txt
http://www.ent.ohiou.edu/~anil/files/createand.txt
http://www.ent.ohiou.edu/~anil/files/output.txt

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 15:39:41

Wow...how many calendar days does it take to run?

Brett

8-)

achamarthy
Starting Member

26 Posts

Posted - 2004-08-04 : 15:41:57

lol...the table size if 1.5 Million and for a search criteria which has 4 Attributes, the response time for SP is 5 Secs and the requirement is to further optimize it.

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 15:49:43

Really? I'm suprised....

What I would do...is start Profiler and look where things "slow down"

I mean look at this


inner join
	@SupplierProfile as SupplierProfile on 
	COALESCE(PS.EventDate,-1) >= COALESCE(SupplierProfile.EventDate,COALESCE(PS.EventDate,-1)) 
	inner join			
	@SupplierRevenue as SupplierRevenue on
	COALESCE(PS.AnnualRevenueID,-1) = COALESCE(SupplierRevenue.RevenueID,COALESCE(PS.AnnualRevenueID,-1))
	inner join			
	@SupplierQSCerts as SupplierQSCerts on
	COALESCE(PS.QSStatusID,-1) = COALESCE(SupplierQSCerts.QSCertID,COALESCE(PS.QSStatusID,-1))
	inner join			
	@SupplierISOCerts as SupplierISOCerts on
	COALESCE(PS.ISOStatusID,-1) = COALESCE(SupplierISOCerts.ISOCertID,COALESCE(PS.ISOStatusID,-1))									
	inner join			
	@SupplierRegions as SupplierRegions on
	COALESCE(PS.RegionID,-1) = COALESCE(SupplierRegions.RegionID,COALESCE(PS.RegionID,-1))									
	inner join			
	@SupplierCountries as SupplierCountries on
	COALESCE(PS.CountryID,-1) = COALESCE(SupplierCountries.CountryID,COALESCE(PS.CountryID,-1))

those are all scans....must not be a lot of data in those table variables...

And I notice there are a lot of existance checks....

Did you inherit this or did you write it?

Brett

8-)

achamarthy
Starting Member

26 Posts

Posted - 2004-08-04 : 16:02:51

This is a stored proc written by third party and now im optimizing it. secondly, some of the business requirement and DB design are forcing me NOT to drop those innerjoins and COALESCE's...

My main aim is to somehow make the stored proc not to have the while condition.
while @counter < @AttributeCount
begin
set @counter = @counter + 1
set @Xpath = '/SearchCriteria/Attributes[' + cast(@counter as char(1)) + ']/Attribute'
.
.
.
etc..

This while loop will run when we have multiple <Attributes> in the search criteria...http://www.ent.ohiou.edu/~anil/files/multiple.txt

Any ideas as to how to skip that while loop while not worsening the Performance?

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 16:15:38

Makes no sense...the only thing in the loop that's change is @Counter

What is @xpath doing here? Also it's char(1), any number over 9 will get truncated.


	set @counter = @counter + 1
	set @Xpath = '/SearchCriteria/Attributes[' + cast(@counter as char(1)) + ']/Attribute'

	
	delete @ATTRIBUTESTABLE
	insert 	@ATTRIBUTESTABLE
	  (AttributeID ,AttributeValue,RequiredFlag, MinLat, MaxLat, MinLon, MaxLon)
	  SELECT top 10 AttributeID , AttributeValue, RequiredFlag, MinLat, MaxLat, MinLon, MaxLon
	  FROM
	  OPENXML (@idoc, @Xpath,2)
		WITH (	AttributeID  int './ID',
			AttributeValue varchar(255) './Value',
			RequiredFlag varchar(255) './Required',
			MinLat float './MinLat',
			MaxLat float './MaxLat',
			MinLon float './MinLon',
			MaxLon float './MaxLon')

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 16:18:30

Did I mention I hate XML

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-08-04 : 16:36:00

quote:
Originally posted by X002548

What is @xpath doing here?

xpath is specifying the particular element path: ie: /SearchCriteria/Attributes[1]/Attribute will find the 1st element, named "Attribute", in the document. So the code is evaluating each "Attribute" element seperately.

Without fully understanding the biz rules-- if you want to get rid of the loop don't call out the xpath explicitly (ie.

/SearchCriteria/Attributes[n]/Attribute

Just make it something like:

SELECT AttributeID , AttributeValue, RequiredFlag, MinLat, MaxLat, MinLon, MaxLon
FROM
OPENXML (@idoc, '/SearchCriteria/Attributes/Attribute',2)
WITH (	AttributeID  int './ID',
	AttributeValue varchar(255) './Value',
	RequiredFlag varchar(255) './Required',
	MinLat float './MinLat',
	MaxLat float './MaxLat',
	MinLon float './MinLon',
	MaxLon float './MaxLon')

which will return all the "Attribute" elements in the doc which can then be joined in your insert statements.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources