[RESOLVED] URGENT please help in imporving this code performace

[waely]

hi,
I have stored procedure that expects project IDs. I created a temp table for the IDs then pace in my where clause.
if I test my sql statement whithout my where clause ( without the ID's) then it takes 1 second to execute but if I include the where clause it takes 20 sec which causes my application to time out. please keep in mind that the number of project ids will keep increasing with time but for now I have about 50.

CREATE PROCEDURE Report  @ListStr varchar(1000) AS
 
Declare @ListTbl Table  (STID int) 
Declare @CP int
Declare @SV int
 
While @ListStr<>''
Begin
    Set @CP=CharIndex(',',@ListStr)
    If @CP<>0
    Begin
        Set @SV=Cast(Left(@ListStr,@CP-1) as int)
        Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP)
    End
    Else
    Begin
        Set @SV=Cast(@ListStr as int)
        Set @ListStr=''
    End
    Insert into @ListTbl Values (@SV)
End
select id, field1,field2, field3 from projecttable 
Where   ID  in (Select STID From @ListTbl)

I appreciate any help.

[si_the_geek]

I would assume that the slowest part is re-building the string, so here's an alternative which doesn't do it:

CREATE PROCEDURE Report  @ListStr varchar(1000) AS

Declare @ListTbl Table  (STID int) 
Declare @StartPos int
Declare @LastStartPos int

SET @StartPos = 0

While @StartPos <> -1
Begin
        SET @LastStartPos = @StartPos + 1
	Set @StartPos=CharIndex(',',@ListStr, @LastStartPos)
	If @StartPos<>0
	Begin
		Set @SV= Cast( SUBSTRING(@ListStr, @LastStartPos, @StartPos - @LastStartPos - 1) as int) )
	End
	Else
	Begin
		Set @SV= Cast( SUBSTRING(@ListStr, @LastStartPos, Len(@ListStr) - @LastStartPos - 0) as int) )
                SET @StartPos = -1
	End
	Insert into @ListTbl (STID) Values (@SV)
End
select id, field1,field2, field3 from projecttable 
Where   ID  in (Select STID From @ListTbl)

I haven't tested it, so I've underlined the parts that I think may not be quite right!

[waely]

thanks si_the-geek. I got only one error regarding declaring SV so I delcare it as int in the beginning

Declare @ListTbl Table  (STID int) 
Declare @StartPos int
Declare @LastStartPos int
Declare @SV int

the code runs with no problems but the out put is just says " 1 record effected" many times where I'm expecting records.
I should've mentioned that my string passed to the stored proc is in this format '184,325,264,258,'
any ideas?

[si_the_geek]

oops, I'd forgotten to put SV back in! (I had put the Insert 'inline' to avoid the variable, but changed my mind)


To stop the "records affected" messages, put this before the While line:

SET NOCOUNT ON

..and this before the Select line:

SET NOCOUNT OFF

I'd recommend checking that my code works as it is meant to - add an extra Select statement at the end to see what is in @ListTbl, and make sure all the values from @ListStr are in it.

[waely]

hi,
I tested the code after I made the modifications. 2 issues. first, because my strings passed '125,145,225,' has a ' , ' at the end it complained so I removed it and it executed fine but the IDs are missing the last digit. ex. if I pass '451,547' I get 45,54

thanks

[si_the_geek]

In that case you need to change the parts I underlined.

The -1 should be -0 (or just removed!), and the -0 should be +1

[waely]

thank very much for your assistance. when I applied your code to my original quary the exectiong time was almost the same (20 sec). it's maybe my quary.

waely

In that case you need to change the parts I underlined.

The -1 should be -0 (or just removed!), and the -0 should be +1