Which is Better ?

[vich]

I have a split database which runs across a network for 10-12 users. In the system there is a table, "Daily Activities" which has 50,000 records and is growing approx 20/30 per day.

I have a form which lists the records that have not been closed. I want to open another form to update the details of the activities and several other tables of information.

Is it better to open the detail form with a bound dedicated record source(query or SQL) with the criteria for the activityID or open the form and populate from a DAO record find or open to the table and do a filter?

I am comfortable with coding any of the approaches but am not sure which would provide the best performance results.

Thanks

 

[fneily]

In my opinion - right now you're working with the Jet database engine which moves data back and forth between tables, forms, reports. So when you run a query on a local machine, it fetches the whole table from the server, moves it across the network to the local and then processess the data. If you made an Access project, you'd be using the MSDE database engine which is a client/server engine completely compatiable with SQL server. The view (query) is ran on the server and only the result is returned across the network. Thus, less network traffic and quicker. Plus Jet is go for small groups where as MSDE can handle hundreds of users. It also has transaction log capablities.
Since you're using the Jet, I would archive the closed records to another table, deleting them from the main table thus keeping down the number of records in the main table since it all goes over the network. Since you'd be deleting, I would also run a daily compact and repair to keep the database size down.
In my opinion.

 

[JeremyNYC]

With that few users and that few records (50,000 is a small number), there's no real need for MSDE. Vich, it sounds like the you are asking whether to use a bound form or an unbound form. While an unbound form gives you more control over what's going on, it requires a lot more work. Even though you are capable of doing that work, why do it? Access will take care of all of that for you.

The only real way to know about performance in any particular situation is to test it out--build a bound form and an unbound form to do the same thing and write some code to test it a couple hundred times and compare performance.

But my guess is that there's no need to do this. That edit form you're talking about will only be opening one record if you pass the proper ID as the filter clause of the statement that opens the form. It will be plenty quick.

JeremyNYC

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[vich]

Thanks for the replies.

I understand the MSDE would greatly reduce network data traffic. It would probably take a fairly large effort to convert and maintain but should consider going there at some point.

With that said.

It sounds like, without testing, that using either ADO or a bound form to retrieve the data either one will pull all of the data back to the local machine and filter(select) the desired record thus I am guessing performance would be similiar. Is this a logical assumption?

Second question then, if using bound form does anyone know if there is any performance benefit over using a query as record source verses a SQL statement?

I appreciate everyones input. I could build scenario's but I felt sure someone has probably done research and testing on this before.

Thanks

 

[JeremyNYC]

First, I'm not entirely sure it's correct that it will pull all the data across and then filter it. I'm pretty sure that's not true, though I wouldn't stake much on that bet.

On the next question, there is a negligible performance gain to using a saved query or saving the sql in the property sheet of the form (Jet creates the query and hides it), because Jet can compile it once and not have to do that at run time. But keeping SQL in your code will not present any noticable lags unless you're using a really complex query.

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org