After updating the statistics, the queries are incredibly fast. The timing issue was ongoing but didn't follow any pattern. I am going to update the statistics prior to...

Thanks for the reply. I'm just trying to figure out how two different databases on seperate machines with similar hardware would be performing so differently (machine A takes 6...

Yes, this is exactly what i was looking for! Thanks so much!!!

Thanks for the reply. i'm not sure the union will work because my query can't use literal values like that. it needs to somehow get all records of...

Hi Jack,

Thanks for the reply. My tests with web services (specifically WCF using TCP binding) have shown that the query I mention in my post takes about 14 seconds...

Thank you for your time. I agree that normalization would be key here but unfortunately is not an option. I won't name names but the database that this...

this is the code of the proc:

---------------------------------------

CREATE PROCEDURE [dbo].[BestSellingItemsWebTransFull]

@beginDate varchar(8),

@endDate varchar(8),

@sourceCode varchar(16),

@sourceRef varchar(16),

@cartid varchar(20),

@customerType char(2),

@division char(2),

@category varchar(4),

@vendorNo varchar(10),

@useCustType int

AS

DECLARE @sourceCodeCart varchar(16)

CREATE TABLE #ORDERNOS (

ORDERNO char(8)

)

CREATE TABLE #ITEMCOUNT (

EDP int,

AMT int

)

CREATE...

Just out of curiosity, would it be more efficient to break a huge proc into smaller stored procedures that do their respective things. This procedure does some ugly things...

Thanks for your help. Your pointers are much appreciated and your help has been great in getting me started on fixing this proc. Take it easy.

Thanks,

Paul

ok, that worked! Here are the results once I ran it:

---------------------------

StmtText ...

I tried that as well and I get the same error with the set on/off in there. I've never used table variables so I'll need to check into that....

It does work if I run the proc without the set on/off.

This is what I tried to run:

------------------------------------

SET SHOWPLAN_TEXT ON

GO

DECLARE @rc int

DECLARE @beginDate varchar(8)

DECLARE @endDate varchar(8)

DECLARE @sourceCode varchar(16)

DECLARE @sourceRef varchar(16)

DECLARE @cartid varchar(20)

DECLARE @customerType char(2)

DECLARE @division char(2)

DECLARE @category varchar(4)

DECLARE @vendorNo varchar(10)

DECLARE @useCustType...

I did the above and got an error message: Invalid object name '#ORDERNOS'.

I am using temp tables in this proc.

--Paul

Thank you very much. That worked like a charm. I really appreciate it.

Paul