Performance

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

I have a table with 2,500,000 records. It has fields
Company, Account, CostCenter, Period, Amount.

The user may ask for example a report on: Company "001"
AND Account Between "000000" AND "200000" AND CostCenter
Between "0000" AND "9999" AND Period Between "2002_07"
AND "2002_12"

Therefore I have to get the beginning balances on each
account and cost center so I have to get totals on:
Company = "001"
Group on Account between ...... and .....
Group on CostCenter between ..... and ....
Period < .... Less Than because I want beginning balances.
Sum Amount

I am using a basic Totals query to get the beginning
balances and depending on the number of records it has to
total it can take a little too long. Is there a better
way to get the beginning balances or maybe an entirely
different method.

Thank you for your help.

Steven.
 
First, make sure you have indexes set on the relevant fields - any that you want
to use in the where clause.
Second, use a where clause separately from the Group By.

SELECT Company, Account, CostCenter, Sum(Amount) as TotalAmount
FROM TableName
WHERE Company = "001"
AND Account Between "000000" AND "2000000"
AND CostCenter Between "0000" And "9999"
AND Period < "2002_07"
GROUP BY Company, Account, CostCenter

The reason for using a where clause instead of a having clause is that the where
clause will limit the records to be summed. A having clause will get all the
records, sum them and then trim the records to be returned to those matching.

If you are doing this in the query grid, you will need Company, Account,
CostCenter in the query twice. Once to group by and once for the where clause.
If you try to apply the criteria to the groupby column, you will create a HAVING
clause which should be slower than using the WHERE clause.
 
Back
Top