Thank you. I'll also check the site.

I'm so sorry, my bad for the...

• This reply was modified 1 year, 10 months ago by  ARPRINCE. Reason: wrong word

Yes- thank you!

ARPRINCE,

Have you looked at the query plans for the two queries?  The LEFT JOIN method is doing a full table scan per join.  That could be a (big) performance...

Set up some data (you should have done this when asking the question)[/

Thank you and you are right, will put in data script in the future.

• This reply was modified 1 year, 10 months ago by  ARPRINCE.

Thank you all!

Sorry but I made a mistake on my original posting. Revised DATA representation is below

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
   ...

There will only be 3 items but an order can have either 1, 2 or all 3 items.

So the final result will always have the 5 columns -->  ID, OrderNo,...

Just an update. This went live over a week ago.

[C] SQL -- TR --  [OP] SQL

I didn't need to reconfigure TR from scratch, this was a back up plan...

• This reply was modified 2 years, 7 months ago by  ARPRINCE.
• This reply was modified 2 years, 7 months ago by  ARPRINCE.

I figured it out just now after reading your response.

I was developing the report on my local machine. I changed the SPROC to include the UniqueID field however, I forgot...

Thanks all! The OUTER APPLY was very helpful. Will keep in mind proper posting etiquette in the future.

OK I did a LEFT JOIN into a temp table and used CTE with ROW_NUMBER() OVER PARTITION BY taking only ROW=1

Maybe there's a better way?

After several tuning, it looks like retention numbers lowers down the count but as you can see, it still creeps up.

• This reply was modified 4 years, 3 months ago by  ARPRINCE.

Well it looks like it is still increasing even when I changed that from 500,000/20,000.

Will look at decreasing in half Transaction retention 48 to 24 and History retention 24 to...

So I tried different values for Transaction and Commands. It looks like it is still increasing. Will play around some more.

Our network admin created a SQL 2017 HA cluster  (vmware using shared disks seetings - 2 nodes). He takes care of the hardware I take care of the SQL. Anyway,...