SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I...


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...

Author
Message
mishra.vipinkumar001
mishra.vipinkumar001
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 46
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:
Customer City DateProvided
Eg. 1 Mumbai 12/02/2011
Delhi 07/30/2008
Delhi 05/18/2009
Bangalore 04/13/2012


Expected Result:


Customer City Status
1 Mumbai M2
Delhi M3
Delhi M3
Bangalore M1
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5412 Visits: 2767
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/
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42332 Visits: 20012
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
Exploring Recursive CTEs by Example Dwain Camps
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99887 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26255 Visits: 12506
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

mishra.vipinkumar001
mishra.vipinkumar001
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 46
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 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 #DISTDIVISION FROM #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
mishra.vipinkumar001
mishra.vipinkumar001
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 46
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 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 #DISTDIVISION FROM #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
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5412 Visits: 2767
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/
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42332 Visits: 20012
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
Exploring Recursive CTEs by Example Dwain Camps
mishra.vipinkumar001
mishra.vipinkumar001
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 46
Hi Guys,

Any Update on this .
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search