Parameter sniffing was the issue alright.

I added a parameter to the query and because it's run from a main SP I moved the first SQL statement to the main SP...

Thanks for that.

Yes, it will be a stored procedure, could you suggest the code as a SP please?

PS Removed NOLOCK!

The time is on the second select statement when @CutOffTid parameter is set by the first select. The first select runs instantly on its own.

Its like it's not treating the...

Unfortunately I don't have "showplan" permissions on the database agr.

But if I set the variable @CutOffTid to the value the first select statement returns then it runs instantly. To...

Thanks for the reply, It's int data type in both tables and a unique index in both tables.

Thanks again.

Is PIVOT not recognised in SQL 2000? Maybe its only, SQL 2005.....

Super, that's sorted now through the GUI.

Thanks for your help.

Dave.

Yes, the transaction log seems to be limited to 99MB because it increases to this size from 10MB after truncation, how can I increase this or make it unlimited?

Thanks

Thanks for your reply Jeff.

The datatype char(2) in both tables and client is indexed on both tables. Here's the SQL which runs instantly but if I change @client to 'XX'...

Thanks for your reply Chris.

Nothing gets returned using like U%

Thanks

Dave.

Thanks for your reply, I didn't realise that it runs an Alter SP command, I assumed it opened thge actual SP.

Thanks again!

dave