Question Access runs slowly... CPU goes idle during query (1 Viewer)

[phinix]

Hi guys,

I have a strange problem, which is a real pain... after I build a query and click to view results Access starts to calculate it, then after few seconds all CPU cores go idle.. then stay idle for about 30-40 second, then finishes query calculation in next few seconds.
Is that normal behaviour? How can I make my pc to work 100% of the time and stop those strange pauses?

 

[DCrake]

What is going on in our query? Are there any complex calculations going on?

 

[phinix]

What is going on in our query? Are there any complex calculations going on?

nope. It's happening for every query I run.
Two tables, records comparison, matching by 3 elements
or two tables pulling combined records
or one table, pulling records that mean some few criteria.
Simple things. Table sizes from 50.000 records to 2M.

 

[DCrake]

Have you got indexes on the fields in question?

 

[boblarson]

Sorry to jump in here David, but I want to ask phinix - Is this on a split database? If so, we experienced the same thing here and found that Access was opening and closing connections and waiting for stuff coming across the network. So, one thing that may help is opening up any table (we use a hidden form that has one lookup table opened) before doing anything (we do it upon startup and leave it). That sped things up a bit.

 

[phinix]

Have you got indexes on the fields in question?

not all of them, but most of the time I work on tables with indexed IDs.
Would this make a difference?

 

[phinix]

Sorry to jump in here David, but I want to ask phinix - Is this on a split database? If so, we experienced the same thing here and found that Access was opening and closing connections and waiting for stuff coming across the network. So, one thing that may help is opening up any table (we use a hidden form that has one lookup table opened) before doing anything (we do it upon startup and leave it). That sped things up a bit.

Well, yes there are separated databses - tables are sometimes 2M records and in Acces file they take around 1.5GB so I keep them in separated database files and link them. So, could it be it?
Do you open this form on any of those databases or on that linked one?

 

[gemma-the-husky]

if you have 2m records, it may depend what technique you use to filter them.

In some cases Jet will give you the sub-set you want . In others, it gives you the lot, and then you have ot choose them, So 2m records takes a long time ot transfer even over a hi-speed LAN.

Maybe that's the cause.

 

[phinix]

if you have 2m records, it may depend what technique you use to filter them.

In some cases Jet will give you the sub-set you want . In others, it gives you the lot, and then you have ot choose them, So 2m records takes a long time ot transfer even over a hi-speed LAN.

Maybe that's the cause.

But all those databases I use are on my PC, just separated drive

 

[gemma-the-husky]

well especially if you have multiple backends - each backend will have to give access the full data set, for access to be able to run the query on the combined data.

if you have such big files, you probably need an SQL back end.

 

[phinix]

well especially if you have multiple backends - each backend will have to give access the full data set, for access to be able to run the query on the combined data.

if you have such big files, you probably need an SQL back end.

What do you mean by backend?
Do you suggest to install all those tables on SQL server?

 

[gemma-the-husky]

Well, yes there are separated databses - tables are sometimes 2M records and in Acces file they take around 1.5GB so I keep them in separated database files and link them. So, could it be it?
Do you open this form on any of those databases or on that linked one?

this is what you said.

I presume (not 100% sure) that if you are trying to get information using a query that needs info from several databases - then the server (your PC) has to pass all the data to your current access session - so you can decide what you want.

Now If all the data is in a single database, then in many cases, access (ie the access db manager process) need only give you the subset you wanted - much quicker.

But it's still a matter of indexes, and the nature of the query - in some cases access still needs to give the process everything

However, access has limits, and if your files are bigger than this limit, then you need to store the data in a different database, such as SQL. Even doing this, it may still depend what data you are tring to extract.

If you can't index it, then the DM Manager, will probably still need to interrogate every record.

You may be able to achieve what you want, by interrogating one of the dbs's to create a small table in your host, and use that to recover data from the others. I think it's just try different things, and see what works.