question

Ondrej-1562 avatar image
0 Votes"
Ondrej-1562 asked ErlandSommarskog answered

Random long run of the SQL Query

Hello,

I have a database where I have a select query which runs slowly just for some parameters. The database has actual statistics (full scan), I removed old execution plans (did not restart, just remove these plans), no missing indexes were found but for some parameters (especially parameter P4 and 2 values for this parameter), this command runs for several minutes, but for other it takes a few seconds.
Execution plan can be found here:
https://www.brentozar.com/pastetheplan/?id=HkWJSj6SO

I know, that the SQL is not ideal and also join over 6 tables is not ideal, but it is hard to say, that the design is not optimal when there is problem with just 2 parameters. I suspect an issue with parameter sniffing, but I am not sure about that (how to solved). I would use a workaround where I would generate a special table with agregated results, but I hope, that there exists a solution for this issue/situation. Can you please provide any ideas how to do that?

Thank you for any help or any guide to solve this issue.

Regards
Ondrej


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ondrej-1562 avatar image
0 Votes"
Ondrej-1562 answered Ondrej-1562 published

Here is a plan which was running for about 2 hours without any result:

https://www.brentozar.com/pastetheplan/?id=SJFBuE2Uu

Looks like the optimizer does not do a good job. :-(

8TiZ9CT.png


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

If I understand your recommendation... Rework the database structure and use manually written queries. Is that correct?

Certainly the latter. And maybe the former to. In the view definition I find:

and ((m.sercis = p.sercis and m.sarze = '') or CONCAT('&', m.sarze) = p.sercis)

This condition is certainly problematic, and I would look into avoid this - if I had had any understanding of what all this is about.

Here are also some problematic conditions:

and (m.mcislo = p.mcislo or (m.mcislo is null and p.mcislo is null))
 and (m.msercis = p.msercis or (m.msercis is null and p.msercis is null))
 and (m.druh = p.druh or (m.druh is null and p.druh is null))

But these can be rewritten as:

and exists (select m.mcislo, m.msercis, m.druh
            intersect
           select p.mcislo, p.msercis, p.druh)

This could permit an index on these columns together with cislo and naklstr and ucokr to be used. That is, you should have a composite index on (cislo, naklstr, ucokr, mcislo, msercis, druh) on both porovnat and majtek1.

Also, in the view definition, change UNION to UNION ALL.

No guarantees that you will see any benefit of these suggestions at all. It was only some low-hanging fruit that I spotted.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.