November 20, 2009 at 11:01 pm
chandrashekar,
It would be much better if you talked to the owners of the original table and got them to normalize that terrible CSV column. If you can't do that, you do NOT need a function call nor a While Loop to build a temporary normalized table for performance purposes. Please see the following article for all of that...
http://qa.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
November 21, 2009 at 11:55 pm
For people wishing to learn SET BASED T SQL Programming - I recommend this post whole heartedly. Every single word needs to be understood at the utmost microscopic detail. Many thanks to you Jeff for teaching people the why's and the wherefore's of SET BASED Programming. Thanks to your lucid style and an amazing grasp of the concept not just at the theoretical level but also at the practical level where most beginners like me get stuck - most beginners like myself would find this post so so so very helpful.
Your specific reiterations such as (Notice the start and end commas) and the again that 'notice that multiple selects are getting executed as opposed to a single select' make learning so much easier, direct and fun!.
I cannot thank you enough...
Regards
Saurabh.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
November 22, 2009 at 10:10 am
Wow! Thanks for the feedback, Saurabh!
--Jeff Moden
November 23, 2009 at 12:55 am
Jeff Moden (11/20/2009)
Nabha (11/20/2009)
MY bad! go with Vikas, that is the right solution for you.Heh... no it's not...that function has a While Loop in it and it's going to be slow. If there's any scalability involved on any of this, not a good idea to use that function.
Tally rules!:w00t: Thanks Jeff. 🙂
---------------------------------------------------------------------------------
November 23, 2009 at 1:07 am
Thanks jefff,
This article is very help full to me and thought me how to handle the performance during situation where we need to loop through each record, which is having records like my sample database.
Thanks a lot
November 23, 2009 at 1:28 am
Nabha (11/23/2009)
Jeff Moden (11/20/2009)
Nabha (11/20/2009)
MY bad! go with Vikas, that is the right solution for you.Heh... no it's not...that function has a While Loop in it and it's going to be slow. If there's any scalability involved on any of this, not a good idea to use that function.
Tally rules!:w00t: Thanks Jeff. 🙂
I too agree....Tally rules...Set based programming rules 😀
Thanks Jeff
-Vikas Bindra
November 23, 2009 at 10:35 am
Simple way to do this without function
Create table #t(t varchar(10))
INSERT INTO #t VALUES ('1,2,3')
INSERT INTO #t VALUES ('3,4,5,11')
select * from #t
where t like '%1%' and t not like '%11%'
November 23, 2009 at 10:59 am
Simple way to do this without function
Create table #t(t varchar(10))
INSERT INTO #t VALUES ('1,2,3')
INSERT INTO #t VALUES ('3,4,5,11')
select * from #t
where t like '%1%' and t not like '%11%'
🙂
INSERT INTO #t VALUES ('3,4,5,12')
select * from #t
where t like '%1%' and t not like '%11%'
---------------------------------------------------------------------------------
December 1, 2009 at 10:49 pm
Hi Jeff,
ALTER FUNCTION [dbo].[fnSplit](
@InputString varchar(8000)
,@Delimiter varchar(10)
)
RETURNS @TempTable table(
ID int identity(1,1)
,[VALUES] varchar(8000)
)
AS
BEGIN
DECLARE @length int
,@Index int
,@LastIndex int
,@counter numeric
SET @InputString = @InputString + @Delimiter
SET @length = len(@InputString)
SET @Index = 1
SET @counter = 1
WHILE (@counter < @length)
BEGIN
IF charindex(@Delimiter, @InputString, @Index) > 0
BEGIN
SET @lastIndex = charindex(@Delimiter, @InputString, @Index) - @Index
INSERT INTO @TempTable ([Values])
SELECT substring(@InputString, @Index, @lastIndex)
SET @Index = charindex(@Delimiter, @InputString, @Index) + len(@Delimiter)
END
SET @counter = @counter + 1
END
RETURN
END
How do i use tally table for the above function, as this is taking some time to execute the queries, and it will affect the performance of the application.
Please let me now how do i can dot it with tally table
Thanks & regards
Chandrashekar
December 3, 2009 at 12:11 am
I think the following is the better code to split the string seperated by comma.
Declare @inputString varchar(1000)
set @inputString='1,2,3,5,6,7,8'
while(charindex(',',@inputString)>0)
begin
print substring(@inputString,1,charindex(',',@inputString,1)-1)
set @inputString=stuff(@inputString,1,charindex(',',@inputString,1),'')
end
December 3, 2009 at 8:06 am
lbabichenko 80114 (11/23/2009)
Simple way to do this without functionCreate table #t(t varchar(10))
INSERT INTO #t VALUES ('1,2,3')
INSERT INTO #t VALUES ('3,4,5,11')
select * from #t
where t like '%1%' and t not like '%11%'
Or maybe this: :w00t:
select * from #t
where t like '%,1,%' or t like '1,%' or t like '%,1'
(DELETE FROM cheek WHERE bodypart = 'tongue') 😀
December 3, 2009 at 8:20 am
Pemmasani Srinivasa Rao. (12/3/2009)
I think the following is the better code to split the string seperated by comma.Declare @inputString varchar(1000)
set @inputString='1,2,3,5,6,7,8'
while(charindex(',',@inputString)>0)
begin
print substring(@inputString,1,charindex(',',@inputString,1)-1)
set @inputString=stuff(@inputString,1,charindex(',',@inputString,1),'')
end
Why do you think a loop is better and better than what?
--Jeff Moden
December 3, 2009 at 8:33 am
vikas bindra (11/23/2009)
Nabha (11/23/2009)
Jeff Moden (11/20/2009)
Nabha (11/20/2009)
MY bad! go with Vikas, that is the right solution for you.Heh... no it's not...that function has a While Loop in it and it's going to be slow. If there's any scalability involved on any of this, not a good idea to use that function.
Tally rules!:w00t: Thanks Jeff. 🙂
I too agree....Tally rules...Set based programming rules 😀
Thanks Jeff
Thank you both for the feedback. I really appreciate it... but the real problem is the denormalized table and unless Chandrashekar can convince folks that it's the wrong way to do it, we're going to be stuck with a function one way or another. Even using a high speed Table Valued Function that uses a Tally table to do the split is going to be slower than doing it correctly.
Nigel posted a nice easy way to do this but it's still going to mean that no index will be used correctly like when the table is properly normalized.
--Jeff Moden
December 3, 2009 at 8:34 am
chandrashekar.2512 (12/1/2009)
Hi Jeff,ALTER FUNCTION [dbo].[fnSplit](
@InputString varchar(8000)
,@Delimiter varchar(10)
)
RETURNS @TempTable table(
ID int identity(1,1)
,[VALUES] varchar(8000)
)
AS
BEGIN
DECLARE @length int
,@Index int
,@LastIndex int
,@counter numeric
SET @InputString = @InputString + @Delimiter
SET @length = len(@InputString)
SET @Index = 1
SET @counter = 1
WHILE (@counter < @length)
BEGIN
IF charindex(@Delimiter, @InputString, @Index) > 0
BEGIN
SET @lastIndex = charindex(@Delimiter, @InputString, @Index) - @Index
INSERT INTO @TempTable ([Values])
SELECT substring(@InputString, @Index, @lastIndex)
SET @Index = charindex(@Delimiter, @InputString, @Index) + len(@Delimiter)
END
SET @counter = @counter + 1
END
RETURN
END
How do i use tally table for the above function, as this is taking some time to execute the queries, and it will affect the performance of the application.
Please let me now how do i can dot it with tally table
Thanks & regards
Chandrashekar
Chandrashekar,
I'll see if I can show you how to use a Tally driven TVF in 2k5 to do this when I take a break for lunch.
--Jeff Moden
December 3, 2009 at 10:19 pm
Thanks jeff..
mean while i will also try my side, as the website performance is decreasing i need to tune my sql queries. please help me in this.
Thanks & Regards
Chandrashekar
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply