Crosstab Query Slowness

Posted on 2012-09-07
Last Modified: 2012-09-10
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
Question by:IEHP1
    LVL 39

    Accepted Solution

    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:
    LVL 4

    Expert Comment

    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.

    Author Comment

    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 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?

    Author Comment

    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......

    Author Comment

    Nevermind, I got it!! Thank you for your help!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now