I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping . Co

  • Hi Guys,

    I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping .

    Could someone please help me out to optimize this SP or eliminate some while looping without affecting its business logic, or any other solution?

    Here is my Business Logic:

    Loop Calculates Number of times customer has visited that particular city in given time span (DateProvided to Till Date).

    Below is the Pseudo code, Sample Data and Sample results for your reference.

    Pseudo code:

    1. Select Customer in a table.

    Eg. There are 5 customers in a table with Id 1, 2,3,4,5

    2. Select Each City for Particular customer.

    Eg . Customer whose Id is 1 travelling to 3 different cities let’s say Mumbai, Delhi and Bangalore.

    3. Now I have to calculate the Visiting status of those Customers based on Date Provided column to till date for each city.

    a. If Customer visited particular city in one year from date provided to till date then M1 for each city.

    b. If Customer visited particular city in two year from date provided to till date then M2 for each city.

    C. If Customer visited particular city more than 3 year from date provided to till date then M3 for each city .

    Sample Date:

    CustomerCityDateProvided

    Eg. 1 Mumbai 12/02/2011

    Delhi07/30/2008

    Delhi05/18/2009

    Bangalore04/13/2012

    Expected Result:

    CustomerCityStatus

    1Mumbai M2

    DelhiM3

    DelhiM3

    BangaloreM1

  • Can you please post the code what you have written with DDL statment of CREATE TABLE, DDL stament of sample data in form of INSERT statment.....

    You can folow the link in my signature about how to post question

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Post your actual code too, please. With your pseudo code, it might provide enough information for folks to make a few guesses. If you're expecting good, solid, tested code, then post up some readily consumable sample data for folks to test against. If you're not sure how to do this, there are instructions in the link in my sig - "please read this".

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Not seeing the code or structures or plans, just guessing at possible solutions, but allow me to point out, there is nothing inherently good, or bad, in nested loop joins. If you have smaller data sets, it's very likely that they will be the most efficient mechanism for retrieving data. But those same loop joins on large data sets are performance killers because, look at the description of a loop join, it's a cursor. Now, it's not a cursor like when you declare a cursor in T-SQL, but internally, it's a cursor. For larger data sets hash & merge joins are usually better performers, again, depending on all sorts of stuff.

    The bigger question for me would be, why would you see the same execution plan for larger result sets? Are your statistics out of date causing you to get a loop join when you should get something else? I'd look into that, not simply focus on a particular type of operator in the plan.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The description od what is calculate says number of times a city is visited is calculated; the sample data and expected results are completely different from this; for example a city which is visited only once has result M2, another also visited only once has result M1, and another visited only twice has result M3. Looking at the date provided field in the sample data, and guessing that the till date (which isn't specified in the sample data) is somewhere around the end of the calendar year in which the last visit occurs, it appears that M1 means city first visited during the year ending till date, M2 means first visited in the year before that, and M3 means earlier than M2, which bears no resemblance to the verbal description. Assuming the table structure is something sane, rather than having different number of columns in different rows as suggested by the sample data (that 1 in the first row is a customer id, absent in the second third and fourth rows, isn't it?) it is trivial, assuming that the actual requirement matches either the verbal description or the sample data and results, to write a single SQL statement which will do the job rather more quickly that 3 nested loops, but as the sample data and results contradict the verbal description it is not in the least bit clear which the code should do, or indeed whather it should do something else altogether.

    Tom

  • Hi Kapil,

    Below are the Business logic and Code.

    If 3+ visit in last one year then A.

    If Atleast 1 visit in past 24 months and 1 visit in the past 12 months then B.

    If 1 visit during last 12 months then C.

    If 1 visit during previous 13-24 months Then D.

    If 1 visit during last 25-36 months E.

    If All Visits Older than 50 months then F

    CREATE PROCEDURE [dbo].[sp_status]

    AS

    BEGIN

    set nocount on

    DECLARE @CUSTNO INT

    DECLARE @TOTALCUST INT

    DECLARE @CUSTINCRE INT =1

    DECLARE @ACQUIREDDATE DATE

    DECLARE @getdate-2 DATE = GETDATE()

    DECLARE@CITY VARCHAR(100)

    DECLARE @STARTDATE DATE

    DECLARE @GETPREFDATE DATE

    DECLARE @MEMBER VARCHAR(15)

    DECLARE @OLDMEMBER VARCHAR(15)='N/A'

    DECLARE @TOTALDIV INT

    DECLARE @DIVINCRE INT

    SET @TOTALCUST = (SELECT COUNT(CUSTNO)FROM #DISTINCTCUST)

    WHILE(@TOTALCUST >= @CUSTINCRE)

    BEGIN

    SELECT @CUSTNO=CUSTNO,@ACQUIREDDATE=ACQUIREDDATE FROM #DISTINCTCUST WHERE CUSTCNT=@CUSTINCRE -- GET CUSTOMER

    SELECT ROW_NUMBER()OVER(ORDER BY DIVISION)DIVNCNT, DIVISION INTO #DISTDIVISIONFROM #DISTINCTDIV WHERE CUSTNO=@CUSTNO group by DIVISION---GET DISINCT DIVISION AND DATE

    SET @TOTALDIV = (SELECT COUNT(DISTINCT DIVISION)FROM #DISTDIVISION)------GET DISINCT DIVISION

    Set @DIVINCRE =1

    WHILE(@TOTALDIV >=@DIVINCRE)

    BEGIN

    SELECT @CITY=DIVISION FROM #DISTDIVISION WHERE DIVNCNT=@DIVINCRE

    SELECT CUSTNO,DIVISION,DATEPROVIDED INTO #GETALLDATA FROM #DISTINCTDIV WHERE CUSTNO=@CUSTNO AND DIVISION=@CITY

    SET @STARTDATE= (SELECT MIN(DATEPROVIDED) FROM #GETALLDATA WHERE CUSTNO=@CUSTNO AND DIVISION=@CITY)

    INSERT INTO #DESTRECENCY VALUES(@CUSTNO,@CITY,'AAA', @ACQUIREDDATE, @ACQUIREDDATE)

    WHILE(@GETDATE >=@STARTDATE)

    BEGIN

    SET @GETPREFDATE =(SELECT MAX(DATEPROVIDED) FROM #GETALLDATA WHERE DATEPROVIDED <=@STARTDATE)

    SET @MEMBER= (SELECT CASE

    WHEN SUM(ISNULL(CASE WHEN DATEDIFF(DAY,DATEPROVIDED,@STARTDATE) between 0 and 365 then 1 end,0)) >= 2

    THEN 'A'

    WHEN sum(ISNULL(CASE WHEN DATEDIFF(DAY,DATEPROVIDED, @STARTDATE) between 0 and 365 then 1 end,0)) >= 1 and

    sum(ISNULL(case when DATEDIFF(DAY,DATEPROVIDED, @STARTDATE) between 366 and 730 then 1 end,0)) >= 1

    THEN 'B'

    WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) between 0 and 365

    THEN 'C'

    WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) between 366 and 730

    THEN 'D'

    WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) between 731 and 1095

    THEN 'E'

    WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) > 1095

    THEN 'F'ELSE 'N/A'

    END FROM #GETALLDATA WHERE CUSTNO=@CUSTNO AND DIVISION=@CITY AND

    DATEPROVIDED <= @STARTDATE) --BETWEEN @GETPREFDATE and @STARTDATE

    --GROUP BY CUSTNO,DIVISION)

    IF ((@OLDMEMBER <> @MEMBER OR @GETPREFDATE = @STARTDATE))

    BEGIN

    SET @OLDMEMBER = @MEMBER

    INSERT INTO #DESTRECENCYVALUES (@CUSTNO,@CITY,@MEMBER, @STARTDATE, @GETPREFDATE)

    END

    SET @STARTDATE =DATEADD(DAY,1,@STARTDATE)

    END

    DROP TABLE #GETALLDATA

    SET @DIVINCRE = @DIVINCRE + 1

    END

    DROP TABLE #DISTDIVISION

    SET @CUSTINCRE = @CUSTINCRE + 1

    END

    --select * from #DESTRECENCY

    TRUNCATE TABLE #DESTRECENCY

    DROP TABLE #DESTRECENCY

    DROP TABLE #DISTINCTCUST

    DROP TABLE #DISTINCTDIV

    DROP TABLE #DSTDIV

    DROP TABLE #DIVISION

    END

    GO

  • If 3+ visit in last one year then A.

    If Atleast 1 visit in past 24 months and 1 visit in the past 12 months then B.

    If 1 visit during last 12 months then C.

    If 1 visit during previous 13-24 months Then D.

    If 1 visit during last 25-36 months E.

    If All Visits Older than 50 months then F

    CREATE PROCEDURE [dbo].[sp_status]

    AS

    BEGIN

    set nocount on

    DECLARE @CUSTNO INT

    DECLARE @TOTALCUST INT

    DECLARE @CUSTINCRE INT =1

    DECLARE @ACQUIREDDATE DATE

    DECLARE @getdate-2 DATE = GETDATE()

    DECLARE@CITY VARCHAR(100)

    DECLARE @STARTDATE DATE

    DECLARE @GETPREFDATE DATE

    DECLARE @MEMBER VARCHAR(15)

    DECLARE @OLDMEMBER VARCHAR(15)='N/A'

    DECLARE @TOTALDIV INT

    DECLARE @DIVINCRE INT

    SET @TOTALCUST = (SELECT COUNT(CUSTNO)FROM #DISTINCTCUST)

    WHILE(@TOTALCUST >= @CUSTINCRE)

    BEGIN

    SELECT @CUSTNO=CUSTNO,@ACQUIREDDATE=ACQUIREDDATE FROM #DISTINCTCUST WHERE CUSTCNT=@CUSTINCRE -- GET CUSTOMER

    SELECT ROW_NUMBER()OVER(ORDER BY DIVISION)DIVNCNT, DIVISION INTO #DISTDIVISIONFROM #DISTINCTDIV WHERE CUSTNO=@CUSTNO group by DIVISION---GET DISINCT DIVISION AND DATE

    SET @TOTALDIV = (SELECT COUNT(DISTINCT DIVISION)FROM #DISTDIVISION)------GET DISINCT DIVISION

    Set @DIVINCRE =1

    WHILE(@TOTALDIV >=@DIVINCRE)

    BEGIN

    SELECT @CITY=DIVISION FROM #DISTDIVISION WHERE DIVNCNT=@DIVINCRE

    SELECT CUSTNO,DIVISION,DATEPROVIDED INTO #GETALLDATA FROM #DISTINCTDIV WHERE CUSTNO=@CUSTNO AND DIVISION=@CITY

    SET @STARTDATE= (SELECT MIN(DATEPROVIDED) FROM #GETALLDATA WHERE CUSTNO=@CUSTNO AND DIVISION=@CITY)

    INSERT INTO #DESTRECENCY VALUES(@CUSTNO,@CITY,'AAA', @ACQUIREDDATE, @ACQUIREDDATE)

    WHILE(@GETDATE >=@STARTDATE)

    BEGIN

    SET @GETPREFDATE =(SELECT MAX(DATEPROVIDED) FROM #GETALLDATA WHERE DATEPROVIDED <=@STARTDATE)

    SET @MEMBER= (SELECT CASE

    WHEN SUM(ISNULL(CASE WHEN DATEDIFF(DAY,DATEPROVIDED,@STARTDATE) between 0 and 365 then 1 end,0)) >= 2

    THEN 'A'

    WHEN sum(ISNULL(CASE WHEN DATEDIFF(DAY,DATEPROVIDED, @STARTDATE) between 0 and 365 then 1 end,0)) >= 1 and

    sum(ISNULL(case when DATEDIFF(DAY,DATEPROVIDED, @STARTDATE) between 366 and 730 then 1 end,0)) >= 1

    THEN 'B'

    WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) between 0 and 365

    THEN 'C'

    WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) between 366 and 730

    THEN 'D'

    WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) between 731 and 1095

    THEN 'E'

    WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) > 1095

    THEN 'F'ELSE 'N/A'

    END FROM #GETALLDATA WHERE CUSTNO=@CUSTNO AND DIVISION=@CITY AND

    DATEPROVIDED <= @STARTDATE) --BETWEEN @GETPREFDATE and @STARTDATE

    --GROUP BY CUSTNO,DIVISION)

    IF ((@OLDMEMBER <> @MEMBER OR @GETPREFDATE = @STARTDATE))

    BEGIN

    SET @OLDMEMBER = @MEMBER

    INSERT INTO #DESTRECENCYVALUES (@CUSTNO,@CITY,@MEMBER, @STARTDATE, @GETPREFDATE)

    END

    SET @STARTDATE =DATEADD(DAY,1,@STARTDATE)

    END

    DROP TABLE #GETALLDATA

    SET @DIVINCRE = @DIVINCRE + 1

    END

    DROP TABLE #DISTDIVISION

    SET @CUSTINCRE = @CUSTINCRE + 1

    END

    --select * from #DESTRECENCY

    TRUNCATE TABLE #DESTRECENCY

    DROP TABLE #DESTRECENCY

    DROP TABLE #DISTINCTCUST

    DROP TABLE #DISTINCTDIV

    DROP TABLE #DSTDIV

    DROP TABLE #DIVISION

    END

    GO

  • Can you please post the table structure and resultant output that you want

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • L' Eomot Inversé (8/30/2013)


    The description od what is calculate says number of times a city is visited is calculated; the sample data and expected results are completely different from this; for example a city which is visited only once has result M2, another also visited only once has result M1, and another visited only twice has result M3. Looking at the date provided field in the sample data, and guessing that the till date (which isn't specified in the sample data) is somewhere around the end of the calendar year in which the last visit occurs, it appears that M1 means city first visited during the year ending till date, M2 means first visited in the year before that, and M3 means earlier than M2, which bears no resemblance to the verbal description. Assuming the table structure is something sane, rather than having different number of columns in different rows as suggested by the sample data (that 1 in the first row is a customer id, absent in the second third and fourth rows, isn't it?) it is trivial, assuming that the actual requirement matches either the verbal description or the sample data and results, to write a single SQL statement which will do the job rather more quickly that 3 nested loops, but as the sample data and results contradict the verbal description it is not in the least bit clear which the code should do, or indeed whather it should do something else altogether.

    The code doesn't help us a great deal either. If you aren't sure what you are trying to do then take a step back from the code and focus on the business requirements. It sounds like it should be trivial. Here's a little code which may help:

    DECLARE @TillDate DATE

    SET @TillDate = GETDATE()

    SET DATEFORMAT MDY

    ;WITH SampleData (Customer, City, DateProvided) AS (

    SELECT 1, 'Mumbai', '12/02/2011' UNION ALL

    SELECT 1, 'Delhi', '07/30/2008' UNION ALL

    SELECT 1, 'Delhi', '05/18/2009' UNION ALL

    SELECT 1, 'Delhi', '05/18/2012' UNION ALL

    SELECT 1, 'Bangalore', '04/13/2012'

    )

    SELECT Customer, City, DateProvided,

    Recency = CASE

    WHEN DateProvided BETWEEN DATEADD(YEAR,-1,@TillDate) AND @TillDate THEN 'M1'

    WHEN DateProvided BETWEEN DATEADD(YEAR,-2,@TillDate) AND @TillDate THEN 'M2'

    WHEN DateProvided < DATEADD(YEAR,-3,@TillDate) THEN 'M3'

    ELSE NULL END

    FROM SampleData

    -- Aggregate to a single row per customer / city

    ;WITH SampleData (Customer, City, DateProvided) AS (

    SELECT 1, 'Mumbai', '12/02/2011' UNION ALL

    SELECT 1, 'Delhi', '07/30/2008' UNION ALL

    SELECT 1, 'Delhi', '05/18/2009' UNION ALL

    SELECT 1, 'Delhi', '05/18/2012' UNION ALL

    SELECT 1, 'Bangalore', '04/13/2012'

    )

    SELECT Customer, City, --DateProvided,

    Recency = MIN(CASE

    WHEN DateProvided BETWEEN DATEADD(YEAR,-1,@TillDate) AND @TillDate THEN 'M1'

    WHEN DateProvided BETWEEN DATEADD(YEAR,-2,@TillDate) AND @TillDate THEN 'M2'

    WHEN DateProvided < DATEADD(YEAR,-3,@TillDate) THEN 'M3'

    ELSE NULL END)

    FROM SampleData

    GROUP BY Customer, City

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Guys,

    Any Update on this .

  • mishra.vipinkumar001 (9/11/2013)


    Hi Guys,

    Any Update on this .

    Have you looked to see whether the code ChrisM posted would help you get somewhere on this?

    Tom

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply