Optimizing store proc

I have the following store proc and was wondering if I can optimized it by using a SELECT CASE instead of all those IF? I tried but don't know how to write it.

Thanks

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[Get_Cl_SearchMultiColumn]( @.strSearchTermColumnNamenvarchar (50),@.strSearchTermSearchTermnvarchar (200) )as if (@.strSearchTermColumnName ='Monitor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2FROM Cl_SystemsWHERE contains(Monitor1,@.strSearchTerm)orcontains(Monitor2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='MonitorSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2SerialFROM Cl_SystemsWHERE contains(Monitor1Serial,@.strSearchTerm)orcontains(Monitor2Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Microscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Microscope1,Microscope2FROM Cl_SystemsWHERE contains(Microscope1,@.strSearchTerm)orcontains(Microscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialMicroscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialMicroscope1,SerialMicroscope2FROM Cl_SystemsWHERE contains(SerialMicroscope1,@.strSearchTerm)orcontains(SerialMicroscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Controller')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Controller1,Controller2FROM Cl_SystemsWHERE contains(Controller1,@.strSearchTerm)orcontains(Controller2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ControllerFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Cont1Firmware,Cont2FirmwareFROM Cl_SystemsWHERE contains(Cont1Firmware,@.strSearchTerm)orcontains(Cont2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialController')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialController1,SerialController2FROM Cl_SystemsWHERE contains(SerialController1,@.strSearchTerm)orcontains(SerialController2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Joystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joystick1,Joystick2FROM Cl_SystemsWHERE contains(Joystick1,@.strSearchTerm)orcontains(Joystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='JoystickFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joy1Firmware,Joy2FirmwareFROM Cl_SystemsWHERE contains(Joy1Firmware,@.strSearchTerm)orcontains(Joy2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialJoystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialJoystick1,SerialJoystick2FROM Cl_SystemsWHERE contains(SerialJoystick1,@.strSearchTerm)orcontains(SerialJoystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Camera')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4FROM Cl_SystemsWHERE contains(Camera1,@.strSearchTerm)orcontains(Camera2,@.strSearchTerm)orcontains(Camera3,@.strSearchTerm)orcontains(Camera4,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='CameraSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1Serial,Camera2Serial,Camera3Serial,Camera4SerialFROM Cl_SystemsWHERE contains(Camera1Serial,@.strSearchTerm)orcontains(Camera2Serial,@.strSearchTerm)orcontains(Camera3Serial,@.strSearchTerm)orcontains(Camera4Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ZMotor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3FROM Cl_SystemsWHERE contains(ZMotor1,@.strSearchTerm)orcontains(ZMotor2,@.strSearchTerm)orcontains(ZMotor3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Stage')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3FROM Cl_SystemsWHERE contains(Stage1,@.strSearchTerm)orcontains(Stage2,@.strSearchTerm)orcontains(Stage3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Lens')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Lens1,Lens2,Lens3FROM Cl_SystemsWHERE contains(Lens1,@.strSearchTerm)orcontains(Lens2,@.strSearchTerm)orcontains(Lens3,@.strSearchTerm)return 0end

I don't know why you need a WHERE clause with CONTAINS predicate but try the link below for CASE statement for an expert. Hope this helps.

http://www.craigsmullins.com/ssu_0899.htm

|||

Well it seems that I am trying to use the CASE in a different situation then what explain everywhere.

I would like to use it one of the parameters sent to my store proc but I am not sure if it is possible and how to write it

Something like:

Select Case @.strSearchTermColumnName

Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end

case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...

End Select

|||

Oups I made a few mistakes in my last statement, I should have been:

Well it seems that I am trying to use the CASE in a different situation than what explained everywhere.

I would like to use it on one of the parameters sent to my store proc but I am not sure if it is possible and how to write it

Something like:

Select Case @.strSearchTerm

Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end

case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...

End Select

|||

What I am saying is I don't know if you can use the CONTAINS predicate with CASE statement. The links below one is a full text expert he knows more about fulltext and the other two are the two versions of CONTAINS. Hope this helps.

http://spaces.msn.com/jtkane/

http://msdn2.microsoft.com/en-US/library/ms189760.aspx

http://msdn2.microsoft.com/en-US/library/ms187787.aspx

sql

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegr oups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>
|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas

"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegr oups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimiser and UDF's

I came across something today that hopefully if my explanation is
wrong someone could correct me.
I was trying to speed up a stored proc that took an integer as a
parameter and then ran around a dozen SQL statements to populate a
table variable.
1st query was :-
select <some columns> from A where A.id = @.id
The others were along those lines but joined to several lookup tables
and other combinations. Basically though it was hitting table A with
the same search conditions each time (id = @.id).
Thinking this was a waste I wrote a UDF that took the id as a
parameter and returned a table with just the relevant records from
table A in it. The SQL in the stored proc was then changed as follows
:-
select <some columns> from myUDF(@.id)
The new stored proc now runs twice as fast, uses half the cpu time and
a third of the disk io.
Would I be right in saying that SQL2k has realised that the UDF had
been called a dozen times with the same parameters, ran it once and
cached the results?
Its the only reason I can see for such a big performance improvement.
cheers.No, UDF results are not cached in SQL 2k, but that might be a nice feature
for Yukon... Sybase, for instance, does cache udf results...If your efforts
lowered the number of joins that were being done, I suspect the improvement
lies there.
"Mike Watson" <mike@.prog99.com> wrote in message
news:q1h7lvoluabdh7ulaigatf1ndn05gaofo8@.4ax.com...
> I came across something today that hopefully if my explanation is
> wrong someone could correct me.
> I was trying to speed up a stored proc that took an integer as a
> parameter and then ran around a dozen SQL statements to populate a
> table variable.
> 1st query was :-
> select <some columns> from A where A.id = @.id
> The others were along those lines but joined to several lookup tables
> and other combinations. Basically though it was hitting table A with
> the same search conditions each time (id = @.id).
> Thinking this was a waste I wrote a UDF that took the id as a
> parameter and returned a table with just the relevant records from
> table A in it. The SQL in the stored proc was then changed as follows
> :-
> select <some columns> from myUDF(@.id)
> The new stored proc now runs twice as fast, uses half the cpu time and
> a third of the disk io.
> Would I be right in saying that SQL2k has realised that the UDF had
> been called a dozen times with the same parameters, ran it once and
> cached the results?
> Its the only reason I can see for such a big performance improvement.
> cheers.
>|||On Tue, 2 Sep 2003 07:45:12 -0400, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>No, UDF results are not cached in SQL 2k, but that might be a nice feature
>for Yukon... Sybase, for instance, does cache udf results...If your efforts
>lowered the number of joins that were being done, I suspect the improvement
>lies there.
>
Thanks for the reply, I'm surprised that the overhead of calling the
UDF around a dozen times is so much cheaper than doing a striaght join
on the table.

Opinions on Option (Keepfixed Plan)

I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
Regards
Hi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards
|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations and
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.
|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan
|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.

> Gert-Jan

Opinions on Option (Keepfixed Plan)

I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
RegardsHi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations and
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> > Hi Frank
> >
> > Have you read these article to determine the cause of the recompilations and
> > address the actual reasons?
> >
> > Troubleshooting stored procedure recompilation
> > http://support.microsoft.com/kb/243586
> >
> > How to identify the cause of recompilation in an SP:Recompile event
> > http://support.microsoft.com/kb/308737
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
>> Kalen Delaney wrote:
>> Hi Frank
>> Have you read these article to determine the cause of the recompilations and
>> address the actual reasons?
>> Troubleshooting stored procedure recompilation
>> http://support.microsoft.com/kb/243586
>> How to identify the cause of recompilation in an SP:Recompile event
>> http://support.microsoft.com/kb/308737
>> Yep, I did all these. I decreased SP:Recompile events in Profiler
>> substantially, however, they were still happening quite a bit.
>> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
>> (anecdotally) increased the performance of the stored proc. In Perfmon,
>> however, I still get a large number for the SQL Statistic/SQL
>> Compilations. It must mean something else than what is reflected by the
>> SP:Recompile event in Profiler.
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.
> Gert-Jan

Opinions on Option (Keepfixed Plan)

I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
RegardsHi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations a
nd
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.

> Gert-Jan

operations through stored procedures

i write a following stored procedure

create proc prc_BUSINESS_MASTER
(
@.chByMode Char(1),
@.intLIST_ID int=null,
@.intLISTBUSINESSID int=null,
@.dtmDATE datetime=null,
@.dtmEXPDATE datetime=null,
@.vchSTATUS varchar(10)=null,
@.vchOFFICE varchar(100)=null,
@.numCITY numeric =null,
@.numCOUNTY numeric=null,
@.numSTATE numeric=null,
@.numCOUNTRY numeric=null,
@.vchSICCODE varchar(50)=null,
@.numASKINGPRICE numeric =null,
@.numDOWNPRICE numeric=null,
@.bitOWNERFINANCE bit=null,
@.numOWNERFINANCE numeric=null,
@.numADJ_NETAMOUNT numeric=null,
@.numSALESAMOUNT numeric=null,
@.dtmYOE datetime=null,
@.dtmYRSOWNED datetime=null,
@.bitBITISACTIVE bit=null,
@.vchCREATEDBY varchar(50)=null,
@.dtmCREATEDDATE datetime=null,
@.vchUPDATEDBY varchar(50)=null,
@.dtmUPDATEDDATE datetime=null

)

as

set nocount on
declare @.byIntErrDescOut int
declare @.interrno int
declare @.intdupchk int
begin

IF @.chByMode NOT IN ('I','U')
BEGIN
SET @.byIntErrDescOut = 1
Return -1
END
SET @.byIntErrDescOut = 0
/* *************** Insertion Area *************** */

IF @.chByMode = 'I'
BEGIN

IF @.vchSTATUS IS NULL OR @.vchSTATUS = ''
BEGIN
SET @.byIntErrDescOut = 1
Return -1
END

/***** Duplicate checking ****/

SELECT @.intDupChk = COUNT(1) FROM BUSINESSMASTER WHEREvchLIST_STATUS=@.vchSTATUS AndvchLIST_OFFICE=@.vchOFFICE
IF @.intDupChk <> 0
BEGIN
SET @.byintErrDescOut = 5
Return -1
END

insert into BUSINESSMASTER
(

intLIST_ID,
dtmLIST_DATE,
dtmLIST_EXPDATE,
vchLIST_STATUS,
vchLIST_OFFICE,
numLIST_CITY,
numLIST_COUNTY,
numLIST_STATE,
numLIST_COUNTRY,
vchLIST_SICCODE,
numLIST_ASKINGPRICE,
numLIST_DOWNPRICE,
bitLIST_OWNERFINANCE,
numLIST_OWNERFINACE,
numLIST_ADJ_NETAMOUNT,
numLIST_SALESAMOUNT,
dtmLIST_YOE,
dtmLIST_YRSOWNED,
bitBITISACTIVE,
vchCREATEDBY,
dtmCREATEDDATE,
vchUPDATEDBY,
dtmUPDATEDDATE

)
values
(
@.intLIST_ID,
@.dtmDATE,
@.dtmEXPDATE,
@.vchSTATUS,
@.vchOFFICE,
@.numCITY,
@.numCOUNTY,
@.numSTATE,
@.numCOUNTRY,
@.vchSICCODE,
@.numASKINGPRICE,
@.numDOWNPRICE,
@.numOWNERFINANCE,
@.bitOWNERFINANCE,
@.numADJ_NETAMOUNT,
@.numSALESAMOUNT,
@.dtmYOE,
@.dtmYRSOWNED,
@.bitBITISACTIVE,
@.vchCREATEDBY,
@.dtmCREATEDDATE,
@.vchUPDATEDBY,
@.dtmUPDATEDDATE

)

SET @.intErrNo = @.@.Error
IF (@.intErrNo <> 0)
BEGIN
SET @.byIntErrDescOut = 2
Return -1
END

/*Insertion Area End*/

/*---Updation----*/

/*
IF @.chbyMode = 'U'
BEGIN
--IF @.intBuyerId IS NULL
BEGIN
SET @.byIntErrDescOut = 1
Return -1
END

*/

IF (EXISTS (SELECT * FROM BUSINESSMASTER
WHERE intLISTBUSINESSID = @.intLISTBUSINESSID ))
BEGIN

/***** Duplicate checking ****/

SELECT @.intDupChk = COUNT(1) FROM BUSINESSMASTER
WHERE intLISTBUSINESSID = @.intLISTBUSINESSID andvchLIST_STATUS=@.vchSTATUS
IF @.intDupChk <> 0
BEGIN
SET @.byintErrDescOut = 5
Return -1
END

UPDATE BUSINESSMASTER
SET
intLIST_ID=@.intLIST_ID,
dtmLIST_DATE=@.dtmDATE,
dtmLIST_EXPDATE=@.dtmEXPDATE,
vchLIST_STATUS=@.vchSTATUS,
vchLIST_OFFICE=@.vchOFFICE,
numLIST_CITY=@.numCITY,
numLIST_COUNTY=@.numCOUNTY,
numLIST_STATE=@.numSTATE,
numLIST_COUNTRY=@.numCOUNTRY,
vchLIST_SICCODE=@.vchSICCODE,
numLIST_ASKINGPRICE=@.numASKINGPRICE,
numLIST_DOWNPRICE=@.numDOWNPRICE,
bitLIST_OWNERFINANCE=@.bitOWNERFINANCE,
numLIST_OWNERFINACE=@.numOWNERFINANCE,
numLIST_ADJ_NETAMOUNT=@.numADJ_NETAMOUNT,
numLIST_SALESAMOUNT=@.numSALESAMOUNT,
dtmLIST_YOE=@.dtmYOE,
dtmLIST_YRSOWNED=@.dtmYRSOWNED,
bitBITISACTIVE=@.bitBITISACTIVE,
vchCREATEDBY=@.vchCREATEDBY,
dtmCREATEDDATE=@.dtmCREATEDDATE,
vchUPDATEDBY=@.vchUPDATEDBY,
dtmUPDATEDDATE=@.dtmUPDATEDDATE
WHERE intLISTBUSINESSID = @.intLISTBUSINESSID

-- AND WhenUpdated = @.ptmsUpdated
SET @.intErrNo = @.@.Error

IF (@.intErrNo <> 0)
BEGIN
SET @.byIntErrDescOut = 4
Return -1
END

END
ELSE
BEGIN
SET @.byIntErrDescOut = 4
Return -1
END

END
End

How can i check this procedure by performing insert ,update operations?

ie., how can i passing and updating informations through stored procedures?

Explain me in detail...

If you want to test it from the sql server environment, highlight the procedure name in the sql server query studio/management studio and right click it.

The menu will give you an option to execute the stored procedure. Choose it and it will build a script to execute the procedure for you.

All you have to do is supply sample values.

|||

I glanced at your procedure and noticed a few things about it:

The visual indentation of the code is horrible. I hope the copy/paste process lost that, because the logic control flow of the program is much harder to follow than it would be with proper indentation.

OPENXML with a namespace

Sorry, I've tried to search to find my answer, but I just can't quite figure
it out... I'm new to querying XML in a stored proc...
I have an XML file that looks like:
<Members xmlns="http://tempuri.org/template.xsd">
<Member>
<OperationType>Insert</OperationType>
</Member>
<Member>
<OperationType>Update</OperationType>
</Member>
</Members>
there's more to it, but I've simplified it down.
and in my stored proc, I'm trying this: (assume that @.idoc is already
declared and @.FileContents contains the contents of the xml file I'm
reading)
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.FileContents, '<Members
xmlns="http://tempuri.org/template.xsd"/>'
SELECT *
FROM OPENXML(@.idoc, '/Members/Member',2)
WITH (OperationType varchar(30))
EXEC sp_xml_removedocument @.idoc
I get no results...
If I take the xmlns declaration out of the <Members> element in the source
file, it works fine and I get a list of the OperationType elements.
What am I missing?
Thanks much,
SherylHi SL,
Welcome to the MSDN newsgroup.
Regarding on the OPENXML with namespace problem, based on my research, we
can use the third parameter of the sp_xml_prparedocument procedure to
specify namespace info. Also, in our sequental xpath, we need to explicitly
identify namespace through a namespace prefix. For detailed info, you can
refer to the following MSDN document and web article:
#How To: Use OpenXML on an Xml document with a default namespace
http://www.sqlxml.org/faqs.aspx?faq=101
#sp_xml_preparedocument
http://msdn.microsoft.com/library/e...o.asp?frame=tru
e
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||That was perfect, thanks!
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:GubgkovKGHA.3680@.TK2MSFTNGXA02.phx.gbl...
> Hi SL,
> Welcome to the MSDN newsgroup.
> Regarding on the OPENXML with namespace problem, based on my research, we
> can use the third parameter of the sp_xml_prparedocument procedure to
> specify namespace info. Also, in our sequental xpath, we need to
> explicitly
> identify namespace through a namespace prefix. For detailed info, you can
> refer to the following MSDN document and web article:
>
> #How To: Use OpenXML on an Xml document with a default namespace
> http://www.sqlxml.org/faqs.aspx?faq=101
> #sp_xml_preparedocument
> http://msdn.microsoft.com/library/e...ft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>|||You're welcome SL,
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

OPENXML with a namespace

Sorry, I've tried to search to find my answer, but I just can't quite figure
it out... I'm new to querying XML in a stored proc...
I have an XML file that looks like:
<Members xmlns="http://tempuri.org/template.xsd">
<Member>
<OperationType>Insert</OperationType>
</Member>
<Member>
<OperationType>Update</OperationType>
</Member>
</Members>
there's more to it, but I've simplified it down.
and in my stored proc, I'm trying this: (assume that @.idoc is already
declared and @.FileContents contains the contents of the xml file I'm
reading)
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.FileContents, '<Members
xmlns="http://tempuri.org/template.xsd"/>'
SELECT *
FROM OPENXML(@.idoc, '/Members/Member',2)
WITH (OperationType varchar(30))
EXEC sp_xml_removedocument @.idoc
I get no results...
If I take the xmlns declaration out of the <Members> element in the source
file, it works fine and I get a list of the OperationType elements.
What am I missing?
Thanks much,
Sheryl
Hi SL,
Welcome to the MSDN newsgroup.
Regarding on the OPENXML with namespace problem, based on my research, we
can use the third parameter of the sp_xml_prparedocument procedure to
specify namespace info. Also, in our sequental xpath, we need to explicitly
identify namespace through a namespace prefix. For detailed info, you can
refer to the following MSDN document and web article:
#How To: Use OpenXML on an Xml document with a default namespace
http://www.sqlxml.org/faqs.aspx?faq=101
#sp_xml_preparedocument
http://msdn.microsoft.com/library/en...asp?frame=tru
e
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||That was perfect, thanks!
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:GubgkovKGHA.3680@.TK2MSFTNGXA02.phx.gbl...
> Hi SL,
> Welcome to the MSDN newsgroup.
> Regarding on the OPENXML with namespace problem, based on my research, we
> can use the third parameter of the sp_xml_prparedocument procedure to
> specify namespace info. Also, in our sequental xpath, we need to
> explicitly
> identify namespace through a namespace prefix. For detailed info, you can
> refer to the following MSDN document and web article:
>
> #How To: Use OpenXML on an Xml document with a default namespace
> http://www.sqlxml.org/faqs.aspx?faq=101
> #sp_xml_preparedocument
> http://msdn.microsoft.com/library/en...asp?frame=tru
> e
> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
|||You're welcome SL,
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)