Timeline for SQL index with parent child design
Current License: CC BY-SA 4.0
17 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 16, 2021 at 4:16 | vote | accept | warrenk | ||
| Nov 11, 2021 at 20:16 | comment | added | warrenk | @J.D. just a select, which is less performant now. | |
| Nov 11, 2021 at 18:38 | comment | added | J.D. |
@warrenk Is that your DELETE and INSERT query?
|
|
| Nov 11, 2021 at 17:46 | comment | added | warrenk | @J.D. Here is a link to a basic query plan: brentozar.com/pastetheplan/?id=SJTR8Ccwt | |
| Nov 11, 2021 at 5:56 | history | edited | warrenk | CC BY-SA 4.0 |
added 4761 characters in body
|
| Nov 11, 2021 at 2:03 | comment | added | J.D. |
Sure thing, I don't mean to harp on it, rather just saying it could be correlative to your main issue as well, or maybe not. Could you also please include your INSERT and DELETE statements, and their correlating actual execution plans (you can upload the plans to Paste The Plan and then link them in your post)?
|
|
| Nov 11, 2021 at 1:43 | comment | added | warrenk | @J.D. I’m using a recursive CTE. Again, this component runs with a reasonable duration and it’s not really the focus of the question. (Though, I’m sure there is opportunity to improve it) | |
| Nov 11, 2021 at 0:19 | comment | added | J.D. | Well here's some perspective, recursion in SQL Server can be very performant. I've repeatedly ran recursive calculations on tables with ~1,000,000 rows in about 1 second total runtime, on modest hardware. To take minutes on the amount of data you're talking is either indicative of the same problem your DML queries are facing, or indicative of the way you architected your calculation queries possibly not being the most efficient relational implementation, such as using a cursor instead of a recursive CTE. Without knowing your calculation queries though, I can only assume it might be the former. | |
| Nov 10, 2021 at 20:11 | history | edited | warrenk | CC BY-SA 4.0 |
added 5105 characters in body
|
| Nov 10, 2021 at 20:07 | comment | added | warrenk | @J.D. Will update with table and index scripts. The primary calculation is very complicated and required the use of a recursive query. I am confident in the query that drives the result sets, and the time it takes for those to complete. When I run the calculation scripts to screen("SELECT"), they complete in full in a few minutes. As soon as I "INSERT INTO X" table, I see 20-30 minute wait times. | |
| Nov 10, 2021 at 15:46 | answer | added | David Browne - Microsoft | timeline score: 0 | |
| Nov 10, 2021 at 14:14 | answer | added | Yuri Levinsky | timeline score: 0 | |
| Nov 10, 2021 at 13:04 | comment | added | J.D. |
Agreed with Michael, your runtimes seem suspiciously slow. Knowing what resources your server is provisioned with would be helpful (feel free to update the post once you hear back from your infrastructure team), but that's not likely the issue. I'm addition to your Table and Index definitions (as Michael asked for), please provide the exact INSERT and DELETE statements you're using, as those will determine what the proper indexing should be (and may also provide a clue why your queries are running so slow). Even your calculations taking minutes on a table that size seems under-performant.
|
|
| Nov 10, 2021 at 13:01 | history | edited | J.D. |
edited tags
|
|
| Nov 10, 2021 at 10:48 | comment | added | Michael Kutz |
Please provide the CREATE TABLE statements and all CREATE INDEX statements. Also. Describe how you're doing the INSERTs. A system half that powerful should be able to add 1M rows to each table in <5m.
|
|
| S Nov 10, 2021 at 6:05 | review | First questions | |||
| Nov 10, 2021 at 9:09 | |||||
| S Nov 10, 2021 at 6:05 | history | asked | warrenk | CC BY-SA 4.0 |