Please select another system to include it in the comparison. Oracle System Properties Comparison Microsoft SQL Server vs. > Index Scan using product_erecept_x_okres_nazev on product_erecept (cost=77.07 rows=689131 width=4) (actual time=1. > Microsoft SQL Server vs. Query with IN: explain analyse select sum(mnozstvi_rozdil) Index Cond: ((okres_nazev)::text = 'Pardubice'::text) Index Cond: ((okres_nazev)::text = 'Brno-město'::text) Recheck Cond: (((okres_nazev)::text = 'Brno-město'::text) OR ((okres_nazev)::text = 'Pardubice'::text)) Query with OR: explain analyse select sum(mnozstvi_rozdil) I'll add info for PostgreSQL version 11.8 (released ). So if you use the OR operator that optimized elaboration will not be used in your case. Probably an optimizer may not implement optimized IN evaluation process, but this doesn't exclude that one time it could happen(with a database version upgrade). If you use the OR operator the optimizer may not consider that you're always using the = operator on the same value and, if it doesn't perform a deeper and more complex elaboration, it could probably exclude that there may be only = operators for the same values on all the involved conditions, with a consequent preclusion of optimized search methods like the already mentioned binary search. In addition, consider that the conditions may not always compare the same value.įor the query optimizer it's easier to to manage the IN operator because is only a construct that defines the OR operator on multiple conditions with = operator on the same value. Greater, greater or equal, less, less or equal, LIKE and some more like the oracle REGEXP_LIKE. Here is a list of what you can use with OR but that are not compatible with IN: The OR operator needs a much more complex evaluation process than the IN construct because it allows many conditions, not only equals like IN. Predicate Information (identified by operation id):ġ - filter("OBJECT_NAME"='DBMS_LOB' OR "OBJECT_NAME"='DBMS_REGISTRY' OR | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | WHERE object_name IN ('DBMS_STANDARD', 'DBMS_REGISTRY', 'DBMS_LOB' ) Įven though the query uses IN, the Execution Plan says that it uses OR:. CREATE TABLE performance_test AS ( SELECT * FROM dba_objects ) I tried it with Oracle, and it was exactly the same. The best way to find out is looking at the Execution Plan. Results may vary on other databases and on other data. Adding more terms makes the difference larger. So in this case the method using OR is about 30% slower. When the column is indexed there is no discernable difference in performance - both are nearly instant. I tried both on a MySQL with 1000000 rows. The best way to know is to profile both on your database with your specific data to see which is faster. I would imagine that OR evaluates them one by one in no particular order. WHERE foo = 'a' OR foo = 'b' OR foo = 'c'Īccording to the manual for MySQL if the values are constant IN sorts the list and then uses a binary search. I assume you want to know the performance difference between the following: WHERE foo IN ('a', 'b', 'c')
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |