0

Good day All

I have a Challenge. I have the Following StoredProcedure that is doing the Following

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp]'))
drop table [temp]

--Creation of Temp1
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE
into temp FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN tbl_Cycles_Staff C
ON C.IDL = MTN.ID

All this takes less than a second with (17672 row(s) affected)

and its Cool and it Bring records like this

ID              DESCR           CYCLETEMPLATE
===============================
7620	     Campbell P Dr	26
7620	     Campbell P Dr	27
7620	     Campbell P Dr	28
7620	     Campbell P Dr	29
7620        Campbell P Dr	      31
7621	      Jones D Dr	        23
7621	      Jones D Dr	        24
7621	      Jones D Dr	        26
7621	      Jones D Dr	        28
7621	      Jones D Dr	        29
7621	      Jones D Dr	        33
7621	      Jones D Dr 	       34

This is Cool, So now i want to Have one Campbell P Dr wilth all the CycleTemplate Feld on one line and not Duplicated and sepated by a "," So in Simple it Should be like this

ID              DESCR           CYCLETEMPLATE
===============================
7620	Campbell P Dr	26,2728,29,31
7621	Jones D Dr	23,24,26,28,29,33,34

So to do this i created a user defined function to Remove the Duplicates in a Table Level, the Function looks like this

/*This Userdefined Function is used to Remove Duplicates*/

ALTER FUNCTION [dbo].[DistinctList]
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX)
DECLARE @Pos INT
DECLARE @rList VARCHAR(MAX)

SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList 
VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END

And the above function remove the first above mentioned problem and place the cycletemplate like this. Now

ID              DESCR           CYCLETEMPLATE
===============================
7620	Campbell P Dr	26,27,28,29,31,26,26,,28,28
7621	Jones D Dr  	23,24,26,28,29,33,34,34,34,34,34

Now as you can see the Duplicates on the Row level are removed but not the Field level are Still there. So i created the Following User Defined Function that Removes the Duplicates in a Field Level like this

ALTER FUNCTION [dbo].[GetCycle_Timetable] (@Descr Varchar(50))  
RETURNS Varchar(500)
AS  
BEGIN
Declare @RetStr as varchar(500)
DECLARE @Cycle Int  --<-- Assuming Cycle field  is of Type Integer
--Creating a Cursor-- 
Declare TmpCur Cursor For 
select CyCleTEMPLATE From temp Where Descr = @Descr
Open TmpCur  --open the cursor
Set @RetStr='' --initialize the string to nothing
Fetch Next 
From TmpCur Into @Cycle --take the cycles into the cursor variable 
While @@Fetch_status=0
Begin
	Set @RetStr = @RetStr +
	 Case when @RetStr=''
   then
		 '' else
   ' ' End 
  + Cast(@Cycle as varchar)
	Fetch Next From TmpCur Into @Cycle
End
Close TmpCur 
Deallocate TmpCur 
return (@RetStr)
END

and my sp i conbine this and Call it like this

Select DISTINCT Descr AS [Staff],[B]dbo.DistinctList[/B](.dbo.[[B]GetCycle_Timetable[/B]](Descr),'') As [Cycles] 
into Temp2 From temp

and it worked Perfectly and brought desired Results as i shown in the Beginning. Now My Problem with this it Runs for 3 Minutes and in an ASP.net page it times out.

Is there another way that i could have dont this ?

Please Help me with your Example Code by Changing the statement in your way.


Thank you

3
Contributors
4
Replies
5
Views
8 Years
Discussion Span
Last Post by vuyiswamb
0

This may correct the issue if implemented correctly, and at the same time eliminate the need of your functions.

To correct the function and not use an Over Clause, just make sure you search for that particular string/value in the field before you insert into it into the destination column.

And please, try to stay away from cursors at all costs!!

1

Your biggest problem here is that you're calling the function to concat a string in the same query as you're distincting() the records. Lets say you have 5 unique people in the list but a total of 90 records then the function should only run 5 times but in this case it is running 90 times. That means you have an exponentially negative performance impact as your data scales up. I have demonstrated a way to concat a string people don't know about and I tried to set up an accurate test environment. Here you go:

IF OBJECT_ID('People', 'U') IS NOT NULL DROP TABLE People
IF OBJECT_ID('PeopleCycle', 'U') IS NOT NULL DROP TABLE PeopleCycle
IF OBJECT_ID('PeopleJoin', 'U') IS NOT NULL DROP TABLE PeopleJoin
IF OBJECT_ID('Concat', 'FN') IS NOT NULL DROP FUNCTION dbo.Concat

IF OBJECT_ID('TempDetail', 'U') IS NOT NULL DROP TABLE dbo.TempDetail
IF OBJECT_ID('TempSummary', 'U') IS NOT NULL DROP TABLE dbo.TempSummary

GO

Create Table People
(
  PeopleId int identity(1000, 1) PRIMARY KEY NONCLUSTERED,
  Name varchar(50),
)


Create Table PeopleCycle
(
  PeopleCycleId int identity(1000, 1) PRIMARY KEY NONCLUSTERED,
  PeopleId int NOT NULL,
  CycleId int,
)

--This is a useless table to demonstrate the join then distinction
Create Table PeopleJoin
(
  PeopleJoinId int identity(1000, 1) PRIMARY KEY NONCLUSTERED,
  PeopleId int NOT NULL
)


GO

CREATE FUNCTION dbo.Concat (@Name varchar(50))
RETURNS varchar(8000)
WITH EXECUTE AS CALLER
AS
BEGIN


Declare @s varchar(8000)
Set @s = ''

Select @s = @s + IsNull(',' + Cast(CycleId as varchar), '')
From TempDetail (NOLOCK)
Where TempDetail.Name = @Name
Group By CycleId
Order By CycleId

IF (@S IS NOT NULL) AND (@S <> '') Set @S = SubString(@s, 2, Len(@S)-1)

RETURN @S

END

GO


Declare @PeopleId int

SET NOCOUNT ON

Insert Into People (Name) Values ('Scott')
SET @PeopleId = Cast(SCOPE_IDENTITY() as int)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 1)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 2)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 3)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 4)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 5)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 6)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)

Insert Into People (Name) Values ('Ken')
SET @PeopleId = Cast(SCOPE_IDENTITY() as int)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 7)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 8)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 9)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 10)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 11)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 12)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)

Insert Into People (Name) Values ('Jake')
SET @PeopleId = Cast(SCOPE_IDENTITY() as int)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 13)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 14)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 15)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 16)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 17)
Insert Into PeopleCycle (PeopleId, CycleId) Values (@PeopleId, 18)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)
Insert Into PeopleJoin (PeopleId) Values (@PeopleId)

SET NOCOUNT OFF

GO

--Create the first table with all the pertinent cycle ids
Select Name, CycleId As CycleId
Into TempDetail
From People (NOLOCK) Inner Join PeopleJoin (NOLOCK) On (People.PeopleId = PeopleJoin.PeopleId)
                     Inner Join PeopleCycle (NOLOCK) On (People.PeopleId = PeopleCycle.PeopleId)


--Lets create a summary table. You dont want to do the aggregation on the join because
--it will run for every row that is joined even though you distinct them out later and
--get the same result. That murders performance and doesn't scale.
Select Name, Cast(NULL as varchar(8000)) As CycleIdList
Into TempSummary
From TempDetail (NOLOCK)
Group By Name
Order By Name

Update TempSummary
Set CycleIdList = dbo.Concat(TempSummary.Name)

Select *
From TempSummary (NOLOCK)
Order By Name
Votes + Comments
The best Answer, Thanks for your Hardwork.
0

Oh, and the results:

Name   CycleIdList
------ ------------------
Jake   13,14,15,16,17,18
Ken    7,8,9,10,11,12
Scott  1,2,3,4,5,6
0

This is the Best Answer i have ever got from Daniweb. Am a C#,ASP.NEt Programmer and do a lot of SQL but never went this deep, and most of the time am the one helping others and i needed help and this is the Best answer i have ever got from this Forum.

Thank you and its working like charm :)

Kind Regards

Vuyiswa Maseko

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.