1

Okay this might sound a noob question, but SQL isn't my really strength, so I am requesting some help here.

I am trying to implement something, but I am concerned about performance issues.

The problem I am trying to fix is something like this:

I have a column with a lot of data separated by commas "," Something like this: data1,data2,data3,data57

What I need is looping through each piece of data separated by commas for all the records, and then do something with that single piece data, do you get it?

I found a solution that can actually help me, but I am worried about system performance, because I might need to make multiple calls to this function using different parameters!

Does a table is created on each call I made to the Table-Valued Function (UDF) or does the sql server saves it as cache? [maybe I would rather need a temporary table?]

Thank you for your help in advance!

Note: The data is not mine, and I should use it as is, so suggesting to change the database is out of question (however I know that would be the best scenario). a Note2: The purpose of this question/problem is to import initial data to the database, performance may not be a serious problem since it won't run many times, but still I wanna regard that issue, and do it the best way I can!

TiagoM
  • 3,458
  • 4
  • 42
  • 83
  • 1
    Yes. That function will create an entry in `tempdb` and populate it then dispose of it on each call. – TZHX Nov 24 '16 at 20:41
  • Alllrighhht... thanks for your comment! Please write a proper answer with that and I will be glad to accept it :) – TiagoM Nov 24 '16 at 20:44
  • By the way if you can provide a better solution to approach this problem, please be my guest, I would be grateful :) – TiagoM Nov 24 '16 at 20:44
  • Wait, why aren't you using [STRING_SPLIT](https://msdn.microsoft.com/nl-be/library/mt684588.aspx) since you're working in SQL Server 2016? – TT. Nov 24 '16 at 21:08
  • And with a little patience (say SQL Server 2018?) you'll be using [STRING_AGG](https://msdn.microsoft.com/en-us/library/mt790580.aspx) for the reverse. Until then you can do that with the [FOR XML PATH('')](https://stackoverflow.com/questions/35148472/how-for-xml-path-works-when-concatenating-rows/35172160#35172160) trick to concatenate strings back to a comma-separated string. – TT. Nov 24 '16 at 21:11

1 Answers1

2

User defined, table-valued functions that are composed of multiple statements, as the one you found is, will create an object in the tempdb system database, populate it and then dispose of it when the object goes out of scope.

If you want to run this multiple times over the same parameters, you might consider creating a table variable and caching the result in that yourself. If you're going to be calling it on different lists on comma-separated values though, there's not a great way of avoiding the overhead. SQL Server isn't really built for lots of string manipulation.

Generally, for one-off jobs, the performance implications of this tempdb usage is not going to be a major concern for you. It's more concerning when it's a common pattern in the day-to-day of the database life.

I'd suggest trying, if you can, on a suitably sized subset of the data to gauge the performance of your solution.

Since you say you're on SQL Server 2016, you can make use of the new STRING_SPLIT function, something like

SELECT t.Column1, t.Column2, s.value
FROM table t
CROSS APPLY STRING_SPLIT(t.CsvColumn, ',') s

May get you close to where you want, without the need to define a new function. Note, your database needs to be running under the 2016 compatibility level (130) for this to be available, simply running on SQL 2016 isn't enough (they often do this with new features to avoid the risk of backwards-compatibility-breaking changes).

TZHX
  • 5,291
  • 15
  • 47
  • 56
  • thanks for your answer, I am trying to run that query to check the result but it's not working so far, it says "Invalid object name 'STRING_SPLIT'". Also I had to change erase s.value, since 's' doesn't stands for any table, maybe it was a typo and you meant t ? thanks ! – TiagoM Nov 24 '16 at 21:49
  • 1
    Ok, so I meant to add s as an alias to the table returned by STRING_SPLIT; but it sounds like you don't have that. Can you check the compatibility level of the database? It might be set to 2014 (120) or 2012 (110), which would make STRING_SPLIT unavailable for you unless you can change it. – TZHX Nov 24 '16 at 21:53
  • I just notice from google that I need to run this command: "ALTER DATABASE TestAzureDB SET COMPATIBILITY_LEVEL = 130" – TiagoM Nov 24 '16 at 21:55
  • I will test this in my local db and let my corporate now about this dependency of compatibility level to make this able to run, if it's not possible for them I will have to find another solution, anyway I will test it and let you know, thanks mate! – TiagoM Nov 24 '16 at 21:56
  • the database is set to 120 just checked now, I tried to change my local db to 120 but still couldn't change it it returns me an error (and I am not trying at the dev db neither), sadly I will have to go with another approach... =/ – TiagoM Nov 24 '16 at 22:00
  • anyway, regarding the OP question here you clearly answered it with more than enough detail, I will accept your answer, my problem is a side problem, the performance question it got answered, thank you @TZHX . Happy coding ;) – TiagoM Nov 24 '16 at 22:02
  • 1
    @TiagoM I wouldn't give up on the UDF you mentioned as a possible solution, it probably will work fine for you. it's easy to get bogged down in looking for a perfect solution, which is often not available. :) good luck with it anyway. – TZHX Nov 24 '16 at 22:03
  • Yeah that's right I agree with you, I am already on it! Glad for your cooperation in this subject, good luck over there too ! :) – TiagoM Nov 24 '16 at 22:05
  • Ehehe don't be such a performance nazi ! :P there are good and bad ways to do stuff, but in this case since its an initial import, I will just go with whatever works :) – TiagoM Nov 24 '16 at 22:06