Optimization Help

[amolbehl]

Hi,

I have had to write a software with VisualBasic 6.0 and MS Access.

Now the database that I deal with has some 1 million records and I perform operations on this and based on certain criteria I need to look at a part of this table and generate a bunch of insert statements.

Overall there are some 100 thousand inserts followed by records getting deleted from the 1 million record table. The operation seems to fail because the MAX LOCKS are reached.

I solved this by editing the registry entry.

However the operation takes a few hours to complete. The main reason for this is the frequent INSERTS.

I just want to know if there is any way of optimizing this.

[Rabbit]

Hi,

I have had to write a software with VisualBasic 6.0 and MS Access.

Now the database that I deal with has some 1 million records and I perform operations on this and based on certain criteria I need to look at a part of this table and generate a bunch of insert statements.

Overall there are some 100 thousand inserts followed by records getting deleted from the 1 million record table. The operation seems to fail because the MAX LOCKS are reached.

I solved this by editing the registry entry.

However the operation takes a few hours to complete. The main reason for this is the frequent INSERTS.

I just want to know if there is any way of optimizing this.

We need the specifics if we're going to optimize anything. What records need to be inserted? How is that determined? How are those tables involved set up? What records need to be deleted? And probably, most important of all, why? Why do you need to do this? What is it you're looking to do, in a general context?

[amolbehl]

Firstly I am using MS ACCESS 2002 with VB6.0 front end.

I have a OrderDetail table with 1.4 million records.
In this I have 100000 unique combinations of (CutomerNo, Date and Route)
I have 15 Classes of Items and 10000 items in these classes.

So I have to take Orders from the OrderDetail based on the unique combination (CutomerNo, Date and Route) and within this I need to run thru each of the 15 item Class and check if one an of the 10000 items exists in this and PACKAGE it which is set of insert statements into another table.

In PACKAGE phase I have to look at the Qty for each Item and keep packing it in.

Now the final result for the PACKED table after I run is approximated at 200000 records. So thts how many inserts I have to do.

Now to start with I have a grouping of unique (CutomerNo, Date and Route) in query. In a loop I go thru this table and take group all the data that suffices into a temporary table then run thru this table for each ITEM CLASS and each item withing that (this is important to make sure about the stability) and each time I do i remove that row from that temporary table ( which means quite a few deletes too) and then the Item is PACKAGED (i.e inserted into the PACKAGE table)

I tried to work with the original table but the temporary table contains a few hundred records at a time and is faster to search when compared to 1.4 million records so for performance reasons had to do this.

Now I am having the problem of "System resources exceeded".

Do you ave any tips as to how I can improve this application??

[Rabbit]

I'm finding it hard to follow what you're trying to do.

Can you give us a 1-2 sentence overview and walk us through an example?

[amolbehl]

Well Briefly I am doing an inventory management for a warehouse.

I am trying to build wht are called Pallets which are loaded to trucks and brought to Stores.

The PACKAGE building is building of such a pallet.

Now the Pallet building requires that we go thru OrderDetail table which has 1.4 million records approx.

And the rest is in my post before.

I hope this helps??

[Jim Doherty]

Firstly I am using MS ACCESS 2002 with VB6.0 front end.

I have a OrderDetail table with 1.4 million records.
In this I have 100000 unique combinations of (CutomerNo, Date and Route)
I have 15 Classes of Items and 10000 items in these classes.

So I have to take Orders from the OrderDetail based on the unique combination (CutomerNo, Date and Route) and within this I need to run thru each of the 15 item Class and check if one an of the 10000 items exists in this and PACKAGE it which is set of insert statements into another table.

In PACKAGE phase I have to look at the Qty for each Item and keep packing it in.

Now the final result for the PACKED table after I run is approximated at 200000 records. So thts how many inserts I have to do.

Now to start with I have a grouping of unique (CutomerNo, Date and Route) in query. In a loop I go thru this table and take group all the data that suffices into a temporary table then run thru this table for each ITEM CLASS and each item withing that (this is important to make sure about the stability) and each time I do i remove that row from that temporary table ( which means quite a few deletes too) and then the Item is PACKAGED (i.e inserted into the PACKAGE table)

I tried to work with the original table but the temporary table contains a few hundred records at a time and is faster to search when compared to 1.4 million records so for performance reasons had to do this.

Now I am having the problem of "System resources exceeded".

Do you ave any tips as to how I can improve this application??

My advice would be to get your company to invest in SQL Server your data size and transaction handling is much more suited to that platform

Jim

[Rabbit]

You're going to have to walk me through an example because:

I don't know why you have to run through each of the 15 item classes and check the 10,000 items. I also don't know what you mean by "PACKAGE" it.

So I have to take Orders from the OrderDetail based on the unique combination (CutomerNo, Date and Route) and within this I need to run thru each of the 15 item Class and check if one an of the 10000 items exists in this and PACKAGE it which is set of insert statements into another table.

I don't know why you have to keep "packing it in" or what that entails.

In PACKAGE phase I have to look at the Qty for each Item and keep packing it in.

And you've completely lost me at this point.

Now the final result for the PACKED table after I run is approximated at 200000 records. So thts how many inserts I have to do.

Now to start with I have a grouping of unique (CutomerNo, Date and Route) in query. In a loop I go thru this table and take group all the data that suffices into a temporary table then run thru this table for each ITEM CLASS and each item withing that (this is important to make sure about the stability) and each time I do i remove that row from that temporary table ( which means quite a few deletes too) and then the Item is PACKAGED (i.e inserted into the PACKAGE table)

What I'm trying to figure out is what it is you're trying to do because it's all the individual inserts that are taking forever. If I knew what you're trying to do, then chances are you can boil it down to a few queries. Instead of 200,000 queries.

[amolbehl]

CustomerNo    Item    OrderDate    Qty    RouteNo    DeliveryMethod    DeliveryDescription
98134    3130    2/28/2006    14    488233    3    
98134    3155    2/28/2006    32    488233    3    
98134    32238    2/28/2006    12    488233    3    
98134    32240    2/28/2006    23    488233    3    
98134    33527    2/28/2006    21    488233    3    
98134    3685    2/28/2006    19    488233    3    
98134    63340    2/28/2006    22    488233    3    
98134    67543    2/28/2006    42    488233    3    
 

This is a small test data I made for testing initially correctness of my algorithm.

Now if u observe just (CustomerNo, OrderDate, RouteNo) which from now on I call a TRIPLET. I have just one unique one

CustomerNo    RouteNo    OrderDate
98134    488233    2/28/2006
 

A ITEMCLASS looks like this

ItemClass    OrderPlacement
12CAN               10
1LTR                 190
15LTR                180
WMPL              100
2LTR                  50
TRH                 999
x                    998
 

I have 15 such itemclasses.

Under each itemclass we have some 30-40 Items.

item    description    ItemClass    HazCode    ItemLength    ItemWidth    ItemHeight
62609    5G TK JOGUE CLUB SODA PRE    x        0    0    0
62496    14OZ NR CLRLY CNDIAN BLKBERRY     x        0    0    0
62497    14OZ NR CLRLY CNDIAN STRWML 1/    x        0    0    0
62498    14OZ NR CLRLY CNDIAN GRAPE 1/1    x        0    0    0
 

there are some 10k such items belonging to different classes.

One specialty of an itemclass is that all the items in it will have the same dimensions.

Now a pallet is a single unit loaded on the truck for delivery to stores.
Usually a pallet has all the same type of items or the same itemclass.
So suppose we have say pepsi cans being loaded to a pallet, we check the available volume of the pallet and the volume of each can case(24 cans) and we fill the stuff up. Because the pallet has same item it will be homogeneous.
Now if a pallet has same itemclass items say Pepsi cans, diet pepsi cans, they will still be homogeneous.
But there is a small possibility that we have a half filled pallet of CANs and now we get bottle so in such a case we will need to compute the volume left and hence fill the bottles cases in such a case that (noOfBottles * Vol of Boittle) <= volumeLeftInPallet.
This is packaging.

Now when orders come from really small stores, then the same step gets broken down further say CANS,16oz bottles, 20oz bottle, and so on.

Hence we have another logical division of Layers, each pallet has approx 5 layers. Now I need to show layer by layer insertion. Which is causing the problem. Because in a order of 1.4 million we have some 50000 pallets and each with approx 5 layers ... well u have like 200000 inserts.

Apart from this for each unique TRIPLET, I insert into a temporary table all the records from the original table as this is all the pallets that will be put into a truck. (Because they are going to a particular customer on a particular date by a particular route.) which match and then within this I go thru the itemclass and then items and then make pallets or PACKAGE.

Well thanx a lot for trying to help me guys, its a complicated thing but I am trying my best to make you understand it.

Just let me know if you have some other question.

[Rabbit]

Let's see if I'm understanding you correctly.

I'm dropping the Delivery fields because they don't seem to relate to the problem at hand.

CustomerNo  Item  OrderDate  Qty  RouteNo
12345       Item1 1/1/2007   3    Route1
12345       Item2 1/1/2007   5    Route1
12345       Item3 1/1/2007   1    Route1
54321       Item1 1/1/2007   2    Route1
54321       Item3 1/1/2007   3    Route1
 

I'm dropping the Item Class stuff because I don't see how it relates to the problem.

Now you have the Item descriptions. Again I'm dropping fields not related to the problem at hand.

Item  Length  Width  Height
Item1 2       3      1
Item2 3       4      2
Item3 2       2      2
 

So, if I'm understanding correctly, you're trying to automatically package the items into pallets for a chosen triplet?