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 |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2005-08-31 : 22:40:00
|
| I have 2 choices, either create this query using ASP.NET and hit the db using paramertized queriesORcreate a stored procedure that would look like:CREATE PROCEDURE blah@asdfsadf INT,@aasdf22 INT,@sortorder INT,@sortby INTAS-- create temp table here that is populated from the select below--IF(@sortorder = 1)BEGIN IF(@asdfasdf = 1) SELECT ..... IF(@asdfsdf = 2) SELECT .........ENDIF(@sortorder = 0)BEGIN IF(@asdfasdf = 1) SELECT ..... IF(@asdfsdf = 2) SELECT .......END-- select data from the temp table hereso which is the best approach? |
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-08-31 : 22:57:21
|
| SQL Server will perform better if you create this as a stored procedure on the server-side. The plan for the procedure will be compiled and stored on the server, and (should be) reused whenever your ASP application calls it. Books Online has lots of information about this.Thanks,----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-01 : 01:00:38
|
| Sometimes when we have lots of conditional execution in a Stored Procedure we put the logic for the individual "cases" into separate SProcs and change the original to EXEC them instead, then each of those separate SProcs gets cached. Caching might be a bit more sure-fire than a single complex Sproc - but if Ryan tells me this isn't neccessary I'll stop doing it from now on!Kristen |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-01 : 02:30:26
|
If each branch of the conditional execution is a separate stored procedure, then each of those stored procedures will get their own cached plans. Kristen, in your example, you're executing a single "ad-hoc" batch that calls a bunch of procedures based on some application logic. If you create a single procedure that encapsulates this, you should get even better cache hits, since we'll create a compiled plan for the "master" proc, and individual compiled plans for each called procedure. There's a chance that your EXEC batch will require a recompile when it hits the server (if we can't find an existing plan that matches the ad-hoc SQL), which is much less efficient than if we found an existing proc.I think my previous post was really trying to say, "when in doubt, use a stored procedure." I'm sure there are always exceptions to the rule  !Thanks,----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
|
|
|
|
|
will this complicated query still be optimized?