Timeline for Slow Cross Apply Query Needs Optimization
Current License: CC BY-SA 4.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 11, 2023 at 15:42 | comment | added | Charlieface |
You can sanitize the query plan using SentryOne Plan Explorer. We can't hepl you further without the plan. Also what is the definition for the @Data1 table variable?
|
|
| May 11, 2023 at 15:42 | comment | added | FlyFish | @JonathanFite I tried the inner join and it increased processing time. | |
| May 11, 2023 at 13:16 | comment | added | FlyFish | I added an additional index and have decreased the time by about 30 seconds. The elements can have any number of values. I'll take a look at @JonathanFite suggestion and post back. | |
| May 11, 2023 at 13:14 | answer | added | Charlieface | timeline score: 0 | |
| May 11, 2023 at 13:12 | comment | added | Jonathan Fite | How many elements are in each delimited parameter? If you have many elements (10+) then an INNER JOIN on the output of STRING_SPLIT might perform better. | |
| May 11, 2023 at 12:46 | comment | added | FlyFish | Unfortunately I can't upload the plan because it contains specific details about the database. I sanitized the query above. I changed all of the SplitParmList to STRING_SPLIT and the performance DECREASED. It went from 4:07 min:sec to 6:37 min:sec. | |
| May 11, 2023 at 12:42 | comment | added | J.D. | Can you please add the actual execution plan to your Post? You can upload it to Paste The Plan and add the link. Though Larnu is probably pretty spot on, on where your bottleneck lies. | |
| May 11, 2023 at 12:08 | comment | added | Thom A |
I really suggest you get rid of that function; it's not performant. A Tally or XML in an inline table value function would be far better, and a CLR splitter even better. Of course, you have access to STRING_SPLIT though, so you should really be using that now.
|
|
| May 11, 2023 at 12:06 | comment | added | FlyFish | @Larnu I wasn't aware of STRING_SPLIT. This function was created a long time ago, and we have just be reusing it. As for indexes, that is the only one on the table. | |
| May 11, 2023 at 11:57 | comment | added | Thom A | Is that your only index? That doesn't look like a good Clustered Index candidate to me; I doubt that all those columns will be always ascending which could mean you suffer fragmentation pretty quickly. What nonclustered indexes do you have? | |
| May 11, 2023 at 11:57 | comment | added | Thom A |
That function is going to be terrible for performance. Multi-line table value functions are known to perform poorly as are WHILE loops, you are using both and have multiple calls to the function; that would bring any well written query to a very slow slog. Why aren't you using STRING_SPLIT or a table type parameter, as mentioned?
|
|
| May 11, 2023 at 11:43 | history | edited | FlyFish | CC BY-SA 4.0 |
Responded to questions
|
| May 11, 2023 at 11:27 | comment | added | Thom A |
"The Data table is indexed" Without knowing the definition of the index(es) this doesn't mean a lot; a single clustered index on Effectivity is unlikely to help, for example. Can you add their definitions for the question to please.
|
|
| May 11, 2023 at 11:25 | comment | added | Thom A |
What is SplitParmList? Can you include the definition of the function? Why aren't you using STRING_SPLIT or (probably better) a table type parameter?
|
|
| S May 11, 2023 at 11:24 | review | First questions | |||
| May 11, 2023 at 11:34 | |||||
| S May 11, 2023 at 11:24 | history | asked | FlyFish | CC BY-SA 4.0 |