Speed on query

S

Steven

I have a table with 15 fields and 1,750,000 records. I am doing query to get
the sum:

Field4 : Group On
Field6 : Group On
Field8 : Group On
Amount: Sum

It takes about 30 seconds to complete on the entire table. Does that sould
about right or is there something I am missing to dramatically speed up the
process.

Thank you for your help.

Steven
 
R

Rick Brandt

Steven said:
I have a table with 15 fields and 1,750,000 records. I am doing
query to get the sum:

Field4 : Group On
Field6 : Group On
Field8 : Group On
Amount: Sum

It takes about 30 seconds to complete on the entire table. Does that
sould about right or is there something I am missing to dramatically
speed up the process.

Thank you for your help.

Steven
Click to expand...

Do all of those fields have indexes?
 
S

Steven

Thank you for your response. In the table design the 3 fields have on the
Field Properties: Indexed (Duplicates Ok). Is that what you mean?
 
R

Rick Brandt

Steven said:
Thank you for your response. In the table design the 3 fields have
on the Field Properties: Indexed (Duplicates Ok). Is that what you
mean?
Click to expand...

Yes. All fields used in GroupBy should be indexed. Beyond that I can't think
of any obvious ways to speed things up other than to make hardware changes to
increase file i/o speed. If you haven't you could compact the file and defrag
the drive, but those effects would not last very long if the data goes through
many changes.
 
B

BeWyched

Hi Steven

Sounds like hardware is holding things up.

I have a table with some 495,000 records. A query, 'Grouping' on 4 of its
fields and 'Summing' a fifth, takes less than 3 seconds to deliver. My PC is
3.6GHz processor with 2 Gb of RAM.

I know your table is double the size not 30 seconds + worth!

Cheers.

BW
 
Top