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

Bill,



I'm not quite sure what you are doing to get the time differences. Is the sproc taking three seconds, but when you just run the script that is within the sproc it takes longer? I seem to recall that Query Analyzer is just using one of the older db access technologies (ODBE or OLEDB, something like that) to run the sproc/script and SQL Server Management Studio is using ADO.NET, so there should be no differences time between them running there and within your app (unless you app is doing something else to eat up time, of course).



If so, it could be explained because the sproc is "compiled" within SQL server (the query plan is already determined and saved), were as a script needs to determine the query plan, then execute the query. You could improve the speed of the script by providing hints about how the query should be executed, but it would be easier to just call the sproc directly from the BO.



If you are using a script in the app, try just calling the sproc and see if that makes a difference.

The raw script executes in 3 sec.

The stored procedure, using the exact same script, takes 10 minutes.

Here is my code to fill the BO:

SqlParameter mItemCode = new SqlParameter("@itemcode", MatCodeTE.Text);
ADUserBO mADUser = new ADUserBO();
SqlParameter mDiv = new SqlParameter("@div", mADUser.LocationIndex);
SqlParameter mInvDate = new SqlParameter("@invdate", InvDateDE.DateTime.ToShortDateString());
rawMaterialValuationBO1.FillByStoredProcedure("spx_GetRunningInventory_Material", mItemCode, mDiv, mInvDate);

This times out in the application because the stored procedure is taking way too long to run.

I have gone through the execution plan of the raw script in the Query Analyzer and updated all statistics where it wanted it.  I even edited some tables to add indexes where I thought it may help.  Nothing is helping that sproc to run quicker.  To me, it should run in 1 or 2 seconds since there is caching and compiling and such going on.  It is a server-based process...it should be lightning fast.

I have never had this happen before.

*scratches head*

Interesting note: the execution plan for the raw sql script is way different than the execution plan for the stored procedure!

Investigating now.

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