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'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).