Procedure with date range and optional parameters, what's most efficient?
in [Programming SQL]
|
Prev: What's wrong with the code
Next: Does anybody know of any good books on ASP.NET Membership and the Login controls?
From: Dan Guzman on 20 Jul 2008 10:25 > Yes, I'd read that about optional params before. I've never put together > a sproc that branches like that. In order to get the execution plan > optimized, one could still keep the different query's sql inside the > single stored procedure; and just branch to the one that fits best? I'm > just guessing that the sql engine would optimize for each complete sql > statement. Yes, with separate SQL statements, SQL Server can do a better job of optimizing the execution plan because only the needed parameters are specified. Each statement will have a distinct execution plan. > Or does one need to branch to separate stored procedures to > get the best distinct plans? Branching to separate procs is a good approach if you have trouble maintaining an optimal plan for multiple statements in a single proc. This technique is also discussed in Erland's article under the Using Subprocedures topic. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "amos" <amos(a)amos2.com> wrote in message news:MPG.22ebf1179ad1d2809896fd(a)msnews.microsoft.com... > In article <5B864ECA-A79D-40E4-B2B7-E7E619090986(a)microsoft.com>, > guzmanda(a)nospam-online.sbcglobal.net says... >> > If there is a simple way to make the where clause faster, it'd be neat >> > to hear it. If I passed in the specific days, instead of a range, would >> > that be better? Normally there'd be no more than 8 days or so, ie the >> > week plus the exception. >> >> I would expect the date range method to be most efficient but you'll need >> an >> index on the date column for an efficient search. Based on the DDL you >> posted, a table scan will be required. >> >> Optional parameters tend to be nasty because there is no >> one-size-fits-all >> plan that is optional in all cases. In this simple case, you could >> instead >> execute different queries conditionally which would greatly improve the >> chances of an optimal plan. I suggest you peruse >> http://www.sommarskog.se/dyn-search.html for some other ideas, Again, >> indexes are still required for optimal performance. >> >> > Yes, I'd read that about optional params before. I've never put together > a sproc that branches like that. In order to get the execution plan > optimized, one could still keep the different query's sql inside the > single stored procedure; and just branch to the one that fits best? I'm > just guessing that the sql engine would optimize for each complete sql > statement. Or does one need to branch to separate stored procedures to > get the best distinct plans? > > I do have the relevant indexes in place, I just left them off in my cut > down table def.
From: amos on 20 Jul 2008 13:54 In article <485A8EE7-CCF0-455D-A37F-AC85C26F92F9(a)microsoft.com>, guzmanda(a)nospam-online.sbcglobal.net says... > > Yes, I'd read that about optional params before. I've never put together > > a sproc that branches like that. In order to get the execution plan > > optimized, one could still keep the different query's sql inside the > > single stored procedure; and just branch to the one that fits best? I'm > > just guessing that the sql engine would optimize for each complete sql > > statement. > > Yes, with separate SQL statements, SQL Server can do a better job of > optimizing the execution plan because only the needed parameters are > specified. Each statement will have a distinct execution plan. > > > Or does one need to branch to separate stored procedures to > > get the best distinct plans? > > Branching to separate procs is a good approach if you have trouble > maintaining an optimal plan for multiple statements in a single proc. This > technique is also discussed in Erland's article under the Using > Subprocedures topic. > > The link you provided is a gold mine for sure. I'm going to absorb what I can from that...there's really a ton there, very well written. I don't recongize anything there that follows that line I've been pursuing. Maybe I just don't recognize it. I'd like to figure out if my homegrown solution is viable, so I'll ask again. I've found that a separate query that evals only a couple of the primary params executes very fast regardless of the date range (where range is usually a contiguous week, and then a single far flung date, like a year prior). If I was writing a query for this, I'd use this simple query as the basis for the query that handles the balance of the params. The two queries would be joined on StudyID. How can I implement that in a sproc? I'd need to execute the base query, then use it's result set with a join on StudyID to tblStudy with the more varied where clause. I'm not sure it'll be an improvement over what my current code, but it should be according to my non-sproc tests.
From: Erland Sommarskog on 20 Jul 2008 18:17 amos (amos(a)amos2.com) writes: > I don't recongize anything there that follows that line I've been > pursuing. Maybe I just don't recognize it. I'd like to figure out if my > homegrown solution is viable, so I'll ask again. Judging from your original post, it seems that your case is somewhat simpler from what I discuss in that article. As long as the date range is selective enough, your query should work fine. But obviously, if you have an explicit @BGID a direct lookup is more effcient. Maybe this: IF @BGID <> 0 AND @ExluceBG = 0 BEGIN SELECT tblStudy.StudyID, tblStudy.StPatID AS PatID, tblStudy.StDOS AS DOS FROM tblStudy WHERE tblStudy.StDOS >= @datDateFirst AND tblStudy.StDOS <= @datDateLast AND tblStudy.StBGID = @BGID ELSE BEGIN SELECT tblStudy.StudyID, tblStudy.StPatID AS PatID, tblStudy.StDOS AS DOS FROM tblStudy WHERE tblStudy.StDOS >= @datDateFirst AND tblStudy.StDOS <= @datDateLast AND tblStudy.StBGID <> @BGID END -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Dan Guzman on 21 Jul 2008 08:10 > I've found that a separate query that evals only a couple of the primary > params executes very fast regardless of the date range (where range is > usually a contiguous week, and then a single far flung date, like a year > prior). If I was writing a query for this, I'd use this simple query as > the basis for the query that handles the balance of the params. The two > queries would be joined on StudyID. How can I implement that in a sproc? > I'd need to execute the base query, then use it's result set with a join > on StudyID to tblStudy with the more varied where clause. Have you considered the OPTION (RECOMPILE) query hint (one of the many techniques in Erland's article)? This might address the performance issue with varying date range criteria. I don't think it's a generally a good approach to run the queries independently and then join the intermediate results (e.g. using intermediate temp tables) because this ties the optimizer's hands. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "amos" <amos(a)amos2.com> wrote in message news:MPG.22ed1bb9913f66c79896ff(a)msnews.microsoft.com... > In article <485A8EE7-CCF0-455D-A37F-AC85C26F92F9(a)microsoft.com>, > guzmanda(a)nospam-online.sbcglobal.net says... >> > Yes, I'd read that about optional params before. I've never put >> > together >> > a sproc that branches like that. In order to get the execution plan >> > optimized, one could still keep the different query's sql inside the >> > single stored procedure; and just branch to the one that fits best? I'm >> > just guessing that the sql engine would optimize for each complete sql >> > statement. >> >> Yes, with separate SQL statements, SQL Server can do a better job of >> optimizing the execution plan because only the needed parameters are >> specified. Each statement will have a distinct execution plan. >> >> > Or does one need to branch to separate stored procedures to >> > get the best distinct plans? >> >> Branching to separate procs is a good approach if you have trouble >> maintaining an optimal plan for multiple statements in a single proc. >> This >> technique is also discussed in Erland's article under the Using >> Subprocedures topic. >> >> > The link you provided is a gold mine for sure. I'm going to absorb what > I can from that...there's really a ton there, very well written. > > I don't recongize anything there that follows that line I've been > pursuing. Maybe I just don't recognize it. I'd like to figure out if my > homegrown solution is viable, so I'll ask again. > > I've found that a separate query that evals only a couple of the primary > params executes very fast regardless of the date range (where range is > usually a contiguous week, and then a single far flung date, like a year > prior). If I was writing a query for this, I'd use this simple query as > the basis for the query that handles the balance of the params. The two > queries would be joined on StudyID. How can I implement that in a sproc? > I'd need to execute the base query, then use it's result set with a join > on StudyID to tblStudy with the more varied where clause. > > I'm not sure it'll be an improvement over what my current code, but it > should be according to my non-sproc tests.
From: amos on 22 Jul 2008 23:11
The stored procedure was much more complex than the example I posted; approx on the order of the northgale example you posted. Now I've redone the procedure so that it uses dynamic sql and it's incredibly much faster. Thank you for the information and examples that you put up on your website, it's so helpful. |