I use DelimitedSplit8K across multiple functions & sometimes it is used several times in the same function.
However, I am noticing a significant decrease in performance & so I want to double-check. I'd like to start using DelimitedSplit8K WITH (NOLOCK). Is there any possibility that two different queries can collide/overlap in data if I do that? Also, would I see a performance increase?
Lastly, I learned from another post that I should be using cross apply rather than inner join when I use DelimitedSplit8K. Are there other things to keep in mind?
I think it would be best to fully understand the oft-misunderstood (including myself) WITH NOLOCK
do some reading on it so that you personally fully understand what it does
https://www.sqlshack.com/understanding-the-impact-of-nolock-and-with-nolock-table-hints-in-sql-server/
2 Likes
Are you using it only with varchar (not nvarchar) data and up to 8000 bytes, not (max) len?
Otherwise, would need to see the query. The performance lag is almost certainly from something else in the query and not the splitter.
2 Likes
Most of the columns are declared as NVARCHAR(MAX). Would it help if I convert them prior to using them with the splitter? It seems to me the same difference, but maybe not.
Also in terms of two queries colliding/sharing data if they both access the function at the same time, is that a ridiculous/improbable idea?
Edit - It's starting to look like whatever the side-effects of NOLOCK would be, they are irrelevant because NOLOCK won't help me in this situation anyway. Is that an accurate assessment?
Yes, you must use varchar(8000) not max to get best performance and accurate results from that splitter.
No, colliding/sharing will make no difference.
NOLOCK is likely irrelevant. Again, would need to see the specific query code to be sure.
1 Like
Did you change the DelimitedSplit8K function to accept varchar(max) - or is it set to varchar(8000)? If it is still set to varchar(8000) then anything you pass will be implicitly converted and truncated.
If you changed it - that is almost certainly contributing to your performance issues.
If you have a column that exceeds 8000 characters and you must split it - then that function is not the right function to be using. I am not aware of any splitters that perform well with the MAX data types - except maybe a CLR splitter.
I believe someone did create nvarchar/(max) version(s) of this specific splitter function, but I don't know the names of them off the top of my head.
Yes - and they all suffer performance issues from what I recall, specifically due to the MAX data type. But - unless the OP responds back there really isn't anything further we can recommend.