Jet database with vb6 quandry

[tedsmith]

I wrote a compicated app many years ago that uses a Jet database originally made with MSAccess2000.
The main table has about 60000 rows of 30 columns and has to be read about 10 times a second. It currently only take about 100th sec for each read at the moment on a fast computer.

The user now want to adopt a more 'modern' database while still using the vb6 application with modifications (even though I suspect it wont go any faster). He doesn't want to completely rewrite the app at this stage but may do so in the future, keeping only the database.

I think he wants to be able to maintain the information in the database data well into the future and have it able to be used by more 'modern' applications well after I am dead!

What alternative database would you suggest and how do I go about modifying my app? SQL type hopefully? Can you suggest any links or threads that might help?

My app uses OpenDatabasel OpenRecordset and dbOpenDynaset expressions extensively in the usual way.

Some datagrids for examining tables have datasources connected in software (not ADODC controls).

I also need to be able dynamically add columns to tables and fill then with data, currently done with execute SQL statements.

Thanks in anticipation.

 

[Andrzejek]

I am sure others will offer some answers, but I am just curious...
Why your database ("about 60000 rows of 30 columns") “has to be read about 10 times a second”?
Does it change that often?


Have fun.

---- Andy

 

[dilettante]

I doubt the entire 60000 rows are being read that often, that doesn't make any sense.

My app uses OpenDatabasel OpenRecordset and dbOpenDynaset expressions extensively in the usual way.

Nothing "usual" about this, DAO is a very secondary way to use databases. In addition to its other problems it makes migration to another RDBMS a lot of work/rewriting.

"Modern?" Kind of funny. Most platforms would kill for anything as stable and powerful as Jet. Try writing multiplatform Java applications that need an embedded database - the choices are truly ugly and archaic.

You might move to ACE instead of Jet but it buys you little besides extra deployment headaches. SQLite is interesting but it is more of a key/value datastore than a real RDBMS.

SQL Server Compact might be an option, but there is just no way it is as easy to use as Jet/ACE and it has the same deployment headache as ACE.

SQL Server Express (SSE) just is not going to perform as well as a local Jet database, but that was never its purpose. Plus it has lots of care and feeding and "DBMS Hell" issues as well as deployment headaches worse than those of any of the embedded database choices. It is not at all rare to have one SSE application clobber another one using SSE on the same box.

And MySQL? Well there you have just about the worst of all possible worlds. Support is erratic at best. If you find a stable version good luck, and without forking over some $$ you won't have an OLEDB Provider, so you're back to clunky ODBC - a world that died in the '90s. Then hold your breath hoping another MySQL application doesn't clobber you again with versioning/instancing Hell like SSE.

Jet is far from ideal, but almost anything one might try to change to make it "better" brings down the same headaches other DBMSs suffer from. If you don't need a shared database an embedded solution like Jet is hard to beat for ease of use and performance. Using a single connection and opening it exclusively can even improve that.

No, when it comes to the deployment and configuration issues Jet beats the alternatives hands down. You need a good reason to move to anything else unless you want another low-paying support job on the side.

 

[strongm]

>that doesn't make any sense

You forget - this is Ted. He sometimes makes ... interesting ... design decisions

>Nothing "usual" about this, DAO is a very secondary way to use databases

Have to say that actually itis well recognized that if using Jet, DAO is actually significantly faster than ADO and its successors. So if performance is/was an issue then DAO against Jet is perfectly reasonable

>"Modern?" Kind of funny

To be fair, Ted does put the word modern in quotes ... perhaps we could make the point that current versions of both Exchange and Active Directory actually use a version of Jet as their underlying database ... and Jet continues to ship as part of the OS.

 

[gmmastros]

Andy makes a good point about the data. If the data is NOT constantly changing, I would suggest you add a trigger to the table that updates another (ridiculously small table) with a DateTime stamp of when the data is changed. Prior to loading all that data, you can check the really small table to see if anything has changed. If not, then use the same data you already had. This approach works really well for 'slowly changing' data.

To be honest, I am a big fan of Microsoft SQL Server. dilettante is correct regarding the deployment headaches. I have approximately 200 customers, each with their own instance of SQL Server Express installed locally at their location. If you are careful enough with the setup application, you can deploy SQL Server relatively easily with little to no manual tweaking of the database engine itself. One reason I like SQL server is that there is a natural (and easy) progression to more and more features. You can very easily (but not cheaply) upgrade to SQL professional or enterprise which will give you a lot more features than you have in the free version.

If you decide to go with SQL Server Express, I would STRONGLY encourage you to install it as a named instance. There are command line arguments in the installer for doing this. This allows you to have multiple instances of SQL Express on the same computer without tripping over each other (as dilettante suggests).

If your client is willing to listen, sticking with Access/Jet is probably your best option. If your client insists on 'upgrading' to a 'modern' database, I would recommend SQL Server Express.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tedsmith]

This might give you an idea of the complexity of this project that has grown over then last 20 years to a pretty sophisticated system.

Unfortunately I am one of the few people left that knows how it works and even though I am 75 they still call me every now and then for advice or to make software changes.

Incidentally I will be visiting USA in September. San Fransisco, New York & Boston.
Any suggestions apart from the usual touristy things in all the books would be welcome?

The main database table is the complete timetable for up to 2000 buses in our city. Each row is an instance of a bus passing up to 20 'way points' in each route over the whole 24 hours of each day. Mon to Fri are the same but Sat and Sun are different.

This database table is used as the source for a monitoring the movement of buses, a GPS driven smart-card cashless ticketing system that changes fares depending on the time of day and the locations traveled, and the part that I am involved in being display of departure information on hundreds of LED and LCD signs at major bus stops and bus interchange stations throughout the city.

This large timetable is read only by my system every 5 minutes.

I cheat by, at the start of each day, extracting a few small timetables of a smaller slice of the main timetable to encompass 1.5 hours of departures from a few stations, Then I update it with the next 5 minutes of departures every 5 minutes
This keeps them to about 1000 records.
I write to these tables to up to 10 times a second and read them three times a second.(bit never at the same time)

The incoming location info from transponders or GPS is compared with where a bus should be in these small timetables and written to these smaller timetables. That way the real time of when a bus will arrive is displayed to waiting passengers.
I use these smaller timetable to feed the passenger signs with the next departures in the next 1.5 hours.
The CPU activity ticks along at averaging 5% handling 40 stations!

I will try SQL express - but expect some more cries for help - I'm getting a bit old!

 

[tedsmith]

This may be a rather odd question but I've never tried it -

Regarding "Jet", in a situation where I use a simple DAO openrecordset snapshot on a MSAccess .mdb table, is the "Jet" part of it residing in my vb6 app (with it's appropriate references) or is it hidden in the .mbb file?

Does the required change in the connection string statement then make it so I am not using "jet" any more when I query these other databases like SQL?

Can I still use the same openrecordset & snapshot routines on any other database?