VB.Net vs Fox Pro vs Access
Need some info on a speed issue here.
Please correct me if I'm wrong!!!!
When you create a form with access or foxpro and start editing records, your connecting directly to the database as opposed to vb where I'm loading a dataset and displaying the data (disconnected mode). So editing/retrieving records will be "potentially" slower with vb then the others, correct?
If I'm on the right track here, is there a better way to speed up retrieving and displaying data?
If I'm leaving out need information, ask away. Any help would be great?
Plus, if there are any links that talk about this please let me know.
Thanks in advance!!
The retrieval speed difference, especially with the PCs of today, will be so miniscule as to be unnoticeable. If you are actually seeing a difference, then there is more going on in your environment.
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
Please use [Code]your code goes in here[/Code] tags when posting code.
Before posting your question, did you look here
Got a question on Linux? Visit our Linux sister site.
Modifications Required For VB6 Apps To Work On Vista
You never connect directly to a database.
For instance, under the hood, there is nothing such as an Access Database. Access is a piece of software to design Forms, Reports, Macros and Modules. In FoxPro, you typically go through ODBC. But lets stick with Access, this is the one I know the most (FoxPro being the dinosaur that it is). What I will say here would hold for FoxPro for different but somewhat similar reasons.
The real database is made out of the tables and queries and is called a JET database. JET is not installed on a computer when installing Access, it is installed and updated separately by Windows itself, in an installation module called MDAC, that does not include only JET, but also most of the other standard data access technologies in the Microsoft World, such as ADO, ADO.NET, ODBC and OleDB (Microsoft Data Access Components)
This has changed somewhat in version 2007 where JET (.mdb files) was replaces by something called an AccessDatabase (.accdb files), but the fact remains that Microsoft Access is but a tool to help you design and access databases handled by a software piece that is separate from Access.
Those facts are usually hidden from view in order to make things easier for the casual user who is the person for whom Access was designed.
One demonstration of that is that you do not need Access installed on a computer in order to work with an Access database. The Data Connections tools in Visual Studio are able to create JET databases (.mdb files) without the need of Access.
So, Access does not connect directly to the database, it needs to go through JET, just as VB.NET needs to do. Something a little different but of the same order comes into play with FoxPro.
There can be a small difference of performance between going to a JET database through Access and through VB.NET, but it is not due to the fact that Access has a direct connection, because it does not. The difference is that the COM/ActiveX world, under which Windows, JET and Office (including Access) where designed is very different form the .NET world.
The COM/ActiveX world relied on pointers (memory addresses) and a few other things to work. This part was handled by compilers and hidden from most programmers. This brought performance and security issues that Microsoft solved by getting rid of pointers (and a few other things) when designing .NET.
Some type of variables also changed formats. For instance, the Date data type was handled as a Double in the COM world, while it is handled as a Long in .NET.
The slight loss of performance between accessing a JET (.mdb) database from COM application (Access) compared to a .NET application is thus not caused by an indirect connection, which both have, but by the fact that the different memory models and data types force a .NET application to do some "translation" when it communicates with a COM application such as JET.
Is there a way to speed up things?
In appearance, yes: call COM database from COM applications. Use Access to communicate with JET.
In practice, yes: call your COM database from a .NET application... but design it properly.
Although a general misconception is that Access is faster than VB.NET, it is usually the reverse.
A superficial examination of the situation seems to point out to a superiority of Access to JET compared to VB.NET to JET, a properly designed .NET application has a lot of chances of being faster that an Access application. But proper design and testing will almost always show that VB.NET can be faster.
One good reason is that COM was designed some 20 year ago. .NET was designed 10 years ago. Microsoft learned a lot in those 10 years of difference, and .NET is a big improvement on COM. This shows in robustness and security, but also in performance.
One case in point in the treatment of dates and times. As stated earlier, COM used Doubles (with decimals) while .NET uses Integer. Ask anybody who knows about microprocessors, and they will tell you that microprocessors work a lot faster with integers numbers than they do with decimals. It used to be a factor around x10 when I worked in Assembler, but that was a long time ago, the difference is probably less nowadays. But still.
VB.NET, when properly configured (Option Strict On instead of Off as it is by default makes a big difference) forces you into writing better code than any other version of BASIC before. And programming in Access was done with VBA, one of those older versions.
Most of the time, when people complain about performance with database in VB.NET, its their fault, not VB's fault. ADO and ADO.NET are 2 very different beasts. Shame to Microsoft for having given a similar name to both for marketing purposes. ADO stands for ActiveX Data Object. ActiveX was replaced by .NET. The A in ADO has absolutely nothing to do with .NET. But it mislead most programmers into thinking that they can design their applications the same way as they did before. When you do so, the performance takes a huge it. When you design an ADO.NET as it should be, the result is usually a very good gain of performance compared to what could be done in Access or VB6.
Programmers are lazy. They jump on stuff such as DataTables, DataSets and the Entity Framework, because this makes their work a lot faster. Microsoft has a great way of showing you how you can have a complete read/write access form to a table created in less than 5 minutes. This sells boxes... well, subscriptions or downloads.
THERE IS NO WAY TO DESIGN PERFORMANCE INTO A DATABASE APPLICATION WHEN YOU PUT 25 DLLS BETWEEN YOU AND THE DATA.
"Real" programmers will design their own set of classes in order to have performance when going to a database. They will write a few many hundreds of lines of code, but gear them specifically for their needs.
When you write a single little DataAdapter.Fill(DataTable), its a lot faster for the programmer... but a lot slower for the data, because under that single line, there are a few thousands of lines of code designed to be able to handle any type of application, not only your specific need as would be the case with a custom designed class.
What we do nowadays with one line of code such as the Fill method used to take many dozens of lines. But in order for that unique line to work, hundreds if not thousands of lines are hidden from you in System.Data. As Hack said, today's microprocessors compensate... but only up to a certain point.
And a little side point if you are serious about programming. Would you use one of those old phones with a wheel dial? FoxPro was designed when those where the only way to phone. Could you live without the Internet. Access was designed for secretaries when about 1 person in 1000 only knew about the existence of an emerging thing that was not yet called the Internet. We are in 2011. You live 2011 with your cell phone and your IPod/Pad/Tune/NameIt, have the decency to also live it with your data. There is a beautifully free version of SQL Server called SQL Server Express. I repeat: FREE. Yes, you will have to learn SQL instead of having a dummy interface query designer generate the SQL for you. Yes, you will have to work a little more. But if you really care about performance. And even more so, if you want your data to still be usable in a few year from now, switch to SQL Server.
I have nothing against Access itself, on the contrary. I have used it for over 10 years. Well, worst than that, I still give training on Access and still have fun and sincerely tell my students that it is a wonderful tool for their needs, believe it or not. But while most of my Access students were programmers at the end of the 90's, I do not have programmers anymore in my classroom. I have engineers, technicians, accountants, network managers, people who might know a little about programming, but are always very prompt to state "I am not a programmer".
Because programmers do not use Access anymore. Look at what Microsoft did with Access 2007 and 2010, bring Access back to the level for which Access was originally created, those courageous (mostly women) secretaries. If you program as a secretary does, Access is a great tool. If you program, it used to be a very good tool, but in todays world, it is almost like a toy for programmers.
Jacques was here today. Ouf!
By tapp in forum VB Classic
Last Post: 10-28-2005, 11:29 PM
By psd in forum VB Classic
Last Post: 10-19-2002, 04:52 PM
By Mike Mitchell in forum .NET
Last Post: 09-13-2002, 05:41 PM
Last Post: 01-07-2002, 10:34 AM
By Carrie in forum VB Classic
Last Post: 04-28-2000, 12:47 PM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL