Run query against different database tables

[shabar]

Hi All

I have two databases having different tables one separate database to run queries where all the necessary tables linked to that DB.

When doing above are there specific things to be considered when execute queries to get good performances (I have created necessary indexes in tables)


Appreciate your response



Cheers

Shabar

[pbaldy]

One potential problem is that if you are joining tables in different back ends in a query, Jet/Ace may not be able to pass the SQL back, and will have to pull all the data from both to the front end so it can process things.

[shabar]

Thax for your reply pbaldy,

You mean to say If tables are in DB_1 and DB_2, if the queries running on DB_3, Needs to pull all the relevant data from Db_1 and DB_2 into DB_3 and then run it will run the query?

The main reason why I created separate database is because of the size limitation (2GB). So incase if the query database exceeds limit of 2 GB due to pulling data before running the query that's gonna be a issue.....

What could be the possible solutions...


Cheers

Shabar

[shabar]

Further

I've got disk space error when import second csv file to a table in new database. But when check the DB size its ONLY 645,000 KB. Only one table is there having 390245 records. New csv file got 531198 records. But The earlier database I have a table with 1.6 Mio records with other tables as well. What could be the reason (Using Access 2007)
Cheers
Shabar

[pbaldy]

Generally when you have a query that joins two tables in a back end, Access can pass the SQL to the back end and it will process it and only return the result set. When the tables are in different back ends, it can't do that so likely each back end will pass the whole table to the front end so it can handle it. That takes time.

A compact/repair may help your db size, but you might also consider going to a more robust back end. SQL Server has a free version, and there are others.

[shabar]

Thax a lot pbaldy. That make scenes

Appreciate your comments

Cheers

Shabar

[pbaldy]

No problem.