January 20, 2008 at 8:52 am
I am looking for a way to optimize the following stored procedure in MSSQL 2000
Given a comma delimited list of ids I need to return a result set of records. Currenlty the stored proceure looks as follows:
CREATE PROCEDURE dbo.usp_GET_ITEMS
@ITEM_IDS varchar(1500)
AS
SET NOCOUNT ON
CREATE TABLE #ITEM (ITEM_ID BIGINT NOT NULL PRIMARY KEY)
DECLARE @SQLString NVARCHAR(2000), @ITEM_ID BIGINT
SET @SQLString = 'INSERT INTO #ITEM (ITEM_ID) SELECT ITEM_ID FROM dbo.ITEM WHERE ITEM_ID IN (' + @ITEM_IDS + ')'
EXECUTE sp_executesql @SQLString
SELECT dbo.ITEM.ITEM_ID, dbo.ITEM.ITEM_DATE, dbo.ITEM.ITEM_TITLE, dbo.ITEM.ITEM_DESC
FROM dbo.ITEM INNER JOIN #ITEM ON #ITEM.ITEM_ID = dbo.ITEM.ITEM_ID
ORDER BY dbo.ITEM.ITEM_DATE DESC
DROP TABLE #ITEM
SET NOCOUNT OFF
The stored procedure is currently called by the following
execute dbo.usp_GET_ITEMS '1,2,3,4,5'
What can be done to make this stored procedure more effecint?
January 20, 2008 at 9:42 am
Try this
[font="Courier New"]CREATE PROCEDURE dbo.usp_GET_ITEMS
@ITEM_IDS varchar(1500)
AS
SET NOCOUNT ON
CREATE TABLE #ITEM (ITEM_ID INTEGER)
DECLARE @Comma_Value VARCHAR(8000)
select @Comma_Value = @ITEM_IDS
DECLARE @Counter INTEGER
DECLARE @Comma_Point INTEGER
DECLARE @String_Length INTEGER
DECLARE @String_Value VARCHAR(10)
SET @Counter = 1
SET @Comma_Point = 0
SET @String_Length = 0
SET @Comma_Value = RTRIM(LTRIM(@Comma_Value))
SET @String_Length = LEN(@Comma_Value)
IF @String_Length <> 0
BEGIN
SET @Comma_Value = @Comma_Value + ','
SET @Comma_Point = CHARINDEX (',', @Comma_Value, 1)
WHILE @Comma_Point <> 0
BEGIN
SET @String_Value = RTRIM(LTRIM(LEFT(@Comma_Value, @Comma_Point - 1)))
IF LEN(@String_Value) <> 0
INSERT #ITEM VALUES (@String_Value)
SET @Comma_Value = SUBSTRING(@Comma_Value, @Comma_Point + 1, @String_Length)
SET @Comma_Point = CHARINDEX (',', @Comma_Value, 1)
END
END
SELECT dbo.ITEM.ITEM_ID, dbo.ITEM.ITEM_DATE, dbo.ITEM.ITEM_TITLE, dbo.ITEM.ITEM_DESC
FROM dbo.ITEM INNER JOIN #ITEM ON #ITEM.ITEM_ID = dbo.ITEM.ITEM_ID
ORDER BY dbo.ITEM.ITEM_DATE DESC
-- SELECT * FROM #ITEM
DROP TABLE #ITEM
SET NOCOUNT OFF[/font]
HTH
Dave J
January 20, 2008 at 10:40 am
Based on actual execution plans, the cost for this one is 30% compared to the original, but only if there is no index defined on ITEM.ITEM_ID.
If there is an index on ITEM.ITEM_ID, then the cost is 95% compared to the original.
So, if you have an index on ITEM_ID then this one is only better if you consider brevity an optimization.
CREATE PROCEDURE dbo.usp_GET_ITEMS_v2
@ITEM_IDS VARCHAR(1500)
AS
SET NOCOUNT ON
SELECT dbo.ITEM.ITEM_ID, dbo.ITEM.ITEM_DATE, dbo.ITEM.ITEM_TITLE, dbo.ITEM.ITEM_DESC
FROM dbo.ITEM
WHERE ',' + @ITEM_IDS + ',' LIKE '%,' + CAST(dbo.ITEM.ITEM_ID AS VARCHAR) + ',%'
ORDER BY dbo.ITEM.ITEM_DATE DESC
SET NOCOUNT OFF
January 21, 2008 at 12:08 am
It's very possible to do this without dynamic SQL. Do a google search for
array sql server function
In fact, this is the very article you should read - http://www.sommarskog.se/arrays-in-sql-2005.html
Enjoy 🙂
January 24, 2008 at 5:23 pm
Thanks so much for the help all the answers, it was much needed. The article was especially helpful.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply