Recursive Query Optimization

Question

question

Bhuvans asked Jan 2, '13

Recursive Query Optimization

Hi, I had a table called Person with the columnname (person_id, person_name,person_permenant_address). I need to create a model . so to reduce my effort i planned to create a query which returns an output columns as (PersonId, PersonName,PersonPermenantAddress) (CAMEL CASE Words). So for this i used a below query as a temporary solution and which handled upto 3 '_' underscores...


WITH CTE AS
(
Select 
REPLACE(
SUBSTRING(UPPER(Column_Name),1,1) + SUBSTRING(Column_name,2,LEN(COLUMN_NAME)),
'_' + LOWER(
SUBSTRING(UPPER(
REPLACE(SUBSTRING(Column_Name,CHARINDEX('_',COLUMN_NAME),LEN(COLUMN_NAME)-CHARINDEX('_',COLUMN_NAME)),'_','')
),1,1)
)
,
SUBSTRING(UPPER(
REPLACE(SUBSTRING(Column_Name,CHARINDEX('_',COLUMN_NAME),LEN(COLUMN_NAME)-CHARINDEX('_',COLUMN_NAME)),'_','')
),1,1)
) as ColumnName

 from information_schema.columns where table_name like 'Person'
 )
 Select 
  REPLACE(
InnerColumnName,
'_' + LOWER(
SUBSTRING(UPPER(
REPLACE(SUBSTRING(InnerColumnName,CHARINDEX('_',InnerColumnName),LEN(InnerColumnName)-CHARINDEX('_',InnerColumnName)),'_','')
),1,1)
)
,
SUBSTRING(UPPER(
REPLACE(SUBSTRING(InnerColumnName,CHARINDEX('_',InnerColumnName),LEN(InnerColumnName)-CHARINDEX('_',InnerColumnName)),'_','')
),1,1)
) 
 
  from
 (
 Select 
 
 REPLACE(
ColumnName,
'_' + LOWER(
SUBSTRING(UPPER(
REPLACE(SUBSTRING(ColumnName,CHARINDEX('_',ColumnName),LEN(ColumnName)-CHARINDEX('_',ColumnName)),'_','')
),1,1)
)
,
SUBSTRING(UPPER(
REPLACE(SUBSTRING(ColumnName,CHARINDEX('_',ColumnName),LEN(ColumnName)-CHARINDEX('_',ColumnName)),'_','')
),1,1)
) 


 AS InnerColumnName 
FROM CTE

) AS AAA

Can we optimize this to implement the things in a more genric way, so that it can handle n number of underscores in a query

t-sql cte query-optimisation

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2013-01-02T10:17:32Z

Usman Butt answered Jan 2, '13

I have been in such situation before, so it was easier for me to address this problem. Following could be the required solution (Comments are there to make you understand the code) USE [tempdb] GO --MAIN FUNCTION CREATION CREATE FUNCTION dbo.RemoveUnderscoreAndMakeCamelCap (@String NVARCHAR(4000)) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover NVARCHAR(4000). WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of -- rows right up front for both a performance gain and -- prevention of accidental "overruns". SELECT TOP (ISNULL(DATALENGTH(@String),0)) --Keeps trailing spaces ROW_NUMBER() OVER (ORDER BY [N]) FROM E4 ) --===== Force the first character of the string to upper case. -- Obviously, non-letter values will not be changed by UPPER. SELECT CamelCapString = REPLACE(UPPER(LEFT(@String,1)) + ( --=== If the current character in the given string is an underscore then -- concatenate the next character as an UPPER case character. -- Otherwise, make it lower case character. SELECT CASE WHEN SUBSTRING(@String, t.N , 1) = N'_' THEN UPPER(SUBSTRING(@String, t.N+1, 1)) ELSE LOWER(SUBSTRING(@String, t.N+1, 1)) END FROM cteTally t --No WHERE clause needed because of TOP above ORDER BY t.N FOR XML PATH(''), TYPE ).value('text()[1]', 'nvarchar(4000)'),N'_',N'') ; GO ---TABLE CREATION WITH COLUMN NAMES IN WRONG CAPS CREATE TABLE [dbo].Person ( person_id INT, person_name NVARCHAR(50), person_permAnent_address NVARCHAR(200), person_contAct_number VARCHAR(20) ) GO --REQUIRED QUERY SELECT STUFF( (SELECT ', ' + [CamelCapString] --CAMEL CAP STRING FROM [INFORMATION_SCHEMA].columns CROSS APPLY dbo.[RemoveUnderscoreAndMakeCamelCap]([COLUMN_NAME]) RUAMCC WHERE table_name = N'Person' FOR XML PATH('')) --MAKE COMMA DELIMITED STRING USING XML PATH , 1, 2, N'') ColumnsNamesInCamelCapAsCommaDelimitedString -- REMOVE FIRST TWO LETTERS i.e. ", " GO ---CLEAN UP DROP TABLE [dbo].[Person] DROP FUNCTION [dbo].[RemoveUnderscoreAndMakeCamelCap]

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question