When it comes to weird performance problems, I’ve seen this cause all sorts of them. The worst part is that there are much smarter ways to handle this.
While Table Valued Parameters aren’t perfect, I’d argue that they’re far more perfect than the alternative.
The first issue I see is that these clauses aren’t ever parameterized, which brings us to a couple familiar issues: plan cache bloat and purge, and constant query compilation.
Even when they are parameterized, some bad stuff can happen.
Since We’re Here
Let’s say you take steps like in the linked post about above to parameterize those IN clauses, though. Does that fix all your performance problems?
You can probably guess. Hey look, index!
CREATE INDEX u ON dbo.Users(DisplayName, Reputation) WITH(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
Now, we’re gonna use a stored procedure I wrote to generate a long parameterized in clause. It’s not pretty, and there’s really no reason to do something like this outside of a demo to show what a bad idea it is.
Here’s how we’re gonna execute it:
EXEC dbo.Longingly @loops = 50, @debug = 0; EXEC dbo.Longingly @loops = 100, @debug = 0; EXEC dbo.Longingly @loops = 1000, @debug = 0;
If anyone thinks people sending 1000-long lists to an IN clause isn’t realistic, you should really get into consulting. There’s some stuff out there.
Here’s what the query plans for those executions look like:
The important detail here is that even with our super duper powered index, the optimizer decides that life would be too difficult to apply our “complicated” predicate. We end up with query plans that scan the index, and then apply the predicate in a Filter operator afterwards.
You’re not misreading, either. The 1000-value filter runs for roughly 7 seconds. Recompile hints do not work here, and, of course, that’d undo all your hard work to parameterize these queries.
Moral of the story: Don’t do this. Pass in a TVP, like the link above shows you how to do. If your ORM doesn’t support that, you might need to switch over to stored procedures.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.