Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

While Loop into a Set Based Insert Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 5:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:35 AM
Points: 18, Visits: 90
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



Post #1345223
Posted Wednesday, August 15, 2012 7:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 1,426, Visits: 3,224
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.
Post #1345248
Posted Wednesday, August 15, 2012 7:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:35 AM
Points: 18, Visits: 90
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
Post #1345266
Posted Wednesday, August 15, 2012 8:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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 (EE:)
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1345289
Posted Wednesday, August 15, 2012 8:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:13 PM
Points: 561, Visits: 4,910
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/
Post #1345293
Posted Wednesday, August 15, 2012 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:35 AM
Points: 18, Visits: 90
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
Post #1345298
Posted Wednesday, August 15, 2012 8:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 6,582, Visits: 8,862
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
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
Post #1345309
Posted Wednesday, August 15, 2012 9:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:45 PM
Points: 3,617, Visits: 5,237
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!
Post #1345641
Posted Wednesday, August 15, 2012 11:32 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:59 AM
Points: 562, Visits: 885
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/
Post #1345673
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse