load large table for faster work

[#1]

Hi!

I have a problem. There is a table with about 10mio rows I need to load every day. I use qvd file for it, but still, If I try to read this file, create some charts, I get error. out of virtual or logical memory etc.

So I thought maby there is some better way to load from sql this data smarter.

Table structure:

EntryNo | Date | Item | Variant | Qty
00001 | 01.01.10 | 810013 | 001/12 | 2
00002 | 01.02.10 | 810013 | 011/12 | -1

Here I need to get Stock balance and stock = sum of all transactions. But there are for same item, same variant thousends of rows. Every day are added some more. So every time I need to get stock, all entrys by item and variant are sum'ed.

Maby somebody has idea to reload this data from sql by gruping this data somehow. For example until 2009 there is for one Items one variant only one entry:

Date | Item |Variant | Qty
2008 | 810013 | 011/12 | 56
2009 | 810013 | 011/12 | 43

[#2]

Hi There

What is the spec of the machine? Because i had a few problems like that as well and as you should know, QV is a bit hardware required, Dependant on the amount off data that is being returned, will affect your performance.

[#3]

2 x Intel Xeon 5140@2,33GHz, 4GB ram, Windows 2003 server 32bit

[#4]

Hi

The question is what your users expect as the lowest granularity in the data. If users can only select days and not the single transactions your SQL can sum the quantities grouped by day, item and variant. I do not know your data but it might be 10 or more times less data to load. The select from the DB will be a bit more demanding but if you write the extracts into qvd files it should not matter too much.

If you have long timespans to cover it might also be sufficient to show older data with less granularity, e.g. based on week or month.This however might cause quite some work in your app and if you can live without that avoid it.

Regards

Juerg