tag:blogger.com,1999:blog-7079873813109086690.post8727575395295700723..comments2023-06-20T21:38:12.042-07:00Comments on ...juggling with .net & sql: SQL Server # TSQL to Convert STRING in PROPER formatvinay pugaliahttp://www.blogger.com/profile/07027426501611091260noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-7079873813109086690.post-45176807887155813742012-10-17T08:32:58.154-07:002012-10-17T08:32:58.154-07:00After more testing, I'm concluding that you ar...After more testing, I'm concluding that you are correct. My approach is to pivot and concatenate the string back together, which is killing performance.Marc Jellinekhttp://marcjellinek.wordpress.comnoreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-75661310861036005392012-10-17T00:37:55.834-07:002012-10-17T00:37:55.834-07:00Hello Marc,
It is really good to see your approac...Hello Marc,<br /><br />It is really good to see your approach and totally agree to the point that SQL Server is better at processing SET-BASED data. And believe me, I was also first planning to almost similar approach what you have suggested but with a small difference - i.e. I was planning to split the string/text to be converted by WORDS unlike your approach of splitting them by characters.<br /><br />I am mainly concerned on two points in your approach,<br />First is that the need for this kind of function will mostly be SCALAR based and hence will have to called for each row.<br /><br />Secondly, after converting your code to a scalar function and after comparing the results with my approach, the following were the figures - <br /><br />For 100 Rows, Comparative execution plan showed<br />My Approach : 50%<br />Your Approach : 50%<br /><br />For 1000 Rows, Comparative execution plan showed<br />My Approach : 50%<br />Your Approach : 50%<br /><br />For 3000 Rows, Comparative execution plan showed<br />My Approach : 50%<br />Your Approach : 50%<br /><br />Please share your views for the same.<br />vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-24945043434945147492012-10-16T06:23:06.741-07:002012-10-16T06:23:06.741-07:00;WITH CTE_String_By_Char AS -- break each s...;WITH CTE_String_By_Char AS -- break each string into numbered characters<br /> ( SELECT [s].[id] as [id], <br /> [s].[string] as [string], <br /> [n].[n] as [character_num], <br /> SUBSTRING([s].[string], [n].[n], 1) as [the_character]<br /> FROM [dbo].[strings] [s], <br /> [dbo].[Tally] [n]<br /> WHERE LEN([s].[string]) >= [n].[n]<br /> ), <br />CTE_Chars_and_Prev AS -- determine the previous character<br /> ( SELECT [main].[id], <br /> [main].[string], <br /> [main].[character_num], <br /> [main].[the_character], <br /> [prev].[character_num] as [prev_char_num], <br /> [prev].[the_character] as [prev_char]<br /> FROM [CTE_String_By_Char] [main]<br /> LEFT JOIN [CTE_String_By_Char] [prev] ON [main].[id] = [prev].[id] AND [main].[character_num] = [prev].[character_num] + 1<br /> ), <br />CTE_Chars_And_Caps AS -- if the previous character is NULL (the start of the string) or the previous character is a space, make the character uppercase<br /> ( SELECT [data].[id] as [id], <br /> [data].[character_num] as [character_num], <br /> [data].[the_character] as [the_character],<br /> CASE<br /> WHEN [data].[prev_char_num] IS NULL OR<br /> [data].[prev_char] = ' ' THEN UPPER([data].[the_character])<br /> ELSE [data].[the_character]<br /> END as [corrected_char]<br /> FROM [CTE_Chars_and_Prev] [data]<br /> ), <br />CTE_Concatenate AS -- put the strings back together again.<br /> ( SELECT DISTINCT [main].[id], <br /> REPLACE(REPLACE(REPLACE(REPLACE(<br /> (SELECT [caps].[corrected_char] [c] FROM [CTE_Chars_And_Caps] [caps] WHERE [caps].[id] = [main].[id] ORDER BY [caps].[character_num] FOR XML PATH ('')) , <br /> '', ''), '', ''), '', ''), ' ', ' ')<br /> as [proper_string]<br /> FROM [CTE_Chars_And_Caps] [main]<br /> )<br />SELECT [original].[id], <br /> [original].[string], <br /> [results].[proper_string]<br />FROM [dbo].[strings] [original]<br /> INNER JOIN [CTE_Concatenate] [results] ON [original].[id] = [results].[id]<br />ORDER BY [original].[id]Marc Jellinekhttp://marcjellinek.wordpress.comnoreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-66109272994428457282012-10-16T06:22:41.905-07:002012-10-16T06:22:41.905-07:00First, looping through a string will work, but it ...First, looping through a string will work, but it takes an iterative approach, which is less performant than a set-based approach (SQL Server excels at set-based operations).<br /><br />Second, the use of a function is correct from a functional decomposition perspective (isolate functionality into reusable pieces), but from a performance perspective is sub-optimal. You will trigger table or clustered index scans as the function is called for each row you feed it.<br /><br />Instead, leverage a tally table and a self-join, like this:<br /><br />First, you need a tally table. I don't much care how it's created or where its stored (if at all), but these are so useful that every instance should have one available. This will create a tally table with 65,536 entries, starting with 1.<br /><br />CREATE TABLE [dbo].[Tally]<br /> (<br /> [n] [bigint]<br /> )<br /><br />;WITH<br /> lv0 AS (SELECT 0 AS g UNION ALL SELECT 0), <br /> lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b), -- 4<br /> lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b), -- 16<br /> lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b), -- 256<br /> lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b), -- 65,536<br /> --lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b), -- 4,294,967,296<br /> Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv4)<br />INSERT INTO [dbo].[Tally]([n])<br />SELECT [n] FROM [Nums];<br />GO<br /><br />Next, throw in a few test strings:<br /><br />CREATE TABLE [dbo].[strings]<br /> (<br /> [id] [int] IDENTITY, <br /> [string] [varchar](max)<br /> )<br />GO<br /><br />INSERT INTO [dbo].[strings] ([string])<br />VALUES<br /> ('This is my first string'), <br /> ('This is my second string');<br />GO<br /><br />Now you just need a query that will leverage the Tally table to break each string into it's individual characters and check if the previous character is NULL (start of a string) or if the previous character is a space. If so, make the current character uppercase; then put the string back together.<br /><br />The CTE's should be commented well enough to let you know what's going on at each step. This could be further optimized, but a set-based operation will almost always perform better than an RBAR operation. (see next comment for the code)Marc Jellinekhttp://marcjellinek.wordpress.comnoreply@blogger.com