Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

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... Expand / Collapse
Author
Message
Posted Friday, August 30, 2013 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:07 AM
Points: 4, Visits: 40
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

Post #1490108
Posted Friday, August 30, 2013 6:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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 #1490118
Posted Friday, August 30, 2013 6:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 7,230, Visits: 13,709
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
Post #1490120
Posted Friday, August 30, 2013 6:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 15,662, Visits: 28,055
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1490136
Posted Friday, August 30, 2013 6:18 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 8,739, Visits: 9,287
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
Post #1490338
Posted Friday, August 30, 2013 11:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:07 AM
Points: 4, Visits: 40
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

Post #1490347
Posted Friday, August 30, 2013 11:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:07 AM
Points: 4, Visits: 40



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
Post #1490348
Posted Saturday, August 31, 2013 3:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1490369
Posted Monday, September 2, 2013 1:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 7,230, Visits: 13,709
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
Post #1490538
Posted Wednesday, September 11, 2013 5:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:07 AM
Points: 4, Visits: 40
Hi Guys,

Any Update on this .

Post #1493597
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse