SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


While Loop into a Set Based Insert


While Loop into a Set Based Insert

Author
Message
Richie T
Richie T
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 113
I was wondering if anyone is able to provide me with any help turning this while loop inside a stored procedure into a set based insert?

The problem that I have is that the stored procedure is taking way to long to run so need to rewrite this more efficiently and have been advised a set based insert would provide me with the performance required but I have never used one and the articles I have read aren't to clear how I could go about doing this.

Any help much appreciated.

Thanks,

Richard



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Split_Membership_Yearss]

AS

DECLARE @RecordCount INT, @Count INT, @Memid uniqueidentifier,@OwnerId uniqueidentifier
DECLARE @start DATETIME, @end DATETIME, @SCHEME_ID uniqueidentifier, @EVENT_DATE DATETIME,@EVENT_TYPE_CODE nvarchar(20),@MemNo numeric, @CreatedOn datetime, @MembershipId uniqueidentifier


SET NOCOUNT ON

SELECT @RecordCount = count(*)
FROM [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
WHERE Proccess = 0 and DATEDIFF(Year, swmship_StartDate,isnull([swmship_EndDate],[RENEWAL_DATE]))>0

WHILE @RecordCount > 0
BEGIN

SELECT top 1 @MemNo=[MEMBER_NO], @Memid =[swmship_MemberId], @start = swmship_StartDate , @end =isnull(DATEADD(DAY,-1,[RENEWAL_DATE]),[swmship_EndDate]),
@SCHEME_ID =[SCHEME_ID], @CreatedOn = [CreatedOn], @MembershipID = [swmship_membershipId] ,

@EVENT_DATE=EVENT_DATE,@EVENT_TYPE_CODE=EVENT_TYPE_CODE
FROM [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
WHERE Proccess = 0 and DATEDIFF(Year, swmship_StartDate,isnull([swmship_EndDate],[RENEWAL_DATE]))>0



-- -- Get number of years
SET @Count= CEILING ((datediff(month, @start,@end) / 12.0)) --(Ceiling)round if there is months and days

BEGIN



INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
--[CreatedOn],
--[CreatedBy],
--[ModifiedOn],
--[ModifiedBy],
--[OwnerId],
--[OwnerIdType],
--[OwningBusinessUnit],
--[statecode] ,
--[statuscode] ,
--[TransactionCurrencyId] ,
--[swmship_membershipname] ,
[swmship_EndDate],
--[swmship_MembershipNumber] ,
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )

--VALUES ( newid(), DATEADD(DAY,-1,DATEADD(YEAR, @Count, @start)), DATEADD(Year, @Count-1, @start ), --from start date to end date
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),@start,
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1


END

WHILE @Count > 1


BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
--[CreatedOn],
--[CreatedBy],
--[ModifiedOn],
--[ModifiedBy],
--[OwnerId],
--[OwnerIdType],
--[OwningBusinessUnit],
--[statecode] ,
--[statuscode] ,
--[TransactionCurrencyId] ,
--[swmship_membershipname] ,
[swmship_EndDate],
--[swmship_MembershipNumber] ,
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )

--VALUES ( newid(), DATEADD(DAY,-1,DATEADD(YEAR, @Count, @start)), DATEADD(Year, @Count-1, @start ), --from start date to end date
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
CONTINUE
END


-- -- Pre 01/09/1981 And End Date Not Equal to 31/12 (dd/mm)

WHILE @Count = 1 and (
Select Cast(Datepart(d,isnull(swmship_EndDate,'1900-01-31'))as varchar) + '/' + Cast(Datepart(M,isnull(swmship_EndDate,'1900-12-01')) as varchar)
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) != '31/12'


BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
--[CreatedOn],
--[CreatedBy],
--[ModifiedOn],
--[ModifiedBy],
--[OwnerId],
--[OwnerIdType],
--[OwningBusinessUnit],
--[statecode] ,
--[statuscode] ,
--[TransactionCurrencyId] ,
--[swmship_membershipname] ,
[swmship_EndDate],
--[swmship_MembershipNumber] ,
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )

--VALUES ( newid(), DATEADD(DAY,-1,DATEADD(YEAR, @Count, @start)), DATEADD(Year, @Count-1, @start ), --from start date to end date
VALUES ( @MembershipID, (Select swmship_enddate From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS] Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn] ),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
CONTINUE
END



WHILE @Count = 1 and (Select Cast(Datepart(d,isnull(swmship_EndDate,'1900-01-01'))as varchar) + '/' + Cast(Datepart(M,isnull(swmship_EndDate,'1900-01-01')) as varchar)
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) = '31/12'


BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
--[CreatedOn],
--[CreatedBy],
--[ModifiedOn],
--[ModifiedBy],
--[OwnerId],
--[OwnerIdType],
--[OwningBusinessUnit],
--[statecode] ,
--[statuscode] ,
--[TransactionCurrencyId] ,
--[swmship_membershipname] ,
[swmship_EndDate],
--[swmship_MembershipNumber] ,
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )

--VALUES ( newid(), DATEADD(DAY,-1,DATEADD(YEAR, @Count, @start)), DATEADD(Year, @Count-1, @start ), --from start date to end date
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
CONTINUE
END


-- -- Pre 01/09/1981 And No End Date

WHILE @Count = 1 and (
Select swmship_EndDate
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) is null


BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
--[CreatedOn],
--[CreatedBy],
--[ModifiedOn],
--[ModifiedBy],
--[OwnerId],
--[OwnerIdType],
--[OwningBusinessUnit],
--[statecode] ,
--[statuscode] ,
--[TransactionCurrencyId] ,
--[swmship_membershipname] ,
[swmship_EndDate],
--[swmship_MembershipNumber] ,
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )

--VALUES ( newid(), DATEADD(DAY,-1,DATEADD(YEAR, @Count, @start)), DATEADD(Year, @Count-1, @start ), --from start date to end date
VALUES ( @MembershipID, DATEADD(d,-1,(Select RENEWAL_DATE From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS] Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn] )),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
CONTINUE
END


-- Pre 01/09/1981 And End Date Not Equal to 31/12 (dd/mm)


WHILE @Count = 1 and (Select Cast(Datepart(d,isnull(swmship_EndDate,'1900-01-01'))as varchar) + '/' + Cast(Datepart(M,isnull(swmship_EndDate,'1900-01-01')) as varchar)
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) = '31/12'


BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
--[CreatedOn],
--[CreatedBy],
--[ModifiedOn],
--[ModifiedBy],
--[OwnerId],
--[OwnerIdType],
--[OwningBusinessUnit],
--[statecode] ,
--[statuscode] ,
--[TransactionCurrencyId] ,
--[swmship_membershipname] ,
[swmship_EndDate],
--[swmship_MembershipNumber] ,
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )

--VALUES ( newid(), DATEADD(DAY,-1,DATEADD(YEAR, @Count, @start)), DATEADD(Year, @Count-1, @start ), --from start date to end date
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
CONTINUE
END


SET @RecordCount = @RecordCount-1
UPDATE [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
SET [Proccess] =1
WHERE MEMBER_NO=@MemNo and SCHEME_ID=@SCHEME_ID and swmship_StartDate=@start

CONTINUE
END





GO
sturner
sturner
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 3259
Man this is one ugly POS, probably written by a VB programmer.

Yes, it can be re-written to be set based and it would run light years faster. To be honest though, doing this sort of thing is what I like to get paid for, and right now I'm pretty busy on multiple projects and don't have a lot of time to do extra work for free.

Maybe someone with some extra time will chime in. Remember though. this is largely a forum providing advice, tips and short solutions to specific issues, not redesigning bad ETL processes. The people that post here do it voluntarily as their time permits.

The probability of survival is inversely proportional to the angle of arrival.
Richie T
Richie T
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 113
Thanks for your response Sturner and appreciate this is a substantial piece of work. Not asking anyone to re-write this for me but if you can point me in the right direction and I can attempt to do this myself.

Can find anything useful regarding turning a while loop into a set based insert.

Thanks Again.

Richie
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5102 Visits: 5478
First thing I would do is de-noodling the code so it can be readable:
a.) Format with proper indenting of code between BEGIN and END
b.) Remove the hoard of commented out code - if you want see the history - use source control system... Don't leave the rubbish in the code, or it very soon will contain more commented out lines than executable lines.
Next step would be removing redundant statements, see commented out lines with my initials (EESmile
So, after this exercise you will get something like:





SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Split_Membership_Yearss]
AS

DECLARE @RecordCount INT, @Count INT, @Memid uniqueidentifier,@OwnerId uniqueidentifier
DECLARE @start DATETIME, @end DATETIME, @SCHEME_ID uniqueidentifier, @EVENT_DATE DATETIME,@EVENT_TYPE_CODE nvarchar(20),@MemNo numeric, @CreatedOn datetime, @MembershipId uniqueidentifier

SET NOCOUNT ON

SELECT @RecordCount = count(*)
FROM [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
WHERE Proccess = 0 and DATEDIFF(Year, swmship_StartDate,isnull([swmship_EndDate],[RENEWAL_DATE]))>0

WHILE @RecordCount > 0
BEGIN

SELECT top 1 @MemNo=[MEMBER_NO], @Memid =[swmship_MemberId], @start = swmship_StartDate , @end =isnull(DATEADD(DAY,-1,[RENEWAL_DATE]),[swmship_EndDate]),
@SCHEME_ID =[SCHEME_ID], @CreatedOn = [CreatedOn], @MembershipID = [swmship_membershipId] ,
@EVENT_DATE=EVENT_DATE,@EVENT_TYPE_CODE=EVENT_TYPE_CODE
FROM [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
WHERE Proccess = 0 and DATEDIFF(Year, swmship_StartDate,isnull([swmship_EndDate],[RENEWAL_DATE]))>0

-- -- Get number of years
SET @Count=CEILING ((datediff(month, @start,@end) / 12.0)) --(Ceiling)round if there is months and days

-- EE: BEGIN - is useless here

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),@start,
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1

-- EE: END - is useless here
WHILE @Count > 1
BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END

-- Pre 01/09/1981 And End Date Not Equal to 31/12 (dd/mm)
WHILE @Count = 1 and (
Select Cast(Datepart(d,isnull(swmship_EndDate,'1900-01-31'))as varchar) + '/' + Cast(Datepart(M,isnull(swmship_EndDate,'1900-12-01')) as varchar)
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) != '31/12'
BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, (Select swmship_enddate From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS] Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn] ),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END

WHILE @Count = 1 and (Select Cast(Datepart(d,isnull(swmship_EndDate,'1900-01-01'))as varchar) + '/' + Cast(Datepart(M,isnull(swmship_EndDate,'1900-01-01')) as varchar)
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) = '31/12'
BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END

-- -- Pre 01/09/1981 And No End Date
WHILE @Count = 1 and (
Select swmship_EndDate
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) is null
BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(d,-1,(Select RENEWAL_DATE From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS] Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn] )),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END

-- Pre 01/09/1981 And End Date Not Equal to 31/12 (dd/mm)
WHILE @Count = 1 and
(Select Cast(Datepart(d,isnull(swmship_EndDate,'1900-01-01'))as varchar) + '/' + Cast(Datepart(M,isnull(swmship_EndDate,'1900-01-01')) as varchar)
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) = '31/12'
BEGIN

INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END

SET @RecordCount = @RecordCount-1

UPDATE [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
SET [Proccess] =1
WHERE MEMBER_NO=@MemNo and SCHEME_ID=@SCHEME_ID and swmship_StartDate=@start

-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END


GO



(If you click the appropriate tags in the left frame when posting, your post will be formatted much much better...)

Now you can start looking into loops...

The idea is to prepare all your data for a single update/insert of all rows, instead of update/insert per row as in loop.
If I have time I will start showing more.
Have you start to do anything so far? Please post!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Charles Hearn
Charles Hearn
SSC Eights!
SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)

Group: General Forum Members
Points: 805 Visits: 5155
Think about how you can build select statements to return the data in sets instead of using nested loops. The goal is to do operations on columns of data and not rows of data.

Take a look at the article below by Jeff Moden on using a tally table. This might give you some ideas.

http://www.sqlservercentral.com/articles/T-SQL/62867/
Richie T
Richie T
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 113
Thanks for that guys much appreciated.

That link you provided is very useful and exactly what I was looking for, should be able to rewrite the proc using this method.

Thanks Again.

Richie
WayneS
WayneS
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10066 Visits: 10574
Check out the 2 "There must be 15 ways to lose your cursor" articles by RBarryYoung at http://www.sqlservercentral.com/Authors/Articles/RBarry_Young/659055/. They walk you through pretty much exactly what you need to do.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7461 Visits: 6431
Eugene Elutin (8/15/2012)
First thing I would do is de-noodling the code so it can be readable:
a.) Format with proper indenting of code between BEGIN and END
b.) Remove the hoard of commented out code - if you want see the history - use source control system... Don't leave the rubbish in the code, or it very soon will contain more commented out lines than executable lines.


This is some of the best advice I've ever seen posted on this forum. It is what I do every time I'm called upon to improve a query's performance. Figuring out what it is doing is half the battle, and is greatly assisted by these steps. If only people posting questions would take the time to do it before they posted, they'd probably find the answer before their posting (no offense meant to the OP when I say this).

You should see some of the junk I've had to process. Mostly stuff created by query builder or some reporting tool. The queries are totally impossible to read, let alone understand, without some pre-formatting.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
rhythmk
rhythmk
SSC Eights!
SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)

Group: General Forum Members
Points: 924 Visits: 1078
Richie T (8/15/2012)
Thanks for that guys much appreciated.

That link you provided is very useful and exactly what I was looking for, should be able to rewrite the proc using this method.

Thanks Again.

Richie


Hi Richie,

Please post your updated query.

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search