Slow After 170,000 Records... 1

[bluenoser337]

My Access 2000 DB has data being written to a single table from a VB6 application (ADO). At around 170,000 records, the time required to store a record can be as long as 10 seconds. At zero records, I can store more than 10 per second. What causes the slowdown...and is there a way around it? With each record insertion I open and then close the connection.
connection.open
set rst = new adodb recordset
rst.open
rst.addnew
rst!"fields"
etc
etc
rst.update
rst.close
connection.close

Of course, not all the code is shown here.

 

[Crowley16]

well, you've answered your own question really...

every time you open it, it opens the entire table, and as the records rise, so does the time needed to access those records...

instead of using ado, try using a sql string instead...

 

[bluenoser337]

Crowley16...thanks for the response! I'm not sure I understand. I'm new at this and I thought I had to use ADO to get the data from VB to Access. If it makes any difference, the data is not being entered by a human but is automatically generated by the VB application (based on measurements of manufactured parts).

 

[Crowley16]

you would need to use ado to get data from vb to access...

however what you can do is to send an sql statement to access that incorporates the data required, especially since you're inserting 1 record at a time...

so you'd open the connection to the access database, and pass in an sql statement, and then tell it to run the sql...

I'm not 100% sure on the exact code required for this though...

 

[bluenoser337]

Thanks! I'll check into this.

 

[MichaelRed]

Regardless of the access method (ADO, SQL, etc) Ms.A. is not a true client-server database, and suffers from the lack of features such as operating "on the server side". A true client-server db engine (SQL Server; ORACLE; or even MSDE) is much more likely to serve your long term needs for recordsets in the range you mention (X * 100K). In particular, you do not mention the frequency of sampling (record insertion) and retention period, which determine the size (record count) of the table. If it continues to grow, Ms. A. will continue to slow down.





MichaelRed
mlred@verizon.net

 

[MikeRBS]

This is an interesting question. I'd not thought too deeply about recordsets when you are adding a record. Presumably if you create the recordset by SELECT * FROM TABLEA WHERE FIELD1 = 0 AND FIELD1 = 1 you have a recordset that has no records but which you can add records to?

Curiously I need to do something where I'm adding records to an empty database so I'll see if it works.

Is there anywhere where it describes how Access works with Jet to create dynasets?

 

[cmmrfrds]

Use the execute method of the ADO connection object and see what your results are it will be faster than the technique you are using.

connection.open
Dim mySQL as String, recordsAffected as long
mySQL = "Insert into mytable (fld1, fld2, etc...) Values (22, "123", "etc")
yourconnection.Execute mySQL, recordsAffected
If recordsAffected >= 1 then
msgbox "record added"
else
msgbox "record not added"
End IF
connection.close

 

[willir]

Okay

And my 2¢ worth...
MichaelRed elaborately explained that Access is not a good client / server database application. There are various reasons. One primary reason is that Access copies the entire record set from the database to the desktop. If you open a form with 170,000 records, Access will copy the entire record set plus the values for the controls (i.e. combo boxes, etc.)

Database applications such as Oracle, MS*SQL are client / server applications where the data is kept on the server, and only the current record is displayed to the desktop. A considerable reduction in bandwidth traffic.

There are two assoicates issues with this problem. Aside from the impact to the LAN when copying large record sets to the desktop, you will need space to accommodate the record set on the desktop. Ideally, the record set will be held in memory - response is fast. BUT, once you run out of memory, the local operating system will write the data to the local hard drive -- all of a suffen performance is severely impacted. A temparary solution to this is to add memory to the desktop. I say "temparary", because you will run into the same issue later on as your database grows.

...Moving on.
You can also reduce the amount of traffic and the high requirement of local resources by reducing the amount of data transfered across the LAN. For example, instead of copying all fields / all data for the 170,000 records - say invoices and invoice detail information - you can reduce the amount of records copied over at once by reducing the size of the record set - for example, just copy the InvoiceID, InvoiceDate and the CustomerCode. Then use the OnCurrent event to copy the rest of the information for the specific record - remaining invoice info, customer info and the invoice detail. There may be a slight lag when the detail information is retrieved, but you will have been much "kinder" to the LAN and local desktop resources.

Richard

 

[BNPMike]

If you open a form with 170,000 records, Access will copy the entire record set

People often say Access copies all the source data across the network. It normally doesn't. It's an urban myth. Even if in the case of recordset it does, that's missing the point because bluenoser only wants to deal with a new record, he shouldn't be retrieving any data from the server.

Database applications such as Oracle, MS*SQL are client / server applications where the data is kept on the server, and only the current record is displayed to the desktop. A considerable reduction in bandwidth traffic.

Both Oracle etc and Jet keep the data on the server. Both only display the current record on the client. Jet often drags lots of data across the network to select the record(s) of interest but be clear, Oracle is internally accessing the same number of records to get the selected one to send to the client. The difference therefore is the data in Jet has to be manipulated across a network whereas with Oracle its and internal 'channel'. Obviously this makes a difference but many networks are incredibly fast nowadays. It's typically faster to access data off a corporate file server than it was to access it of a local harddrive of a pc of a decade ago. Also remember that databases are not entirey i/o bound. They call on considerable cpu resource. Access has a powerful desktop pc working on each and every thread - no sharing a common processor, common memory and caches.

There is something odd about bluenoser's situation but we shouldn't say the reason is Access=Bad; Everythingelse=Good.

 

[willir]

Interesting stuff Michael.

Kind of differs from the books and other resources I have read. (I don't have them with me at the time, but I will review tongiht. Plus any white papers by Microsoft)

However, I know that you have a heck of a lot of experience with databases, and I bow to your wisdom.

 

[willir]

Okay BNPMike

Many people mistakenly believe that an Access database file stored on a file server acts as a database server. This is not the case. The difference is in the way data is retrieved when Access is acting as the front end to a database server versus when the data is stored in an Access MDB file. Suppose that you have a table with 500,000 records. A user runs a query based on the 500,000-record table stored in an Access database file server. The user wants to see a list of all the Californians who make more than $75,000 per year. With the data stored on the file server in the Access MDB file format, all records are sent over the network to the workstation, and the query is performed on the on the workstation. This results in significant network traffic.

From Mastering Access 2000 Development, the Authoratative Solution by Alison Balter, 1999, Chapter 19, page 667, Client/Server Techniques. ISBN 0-672-31484-3. I have read similar text in other reference books.

Three years ago, one of my colleagues built an Access database. The database was modest, about 15 MB, and 10,000 records. The problem was that the database was located on a server 1500 miles away from my location. It would take me about 15 to 30 min to open the database, and 2 to 5 min to advance one record - painful! All sites connecting to the database had the same problem. The Oracle solution was light-speed faster -- 2 min or so to load, record navigation 10 to 120 sec.

Again, I value your input, and have enjoyed your previous posts. Databases is your bread and butter. But I find it difficult to explain why an Access database is so incredibly slow over the WAN, while an Oracle database has acceptable performance. (I am not a big fan of WAN resourced applications - but, I am in the minority on this topic)

With the data stored on the file server in the Access MDB file format, all records are sent over the network to the workstation, and the query is performed on the on the workstation. ...seems to explain the performance issues.

As I understand it, strategies to get around this bottle neck is to used stored procedures, stored queries, ADP and other techniques.

I would value your rebuttle...

Richard

 

[BNPMike]

Suppose that you have a table with 500,000 records. A user runs a query based on the 500,000-record table stored in an Access database file server. The user wants to see a list of all the Californians who make more than $75,000 per year. With the data stored on the file server in the Access MDB file format, all records are sent over the network to the workstation,

In that example, most likely all the records would be sent over the network. Jet would use an area sweep. Maybe if you put an index on the salary field you could avoid this but typically that wouldn't be the case.

The issue is that a lot of database activity is not like the example. You want the housing details of Mr Viescas in 23 Sunset Boulevard. In this case you would have a very badly designed database if it swept 500,000 records.

Also the majority of databases take some time to reach half a million records in one table. I've woked in mulit-national banks with databases much smaller than that.

All I'm saying is every database should have careful design as regards performance. If you do this with Access applications you will often but not always be able to get excellent response. If you store huge amounts of data and do massive scans or use complex SQL (ie more than a couple of lines) then it's time to try a server database.

 

[bluenoser337]

Solved by using "INSERT INTO" sql in VB code for each occurence. BTW...this system is a stand-alone.

 

[BNPMike]

I've just been looking at some Microsoft stuff about efficiency and it looks like if you put an index on salary you can select WHERE SALARY > 75000 and Access will only fetch the relevant records across the network. Obviously it has to fetch one or more database pages to get configuration information and then a number of index pages to find out which blocks to call for on the fileserver.

For this kind of thing it useful to seed your Google surfing session with "Rushmore". Rushmore is where Access/Jet uses two or more indexes to further reduce fileserver reads, but it leads you to useful articles on efficiency generally.

 

[BNPMike]

Here's some more Microsoft (Whitepaper: Choosing the Appropriate Database Development Tool):

The Microsoft Jet engine and FoxPro engine are tuned to be as efficient as possible in sending indexes and/or data over the network to the client. To locate a single record, only those pages of the index that are required to locate the record are sent from the server. At that point, either engine can request from the file server the page (or pages) that contains the record's actual data

 

[willir]

BNPMike

Well you can't argue with "The Source". But I am still curious why performance sucks across the WAN with Access in normal mode (meaning non-ADP, stored procedures, ODBC connections, replication, etc.), but similar or even larger Oracle databases perform well. I have read past posts by you and I respect your wealth of experience, but I am still puzzeled by the huge drop in performance.

...But lets save this discussion for another post. Our Nova Scotian BlueNoser got his question answered, and my attempt to answer his question was on the wrong track.

Richard

 

[MichaelRed]

I once worked for a person who used the phrase " ... I love the written word, it is always easy to interpert it to mean what I want it to say ... ". Likewise, MS has sufficient verbiage posted in various formats that it is easy to find varied (and often confllicting) statements on the details of various topics. In many (most) of these issues, the context of the statement needs to be carefully reviewed and understood before the 'lesson' is applied. This is, in my opinion, one of the major reasons for the entire collection of 'white papers'. In general, Ms. A. Is NOT a database Server. It is a FILE server. While Ms. does modify the design of Ms. A. and the Jet engine, I have not seen a statement contrary to this from Ms., while I have seen (and observed) the opposite. Other discussions within htes (Tek=Tips) fora aboound with this topic, with many opinions but general agreement that Ms. A. is a (network) hog, with the primary reason for the pigish activity being the file server activity. In reqesting a recordset, you get the necessary and sufficient ingredients for the recordset, but the actual construction of that recordset occurs on the client side. Other discussions tend to support the concept that Ms. A. does a good job of caching the data it does retrieve. I, alonmg with most visitors to these fora are not in any position to delve into the inner workings of Ms. A., much less the Jet engine, but rely on MS. 'official' documentation (manuals / help files) and experience. From theese, one could not conclude that Ms. A. is a 'real' database server.

On the other hand, I would be appreciative of evidence to the contrary, supported by test routine(s) which conclusively support that conclusion while explaining the piggish behaviour.





MichaelRed
mlred@verizon.net

 

[BNPMike]

I'm going to do two things.

Number one is I'm going to get Microsoft Jet Database Engine Programmer's Guide - the professional information source.

Number two is I'll generate a 1 million row table on a corporate server and add a record without creating a huge recordset. I'll post the timings here. I 'll try and do the same for MS SQL Server.

 

[MichaelRed]

I'll generate a 1 million row table on a corporate server and add a record without creating a huge recordset. I'll post the timings here. I 'll try and do the same for MS SQL Server.

Thanks. This is a good start to providing real answer(s). It would, to really quantify the issue(s) need to deal with a number of configurations of the app/db, such as what data access methods (RDO | DAO |ADO | Execute(SQL) | etc] are used for both read and write operations of the data; wheather the data is local or remote(networked); wheather the (particularly read) operations were using possibly / probably cached data - just for obvious starters. So many variables exist it the 'real world' that the undertaking is well beyond the pratical capaility of most, which is at least ONE of the reasons the questions continue to be discussed, as well as I have never actually attempted to explicitly quantify the relative performance of various configurations beyond observation of what I have seen / heard from others. It is reasonably clear that remote access will be slower than local access. It SEEMS obvious that overall network traffic will afect response time for any/all remote access, but then It may also be true that specific network settings could be a factor, as I have been told that variosu network parameters can be set to tune performance in favor of (or to the detriment of) database applications.

I do not expect that a mutual help organization (e.g. Tek-Tips) member should or will unravel the entire picture, but am grateful that you (or anyone) would attempt to contribute to (at least my) understanding the situation(s) a bit more.




MichaelRed
mlred@verizon.net