Source language: Translate to:

question about access database

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

question about access database

Postby mr cubefan » Fri May 02, 2014 3:13 pm

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.
User avatar
mr cubefan
 
Posts: 27
Joined: Thu Mar 10, 2011 6:23 pm

Re: question about access database

Postby Neosoft Support » Mon May 05, 2014 11:09 am

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: question about access database

Postby mr cubefan » Mon May 05, 2014 6:46 pm

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.
User avatar
mr cubefan
 
Posts: 27
Joined: Thu Mar 10, 2011 6:23 pm

Re: question about access database

Postby Neosoft Support » Tue May 06, 2014 10:59 am

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: question about access database

Postby mr cubefan » Tue May 06, 2014 6:09 pm

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.
User avatar
mr cubefan
 
Posts: 27
Joined: Thu Mar 10, 2011 6:23 pm


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests