CPU Usage

[worldwidewall]

Hi



I am running access 2000 and Windows XP.

When I leave Access alone to run a query that takes hours, I notice that the CPU usage is hovering around the 50% Mark. How do I get Access to take full advantage of the processor power? While the query runs I want the CPU to be pegged at 100% usage. I would imagine if I could do this my query should take 50% of the time to run?

Thanks

Dave

[orange]

What kind of query takes hours to run? Please describe your environment, database and query so that others may provide some comments/advice.

[worldwidewall]

It's a make table query that takes data from 6 tables. There are multiple joins in each table. As an example one table that I just made is 810 MB of data on it's own with 18 million records. It took about 2.5 hours to run. I have several other queries like it some even bigger and I want them to run as quickly as possible. I imagine if I could get access to max out my computer processor they would happen roughly twice as fast?

Thanks

[orange]

I'm sure you could max out machines if you tried. Have you tried to optimize your queries?
Perhaps if you described what you want to occur, someone might come up with some options that would reduce execution times.
It's hard to understand a production environment where several queries take 2.5 hours each to run.
What have you done to
- normalize your tables
- identify your needs in some priority list
- optimize your queries

[worldwidewall]

Hi orange

Thanks for responding.

I am a bit of a hack when it comes to codeing and database work but I am pretty sure I am using Access for something it was not likely designed specifically for.

Each field in all of my tables contains a only a single character, could be a letter or number. If I normalize, I would simply be replacing that single digit with a table index. I know that normalizing is important, and I have done it before in other databases, but in this case would it really make my queries any faster?

[orange]

Can you post your query and/or some subset sample records from your database?
What problems do you have with these long running queries?

[worldwidewall]

Orange

The only problem I have with my queries is that they take too long. The results are fine. They also blow the back end of the database up to the 2gb max on occasion....but I can get around that problem.

[John_G]

One thing you can do is ensure that there are indexes on the fields you are using to do the joins, particularly on the "one" side of lookup tables.

The other thing to try is instead of using one query to join all six tables, start off with a blank table (the one that is the result of the big make-table query), and then run a series of smaller queries to append data to it, the first of which would initially populate the table, and the others would add to it.

Using separate smaller queries would almost certainly eliminate the 2GB limit problem.

You might want to think about whether or not you need something more than MS Access (SQL Server for example).

HTH

John

[worldwidewall]

John G

Thanks for your pointers. I don't have indexes on all the fields there are joins on, I didn't realize I could have more than one index per table, I will try that.

I have tried to break things into smaller bits by using a make table query and then running smaller queries off that data but I could break it up further.

Never used SQL Server before. Maybe it't time to learn that too.

Thanks for you help

[orange]

I agree with your comments John, but I was hoping to get some positive response from worldwidewall.