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 |
|
kaplooeymom
Starting Member
5 Posts |
 Posted - 2007-10-17 : 11:50:36
|
| I have a multiple table dataset that needs to be returned, with at least 5 joins, some inner, some left outer. Currently, this is done via a parameterized stored prodedure, which is used fairly frequently. The parameters only affect the where clause, not the joins.Would it be better to create the view with the joins already done, then pass in the parameters with the stored procedure? Which is better for overall performance? Which is better for quicker response times to the calling asp.net application?Thanks in advance,Leah |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-17 : 12:00:20
|
If you call the SP frequently it will have a query plan cached in memory for you.No need to put a VIEW.However, if there are many queries having the same JOINs, it might help to put the JOINs in a VIEW.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 12:38:27
|
| I would have a hand-optimised SProc over a view for a performance-critical section of code.You can change the Sproc to have multi-line code, pre-process to temp tables, all sorts of tricks to get the best possible performance in all scenarios - particularly where you have multiple parameters and various combinations may need different lines of attack.At any time in the future you can re-do the logic in the Sproc, as the need arises, without changing the application.Kristen |
|
|
|
|
|
|
|
|
Views vs Complex Joins in Stored Procedures