I have written a Stored Procedure having 3 nes

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

To achieve it, i have created two tables like this:

/****** Object: Table [dbo].[Cities] Script Date: 08/30/2013 20:13:28 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Cities](

[Id] [int] NULL,

[CustomerId] [int] NULL,

[City] [varchar](max) NULL,

[DateProvided] [datetime] NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

/****** Object: Table [dbo].[Cities] Script Date: 08/30/2013 20:19:01 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Cities](

[Id] [int] NULL,

[CustomerId] [int] NULL,

[City] [varchar](max) NULL,

[DateProvided] [datetime] NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

GO

Inserted One customer in Customer Table and One Lakh Records in the table Cities corresponding to that Customer and used following query, results were out in 0 - 1 Seconds

/********************Query***********************************************/

SELECT

Customer.Id, Cities.City, CASE WHEN DATEDIFF(YEAR, Cities.DateProvided, GETDATE()) <= 1 THEN 'M1' ELSE

CASE WHEN DATEDIFF(YEAR, Cities.DateProvided, GETDATE()) <= 2 THEN 'M2' ELSE

CASE WHEN DATEDIFF(YEAR, Cities.DateProvided, GETDATE()) >= 3 THEN 'M3' ELSE NULL END END END

FROM Customer

INNER JOIN Cities ON Customer.Id = Cities.CustomerId

Regards
AJ

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Abhishek Jain13y ago
To achieve it, i have created two tables like this:

/****** Object: Table [dbo].[Cities] Script Date: 08/30/2013 20:13:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Cities](
[Id] [int] NULL,
[CustomerId] [int] NULL,
[City] [varchar](max) NULL,
[DateProvided] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object: Table [dbo].[Cities] Script Date: 08/30/2013 20:19:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Cities](
[Id] [int] NULL,
[CustomerId] [int] NULL,
[City] [varchar](max) NULL,
[DateProvided] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Inserted One customer in Customer Table and One Lakh Records in the table Cities corresponding to that Customer and used following query, results were out in 0 - 1 Seconds

/********************Query***********************************************/

SELECT
Customer.Id, Cities.City, CASE WHEN DATEDIFF(YEAR, Cities.DateProvided, GETDATE()) <= 1 THEN 'M1' ELSE
CASE WHEN DATEDIFF(YEAR, Cities.DateProvided, GETDATE()) <= 2 THEN 'M2' ELSE
CASE WHEN DATEDIFF(YEAR, Cities.DateProvided, GETDATE()) >= 3 THEN 'M3' ELSE NULL END END END
FROM Customer
INNER JOIN Cities ON Customer.Id = Cities.CustomerId

Regards
AJ
0