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.

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

Investigating now.

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*

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.

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