Viewing 15 posts - 1 through 15 (of 215 total)
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 =...
January 22, 2025 at 4:13 pm
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 =...
January 22, 2025 at 4:13 pm
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...
August 27, 2024 at 7:48 am
And deleting 500k records takes ~15 seconds which I don't think is too bad. Inserts are similar
We have three working ideas:
...
August 22, 2024 at 1:52 pm
posting the query plan as a zip.
Also: https://www.brentozar.com/pastetheplan/?id=r12r1TEo0
August 22, 2024 at 1:40 pm
@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...
August 22, 2024 at 10:32 am
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...
August 22, 2024 at 10:28 am
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...
August 20, 2024 at 9:33 am
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...
August 20, 2024 at 7:05 am
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...
August 19, 2024 at 4:47 pm
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...
August 19, 2024 at 7:56 am
The database server is 384 GB. SSIS is on a separate server.
August 15, 2024 at 9:40 pm
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,...
August 15, 2024 at 4:08 pm
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...
August 15, 2024 at 1:19 pm
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.
November 4, 2021 at 11:33 am
Viewing 15 posts - 1 through 15 (of 215 total)