All geek questions in one place

SQL optimization and disjunctive normal form

So, I wrote a query in Visual Studio 2010 (I mean, I opened the server explorer, right-click on the server and chose New Query). The request includes a condition

A AND B AND C AND D AND E AND F AND (G OR H)

which is a conjunctive normal form (CNF). When I ran the query (attached to MSSQL Server 2008), it changed the text to

 A AND B AND C AND D AND E AND F AND G OR A AND B AND C AND D AND E AND F AND H

which is a disjunctive normal form (DNF).

From the small number I found online, it looks like DNF allows SQL to run conjunctives separately and merge them at the end.

However, for something similar, with so many repetitive conditions, does DNF really give an edge over CNF? If this is not the case, how can I make the optimizer accept the condition as is? If so, should I write the request in the application code in the form of CNF, because it is shorter and more accurate, or in the form of DNF, because it saves time for the optimizer?

+6
source share
2 answers

I do not know about the relative advantages of DNF / CNF in this situation or even how to force the optimizer in this way.

Generally speaking, you do not want to force the optimizer to perceive your "perceived", "current", optimizations compared to the one that it will generate (there are exceptions to this, but they are usually rare). This is largely due to the fact that the "best" optimization may change over time, as a side effect of other actions (for example, adding an index). If you force the optimizer to accept a certain optimization, you block it in this way, even if the new one may work better.

Given this, you should write the query in the form that is easiest to read and maintain (CNF), and let the optimizer change it if necessary - this is the whole essence of SQL, which is a declarative language, to allow the optimizer to extinguish with things as needed.

+2
source

On top of my head, I wonder about indexing on G or H. If G was indexed, but H is not ... maybe the disjunctive would make more sense.

In any case, you can run the performance analyzer yourself to see the net performance difference.

In addition, here are some studies that you could access if you want to dive: Material for reuse: http://ieeexplore.ieee.org/xpl/freeabs_all.jsp?arnumber=842265&abstractAccess=no&userType=inst

0
source

Source: https://habr.com/ru/post/893511/

More articles:


All Articles