August 30, 2013 at 6:18 am
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
August 30, 2013 at 6:29 am
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/
August 30, 2013 at 6:31 am
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".
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
August 30, 2013 at 6:55 am
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
August 30, 2013 at 6:18 pm
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
August 30, 2013 at 11:14 pm
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
August 30, 2013 at 11:14 pm
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
August 31, 2013 at 3:55 am
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/
September 2, 2013 at 1:36 am
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
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
September 11, 2013 at 5:50 am
Hi Guys,
Any Update on this .
September 11, 2013 at 6:00 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy