• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 737
  • Last Modified:

Crosstab Query Slowness

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
0
IEHP1
Asked:
IEHP1
  • 3
1 Solution
 
als315Commented:
I'm no failar with MySQL, but generally hard crosstab queries should be made on server side or you can make a local copy of all data in Access DB.
May be these links can help you:
http://www.mysqlperformanceblog.com/2010/07/12/intro-to-olap/
http://en.wikipedia.org/wiki/Mondrian_OLAP_server
http://jpivot.sourceforge.net/index.html
0
 
FugasCommented:
Hi, you should better try to do the query with joins. And maybe when you upload your empty database and that query we could help you more. 3,5 million records is not a lot for any database server, but ACCESS could have problems.
0
 
IEHP1Author Commented:
I am doing the query with joins??? Did you think I was just putting tables in there with no links??? Thanks for the information but I had already posted that I can see the server processing the query one record at a time....so once the server processing is finished, Access query completes really soon after that (within a couple seconds or so).

I think maybe it is just because the server doesn't have enough memory and CPU speed to process the crosstab query which is using the biggest table we have at 3.5 million records. But I was just trying to find out why the server has to process thousands of queries for this one crosstab query??? It seems like a weird MS Access thing or something that I hadn't been aware of until now (probably because the employers I worked for in the past had much better server processing).

What do you think?
0
 
IEHP1Author Commented:
Let me put it this way (I am trying to explain this "phenomenon" the best I can): So I can see on the MySQL server that it processes a first query (where it only pulls basic information), then it changes the SQL statement to a statement that is table scanning every member in the table (one record at a time).

I am so baffled why it can't just execute the one query I want it to do instead of going through multiple SQL select statements (every time I hit the Process tab it refreshes so that I can see what the MySQL server is executing).

I checked out the links and thank you by the way for that, but what we have currently is what we have and my manager needs to get this query to "rock 'n' roll" if ya know what I mean?

I know that this might sound weird but that is what is happening on the Server side. MS Access SQL is not compliant with MySQL server right? Does this have something to do with it?

I think it might be a good idea also to change the subdatasheet to None instead of Auto right?

Please help me with anything you know......
0
 
IEHP1Author Commented:
Nevermind, I got it!! Thank you for your help!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now