Can adding an index make a non SARGable query SARGable?
sqlservercode.blogspot.com
Can adding an index make a non SARGable query SARGable?
1–10 of 41 posts
Re: Can adding an index make a non SARGable query SARGable?
#2Databases can be a black box from the developer perspective. This solution might perform differently across a number of variables:
Database management solution (sql server, postgres, mysql, etc) Database version (mysql 5.6, 5.7, 5.8, mariadb, etc)
This method of finding whether your hunch works or not is the easiest alternative other than trying to read the code (or documentation, which many db engines have excellent documentation for this sort of thing).
Re: Can adding an index make a non SARGable query SARGable?
#3I had no idea what sargable was. I guess it means using an index? Is this a sql server specific term? https://www.sqlshack.com/how-to-use-sargable-expressions-in-...
Why not just use a function index?
Re: Can adding an index make a non SARGable query SARGable?
#4SQL Server Enterprise will also allow you to create a view with somewhat complex joins and conditions, index it, and then other queries can benefit from the indexed joins.
It’s things like this that keep us paying $millions for SQL Server Enterpise vs. Postgres.
Re: Can adding an index make a non SARGable query SARGable?
#5Could you not just add an index on `Right(SomeColumn,3)`? Creating indexes for common queries seems less weird than adding columns...
Re: Can adding an index make a non SARGable query SARGable?
#6Could you not just add an index on `Right(SomeColumn,3)`? Creating indexes for common queries seems less weird than adding columns...
The issue with nonsargable expressions is not that you can’t create an index that contains what would be the output of that expression. It’s that you are forced to do a row-by-row evaluation of each record in order to even utilize the corresponding index to do a seek/scan against. You basically force a scan of the smallest available index. And there are tons of commonly used , nonsargable expressions. Implicit conversions, string parsing, date parsing, isnull/coalesce.
Re: Can adding an index make a non SARGable query SARGable?
#7SQL Server Enterprise will also allow you to create a view with somewhat complex joins and conditions, index it, and then other queries can benefit from the indexed joins. It’s things like this that keep us paying $millions for SQL Server Enterpise vs. Postgres.
Actually the last time I checked, materialized views had a ton of restrictions on them, including things like not being able to use joins. Has that changed in recent versions?
Re: Can adding an index make a non SARGable query SARGable?
#8Databases can be a black box from the developer perspective. This solution might perform differently across a number of variables: Database management solution (sql server, postgres, mysql, etc) Database version (mysql 5.6, 5.7, 5.8, mariadb, etc) This method of finding whether your hunch works or not is the easiest alternative other than trying to read the code (or documentation, which many db engines have excellent…
Explain and explain analyze are very powerful tools for understanding query performance.
http://www.postgresql.org/docs/9.4/static/using-explain.html
(Blog post I wrote) http://jimkeener.com/posts/explain-pg
Re: Can adding an index make a non SARGable query SARGable?
#9Could you not just add an index on `Right(SomeColumn,3)`? Creating indexes for common queries seems less weird than adding columns...
The issue with nonsargable expressions is not that you can’t create an index that contains what would be the output of that expression. It’s that you are forced to do a row-by-row evaluation of each record in order to even utilize the corresponding index to do a seek/scan against. You basically force a scan of the smallest available index. And there are tons of commonly used , nonsargable expressions. Implicit conver…
Can you explain what sargable is? I've never used SQL server, but I'm sure a similar expression could be indexed in postgresql. (Not at a computer to check.)
Re: Can adding an index make a non SARGable query SARGable?
#10SQL Server Enterprise will also allow you to create a view with somewhat complex joins and conditions, index it, and then other queries can benefit from the indexed joins. It’s things like this that keep us paying $millions for SQL Server Enterpise vs. Postgres.
Actually the last time I checked, materialized views had a ton of restrictions on them, including things like not being able to use joins. Has that changed in recent versions?
You've been able to do joins in them for a decade. You just can't do self-joins. (But there are indeed a ton of restrictions.)