Page 1 of 1

question about access database

PostPosted: Fri May 02, 2014 3:13 pm
by mr cubefan
Hi neobookers, im using a neobook database (DBpro) software that connect to a access 2007 database in a remote server. The software runs fine with no problems, but when 3 or more (no more than 6) pc are using the database there is a lag "freeze" of 3 or 5 seconds with every update or query. i like to know if this is "normal" or can be improved. the driver is jet 4.0 and the option serverside is enabled.

Re: question about access database

PostPosted: Mon May 05, 2014 11:09 am
by Neosoft Support
You might take a look at this article from Microsoft about speeding up Access databases:

http://msdn.microsoft.com/en-us/library/office/dd942824(v=office.12).aspx

It includes some tips for multi-user settings too.

Re: question about access database

PostPosted: Mon May 05, 2014 6:46 pm
by mr cubefan
Thanks for the answer!, i ve already tried some of the tips of that article, but no speed up. the issue happens when the pc "1" performs a query in table "A" and update the resulting dataset. meanwhile the pc "2" performs a different query in table "A" and the resulting dataset is copied to the table "B". Table "A" is in one access database file and table"B" in another one.

Re: question about access database

PostPosted: Tue May 06, 2014 10:59 am
by Neosoft Support
If the database is really large, then those queries might be asking the server to send a huge amount of data to the local machine. You could try limiting the results with your SQL code.

You could also try experimenting with a "server size" cursor. From the DBPro help file:

By default NeoBookDBPro uses something called a client-side cursor. Simply put, this is a method of accessing data that relies on the client application to handle most of the data storage and processing. Client-side processing is very flexible and generally provides the fastest performance for small to medium sized databases. For large databases, however, a client-side cursor can sometimes consume too many system resources resulting in poor performance. To compensate for this problem, you may want to use a server-side cursor when working with very large databases. In NeoBookDBPro you can switch to a server-side cursor by adding "CursorLocation=Server" to your connection string. For example:

dbpOpenDatabase "MyDB" "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[PubDir]AddressBook.mdb;CursorLocation=Server"

With a client-side cursor (the default) all data is copied to the local machine and processed there. This provides access to features not normally supported by servers such as sorting and indexing. When using a SQL query, only the data returned is copied to the local machine. A server-side cursor doesn't provide as much flexibility, but is often more appropriate for large databases, and may be required when the size of a database exceeds the available memory and disk space available on a local machine.

Re: question about access database

PostPosted: Tue May 06, 2014 6:09 pm
by mr cubefan
Thanks! the cursor location option is set to "server", this gave some speed to the database, about the results from the queries, i think you are right, there is to much data (like 500 datasets), so i will change how much data is processed.
Tomorrow i will post any news.