February 27, 2009 at 7:33 am
Here another one, can you please help me optimize this one as well.
---------------------------------------------------------------------
USE [MRG_M002]
GO
/****** Object: StoredProcedure [dbo].[ps_SVM_COPIE_BESOINS] Script Date: 02/27/2009 09:31:16 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ps_SVM_COPIE_BESOINS]
@anneeSource INT,
@anneeDest INT,
@cs INT,
@codPeint INT,
@tousRTSS BIT
AS
DECLARE @curseur CURSOR,
@res_cs varchar(4),
@rtss varchar(14),
@longrRTSS INT,
@chDebut INT,
@chFin INT,
@longrSect INT,
@classe INT,
@peintRG INT,
@peintCL INT,
@peintRD INT,
@type INT,
@geo BINARY,
@geoXLO FLOAT,
@geoYLO FLOAT,
@geoXHI FLOAT,
@geoYHI FLOAT,
@id_route_debut NUMERIC(18,0),
@ch_route_debut FLOAT,
@dc_route_debut FLOAT,
@id_route_fin NUMERIC(18,0),
@ch_route_fin FLOAT,
@dc_route_fin FLOAT,
@numChamps int
--Verif si CS ou DT?
if @tousRTSS = 1
BEGIN
--On va prendre tous les RTSS
SET @curseur = CURSOR FAST_FORWARD
FOR
SELECT COD_CENTR_SERVC, RTSS, VAL_LONGR_SOUS_ROUTE, 0 as VAL_CHANG_DEBUT, VAL_LONGR_SOUS_ROUTE as VAL_CHANG_FIN,
VAL_LONGR_SOUS_ROUTE AS VAL_LONG_SECT, CLS_FONCT,@codPeint as IDE_PEINT_RG, @codPeint as IDE_PEINT_CL, @codPeint as IDE_PEINT_RD,
'' as TYPE_DEMANDE, NULL as GEO_PLANF, 0 as GEO_PLANF_XLO, 0 as GEO_PLANF_YLO, 0 as GEO_PLANF_XHI,0 as GEO_PLANF_YHI,
0 as IDE_SEGMN_ROUTE_DEBUT, 0 as CH_SEGMN_ROUTE_DEBUT, 0 as DECAL_SEGMN_ROUTE_DEBUT,
0 as IDE_SEGMN_ROUTE_FIN, 0 as CH_SEGMN_ROUTE_FIN, 0 as DECAL_SEGMN_ROUTE_FIN
FROM X_BGR_V_GOFT_RTSS
WHERE COD_CENTR_SERVC like @cs
END
else
BEGIN
SET @curseur = CURSOR FAST_FORWARD
FOR
SELECT COD_CENTR_SERVC, NOM_RTSS, VAL_LONG_RTSS, VAL_CHANG_DEBUT, VAL_CHANG_FIN, VAL_LONG_SECT, CLS_FONCT,
IDE_PEINT_RG, IDE_PEINT_CL, IDE_PEINT_RD, TYPE_DEMANDE, GEO_PLANF, GEO_PLANF_XLO, GEO_PLANF_YLO, GEO_PLANF_XHI, GEO_PLANF_YHI,
IDE_SEGMN_ROUTE_DEBUT, CH_SEGMN_ROUTE_DEBUT, DECAL_SEGMN_ROUTE_DEBUT, IDE_SEGMN_ROUTE_FIN, CH_SEGMN_ROUTE_FIN, DECAL_SEGMN_ROUTE_FIN
FROM SVM_SEGMN_PLANIF
WHERE YEAR(DAT_DEMANDE) = @anneeSource
AND COD_CENTR_SERVC like @cs
AND (IDE_PEINT_RG = @codPeint or IDE_PEINT_CL = @codPeint or IDE_PEINT_RD = @codPeint)
END
OPEN @curseur
FETCH NEXT FROM @curseur
INTO
@res_cs,
@rtss,
@longrRTSS,
@chDebut,
@chFin,
@longrSect,
@classe,
@peintRG,
@peintCL,
@peintRD,
@type,
@geo,
@geoXLO,
@geoYLO,
@geoXHI,
@geoYHI,
@id_route_debut,
@ch_route_debut,
@dc_route_debut,
@id_route_fin,
@ch_route_fin,
@dc_route_fin
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM @curseur
INTO
@res_cs,
@rtss,
@longrRTSS,
@chDebut,
@chFin,
@longrSect,
@classe,
@peintRG,
@peintCL,
@peintRD,
@type,
@geo,
@geoXLO,
@geoYLO,
@geoXHI,
@geoYHI,
@id_route_debut,
@ch_route_debut,
@dc_route_debut,
@id_route_fin,
@ch_route_fin,
@dc_route_fin
SELECT @numChamps = COUNT(IDE_SEGMN_PLANIF) FROM SVM_SEGMN_PLANIF
WHERE YEAR(DAT_DEMANDE) = @anneeDest
AND COD_CENTR_SERVC like @cs
AND NOM_RTSS = @rtss
AND VAL_CHANG_DEBUT = @chDebut
AND VAL_CHANG_FIN = @chFin
if (@numChamps < 1)
begin
INSERT INTO SVM_SEGMN_PLANIF (COD_CENTR_SERVC, NOM_RTSS, VAL_LONG_RTSS, VAL_CHANG_DEBUT, VAL_CHANG_FIN, VAL_LONG_SECT, CLS_FONCT,
IDE_PEINT_RG, IDE_PEINT_CL, IDE_PEINT_RD, TYPE_DEMANDE,
IDE_SEGMN_ROUTE_DEBUT, CH_SEGMN_ROUTE_DEBUT, DECAL_SEGMN_ROUTE_DEBUT,
IDE_SEGMN_ROUTE_FIN, CH_SEGMN_ROUTE_FIN, DECAL_SEGMN_ROUTE_FIN, DATE_CREATION, DAT_DEMANDE)
VALUES ( @res_cs,
@rtss,
@longrRTSS,
@chDebut,
@chFin,
@longrSect,
@classe,
@peintRG,
@peintCL,
@peintRD,
@type,
@id_route_debut,
@ch_route_debut,
@dc_route_debut,
@id_route_fin,
@ch_route_fin,
@dc_route_fin,
GETDATE(),
GETDATE() )
end
END
CLOSE @curseur
DEALLOCATE @curseur
RETURN 0
----------------------------------------------------------------------
Thanks
February 27, 2009 at 9:23 pm
If you were to go through the code and document it, I'll just be you could figure it out yourself. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2009 at 1:53 pm
Thanks Jeff my problem is fixed now
March 1, 2009 at 9:26 am
Stanley Pagenel (2/28/2009)
Thanks Jeff my problem is fixed now
Yeah, I saw how you think you fixed it on the other thread of yours... lemme tell you the same thing I said there...
Nope... if all you've done is replace a Cursor with a Temp table and While loop, all you've done is replace one procedural problem with another. If you make the Cursors FORWARD ONLY, READ ONLY, they'll be just as "fast" (just as slow, I should say).
The only way to fix the slowness of a Cursor (which contains a While loop, by the way), it to figure out what the code does and write some good ol' SET BASED code to replace it. Unless you do that, your code will still be plagued with resource and performance problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2009 at 10:02 am
oohhh, this is pork chop territory. Me gonna duck and keep my faceguard on ROTFL
hey Jeff fancy seeing you in this thread 😉 LOL
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2009 at 11:12 am
Perry Whittle (3/1/2009)
hey Jeff fancy seeing you in this thread 😉 LOL
Heh... I've not been as active on this forum in the last 2 weeks as I normally am... ya just had to know that I was gonna see the word "Cursor" in the title, though.
I'm just absolutely astounded that people still believe in the myth of replacing cursors with Temp Tables and While loops... it's almost like decaf coffee... why bother? 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2009 at 2:48 pm
So, Jeff, what's this "RBAR" I've heard so much about? 😉
March 2, 2009 at 2:59 pm
Look in his signature block. He defines RBAR there.
March 2, 2009 at 3:31 pm
Lynn, I probably should have used something more than a winking emoticon to show that I am certainly aware of the term but was just trying to show Jeff some empathy, even if in a mildly sarcastic way, for having to point out the RBAR-ness of the proposed solutions multiple times.
More to the point of this thread, sometimes it is hard to understand the concept of set-based vs. row-by-row processing. For those who've recently started using SQL after sometimes years of doing procedural programming, it can be a bit of a mind-twister. I've gone through that process myself and now have had the joy (no sarcasm here) of helping teammates see the difference.
Whether we're looking at a cursor or a WHILE loop in T-SQL, or a for-each loop in SSIS, we have to consider if there's a way to provide a set-based solution as that almost always will be more efficient.
March 2, 2009 at 6:57 pm
john.arnott (3/2/2009)
Lynn, I probably should have used something more than a winking emoticon to show that I am certainly aware of the term but was just trying to show Jeff some empathy, even if in a mildly sarcastic way, for having to point out the RBAR-ness of the proposed solutions multiple times.More to the point of this thread, sometimes it is hard to understand the concept of set-based vs. row-by-row processing. For those who've recently started using SQL after sometimes years of doing procedural programming, it can be a bit of a mind-twister. I've gone through that process myself and now have had the joy (no sarcasm here) of helping teammates see the difference.
Whether we're looking at a cursor or a WHILE loop in T-SQL, or a for-each loop in SSIS, we have to consider if there's a way to provide a set-based solution as that almost always will be more efficient.
BWAA-HAA!!! I caught the irony of your question, John... thanks for the laugh... I needed a good one today. 😛
Shifting gears to the more serious note and not directed to anyone in particular, I frequently descibe the first step to people towards making the paradigm shift from procedural (RBAR) T-SQL to Set Based as...
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
I certainly agree that there's more to it than that, especially with tools like the use of a Tally table, the "quirky" update (as Phil Factor calls it), and pseudo-cursors, but without the fundamental understanding that Set Based is really "column based" instead of "row based", there's no sense in teaching such tools until that understanding is achieved.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply