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: amos on 18 Jul 2008 20:09 A much more complex version of this stored procedure has been working ok for me but I'm not sure it's well formed. In particular, the date range poses an issue when a study date range starts getting larger. Most of the time the range requested is today through 7 days ago. Sometimes the range is expanded to catch a single exception record. While there might be 3000 records in the recent week range, there is one exception study from a year ago. Things slow down a lot at that point; sql server is evaluating a years worth of data. 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. The general approach I've taken with the optional params may not be the best either? USE [mydb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblStudy]( [StudyID] [int] NOT NULL, [StDOS] [datetime] NOT NULL, [StBGID] [int] NOT NULL, CONSTRAINT [PK_tblStudy] PRIMARY KEY CLUSTERED ( [StudyID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] USE [mydb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[procStudyList] @datDateFirst Datetime, @datDateLast Datetime, @BGID int, @ExcludeBG bit AS BEGIN SET NOCOUNT ON; SELECT tblStudy.StudyID, tblStudy.StPatID AS PatID, tblStudy.StDOS AS DOS FROM tblStudy WHERE tblStudy.StDOS >= @datDateFirst AND tblStudy.StDOS <= @datDateLast AND tblStudy.StBGID = CASE WHEN @BGID <> 0 THEN CASE WHEN @ExcludeBG = 0 THEN @BGID ELSE CASE WHEN @BGID = tblStudy.StBGID THEN 990099 ELSE tblStudy.StBGID END END ELSE tblStudy.StBGID END END
From: Dan Guzman on 18 Jul 2008 22:41 > 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. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "amos" <amos(a)amos2.com> wrote in message news:MPG.22ead0851ac1e0249896f6(a)msnews.microsoft.com... >A much more complex version of this stored procedure has been working ok > for me but I'm not sure it's well formed. > > In particular, the date range poses an issue when a study date range > starts getting larger. Most of the time the range requested is today > through 7 days ago. Sometimes the range is expanded to catch a single > exception record. While there might be 3000 records in the recent week > range, there is one exception study from a year ago. Things slow down a > lot at that point; sql server is evaluating a years worth of data. > > 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. > > The general approach I've taken with the optional params may not be the > best either? > > USE [mydb] > GO > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > CREATE TABLE [dbo].[tblStudy]( > [StudyID] [int] NOT NULL, > [StDOS] [datetime] NOT NULL, > [StBGID] [int] NOT NULL, > CONSTRAINT [PK_tblStudy] PRIMARY KEY CLUSTERED > ( > [StudyID] ASC > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] > ) ON [PRIMARY] > > USE [mydb] > GO > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > ALTER PROCEDURE [dbo].[procStudyList] > @datDateFirst Datetime, > @datDateLast Datetime, > @BGID int, > @ExcludeBG bit > > AS > BEGIN > SET NOCOUNT ON; > > SELECT tblStudy.StudyID, tblStudy.StPatID AS PatID, tblStudy.StDOS > AS DOS > FROM tblStudy > WHERE tblStudy.StDOS >= @datDateFirst AND tblStudy.StDOS <= > @datDateLast > AND tblStudy.StBGID = CASE > WHEN @BGID <> 0 THEN CASE > WHEN @ExcludeBG = 0 THEN @BGID > ELSE CASE > WHEN @BGID = tblStudy.StBGID THEN 990099 > ELSE tblStudy.StBGID > END > END > ELSE tblStudy.StBGID > END > END
From: Tom Cooper on 19 Jul 2008 00:01 In addition to Dan's comments about indexes and dynamic searches, I would add that your query might run much better if the clustered index was on stDOS. Clustered indexes are very good for range searches. Since this stored proc always does a range search on the date, it might be helped if the primary key constraint was made nonclustered and the clustered index was on stDOS. Of course, you only get one clustered index per table, and it is possible that changing the clustered index might make some other process run slower. The only way you can tell is by testing. But if this were my system, and the primary performance problem I had with this table was this stored proc, changing the clustered index would be something I would look into. Tom "amos" <amos(a)amos2.com> wrote in message news:MPG.22ead0851ac1e0249896f6(a)msnews.microsoft.com... >A much more complex version of this stored procedure has been working ok > for me but I'm not sure it's well formed. > > In particular, the date range poses an issue when a study date range > starts getting larger. Most of the time the range requested is today > through 7 days ago. Sometimes the range is expanded to catch a single > exception record. While there might be 3000 records in the recent week > range, there is one exception study from a year ago. Things slow down a > lot at that point; sql server is evaluating a years worth of data. > > 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. > > The general approach I've taken with the optional params may not be the > best either? > > USE [mydb] > GO > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > CREATE TABLE [dbo].[tblStudy]( > [StudyID] [int] NOT NULL, > [StDOS] [datetime] NOT NULL, > [StBGID] [int] NOT NULL, > CONSTRAINT [PK_tblStudy] PRIMARY KEY CLUSTERED > ( > [StudyID] ASC > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] > ) ON [PRIMARY] > > USE [mydb] > GO > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > ALTER PROCEDURE [dbo].[procStudyList] > @datDateFirst Datetime, > @datDateLast Datetime, > @BGID int, > @ExcludeBG bit > > AS > BEGIN > SET NOCOUNT ON; > > SELECT tblStudy.StudyID, tblStudy.StPatID AS PatID, tblStudy.StDOS > AS DOS > FROM tblStudy > WHERE tblStudy.StDOS >= @datDateFirst AND tblStudy.StDOS <= > @datDateLast > AND tblStudy.StBGID = CASE > WHEN @BGID <> 0 THEN CASE > WHEN @ExcludeBG = 0 THEN @BGID > ELSE CASE > WHEN @BGID = tblStudy.StBGID THEN 990099 > ELSE tblStudy.StBGID > END > END > ELSE tblStudy.StBGID > END > END
From: amos on 19 Jul 2008 16:40 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 19 Jul 2008 16:46
I appreciate both of your replies. The real table didn't have any clustered index; only the sample table whose def I posted. I put a clustered index on StDOS and the sproc seemed to run about 20% slower. Not sure why. I am following up on my notion that if utilizing separate query to pull down the core resultset, and then applying any addtl criteria to that. It does seem promising in my initial testing. I'm testing using plain queries at this point. But what's the best way to pull it off inside the stored procedure? I'm sure there is at least one way to execute sql inside a stored proc (and have it's plan optimized by the engine) and then inside the same stored procedure optionally apply the addtl params via a join on the PK StudyID. BOL will fix me up, but any pointers here will be appreciated. |