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 NullBEGINSET @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