Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Can adding an index make a non SARGable query SARGable?

sqlservercode.blogspot.com

Post reply on HN
Print view View original

21–30 of 41 posts

Re: Can adding an index make a non SARGable query SARGable?

#21

Post by Pxtl »

Topgamer7 wrote: post #2

Databases 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…

Hah, if versions were enough for consistent performance I'd be in heaven.

I'm plagued by cases where the same schema with the same indices on same version of servers running on the same architecture gets different plans and performance.

Re: Can adding an index make a non SARGable query SARGable?

#22

Post by Pxtl »

hotsauceror wrote: post #6
Shish2k wrote: post #5

Could 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…

... if isnull/coalesce is really non-sargable, it's time for me to give up and switch to Mongo.

Re: Can adding an index make a non SARGable query SARGable?

#23

Post by Pxtl »

taspeotis wrote: post #4

SQL 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.

I think in all the dozens of times I've tried it I've never had a view pass their draconian requirements for indexing.

There's always a self-join or a left join or a sqlclr function or a format or an unsupported aggregate or something else that was a critical thing that I really needed in the index.

If the Microsoft SQL Server team really thinks this feature is a critical selling point, maybe they should work more on it since they were introduced back in like SQL Server 2005 and still have the same miserable whips-and-chains-bondage level masochism experience.

Re: Can adding an index make a non SARGable query SARGable?

#24

Post by Pxtl »

BrentOzar wrote: post #10
hotsauceror wrote: post #7

Earlier quoted context omitted.

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.)

They're basically only usable on a super blue moon lunar eclipse.

Re: Can adding an index make a non SARGable query SARGable?

#26

Post by cryptonector »

Topgamer7 wrote: post #2

Databases 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…

It's the price you pay for having a declarative language that lets the language implementation pick from a large universe of equivalent ways to actually compile and run the code. You now have to know how to tune the thing, and that requires having either specialist knowledge of the implementation in question, or the skills to find and apply that knowledge quickly.

The value this provides is making development very agile (in the dictionary sense of the word).

Re: Can adding an index make a non SARGable query SARGable?

#27

Post by marzell »

hotsauceror wrote: post #17
jwcacces wrote: post #14

Earlier quoted context omitted.

In postgres you could just make an index on the RIGHT(SomeColumn,3) expression. See https://www.postgresql.org/docs/current/sql-createindex.html > An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on upper(col) would allow the clause…

That’s actually pretty slick. I’d pay a fair bit of money for SQL Server to have computed indexes like that.

I mean, you could add a new computed column to a table and index that... It's really effectively the same thing, just more visible. Still requires a table scan and storage/maintenance of the index/column. I guess technically it may require additional storage but it's not radically different. Either way, still requires some foresight to implement it before your queries require it so neither is a magic bullet.

Re: Can adding an index make a non SARGable query SARGable?

#28

Post by wbl »

Pxtl wrote: post #21
Topgamer7 wrote: post #2

Databases 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…

Hah, if versions were enough for consistent performance I'd be in heaven. I'm plagued by cases where the same schema with the same indices on same version of servers running on the same architecture gets different plans and performance.

Regather the table statistics. If it was the same data I'd be shocked, but this isn't that surprising.

Re: Can adding an index make a non SARGable query SARGable?

#29

Post by grumpydba »

taspeotis wrote: post #4

SQL 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.

> It’s things like this that keep us paying $millions for SQL Server Enterpise vs. Postgres.

Using these millions to better design your applications is better in the long run. You can totally live without those with a good schema and better queries.

I think features like column store and always on are worth more that those band-aid features aimed at pleasing ugly ERPs.

Re: Can adding an index make a non SARGable query SARGable?

#30

Post by dragonwriter »

shawnz wrote: post #19
taspeotis wrote: post #4

SQL 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.

Isn't an mssql indexed view essentially a materialized view? Postgres also supports indexes on materialized views.

An MSSQL indexed view is like a materialized view, except it's not actually materialized beyond the index, as I understand it, and there are restrictions on the view definition that mat views don't have, and it updates automatically.
Post reply on HN