March 14, 2005 at 8:54 am
I need some opinions, I have been putting multiple select statements in to stored procedures. Is this bad? Should i be writing separate sp for each query?
ALTER PROCEDURE
sp_GetVIN
@SlsCode
nvarchar(6) = NULL,
@Model
nvarchar(50) = NULL,
@View
varchar(50) = 'All',
@Opportunity
int = 1,
@PartNumber
nvarchar(50) = NULL
AS
SET NOCOUNT ON
IF @View = 'Model'
IF @Opportunity = 1 OR @Opportunity = 3
BEGIN
SELECT VIN
FROM tblDealerInventory_Master INNER JOIN
tblModelVin
ON tblDealerInventory_Master.ModelBodyVINCode = tblModelVin.VINCode
WHERE (SalesPointNumber = @SlsCode)
AND (tblModelVin.Model = @Model)
AND (
CASE
WHEN @Opportunity = 1 THEN tblDealerInventory_Master.[Running Boards]
WHEN @Opportunity = 3 THEN tblDealerInventory_Master.[Tonneau Covers]
END = '0'
)
GROUP BY VIN
END
ELSE
BEGIN
SELECT tblDealerInventory_Master.VIN,
tblModelVin.Model
FROM tblDealerInventory_Master INNER JOIN
tblModelVin
ON tblDealerInventory_Master.ModelBodyVINCode = tblModelVin.VINCode
WHERE (tblDealerInventory_Master.SalesPointNumber = @SlsCode)
GROUP BY tblDealerInventory_Master.VIN,
tblModelVin.Model
HAVING (tblModelVin.Model = @Model)
END
ELSE IF @View = 'All'
IF @Opportunity = 1 OR @Opportunity = 3
BEGIN
SELECT VIN
FROM tblDealerInventory_Master INNER JOIN
tblModelVin
ON tblDealerInventory_Master.ModelBodyVINCode = tblModelVin.VINCode
WHERE (SalesPointNumber = @SlsCode)
AND (tblModelVin.Model = @Model)
AND (
CASE
WHEN @Opportunity = 1 THEN tblDealerInventory_Master.[Running Boards]
WHEN @Opportunity = 3 THEN tblDealerInventory_Master.[Tonneau Covers]
END = '0'
)
GROUP BY VIN
END
ELSE
BEGIN
SELECT tblDealerInventory_Master.VIN,
tblModelVin.Model
FROM tblDealerInventory_Master INNER JOIN
tblModelVin
ON tblDealerInventory_Master.ModelBodyVINCode = tblModelVin.VINCode
WHERE (tblDealerInventory_Master.SalesPointNumber = @SlsCode)
GROUP BY tblDealerInventory_Master.VIN,
tblModelVin.Model
HAVING (tblModelVin.Model = @Model)
END
March 14, 2005 at 9:17 am
I haven't read all the procs but here's my rule of thumb :
If I have different select statements (the columns changes, or the joins change), then make difference sps and call them accordingly in the triage proc. If it's the same statement but with different where conditions then you can always write the select like this :
Select Col_id, Col1 from dbo.MyTable where MyField = @MyField or @MyField = 'All'
--or @MyField is null
March 15, 2005 at 2:12 am
Putting ORs in the WHERE clause is going to impact the execution plan and performance - by how much depends on the whole statement.
Unless performance is absolutely critical, though, I would put most weight on maintainability. Which approach is going to be easier to debug if a problem arises? Which is going to be easier to modify? Remember that the poor fellow tasked with this may be new to the system so try not to have logic going on in unexpected places!
March 15, 2005 at 6:23 am
Actually my demo is meant to be used like this :
Select Col_id, Col1 from dbo.MyTable where
(MyField = @MyField or @MyField = 'All')
and
(MyField2 = @MyField2 or @MyField2 ='all')
if @MyField and @MyField2 are passed in parameter the query optimizer will check (@MyField = 'All') only once and decide on which index to use.
Ex :
@MyField = 'All'
(MyField = @MyField or 'All' = 'All') will always be true so it won't try to match MyField to @MyField
March 15, 2005 at 11:00 am
The execution plan for a stored procedure is built the first time it is executed. So, what may happen is that the optimizer will produce a plan that is only valid (optimum) for one path: the one that was taken the first time it executed.
Breaking this up into several procedures will allow the optimizer to work for each one, which should improve performance. You'll probably want to test that, if you can.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply