Thanks,
Bill

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*

Investigating now.

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.

Still investigating.

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

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

A few random comments that may help:

1) Be careful with data types in a Where clause. I see you have what looks like date parameters defined as varChar. If the database column is a real date and you are comparing with a varChar then SQL Server will not use any index you may have on that column.

2) Big time differences like this will invariable mean that one way is using indexes and the other is using full table scans. The Profiler will show this up.

3) I notice you have:

@itemcode varchar(30),IF @itemcode = '' OR @itemcode IS Null   BEGIN         SET @itemcode = '%%'   ENDWHERE       Items.Code LIKE @itemcode 

I think a more efficient approach would be to sort out your parameter and have a defualt and only pass in data if you have a specfic selection criteria. Then you could have:

@itemcode varchar(30) = Null,WHERE       ((@itemcode Is Null) Or  (Items.Code = @itemcode)) 

Cheers, Peter

1. You can tell a query which index to use by using the WITH and INDEX commands.  Sometimes SQL needs a little help...we ran into this the other day.  It would look like this:
   
   
   SELECT * FROM Customers WITH(INDEX(IX_MyIndex))
   
   
2. The framework is not going to change anything in regards to the execution speed and performance....so if you get it down to 1 second in SQL Server management Studio executing the sproc...this will not change on the framework side unless you have some type of connection issue or something else in the mix.
   
3. DateTime columns are aweful about slowing down queries when in the WHERE...one way to get around this is to store dates as a BigInt data type and then store then DateTimes using Ticks.  We then create a Custom property on the BO that wraps this as a DateTime so that while using the BOs inside of your app you interact with a DateTime...but it is stored as Ticks on the SQL Server side...and this will drastically improve performance....by like a ton when you are testing with <, >, or betweens.

While I've never tried converting a date to a bigint I thought I would just let you have my specific experience in that area. Most of our reports are date range based and use transactional data as their source. For this reason we have clustered index on the 'date created' column in the transaction files. I've just connected to one of our sites where the main transaction file is 11+ million rows. I opened Query in SQL Server entered the following (date randomly selected):

Select HIDDateTime

From dbo.tblHIDHides

Where HIDDateTime Between Convert(DateTime, '2006-05-04', 102) And Convert(DateTime, '2006-05-05', 102)

So, no stored procedure, no caching from previous queries. The result: 6501 rows returned in < 1 second.

The database is low end Xeon database server with just 2Gb of memory, Windows 2003 Standard that wasn't busy when I did the above test.

Cheers, Peter

One other thing on this too, we have to be able to have extremely fast queries run on MS Express with siingle core processors as we have a lot of users with 2 GB plus databases that will till use MS SQL Express on existing equipment iin the field...we call this zero impact.  It may not be but we have to get as close to that as possible for existing users.  Then are the much larger sites they will have a more complex server setup and a full version of SQL Server...so we have to work in a lot of different environments...so absolute optimization is the only way we can do this.

Thanks for the extra info. Hopefully we never have to get down to that level but it is very interesting that changing the data type made such a humongous improvement.

 Cheers, Peter