Hello!

The question is about optimizing SQL queries.

Tell me, in what order should I write the conditions between AND operators? MSSQL 2005 server.

  • @Genniy, judging by your comment, you yourself know all this. Experiment with MSSQL and report back to the community. Everyone will be grateful and may want to discuss the results. - avp
  • I read about it of course. But in practice, I do not see the result and logic. In theory, you need to discard the largest piece initially, but why do they write everywhere that follows in growth. - Genniy
  • Give some specific examples (SQL) in the UPD to the question. - avp
  • one
    Honestly, I don’t know how in SQL Server, but in general, normal people always sort by load, which gives a condition (because in a normal logic handler, after a first false series, it breaks off a series). That is, if the ID field is PRIMARY, then it is illogical to do so: (mysql) SELECT * FROM table WHERE ( NAME LIKE '% A%') AND ( ID > 20) For in this case, check for the presence of "A" in the NAME field will be held throughout the table. - Sh4dow

2 answers 2

If all AND operators, then the order does not matter - this is approximately as multiplication. In this case, the DBMS itself must choose the optimal execution plan for the query.

  • And what about the increase in the probability of the truth of the condition ...? - Genniy

The optimizer can change the order of predicate execution, trying to reduce the size of the intermediate sample. I do not know the way to prevent him from doing so. Therefore, you can write in any order. :-) Well, keep the statistics up to date.