PivotChart too slow on my query...

[kilou]

Hi,

I have a table called T_Batch which stores batch information as follow:
T_Batch
LotID
ProductName
Type
DateMfg

From this table I have created one query Q_Area which calculate the area in which the product is manufactured (one product is manufactured in one area 1 while others are manufactured in area2).

Q_Area
LotID (from T_Batch)
Area (calculated)

A second query, Q_Prod, is based on both T_Batch and Q_Area and contains:

Q_Prod
LotID (from T_Batch)
Type (from T_Batch)
Area (from Q_Area)
DateMfg (from T_Batch)
DateMfgWeek (calculated from DateMfg with Format([DateMfg],"ww"))
DateMfgMonth (calculated from DateMfg with Format([DateMfg],"mm"))
DateMfgYear (calculated from DateMfg with Format([DateMfg],"yyyy"))

Now obviously I want to create a PivotChart on Q_Prod to count the nb of manufactured batches per year/month or week depending on the Type and the Area. Sounds pretty simple but Q_Prod contains 13247 rows and the PivotChart takes age to get displayed. So I figure there is something wrong in my design.

Q_Prod query itself is pretty fast to get displayed but the PivotChart or PivotTable based on it are slow to appear. Of course I can restrict Q_Prod by using parameters (like retrieving records only in 2008...) but then how can I display the total nb of batches per year?? The only parameter I could select would be the type but it doesn't help a lot because 80% of records are of type1 so it's still too slow.

What am I doing wrong here? Is there anyway to speed up the PivotChart display yet still retrieve all the records? Is 13000 rows too much for Access? (I moved from Excel to Access expressively to "solve" that kind of issues...)

Thanks

 

[kilou]

I ran the performance analyzer on the query and Access proposed to create a relationship between T_Batch and itself on LotID....

I tried but it doesn't improve speed.

 

[kilou]

Any idea? I can't believe Access is so slow to compute a count on 13000 rows. I mean it's ok when it's simply a query with calculated weeks, months and years but I also need to compute the nb of workdays between 2 dates and return the average time (via a UDF VBA function, designed by MS...). When doing this the pivotchart takes ages to get displayed. Even with a simple DateDiff the PivotChart is quite slow to appear.

There must be something wrong because 13000 rows is not a lot for Access no? I've tried indexing fields on and off but it doesn't really help. And I mean I cannot use parameters in the query to restrict records because the purpose of a PivotChart is namely to be able to summarize a large amount of info, over short and long periods...

Ive just seen that computing a PivotChart on a table (not a query) takes more time in Acces than the same table opened as a spreadsheet in Excel. Excel PivotChart seems instantaneous. What's wrong then?