|
dragon_tat
Starting Member
USA
17 Posts |
 Posted - January 26 2004 : 10:40:04
|
I have read with interest discussions involving using MS Access vs. SQL Server vs. mySQL and I think someone actually mentioned Oracle. In my day job as a programmer for stand alone applications, most of which utilize databases, I have found that most people jump to a database architecture without taking adequate time to actually specify the needs of their application and the factors by which the users (customers) of the database application utilize that application. With the availability of “broadband” internet access in the US we sometimes forget that dial-up access accounts still vastly outnumber other access methods! We also forget that much of the users experience in terms of speed comes from the speed of their personal computer and their browser’s ability to render HTML pages (more specifically HTML tables and images). Remember that when using any scripting language based platform, the scripts take time to be interpreted to the functions necessary to do basically everything. In the greater environment VP-ASP operates in, any of the databases would perform well. I have used Access in an everyday environment where thousands of transactions are processed routinely using a database of a few hundred thousand records. It performed just fine. But I still prefer the power and flexibility SQL Server or Oracle, in spite of the exponential leap in administration! The text following the above thoughts comprise my normal “soap box” position of deciding on one architecture or another without taking the time to consider the whole system and how users (customers) will be served. You all have been warned. And many thanks to Mr. Jon Madrid for helping us all with his superior advice.
Database architectures and their speed to retrieve data are substantially affected by two important and usually underrated factors: the type of engine used to access the database and how SQL statements are structured and used. Database access methods and components can be boiled down to two different types; those that directly access the ODBC managers and those that utilize an engine, or interface, to access the ODBC manager – such as MS Jet, RDO, ADO, and many others. In general, most programmers today use an interface to the ODBC manager in order to escape from the complexity of dealing with the ODBC manager or OLE DB providers. Those are dark, ugly waters filled with compliancy issues and database architectural update changes. For myself, I have written ODBC interfaces only twice and will hopefully never do it again. For the majority of us who use interfaces to access the database, the type of engine can substantially effect the data access speed equation. For example, MS Jet is oh-so convenient for simple applications; however it starts to break down when the database begins to exceed a couple thousand objects. ADO is far more robust when the number of database objects grows from a couple thousand to many tens of thousands. So what are database objects? Well, in some ways that’s a big question. Database objects include the data access components, open recordsets, the number of users currently accessing the database and many others. Understand that the numbers of records stored in tables are not necessarily objects at any given moment in time. They are available as objects when accessed. MS Access is limited to 32,000 some odd objects at any given time. That said, I have used Access to store records in tables of hundreds of thousands and it performed well (a Call Delivery Record, or “CDR”, database). This leads us to how the database is accessed and how recordsets are retrieved and utilized.
In well designed databases and well written applications the amount of time recordsets are open as objects is very short, usually measured in a few tens of milliseconds or less. As in playing golf, a lower number is always best. For myself, I prefer to open the database, retrieve the records and stuff them into an array(s). Then immediately close the database, making sure that the retrieved recordset object is set to “Nothing,” in Visual Basic terms. Then the arrays may be sliced and diced as needed to meet the needs of the application. This practice optimizes system and database resources, thereby improving this factor in the speed equation. I also try to optimize SQL searches of tables so as not to retrieve unnecessary recordsets, especially “Joined” recordsets from multiple tables. Further, improvement in speed is usually achieved by splitting large flat-file tables into more specific ones. For example, in a Product table with 40,000 records it may be sensible to create category specific tables such as Food, Furniture, Music, or Antiques.
In “Our” application using VP-ASP speed is effected by the use of ASP/VBS scripting, the architecture the website and database is currently running on, the horrific performance of Web browsers rendering pages and graphics, customer access methods to the ecommerce website and the overall design and structure of the database. And the vast majority of database accesses are for reading records, without the various locking mechanisms available when writing to the database. Since I am not in the web hosting business, I frequently have to consider the experience of the local database administrator - who is frequently nominated for the position because they appear to be the local computer expert at my client’s company. Lastly, the finest database architecture will perform poorly if the needs of the application, hastily written code is utilized, and the access method of the “Customers” are not factored into the project plan. For the record, I am not a pundit for MS Access or any other database architecture in particular. But I do enjoy the functions, objects and methods exposed by SQL Server and Oracle for more demanding environments.
My "soap box" has been retired.
|
|
All Forums
Database Architectures, Types, and Speed
New Topic
Printer Friendly
Topic 
TrustGuard - PCI Security Scanner
