May 14, 2014 at 7:45 am
Hi, I'd like to be able to offer the option of selecting a project by the ProjectID number, and if a projectID is not supplied then the default result set would be select * from tbl_Projects. I'm just wondering if there is a better way to do that than what I have written below. Any better suggestions?
ALTER PROCEDURE [USP_SelectProject]
-- Add the parameters for the stored procedure here
@ProjectNumber as int
AS
BEGIN
If @ProjectNumber is NULL
BEGIN
SELECT ProjectID, ProjectName, ProjectDescription FROM tbl_Projects
END
ELSE
BEGIN
SELECT ProjectID, ProjectName, ProjectDescription FROM tbl_Projects
where
ProjectID = @ProjectNumber
END
END
May 14, 2014 at 7:48 am
Something like this?
SELECT ProjectID, ProjectName, ProjectDescription
FROM tbl_Projects
WHERE (ProjectID = @ProjectNumber OR @ProjectNumber IS NULL);
Performance may be a little better firing them individually without the OR in there, but this will do what you're after with only one SELECT statement.
May 14, 2014 at 8:20 am
To make sure you don't get a really poor plan, force SQL to recompile the query:
SELECT p.ProjectID, p.ProjectName, p.ProjectDescription
FROM tbl_Projects p
WHERE
p.ProjectID = @ProjectNumber
OPTION(RECOMPILE)
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 14, 2014 at 8:31 am
The execution plans for those two queries is likely to be completely different. If you were to write the query as a single "catch-all" query, it would be optimised either for one project or for the whole table, not for both. One of the two options would take a hit.
-- substitute MyTable for your own table with a clustered index, and ID for the cluster key.
SET NOCOUNT ON
DECLARE @ID INT = 257
PRINT char(10) + 'Query 1, logical reads 2, clustered index seek, 1 row returned in 1s'
SET STATISTICS IO, TIME ON
SELECT ID
FROM MyTable
WHERE ID = @ID
SET STATISTICS IO, TIME OFF
PRINT '--------------------------------------------------'
PRINT char(10) + 'Query 2 CATCHALL, logical reads 155, clustered index scan, 1 row returned in 50s'
SET STATISTICS IO, TIME ON
SELECT ID
FROM MyTable
WHERE @ID IS NULL
OR ID = @ID
SET STATISTICS IO, TIME OFF
PRINT '--------------------------------------------------'
PRINT char(10) + 'Query 3, logical reads 2, CI scan (0 rows) + CI seek (1 row), 1 row returned in 1s'
SET STATISTICS IO, TIME ON
SELECT ID
FROM MyTable
WHERE @ID IS NULL
UNION ALL
SELECT ID
FROM MyTable
WHERE ID = @ID
SET STATISTICS IO, TIME OFF
PRINT '--------------------------------------------------'
SET @ID = NULL
PRINT char(10) + 'Query 4, logical reads 155, clustered index scan, all rows returned in 50s'
SET STATISTICS IO, TIME ON
SELECT ID
FROM MyTable
WHERE @ID IS NULL
OR ID = @ID
SET STATISTICS IO, TIME OFF
PRINT '--------------------------------------------------'
PRINT char(10) + 'Query 5, logical reads 155, CI scan (all rows) + CI seek (0 rows), all rows returned in 50s'
SET STATISTICS IO, TIME ON
SELECT ID
FROM MyTable
WHERE @ID IS NULL
UNION ALL
SELECT ID
FROM MyTable
WHERE ID = @ID
SET STATISTICS IO, TIME OFF
PRINT '--------------------------------------------------'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 14, 2014 at 9:04 am
ChrisM@Work (5/14/2014)The execution plans for those two queries is likely to be completely different. If you were to write the query as a single "catch-all" query, it would be optimised either for one project or for the whole table, not for both. One of the two options would take a hit.
Unless you force SQL to recompile the code :-).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 14, 2014 at 9:10 am
ScottPletcher (5/14/2014)
ChrisM@Work (5/14/2014)The execution plans for those two queries is likely to be completely different. If you were to write the query as a single "catch-all" query, it would be optimised either for one project or for the whole table, not for both. One of the two options would take a hit.
Unless you force SQL to recompile the code :-).
Exactly. Put steel rims on them there wooden spoked wheels 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 14, 2014 at 9:33 am
I prefer splitting it into two procedures. Gives stable execution plans, doesn't need the extra CPU of recompiling on every execution.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2014 at 12:26 pm
That's not for me. I don't like the maintenance pain of "separate but equal" procs. Besides, for two params do I then have 4 separate procs, for 3 parameters I have 8 separate procs, ...?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 14, 2014 at 1:03 pm
ScottPletcher (5/14/2014)
Besides, for two params do I then have 4 separate procs, for 3 parameters I have 8 separate procs, ...?
No, that would be silly. Dynamic SQL works excellently when there's multiple optional parameters. It works fine for 1 optional as well, might be more work than ideal
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Oh, the recompile option only works (results in optimal plans) SQL Server 2008 SP2 or SQL Server 2008 R2 (I think RTM) or above. Other versions will either result in an inefficient execution plan or (SQL 2008 RTM) a possibility of running into an incorrect results bug.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2014 at 1:22 pm
GilaMonster (5/14/2014)
ScottPletcher (5/14/2014)
Besides, for two params do I then have 4 separate procs, for 3 parameters I have 8 separate procs, ...?No, that would be silly. Dynamic SQL works excellently when there's multiple optional parameters. It works fine for 1 optional as well, might be more work than ideal
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Oh, the recompile option only works (results in optimal plans) SQL Server 2008 SP2 or SQL Server 2008 R2 (I think RTM) or above. Other versions will either result in an inefficient execution plan or (SQL 2008 RTM) a possibility of running into an incorrect results bug.
Hmm, so we are supposed to go to the trouble and coordination effort of making two completely separate procs, but the second we add another parameter we undo it?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 14, 2014 at 2:10 pm
ScottPletcher (5/14/2014)
Hmm, so we are supposed to go to the trouble and coordination effort of making two completely separate procs, but the second we add another parameter we undo it?
Well if you know that's the kind of changes that your system is likely to get (you often get asked to add extra search parameters to procedures), then just make it dynamic from the start. If it's something like 'write a procedure to get all the products or just the products in a single category for the menu', where there aren't a whole bunch of other possible parameters, then you're pretty safe with just the two procedures.
Basically, you have a choice of doing extra up-front work in case there might at some point be extra parameters added or doing just what's needed for the requirements you have at the moment. Which you prefer will depend on you and your system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply