I am not getting it.  Earlier this spring I discussed in these forums the utilization of UDFs in filling a BO.  The conclusion was to not use them since they have a great deal of overhead involved.  So, instead, I went with some raw SQL to achieve the same results.  I was able to get one of my more complex queries down to about 3 seconds.  That was wonderful.  Now, I am simply taking the same script that runs in the SQL Query Analyzer at 3 seconds and placing it into a stored procedure with three parameters.  These parameters are declared at the beginning of the script in the query analyzer, too.  I run the stored procedure and it takes many minutes to run (last run: 10 min 04 sec).  I really don't get this.  It is the exact same script.  Is there really that much of a difference in quality between raw SQL script and the same SQL script placed into a stored procedure?  What am I doing wrong?  I have attached the script that I am running in the Query Analyzer and the stored procedure.  I know that these are not pretty...I am still working through the details of this query.  Any help, tips, criticism is welcome!

Thanks,
Bill

I took the parameters out of the stored procedure and hardcoded them into the sproc just like the raw script.

BAM!!  2 sec. to run the sproc!!!!

Why would processing parameters cause the sproc to execute 300x slower?

I am going to add them back one at a time and see which one is causing the slowdown.

If I remove the first parameter (varchar(30)), then the stored procedure works under 4 sec every time.  If I reintroduce the parameter, then it goes right back to the 10 minute mark.  The third parameter is a varchar(10).  I do not think the type is the problem, here.

Still investigating.

OK, that is different. I understand why you are baffled.



I'd see what replacing the LIKE in your where clause with an equals does, leaving @itemcode as a varchar(30). I.e.







Now, you might need the LIKE, but at least this might help see where the problem lies.



I'm assuming that Items.Code is indexed. Also, is there just one code in the field? I.e. it isn't a list of codes or anything weird like that is it?

I'll try the sproc without the LIKE...I suppose an IF block may work better.

The code column is indexed.  Only one code would be passed if the user wanted the list limited.

Thanks for you attention on this, Greg.  Much appreciated.

Bill

The stored procedure does not like the LIKE.

Who woulda thunk it?!?!?!

I have removed the LIKE and have followed another approach:

IF @itemcode = ''
    BEGIN
        'run script without the Items.Code filter
    END
ELSE
    BEGIN
        'run the script with the Items.Code = 'MyCode'
    END

Thanks, again, Greg!!
Bill

I'm not quite sure why LIKE isn't...er...liked by SQL Server, but I do know enough to include that in my list of things to check out when a query is slow.



Glad you got it working (faster).