Hi Federico

By "If I run the query for one ID, the data returns instantaneously" I meant that if I execute the query for just one ID value it returns the...

Added files as text and xml

Hi Scott

Table DDL attached along with execution plan (where using a EXISTS condition).

I did notice there is a big difference between the estimate and actual number of rows between the...

Tried the logging option in the job step but got the following error:

The log provider type "{2E5B5C92-2968-4520-95A8-3A33D55E4507}, {2E5B5C92-2968-4520-95A8-3A33D55E4507}" specified for log provider "{D5145C67-8C71-4CD3-9652-0792EC434AD8}" is not recognized as a valid log...

I haven't tried that, I'll take a look.

The setting I'm trying to use is within the package itself. But the connection won't work as its values are set from a...

Its more a case that I can't turn logging on where my connections are set using config files.

I can worry about tailoring the logging options later, I just want to...

Scratch this guys, its only after posting that I see that it wouldn't update anyway as there's no difference in the values.

I'm just going to go sit in the corner...

I got a big improvement by introducing a new index based on batchdate and batchstatus (disregard the cover index name)
CREATE NONCLUSTERED INDEX [IDX_NI_CA_Batch_CoverIndex2] ON [dbo].[Batch]
(
    [BatchGLDate] ASC
    ,[BatchStatus] 

Triangular Join - I knew there was a name for that (wrote my result set on a piece of paper that looked like this)

Thanks Drew, I've been out...

As I have 2 log tables to pull data from, how would this work?

do you mean by just having a clustered index by the modified date on both log tables...

Thanks Phil

I've tweaked the code slightly so that there are no NULL values as I was thinking the exact same thing.

I'm currently working on getting rid of the temp tables...

Thanks guys for the replies

John - I got it working using the method you outlined, I used LEAD instead of LAG

Massive improvement on the performance too

Now I just need...

First I've heard of them john, any good articles you'd recommend or would a google suffice?

Sorry Chris, forgot to attach the indexes

Happy enough to go with the TOP 1 in a CROSS APPLY

Thanks anyway

That makes sense Jacob

I had the idea that the index on the logdate field would improve performance but since it's running a select against the whole table it makes little...