Query Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Performance

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-23 : 12:13:20

HI

Hi

We have scenario like this…please tell me your valuable suggestions.
We have 300000 lacks records in a table. Functionality is, we have to take each by each record and apply the formula and stored it in a separate table. Which means it’s like looping operations.

Bcoz the business scenario is like these…please tell me how to optimize.
Please help out any optimized architecture for the same …

Thanks
woodhouse

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 12:29:15

whats the formula you want to apply? what field is used to link b/w tables? or are you trying to dump entire data onto other table?

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-23 : 13:06:38

Hi

The scenario is like this...we have 25,000 items. So each item having 12 months data
Its total 3 lacks records.

For this we need take each item. Based on the item we need to pull 12 months data and apply some inventory optimization formula’s (Its Generic Formula LFT, Minmax, POR, EOQ its all are related to inventory)

So its taking too time to execution..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:09:08

are you using cursor or set based soln?

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-23 : 13:13:31

Hi

I have done using WHILE loop

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:16:37

no need of loop.. just use set based insert/update

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-23 : 13:18:46

Hi

Thats fine but i want to fetch each item of 25,000
Based on the item i need to pull 12 months data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:25:43

you can use set based solution also. can you post sample data in below format?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-23 : 13:48:25

HI

Simple

I have two tables one is master table & data table


Master Table
Part_ID  PART_NAME COL3 COL4 etc....
100  AAA       X    X    ....

Data table 
ID Part_ID  Demand_data 
1   100      12
2   100      13
3   100      14
4   100      15
5   100      16
6   100      17
7   100      18
8   100      19
9   100      20
10  100      21
11  100      22 
12  100      23

The tables are like above...

What i have done

using loop i will fetch one by one Part_ID

Beased on the part_id to pull out the 12 months data in Data table

For above two scnario i have used WHILE loop

so Total 25,000 records in master table 3 lacs records in dta table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:51:55

you dont need while loop. all that you need is


SELECT m.Part_ID,m.Part_Name,d.*
FROM Master m
INNER JOIN Data d
ON d.Part_ID=m.Part_ID

to get 12 months data for each part

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-23 : 13:57:03

hi

fine i agree i think no need to pu join bcoz i already having the part_id in data table so i can use directly ...

but my issue is

i need to apply 10 model calculation ffor each 12 months data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 14:06:01

same as how i showed you, you can apply formula also in set based way

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-23 : 21:18:38

Hi

can you pls explain or put some example here it would be helpful for me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-24 : 04:45:50

i can give you solution based on your scenario provided you post some sample data to illustrate your scenario and some info on functions you want to perform on them. also are you using sql 2005?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources