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.
| Author |
Topic |
|
CMartin
Starting Member
13 Posts |
 Posted - 2002-08-05 : 23:26:54
|
| I always try to avoid using Temp tables or Dynamic SQL, but in some situations it's quite impossible not use one of the them in complex queries.I have this situation:A store procedure that will receive several parameters that defines what type of info is to be retrieved and on which columns the results must be ordered. Some of the columns will have calculated results and some of the order by clauses involves also calculating/counting.Store procedure uses about 5 tables one of them with about 3 million records, with 5 columns to retrieve. It will be accessed about 40,000 times a day. Each time is accessed it can return recordsets with about 200 rows with 18 columns.Due to the nature of this store procedure I need to use a temp table or to create dynamic sql to achieve what is required. Other solution would be to create several store procedures but I want to test first with only one.If I use dynamic sql the execution plan will almost never be reused and this could mean about 40,000 compilations per day. In the other hand, if I use a temp table the execution plan could be reused but it could cause some problems in server performance.If you had to opt between using a temp table or to create dynamic sql (by using sp_executesql) what would be your option, not only is this specific situation but also in general?ThanksCarlos |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-06 : 07:58:09
|
| Well, without the exact code you're using, it's hard to make any other suggestions on how to improve the stored procedure.<rant>There is nothing inherent about dynamic SQL or temp tables that means they ALWAYS, UNEQUIVOCALLY perform badly, or even perform worse than other methods. In my experience both temp tables and dynamic SQL have been faster than other means, and certainly fast enough for the purpose intended. It depends greatly on how your database is structured and what kind of hardware you're running SQL Server on. You won't know that they perform better or worse unless you TEST multiple options. You only close off your options by assuming they will perform badly and remove them from your consideration.</rant> |
 |
|
|
|
|
|
What's worst to use? Temp Tables or Dynamic SQL