Queries in the back-ends??? (1 Viewer)

[Newman]

I have queries that give me hundreds of records which they get from many tables with hundreds of thousands of records each.
I am looking for something like a passthru query that would return only the records needed from the back-end.
Do you believe that it could be done without problem or do you suggest that I stick to the basic "back-end is for the tables only" method.
Thanks!

 

[llkhoutx]

A backend with hundreds of thousands of records is not suitable for Access. Queries will run very, very solowly, for hours.

A SQL Server of MSDE BE of tables hundreds of thousands of records is will suited for Access with its ability to creat pas-through queries.

 

[Newman]

Actualy, it takes around 15 minutes to get the reports printed. It is not much a problem knowing that these are monthly reports. They can call it and go for a coffee and a smoke.
The problem lies in the fact that my customer has some old computers (Pentium2-350Mhz with 256M of RAM) from which it is impossible to get the report. These computers freeze.

For the SQL-Server, my customer work on a rented server. They agreed to let us install the Access databases, but they would not let us install anything bigger than that. And the MSDE option is out of question since there is too many persons working on it simultaneously.

Thank you for your suggestions.

 

[RoyVidar]

I don't think having the queries on the backend would give any gain, since you'd process them form the client anyway, and how would you reach them?

I'd try working on the indexes, and see if that helps (any fields occuring in the from clause, where clause and orderby clause).

If that doesn't give enough, I think I'd try creating temporary tables in the front end, to see if that can speed up the process. Try different alternatives from dumping all information over, to doing some preprocessing in the fetching query/queries. This will of course bloat the front ends, so you wuld need to have some strategies to deal with that too (compact on close...?).

If neither of these approaches work (or perhaps in any case), try persuading the customer to uppgrade their equipment. I would be tempted to say that their equipment does not match their informational need.

 

[Newman]

I already did temporary tables in the front-end, with not much success...
I was tempted to put both front-end and back-end on the server. Just a link to the db on the local machine. That way, I thought that the server would be doing the query.
I just realised, reading your post, that Access is always doing the query on the local machine, no matter where the database is located.

Thank you!

 

[Pat Hartman]

Jet is a file server. Fileservers simply retrieve files so Jet has to do all its processing locally. If you have indexes, there are cases where Jet will pull down the index file which is much smaller thant the data file and that speeds up processing but if no index is available, then the entire 100,000 recordset will be transfered to the client for processing.

If you upsize the tables to SQL Server or Oracle or whatever you have available, you can then offload much of the processing to the server. Jet sends most queries to the server for processing even if they are not specifically pass-through queries. So, as long as the query has selection criteria that limits the rows returned, you will see dramatic performance improvements.

Most people design Access databases as if the tables were local. They base their forms/reports directly on tables or on queries without criteria. This needs to change when you upsize. All forms MUST be based on queries with slection criteria to take advantage of the server. If you don't make this change then even with an SQL server back end you'll still be porting 100,000 rows to populate a form

Putting both the fe and be on the server just slows things even more since now their is a lot of increased network traffic to handle getting things from the fe.

 

[Newman]

Thank you Pat!