Stored Procedure - Tuning and Optimization

Last Post 18 Sep 2010 03:14 PM by rm. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

14 Mar 2010 03:41 PM

I have created a stored procedure which does following tasks.
1. It reads the main table data and creates a temporary table and po***tes with data based on certain criteria
2. Again it po***tes data into certain columns of the main table by using this temporary table data

Here i am using Select statement to create the temp table and using while loop to po***te the main table. Here creation of temporary table takes more time rather than po***ting the main table.Is there any way i can tune in my procedure by following some other method which will speed up stored procedure execution time.Here you go for the piece of code which i am using.

A. Tables used:
REAS_CBSA- Main Table which is the fact table which has the data in it
REAS_Dim_Periods - Dimension table which has the month information in it
CBSA_Temp1 - Is the temparory table created

B. Agenda: i want to po***te 1 month ago values for the column HPI_1m_ago for the the current data period by refering dimension table with respective to all the periods which we have from January 1976 to till date on month basis.Here month refers to period.

C. To create temporary table:
Set @SQL_CBSA_1m_Ago=('select a11.tier_code tier_code, a12.period_no period_no, a11.CBSA_code CBSA_code, max(a11.home_price_index) HPI_m_ago
into CBSA_Temp1 from REAS_CBSA a11 join REAS_Dim_Periods a12 on (a11.period_no = (a12.period_no - 1))
group by a11.tier_code, a12.period_no, a11.CBSA_code')
Exec (@SQL_CBSA_1m_Ago)

D. To update main table(variables used and the while loop)

a. Variables used
Declare @SQL_CBSA_1m_Ago nvarchar(1000)
Declare @Period_wise_update_CBSA table ( si_no smallint identity , period_no smallint)
INSERT INTO @Period_wise_update_CBSA
select distinct period_no from REAS_CBSA
declare @period_no_max_CBSA smallint
declare @period_no_min_CBSA smallint
select @period_no_max_CBSA = max(si_no) from @Period_wise_update_CBSA
--select @period_no_min_CBSA = MIN(si_no) from @Period_wise_update_CBSA
declare @period_no smallint

b. While loop

SET @period_no_min_CBSA = 1
while @period_no_min_CBSA <= @period_no_max_CBSA
Update A
set HPI_1m_ago = B.hpi_m_ago
Join CBSA_Temp1 B on A.period_no=B.period_no and A.tier_code=B.tier_code and A.CBSA_code=B.CBSA_code
and A.period_no = @period_no
select @period_no = period_no from @Period_wise_update_CBSA where si_no = @period_no_min_CBSA
select @period_no_min_CBSA = @period_no_min_CBSA + 1

Problem Facing: The creation of temporary table and po***tion of main table is taking very long time which is not acceptable for me due to time constraint which we do in the regular data update. If someone help me out in getting the solution on this, i would be very gratefull to them.

Thanks in advance,

New Member
New Member

14 Mar 2010 04:56 PM
Well you have a lot going on there and its hard to give an extensive answer without know a lot more about the tables, data etc. But I would suggest tracing the operation at the statement level so you can see exactly which statements are giving you the most trouble and concentrate on them. I am not sure why you have the temp table po***teion wrapped in dynamic sql. Having said that if the po***tion of th etemp table is taking a long time I suspect the underlying tables are not properly indexed to support the query. If you look at Statistics io or the graphical query plan you can easily confirm that. The same is true for the update of the main table. At the very least you should have an index on the temp table to support the join but you may also need additional index on the other table you are joiing to.
New Member
New Member

17 Sep 2010 09:34 AM

Gunney, thanks for your reply. I am coming back after six months. I was into some other project and could not look into your soln provided. Sorry about that. Now i am back in this project and need to find the soln as soon as possible. I hope you guys will help me in this regard.

You are right, i made the code so robust unnecessarily. Now i found a soln where in i need not to create temp tables for updating the values. I can directly update the table by just using update statement. But i have to create the index on this table in order to further optimize the procedure.

I have to create the index before i start my update process and drop the same index after the update process is over.

I need syntax for both creating and dropping the index as a dynamic SQL statements inside a procedure.

Please help me on this.

Vijay (Awaiting for your reply)

New Member
New Member

17 Sep 2010 10:35 AM
Well I am not sure what you are looking for. The create index syntax is well documented with examples in BooksOnLine. And why do you need to use dynamic sql to create the index in the first place? Is there something specific to the creation of the index that you need help with that isn't answered in the help files?
New Member
New Member

18 Sep 2010 04:17 AM

well, i know the syntax which can be used for creating the index. But when we are using it in Stored procedure, the syntax slightly differs. I want that syntax.
to answer second part, the index(Unique) what i am creating should be usefull for updating the table. Because i am going to perform insert operation after the update. That time my indexes sheould not give any trouble. Hence i will drop the index created on the table.

Hope you got some idea, what i am doing here.

New Member
New Member

18 Sep 2010 03:14 PM
Syntax of index creation is same, you just need to put it into dynamic sql. Saomthing like:

declare @col sysname, @tab sysname, @ind sysname, @sql varchar (500)
set @col = column name
set @tab = table name
set @ind + index name
set @sql = 'created index ' + @ind + ' on ' + @tab + ' (' = @col + ')'
exec (@sql)

Acceptable Use Policy