Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There is a very frustrating side after 2 days ... but hey, I submit myself and I ask: o (   I Can not Seem To turn this while loop in recursive CTE ... If someone could help me please.

I would like to improve performance.

Thanks.

WHILE @DureeAvancement<@DureeDemandee AND @NbPeriodesCroisees<65  AND @old_No<>@x_No
    BEGIN 
        SET @old_No=@x_No;
        SET @concat_Logements=NULL;
        SELECT TOP 1 @x_No=No,@x_Duree=P.Duree,@x_Prix=P.Prix,@x_Promo=P.Promo,@x_Logements=Logements,@x_JOUR=P.Jour
        FROM Periodes P WHERE P.TYPELOGEMENT=@TypeLogement AND P.Active=1 AND P.QUANTITE>0 
        AND  DATEDIFF(DAY,P.Jour,@Start)=0
        AND P.DUREE<=(@DureeDemandee-@DureeAvancement)
        order by DUREE DESC
        IF  @old_No=@x_No BREAK;            
        IF @old_Logements='' SET @old_Logements=@x_Logements;
        SELECT @concat_Logements=COALESCE(@concat_Logements + ',', '') + o.ITEM FROM (SELECT  Item    from [dbo].[SplitStringLogements](@old_Logements) INTERSECT SELECT Item  from [dbo].[SplitStringLogements](@x_Logements))o;
        IF  @concat_Logements IS NULL and @ChangementLogement=0 BREAK;
        SET @concat_Logements='['+@concat_Logements+']';
        SET @old_Logements=@concat_Logements;
        SET @NbPeriodesCroisees=@NbPeriodesCroisees+1;
        SET @DureeAvancement=@DureeAvancement+@x_Duree;
        SET @TotPrix=@TotPrix+@x_Prix;
        IF @TotPrix>@MaxPrice BREAK;
        SET @TotPromo=@TotPromo+@x_Promo;
        SET @Start=DATEADD(DAY,@x_Duree,@x_JOUR);
        SET @ContactPerio=@ContactPerio+CHAR(13)+CAST(@x_No AS NVARCHAR(18))+'  '+CAST(@x_JOUR AS NVARCHAR(18))+'   '+CAST(@x_Duree AS NVARCHAR(18))+'  '+CAST(@x_Promo AS NVARCHAR(18))+'  '+CAST(@x_Prix AS NVARCHAR(18))+'   '+@x_Logements;

    END

UPDATE :

CREATE FUNCTION [dbo].[DispoPrix](@TypeLogement bigint,@Start DATETIME,@DureeDemandee int=7,@MinPrice [decimal](18, 0)=0,@MaxPrice [decimal](18, 0)=10000000,@ChangementLogement int=0)  
RETURNS @RtnValue TABLE 
(
    [Prix] [decimal](18, 0) NULL,
    [Promo] [decimal](18, 0) NULL,
    [Logements] [nvarchar](300),
    [ContactPerio] [nvarchar](max)
) 
AS  
BEGIN 

DECLARE @x_No [bigint]=0,@old_No [bigint]=-1,@x_Logements [nvarchar](300)='',@old_Logements [nvarchar](300)='',@concat_Logements [nvarchar](300),@x_Duree [int],@x_Prix [decimal](18, 0)=0,@x_Promo [decimal](18, 0)=0,@x_JOUR [DateTime];
DECLARE @EndDate [datetime], @ContactPerio [nvarchar](max)='';

DECLARE @NbPeriodesCroisees int=0,@DureeAvancement int=0,@TotPrix [decimal](18, 0)=0,@TotPromo [decimal](18, 0)=0;



WHILE @DureeAvancement<@DureeDemandee AND @NbPeriodesCroisees<65  AND @old_No<>@x_No
    BEGIN 
        SET @old_No=@x_No;
        SET @concat_Logements=NULL;
        SELECT TOP 1 @x_No=No,@x_Duree=P.Duree,@x_Prix=P.Prix,@x_Promo=P.Promo,@x_Logements=Logements,@x_JOUR=P.Jour
        FROM Periodes P WHERE P.TYPELOGEMENT=@TypeLogement AND P.Active=1 AND P.QUANTITE>0 
        AND  DATEDIFF(DAY,P.Jour,@Start)=0
        AND P.DUREE<=(@DureeDemandee-@DureeAvancement)
        order by DUREE DESC
        -- Si pas de nouvel enregistrement, on sort de WHILE
        IF  @old_No=@x_No BREAK;

        -- Si pas de logement similaire disponible, on sort de WHILE
        IF @old_Logements='' SET @old_Logements=@x_Logements;
        SELECT @concat_Logements=COALESCE(@concat_Logements + ',', '') + o.ITEM FROM (SELECT  Item    from [dbo].[SplitStringLogements](@old_Logements) INTERSECT SELECT Item  from [dbo].[SplitStringLogements](@x_Logements))o;
        IF  @concat_Logements IS NULL and @ChangementLogement=0 BREAK;
        SET @concat_Logements='['+@concat_Logements+']';
        SET @old_Logements=@concat_Logements;
        --
        SET @NbPeriodesCroisees=@NbPeriodesCroisees+1;
        SET @DureeAvancement=@DureeAvancement+@x_Duree;
        SET @TotPrix=@TotPrix+@x_Prix;
        -- Si Trop Cher, on sort de WHILE
        IF @TotPrix>@MaxPrice BREAK;
        SET @TotPromo=@TotPromo+@x_Promo;
        SET @Start=DATEADD(DAY,@x_Duree,@x_JOUR);
        SET @ContactPerio=@ContactPerio+CHAR(13)+CAST(@x_No AS NVARCHAR(18))+'  '+CAST(@x_JOUR AS NVARCHAR(18))+'   '+CAST(@x_Duree AS NVARCHAR(18))+'  '+CAST(@x_Promo AS NVARCHAR(18))+'  '+CAST(@x_Prix AS NVARCHAR(18))+'   '+@x_Logements;


    END

IF  @DureeAvancement=@DureeDemandee AND @TotPrix>@MinPrice AND @TotPrix<=@MaxPrice
BEGIN
    INSERT @RtnValue

    SELECT  @TotPrix,@TotPromo,@concat_Logements,@ContactPerio
END

RETURN;

END

The Periodes Table

CREATE TABLE [dbo].[Periodes](
    [No] [bigint] IDENTITY(1,1) NOT NULL,
    [TYPELOGEMENT] [bigint] NULL,
    [Jour] [datetime] NULL,
    [Duree] [int] NULL,
    [Prix] [decimal](18, 0) NULL,
    [Promo] [decimal](18, 0) NULL,
    [Quantite] [int] NULL,
    [Logements] [nvarchar](300) NULL,
    [Active] [int] NULL,
 CONSTRAINT [PK_Periodes] PRIMARY KEY CLUSTERED 
(
    [No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Sample Data in Periodes Table, 3 days and prices

No  TYPELOGEMENT    Jour    Duree   Prix    Promo   Quantite    Logements   Active
78857   1   2014-10-01 12:00:00.000 1   70  79  0   [10,11,12]  1
78858   1   2014-10-02 12:00:00.000 1   80  80  0   [9,11,13]   1
78859   1   2014-10-03 12:00:00.000 1   90  95  0   [9,11,12]   1

And the split function who works fine in performance i think.

CREATE  function [dbo].[SplitStringLogements] 
(
    @str [nvarchar](max)
)
returns @Tbl table(
[NO] [bigint] null,
[ITEM] [nvarchar](18) null
)
AS
BEGIN

SET @str=REPLACE(REPLACE(@str,']',''),'[','');
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(',', @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(',', @str, b + 1)
    from tokens
    where b > 0
)
INSERT @Tbl
select
    p-1 No,CAST((
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end)) AS bigint)
    AS Item
from tokens
RETURN

END

Thanks !

share|improve this question
    
Can you post the table definitions and maybe some example data? –  Sebastian Meine Nov 14 '13 at 20:37
    
As for the split function, I think a recursive CTE is a less than optimal solution. You should compare it to set-based approaches like this one. –  Aaron Bertrand Nov 14 '13 at 21:27
    
Thank you I'll try that ! –  fraisdispo Nov 14 '13 at 22:15
    
It's not going to fix the overall problem, but it should be better generally. –  Aaron Bertrand Nov 14 '13 at 22:15
    
about 30% faster and eliminates the limitation of recursion, even if it is not a problem here, thank you for the split! .... remains the main:( –  fraisdispo Nov 14 '13 at 22:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.