October 7, 2008 at 12:02 pm
Hi all,
I have a SP that is using nested SELECTs with IN's... very processor heavy. I need to re-write it properly but it's complex and confusing.
Can anyone please help? I've been trying for hours, but I can't get it.
Here it is:
------------------
USE [OurDB]
GO
/****** Object: StoredProcedure [dbo].[spGetProductTypesWithAccess] Script Date: 10/07/2008 10:32:55 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[spGetProductTypesWithAccess]
@customerID int,
@loginUserName nvarchar(50)
AS
SELECT DISTINCT tblProductType.productTypeID, tblProductType.productTypeDesc
FROM
tblProductType INNER JOIN
tblProducts ON tblProductType.productTypeID = tblProducts.productTypeID
WHERE
(tblProducts.customerID = @customerID)
AND
(tblProductType.productTypeID IN
(SELECT tblProducts.productTypeID
FROM tblProducts
WHERE itemID IN
(SELECT itemID
FROM tblInventory))) AND
(tblProductType.divisionID IN
(SELECT divisionID
FROM tblLogins_Divisions
WHERE userID IN
(SELECT userID
FROM tblLogins
WHERE loginUsername = @loginUserName)))
October 7, 2008 at 4:38 pm
From the looks of things, all but two of the subqueries in your WHERE clause are used only to determine if a column has a match in another table. This is exactly what joins are meant to do. In fact, one of the subqueries is redundant since there is already a join that establishes that tblProductType.productTypeID must have a match in the tblProducts table.
Leave the two comparisons to variables in the WHERE clause and you get something like this
------------------------------------------------------------------------------
SELECT DISTINCT tblProductType.productTypeID, tblProductType.productTypeDesc
FROM
tblProductType INNER JOIN
tblProducts ON tblProductType.productTypeID = tblProducts.productTypeID INNER JOIN
tblInventory ON tblInventory.itemID = tblProducts.itemID INNER JOIN
tblLogins_Divisions ON tblLogins_Divisions.divisionID = tblProductType.divisionID INNER JOIN
tblLogins ON tblLogins.userID = tblLoginsDivisions.userID
WHERE
(tblProducts.customerID = @customerID) AND
(tblLogin.UserName = @loginUserName)
--------------------------------------------------------------------------
I hope I didn't mistype any of that, but it should be close enough to get you there. The important thing to remember is to let the joins establish the relationships between large tables. This can be especially helpful to performance if your tables are indexed on the columns being used for the joins.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply