Answer

I have written a Stored Procedure having 3 nes

Vipin Mishra

11y

976

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.