[RESOLVED] [ACCESS] Access Sending entire table accross network before filtering data.

Question:
I am looing for ways to improve Query speed, reduce data retreived from table, and limit the amount of data required to send over the network.

I assume that if I was able to limit the data sent from the BackEnd Server Database BEFORE it gets sent to the MS Access FrontEnd, this would drastically help, as it seems I am sending too much, to most of the table back.

Do WHERE conditions check the table items on the server side, or client side? What ways filter or check information on the Server Side?

Information:

I haven't posted any queries yet, but if it is easier to durdge through 10-20 queries/Stack Queries I can do that, or a combination there of.

I have a report with 10 sub-reports. It is an overview of an employee's performance for a year. The MDB file is only 70megs. When I was using a VPN connection from home, I saw that I ended up transfering about 20-30 megs of data when trying to run the report, it also of course, took a long time.

I have been looking around and found various options on improving speed, but no one talks directly about the timeline, or order of opperation that access uses.

This is my guess as the timeline. Please post the correct timeline at which point the entire remaing table is sent over the network.

Query sets link to table.
Checks connections. (SELECT FROM)
Reviews relations. (Joins)
Retreives data (which includes sending it over the network)
Filters data (Where condition)
Groups data (Group By)
Re-filters data (having)
SortsData (Order By)

I have the JET DEBUG=on option on, and when reviewing the showplan.OUT file, I really have no clue what any of it means.

Also, it seems like the amount of data transfered is REDUCED if I remove the where condition from a query, why is that?

As I'm not sure if there is an end all solution I'll start with what I've tried, and what I am trying.

First I've been going through my queries trying to replace any VB Specific Functions with classic or true SQL commands, as one of the suggestions in the query speed up tricks.

I've been going through and creating Indexes on items used in where conditions.

I have been trying to use JOIN's as much as possible instead of where conditions or other odd ways.

Links I found:
Query Speed up tricks.
http://allenbrowne.com/QueryPerfIssue.html
Show Plan information
http://articles.techrepublic.com.com...1-5064388.html
MS Help and Support page
http://support.microsoft.com/kb/239527/EN-US/
Indexes
http://www.techonthenet.com/access/tables/indexes.php

Hi!

What kind of database lives on the other end of the network? Access, or something else?

Also, are you connecting via code, or using linked tables?

My understanding is that if you are using Linked tables, this will be slower, especially if you are connecting to a different DB (such as SQL server). In this case, there is a multiple translation process from JET to ODBC to Whatever other Db you are linking to.

Also, are you indexing text fields, or just numeric? I do believe things can be slowed WAY up

If you are connecting to anything but another access database, you should look into executing stored procedures on the server end via code on the client end.

If you are using a set of Access tables on the server end, you could create the query there, then execute it over the VPN.

Last, I believe the core problem is that you are connecting over the VPN. Probably the very best thing you can do is load your stored procedures (or Access queries) on the server end, and then execute from the client over the VPN. Or, second-best, Import the table set for the purpose of running the query at home, at the time you need the data, then output the report. Further, the VERY worst scenario (in MY theory) is trying to send queries over a VPN using linked tables, ESPECIALLY if the Db on the server is something other than access!!

I am not as well-versed in some of this as many of the super folks here on the forum, so they may have better answers. There is also a good chance I am just plain WRONG on some of it. However, I am in the midst of working through some of these same issues as a migrate from an Access back-end to a SQL Server back-end, so I may have some ideas. Also, I am interested to see what others come up with. Let me know . . .

You were right about most of it, but I don't think the VPN part is quite right (it is basically just a slightly slower network). That could be improved tho, by using something like Citrix, so all of the work is done on the server, and the only thing sent to the PC is screen updates and if apt print jobs (and input from keyboard/mouse get sent the other way).

Quote:

---

Originally Posted by rack

Do WHERE conditions check the table items on the server side, or client side?

---

The entire SQL statement runs server side.. but file-based databases like Access are a bit different, as the work of the server is actually done by the client; so if you have the choice, use a server based system (like SQL Server/Oracle/MySQL/...) for the back-end.

hi,

if you have an access db backend and an access db Fron End and use bound forms /reports via linked tables then the db will have to pull nearly all the data from the backend. one way to reduce this would be to use ADO. this would also give the added comfort of being able to manage the connections the user requies.

I have a DB with linked access tables and it is very poor at performing basic tasks. i actualy develop the db on my C:drive by de-linking all the tables.



thansk
David

Quote:

---

That could be improved tho, by using something like Citrix, so all of the work is done on the server, and the only thing sent to the PC is screen updates and if apt print jobs (and input from keyboard/mouse get sent the other way).

---

Si- Is this what the folks in my IT dept. refer to as "terminal server"?

Not exactly, but it's a similar idea done in a more user-friendly way.

So from everything said so far, and a few things I read last night, it sounds like the options are as follows for me.

1. Citrix
2. SQLServer, MySQL, or Oracle Back End
3. Terminal Service of some kind
4. Insanely fast Internet connection for the VPN.
5. ASP.NET (ASPX)

Did I miss any options?

I know our company has an Orcale licence and database installed for the payroll and a few other systems.

I also know that SqlServer Express is free even for commercial use.

1. Is converting just the back end to SqlServer, or SqlServer Express extreamly involved?
2. Meaning am I going to loose the abillity to used linked tables?
3. Am I going to be able to use bound controls, prebuilt stored queries, access reports?
4. Will I need to use ADO to connect to the backend instead?

If the migration from an Access BackEnd to a SQLServer BackEnd isn't too hard, I think that may be the best option, as it sounds like you are saying that SqlServer runs the queries on the server side, as well as having Stored Procedures.

Thanks for the imput thus far, I eagerly wait for your responce. :)

Migrating to SQL Server isn't too hard, as Access includes a tool to do it (not sure if it supports SQL Server 2005, but in theory it should).

As was mentioned above, Linked tables are a bad idea when it comes to speed (but I don't know if SQL Server as the backend would be better or worse than Access). It is definitely worth looking into alternatives, and if it is just the speed of the report you are worried about then conversion to a Stored Proc may be easy enough.

Bound controls can be bad too, but not to the same level, so aren't such a big deal.

I've read the same thing a million times over and feel like an atom-a-ton saying it.

"I recently took over a database started by someone else, and now am updating, creating, and managing its BackEnd/FrontEnd"

So as I learn more and more, I realize how silly the access backend for a multi user system is, when you have users accessing the database from places so far away that they only get 1-5MegaBit connections. Drastically slower then an Internal Network (Intranet, LAN).

As there are numerous existing reports created in MS Access, using queries that are pre-built, I have been at a loss on ways to migrate away from this.

All new forms I create inside the MS Access front end do not use Databound Controls. I do still use DAO though, but that is because I have herd that when working with Access, in access, with an access backend, DAO is the best option.

I have been reading up about ASPX (ASP.NET) but this would eliminate the front end as well. I would then need to re-create everything from forms and queries, to reports. I have yet to get to an area in ASP.NET where it talks about a report feature. Which leads me to believe I may need to find a reporting tool, or reporting object I can install and attach.

It sounds like a MS Access BackEnd would still be possible (though not recommended) with ASP.NET as ASP.NET runs all code server side, and sends back only an HTML page to the user.

Regardless it sounds like I have my work cut out for me in learning one of these new options I know little about.

I will have to see if a SqlServer BackEnd allows Linked Tables, and if it does, if it will end up having the same problem that Access doe in regards to sending basically the entire table back to the client.

The unfortunate thing is that I recently created 2 huge new sections that will be a pain to migrate to ASP.NET and VB.NET from VBA (which is similar to VB6)

So just to confirm, it sounds like no matter how I write, structure, or set up the saved query, MS Access being the backend forces the entire table to be sent to the client ?

Quote:

---

So just to confirm, it sounds like no matter how I write, structure, or set up the saved query, MS Access being the backend forces the entire table to be sent to the client ?

---

My understanding is . . . Not quite, although it sounds like if you are using bound controls, it might as well be.

If you can get away from any bound controls, and want to use the existing set-up for NOW (until you get Oracle or SQL Server happening!), I (in my limited knowledge . . .) recommend creating Access Queries on the BACK END which you execute (via code) from your client. This leaves the query processing on the back side, and returns only the result set. Unless I am completely wrong (always a possibility), this is as close as you can get to calling stored procedures from an access back end, and it should be the FASTEST option (Except Citrix) as long as you are using an access back-end.

An access client CAN "link" tables to a SQL Server (or any other ODBC-compliant database), but I am told the technology is old, and again, you are faced with a multiple-translation connection which is probably the WORST option available.

The BEST bet (I can't speak for Oracle) will be a SQL Server (express or otherwise) backside with stored procedures which you execute via CODE from your client. If you are not familiar with it, I have JUST learned how to do it. Once I figured out the technical part, life became SOOO much better than using Access as a back-end. I think the only better bet would be the SQL Server set-up I just described, coupled with a Citrix connection.

I am happy to provide some samples, but other (more knowledgable) members probably can do a better job. let me know . . . It always helps to learn by teaching!

Quote:

---

Originally Posted by rack

So as I learn more and more, I realize how silly the access backend for a multi user system is, when you have users accessing the database from places so far away that they only get 1-5MegaBit connections.

---

It depends on the number of simultaneous users - Access gets much slower for each additional one (5+ is an issue), whereas a server based database system can handle many more without a significant slowdown.

Simply switching to a different backend database system could well make it much faster, maybe even without the simultaneous users issue (but as I don't use Access front ends, I'm not sure).

Quote:

---

As there are numerous existing reports created in MS Access, using queries that are pre-built, I have been at a loss on ways to migrate away from this.

---

Depending on the content of the queries, you might be able to very easily move them to Stored Procedures (which would mean all of the work is done server side, only the final data gets sent).

Quote:

---

All new forms I create inside the MS Access front end do not use Databound Controls.

---

Is that part of the process a speed issue? If not, I wouldn't worry about it too much at the moment (but come back later if there are still speed issues in any part of the system, and do some testing to see if it does have an effect).

Quote:

---

So just to confirm, it sounds like no matter how I write, structure, or set up the saved query, MS Access being the backend forces the entire table to be sent to the client ?

---

I think that is pretty much correct.

Technically it is a bit more complex (as there are things like locking info to worry about too), but that is a reasonable way to think of it.

Quote:

---

I have been reading up about ASPX (ASP.NET) but this would eliminate the front end as well. I would then need to re-create everything from forms and queries, to reports. I have yet to get to an area in ASP.NET where it talks about a report feature. Which leads me to believe I may need to find a reporting tool, or reporting object I can install and attach.

---

ASP and ASP.net create HTML pages... so it is easy to create simple reports in HTML (but multiple pages need a little more work, and adding graphs etc typically needs extra components).

Quote:

---

It sounds like a MS Access BackEnd would still be possible (though not recommended) with ASP.NET as ASP.NET runs all code server side, and sends back only an HTML page to the user.

---

True, but in that situation Access isn't as bad as your current setup (as there is only one user connected, ASP itself). If you go for ASP, it would still be better to use SQL Server etc if you can.

Quote:

---

Originally Posted by RunsWithScissors

I (in my limited knowledge . . .) recommend creating Access Queries on the BACK END which you execute (via code) from your client. This leaves the query processing on the back side, and returns only the result set.

---

I'm not sure if that is correct, but it is a possibility (if it automatically uses the "MS Remote" OLEDB provider, but that is hard enough to set up manually).

Using Citrix would mean that it definitely executes on the server side of things, but it can be hard to set up and maintain.

Quote:

---

So just to confirm, it sounds like no matter how I write, structure, or set up the saved query, MS Access being the backend forces the entire table to be sent to the client ?

---

I may be wrong here but i don't think this entirely correct. if you setup the FE using ADO (Not sure about DAO behaviour on this point) but when you query the data using VBA then it will only pull the results that you need to return down the line. dependant on the type of connection that is used. writing this i think that when the connection i opened that it does pull quite a bit down but i don't think it is like using linked tables(Si may correct me on this)

I have built Access DB's with a user base >250 (Not all on a the same time working shift paterns) and the applicaiton worked with minimum errors the data was actualy embeded in the Front end DB on a LAN. I now have a user Base of 12 and the applicaiton (inherited) crashes on a daily bases due to the arcitecture, linked tables IMO are a total waste of time and cause more issues that they are worth.

All my own applications either have the data within and use DAO(I know it's not the best way but it works well with my infrastructure) or the backend has connections to it Via ADO. I feel this works better and allows me to controll the connections at runtime and prevents the (too many users error)

I personaly would also refrain from using queries built in access for returning results to the front end as this can cause major issues with too many connections.

I would prefer to have a server side DB but my IT dept does not allow for "unsuported applictions"

i may be off the wall but... would it not be possible to set up a small client on the server that will allow you to pass a SQL string and return the query results. this may prevent you having to pull all the data down the wire if the above is not entirly correct or use an FTP for a remote site.

Thanks
David

Hmm, I know that the eventual goal, or wish, is that I turn the database into an online application, where users can connect to a web site, sign in, access the reports they need, or enter in data, and then log off.

I think that to expediate the moving to an online "Front End", and to reduce the data sent over a network, I will just continue to learn about ASP and SqlServer. Once I know enough to fiddle, I will upgrade the backend to SqlServer, and then see if I can link the Front End MS Acces tables (temporarily) to the SqlServer Backend, this way I can work on the Front End, while users are saving data to the new, current, and correct backend. At the same time allowing me to find errors, or differences relating to the difference in SQL commands, etc.

Luckily all the reports that have been created prior to me taking over had no graphs, and all the ones I created also have no graphs. Though some of them do have multipule pages, which you stated will be a challenge.

SqlServer Backend
+
ASP.NET FrontEnd

This sounds like my direction.

I want to thank everyone for their input. It helped me understand more, and direct my attention more toward what I should be. Bonus will be that I learn more about .NET since Most of my VB programming has been with VB4, VB6, and VBA. Also will gain knowledge of IIS.

I'm always open for input or suggestions if any more come up. For now, I belive I am going to mark this thread complete so that hopefully it will benefit others in the future. =)

UPDATE!!!

I was finnishing up my optimizing of the queries when I came accross a query that uses 2 other queries.

While looking at the two other queries I noticed that there was a lot of information from the tables being requested, that I obviously didn't need.

I checked my packets received before, and then after, running the sub report that this query used. It transfered 27 megs!!!!

I went to work, removing extra fields that didn't need to be in there, ran the report again, *Crossed my fingers* and guess what? It only transfered 2,558k or 2.5 megs. Obviously this is still returning every record, but it must only return the fields in that record that are requested.

This means that the smaller the field size, the fewer the fields, the less data gets transfered.

I just wanted to add this, as this was something that I wasn't funny aware of, or didn't fully understand.

That's a very nice improvement - good work! :thumb: