So I have created a crosstab query that takes about 8 minutes to run with only 4 fields (2 Row Headings) with 4 where clauses.
The trivial thing that is happening is that when I monitor the MySQL Server Processes tab, it seems to be going through each and every one of the primary key's fields, sending it over to Access, then continuing to go through the primary key field records one-by-one until it finishes, then Access wraps it up into the Crosstab format in Datasheet View.
There is a program my manager uses which shows how many queries the server is processing which shows something crazy like above 1,000 (at the current time, only him and I are the ones performing queries on the Server so we know it is from the 1 crosstab query we are sending).
There is 1 table which has some 3 and a half million records in it (pretty huge, huh?) which I believe is causing the slowness (the other tables are small compared to that table). That table holds the primary key id (all unique), a month field (1 entry for each month but multiple months - possibly 4/1/2011 through 9/1/2012 as of now and only getting bigger as time goes on), then 2 other fields where there is only one entry for each one. In other words, the primary key field and the other 2 fields repeat their values for each date entry (which is making the table huge)
My idea would be to create a macro that deletes a reporting table (totally separate table created for this problem) and rebuilds it monthly or something with only the last 3 months worth of data so that I can report off of that one. Please tell me if that is a good idea?
But why is MS Access sending thousands of queries (what is the process behind that? Is that Access's way of querying the server for crosstabs?).
Thank you in advance for your expertise and insights