Performance Costs of Dynamic Queries 
Author Message
 Performance Costs of Dynamic Queries

I have a bunch of tables with similiar but slightly different structures.
Each table needs to have a copy of one stored procedure where the only
difference is between the store procedures is that they select from the
different tables. I was thinking of combining the stored procedures into 1
and just pass in the table name as a parameter. Something like this

Create procedure doit

   as




Will this be a big performance hit on the server or is it ok to do this?



Fri, 04 Apr 2003 03:00:00 GMT
 Performance Costs of Dynamic Queries

It's hard to say exactly--testing is your best bet, but here's some
generaliztion:

When you create a stored proc, SQLServer compiles an execution plan the
first time it is run.  Each time it is called thereafter, it does not have
to calculate the execution plan again, but simply refers to a saved
execution plan.  The more complicated the query, the more indices and
statistics are needed to derive an execution plan.  If you use dymanic SQL,
the query execution plan has to be derived EVERY time you execute it,
instead of just once.

However, if your query is really simple, as in the example below, it's not
going to take very long for SQL to calculate a query execution plan each
time, so in your specific example, I don't think it will cause much
performance degradation.

HTH.


Quote:
> I have a bunch of tables with similiar but slightly different structures.
> Each table needs to have a copy of one stored procedure where the only
> difference is between the store procedures is that they select from the
> different tables. I was thinking of combining the stored procedures into 1
> and just pass in the table name as a parameter. Something like this

> Create procedure doit

>    as




> Will this be a big performance hit on the server or is it ok to do this?



Fri, 04 Apr 2003 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Query Analyzer - I/O Cost, CPU Cost, and Cost

2. Performance of Dynamic Time-Series MDX Query

3. how to improve query performance wihout using dynamic sql

4. Dynamic SQL, Coalesce, Stored Procs execution plans and cost

5. Dynamic 4GL cost

6. SQL Server Performance Problem - Good query performance, bad update performance

7. Dynamic Query w/ Dynamic Table Name (with a Northwind Example)

8. Dynamic Query w/ Dynamic Table Names

9. dynamic type create (was return setof record from function with dynamic query)

10. User Defined Functions : Is there a performance cost?

11. Undocumented feature costs a lot of performance in COPY

12. Undocumented feature costs a lot of performance in COPY


 
Powered by phpBB® Forum Software