Access Queries (In Your Experience...)

[MethadoneBoy]

Hi guys,

I'm working with a database that cotnains a huge volume of text information. There are four major queries that pull different permutations of this data but each one of these queries takes an awfully long time to run due to the sheer size of the data being retreived. What's more, each of these queries have to be run consecutively because they're in the same database.

To speed up the process by allowing each query to run concurrently, I decided to move the queries into separate databases. What I was hoping to do was copy a vital table in the original database (that all the queries use) into each of the slave databases and then run each query independently. Which is where the problem comes in. Copying the table takes an awfully long time as well.

Does anyone here have experiences similar to this? Is there any way I can copy a table into an external database relatively quickly? At this stage, I'd even be willing to entertain outputting the table into an Excel file and reading in from there.

Any help would be appreciated.

Thanks

[DKenny]

What type of database are we talking about? Access, SqlServer, Oracle, MySQL?

[salvelinus]

Well, he said Access in his title. Newer versions of Access allow stored procedures, or so I've read, which might help.
When are these queries run? Possibly you could run them late at night when no one's around. That's how a lot of stuff used to be run.
You could index the relevant fields to speed things up. If this resulted in too many indexes for efficient updating, etc., you could create them before the query is run & delete them after, although that would take time too.
If a large portion of the results is returned every time you run the query (i.e., you always run it on all data from beginning of the year till present), you could archive the previous results & run a smaller query on just the date since it was last run & append the results to the archive table.
Another option if the records of subsequent queries come from the results of the previous query(s) is to just run the subsequent query on those results.

[Webtest]

I speeded up access to a remote Oracle database a lot by using a linked table instead of using a query to fetch a recordset from the database. At least it works for me!

[dannymking]

Take it out of access and place it as a vbscript file which can then be scheduled as a when needed to run.

[NeedSomeAnswers]

Can you not take it out of access completely ??

You are always going to have speed issue's with large volumes of data in an Access database, why not try downloading MySQL ?

Its free and and handles large volumes of data far better than access, and should give you faster querying speeds.

In fact i would recommend virtually any other database for large volume data over Access !

If not ...

what indexing have you got on the columns you are querying on ?

Also what query's are you running ?, can you post one or an example ?