Recently someone brought a tweet to my attention regarding STRING_SPLIT and how performance of it was faster than table valued parameters (TVP). As you may have read in one of my prior posts TVP’s have issues related to lack of good row estimates.  This can be fixed with a little extra work.  STRING_SPLIT caught my attention because I was wondering how it comes up with row estimates.  I did some experimentation and found a couple things worth mentioning.

50!

What’s the significance of that number?  It seems to be the estimated number of rows that comes out of STRING_SPLIT regardless of how many values are passed into it.  I did some tests where I passed just a few parameters, and others where I passed over 3000 – either way the optimizer assumed there were 50 rows.

Why can this be problematic?  This can generate plans that are not optimized if there is a lot of variance in the number of parameters passed – especially if you are not passing 50 values.

To prove this out I created two different queries.  One which used the output of SPLIT_STRING and another which contained a list of values.

Query with  STRING_SPLIT

Query with list of values

I’m sure someone is bound to comment on the practicality of using an IN list like this.  The reason I started researching STRING_SPLIT was to reduce plan cache clutter from queries like this.  Since we didn’t see memory pressure  or churn of these queries in the cache we decided to leave them alone.  Also, the real world scenario used parameterized values (unlike my demo). But back to the main point of this post…

Here is statistics IO and TIME output of the query executing:

2,884,030 logical reads and about 3 seconds of CPU time.  The reason for this is the nested loop joins on the non clustered index and on the the key lookups on clustered index:

Since the optimizer thought we were only going after 50 account numbers (as opposed to the true value of 3971) it performed a lot more work by accessing the non clustered index on AccountNumber and subsequently the clustered index in order to perform Key Lookups.

This execution plan would make sense for 50 account numbers passed in.  Lets see how things look when the optimizer knows there are 3971 values being passed in.

The execution plan looks very different.  When we restructure the query with a list of AccountNumbers in the in clause, the optimizer has a much more realistic idea of how many values are being passed in.  As a result the optimizer decided to scan the CustomerTest table.  Something else worth noting – the optimizer now has enough information to recognize this query might benefit from an index.

The results of this significantly reduced the IO of the query:

The logical reads on this query are now only 3,768 + 36,351 = 40,119.  Additionally CPU time is now approximately cut in 1/3rd.

One more thing on STRING_SPLIT

If you intend to use SPLIT_STRING to join to non VARCHAR, CHAR, NCHAR, or NVARCHAR column types you will get an implicit conversion.  Generally implicit conversion are most problematic when they affect an index seek; however, to a lesser extent they affect cardinality estimates.  When converting to numeric types from an implicitly converted CHAR/NCHAR types index seeks are not impacted; however, it does tend to mess with cardinality estimates.  I joined the output of STRING_SPLIT to an int column and you can see the plan warning for this:

Am I saying STRING_SPLIT is all bad?

No, not at all.  In some scenarios it might turn out you don’t have enough variance in values passed to STRING_SPLIT to where it’s not an issue; however, if you can’t determine that, the safe bet is to use an intermediate #temp table.  I actually did that just to validate and you can see the plan looks more similar to that from the IN clause with values.  Additionally the overhead of the insert to the #temp table is nearly negligible (given you don’t have any underlying tempdb contention issues).  In my example the #temp table insert only added 28 milliseconds to the run.

And what about that missing index?

I figured I would add the missing index and see  the effect on performance.  This is the results of running it with the IN list values:

The index got picked up and significantly reduced the logical IO and cpu/elapsed time.  Something else worth mentioning – if you run the STRING_SPLIT query it does not pick up the newly created index.  Only the temp table version of STRING_SPLIT will pick up the index.