MS SQL

Help with the Following SQl SP

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Mar 2007

Posts: 158

Reputation: vuyiswamb is an unknown quantity at this point

Solved Threads: 5

vuyiswamb

Offline

Junior Poster

Help with the Following SQl SP

Jun 11th, 2009

Good day All

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
ID              DESCR           CYCLETEMPLATE
===============================
    Campbell P Dr	26
    Campbell P Dr	27
    Campbell P Dr	28
    Campbell P Dr	29
      Campbell P Dr	      31
     Jones D Dr	        23
     Jones D Dr	        24
     Jones D Dr	        26
     Jones D Dr	        28
     Jones D Dr	        29
     Jones D Dr	        33
     Jones D Dr 	       34
ID              DESCR           CYCLETEMPLATE
===============================
    Campbell P Dr	26
    Campbell P Dr	27
    Campbell P Dr	28
    Campbell P Dr	29
      Campbell P Dr	      31
     Jones D Dr	        23
     Jones D Dr	        24
     Jones D Dr	        26
     Jones D Dr	        28
     Jones D Dr	        29
     Jones D Dr	        33
     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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
ID              DESCR           CYCLETEMPLATE
===============================
7620	Campbell P Dr	26,2728,29,31
7621	Jones D Dr	23,24,26,28,29,33,34
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
/*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
/*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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

Help with Code Tags

(Toggle Plain Text)

Select DISTINCT Descr AS [Staff],dbo.DistinctList(.dbo.[GetCycle_Timetable](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

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

•

Join Date: Apr 2006

Posts: 143

Reputation: cutepinkbunnies is an unknown quantity at this point

Solved Threads: 8

cutepinkbunnies cutepinkbunnies is offline

Offline

Junior Poster

Re: Help with the Following SQl SP

Jun 13th, 2009

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!!

•

Join Date: Feb 2009

Posts: 3,460

Reputation: sknake has much to be proud of

Solved Threads: 629

sknake

Offline

.NET Enthusiast

Re: Help with the Following SQl SP

Jun 13th, 2009

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Feb 2009

Posts: 3,460

Reputation: sknake has much to be proud of

Solved Threads: 629

sknake

Offline

.NET Enthusiast

Re: Help with the Following SQl SP

Jun 13th, 2009

Oh, and the results:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
Name   CycleIdList
------ ------------------
Jake   13,14,15,16,17,18
Ken    7,8,9,10,11,12
Scott  1,2,3,4,5,6
Name   CycleIdList
------ ------------------
Jake   13,14,15,16,17,18
Ken    7,8,9,10,11,12
Scott  1,2,3,4,5,6

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Mar 2007

Posts: 158

Reputation: vuyiswamb is an unknown quantity at this point

Solved Threads: 5

vuyiswamb

Offline

Junior Poster

Re: Help with the Following SQl SP

Jun 15th, 2009

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