This looks like a simple pivot.

SELECT ItemId, ItemName,  Dimn as Dimension, Model, WarehouseLoc
FROM (
select a.ItemID, a.ItemName, b.Spec, b.SpecValue
from dbo.#Item a
join dbo.#ItemSpecs  b
on b.ItemID =...

This looks like a simple pivot.

SELECT ItemId, ItemName,  Dimn as Dimension, Model, WarehouseLoc
FROM (
select a.ItemID, a.ItemName, b.Spec, b.SpecValue
from dbo.#Item a
join dbo.#ItemSpecs  b
on b.ItemID =...

The next step of the process aggregates the data by date hence partitioning by date.  That runs 18 - 20 times per day.  We also purge data after 3-4 months...

And deleting 500k records takes ~15 seconds which I don't think is too bad.  Inserts are similar

We have three working ideas:

• reduce the number of rows going through the process

...

posting the query plan as a zip.

Also: https://www.brentozar.com/pastetheplan/?id=r12r1TEo0

@pitterson.  We are looking at re-engineering the process but are trying to estimate the benefits.

What do you mean by performance modeling and scenario analysis?  What tools and techniques do you...

Attached is the SQL query plan.

There are two main queries.  The first uses a date parameter and the second doesn't.  The second query is much higher cost that the first...

• This reply was modified 1 year, 1 month ago by Jez-448386.

That's exactly the point.  There are far too many variables for discussion here.  I haven't specifically asked "how do I tune this query?"  I've been asking "how do I evaluate...

Frederico,

How would you estimate the I/O cost of your idea?  How does that compare with Brian's idea of using a MERGE rather than a DELETE and INSERT?

That's the question I'm...

Yes Brian you are right.  We delete before inserting again.  Historically, the team found than using a MERGE statement (because sometimes we need to insert a new record so it...

This is the query with the plan (names have been changed):

DECLARE @DateColumn  DATE = '2024-07-01';

DELETE s 
FROM [Schema].TableName s 
INNER JOIN #WorkingTable q
ON q.Column1 = s.Column1
AND...

• This reply was modified 1 year, 1 month ago by Jez-448386.

The database server is 384 GB.  SSIS is on a separate server.

The infrastructure and database is provided "as a service".  There is only one level up from where we are  with 500 GB and we could, potentially, move to that.  However,...

Thanks for the reply.

Our highest resource use query is this:

Delete a
FROM Big.Table a
JOIN #SmallTable b 
on b.<<columns>> = a.<<clustered index columns>>
WHERE a.Date = @Date

Big.Table is 3 billion rows...

I have found an answer.

When I recreated the script component, the Microsoft.SqlServer.ManagedDTS reference was missing.  Added the reference and it now works fine.