Database Advice and selection Please

[Answer]

i Have reading a plethora of articles about Access and MSDE. It seems MSDE is a better database then access. Although i would like you database experts here to give me your opinion on what is going to work better for my situation. Its a vb.net project.

 

What I have is about 20 dbase III databases. Now, i can access them just fine using jet on my local machine or across a network, the problem is, is that jet doesnt support multiuser access for dbase III. No lock file is created like there is when you use a access database.

 

Now, I am going to distribute this app to our different companies. Each company will have its own databases. i am just guessing here, but probably 75% of the companies, there will be no need for multiuser access to the databases. The other 25% there will be some type of multiuser access. I would venture to guess that 20 users will probably be the max, and of course these users most likely arent going to be writing or reading all the time.

 

Now i did some testing and using dbase accross a network on a file share was VERY fast. Adding 10,000 records only took .2 seconds longer then a database local on my harddrive. And thats over a 22 mbit wireless connection. But like i said earlier, multiuser = no no.

 

So i have thought of 3 different options i could do. Write a class to import and export the dbase data into an access database then use the access database. I am familar with ado ( not ado.net ) and using jet.

 

 

Write a class to import/export data into MSDE. I am not familiar with SQL type databases. Although i wouldnt mind learning as long as database access is going to perform on par with access.

 

Use my own XML file to "lock" records and i will have to check the XML file to make sure no one else is using that record everytime before i add or do a update. - dont like this one.

 

 

I like the idea of using MSDE but, i have some concerns. Such as it was kinda pain to setup on my local machine, so it might be a pain for the other 75% who dont need the multiuser access anyway. I know you can include it in a setup file, but i am not familar with doing it. It seems to that MSDE runs as service which uses more resources then acccess.

 

And from my testing so far, adding 10,000 records is quite a bit slower then using access or dbase. I used an insert command, then did a executequery using a sqlcommand object for msde.

 

What do you guys think?

access or MSDE?

Edited July 31, 2003 by Answer

[Nerseus]

Is size of the database going to be an issue? I know Access has some practical limits (don't know them offhand) that might not be as big of an issue for MSDE. I'm asking because you've said you test with inserts of 10,000 or 100,000 records, but is that going to be standard?

 

I'd strongly suggest trying to install MSDE and working with it for a week or so to get a feel for it. The difficulties in setting it up would be a one time hit that shouldn't affect your decision as much as ease of use and performance. Of course, MSDE comes with no user interface (like Access), though you could use SQL Server if you have a copy.

 

As for transferring the database schema and data, if you have SQL Server (not just MSDE), you can use the DTS that are built into it to copy an entire database from any source to any destination. Very cool, very fast, and very good. And, if you need to convert any data, it provides a JScript or VBScript intermediate layer to do the conversion.

 

One other consideration is that MSDE is fairly new. That *may* mean it may not stick around - maybe MS has plans to turn MSDE into something more akin to Access. Then again, maybe Access won't stick around (or stick around like FoxPro sticks around - more or less by a thread). Who knows what MS's decision will be? It's hard to make that part of your decision since it's such an unknown, but if you lean towards more "standard" platforms, Access might seem that way.

 

Also, have you talked to your clients? Do they care what the Database is? Would they want to write reports or interfaces to the database? Would they ever buy your sourcecode and take ownership of the database?

 

-Nerseus

[Answer]

Nerseus, you brought up a lot of good points that i hadnt thought about. Such as MSDE could just be a temporary thing. I have noticed that MS has discontinued jet with MDAC. Could be a sign.

 

10,000 Records is not normal at all. I tend to like to beat the piss out of things to make sure they will stand up and to get a good feel for it. 99% of the time, it will be inserting/deleting/updating one record. It is a vb.net project, and size i dont think will be an issue as 90% of the debase records are under 1mb and the rest are 3-5mb. One or two do get into the 100mb range, but there log databases which should be trimmed more regularly. Any my program will be maintaining them to around 30mb. the 20 or so dbase databases will be put into 2 or 3 different access databases.

 

I think access is the way to go, it doesnt require a genius to handle it, plus it will allow any of my customers to edit the databases easily if they have office installed. I liked MSDE cuase it seemed like a better multiuser solution.

[Derek Stone]

There's absolutely no reason to use Access. Contrary to what Nerseus said internal reports from Microsoft indicate that Access won't be supported in the near future. SQL Server has and will be around for years to come, meaning that MSDE will most likely be as well. All MSDE is is a recompiled version of SQL Server optimized for 5 or less concurrent users. There is also a 2 GB database size restriction, but that is hardly a problem for most businesses. I recently moved my accounting system from Access to SQL Server and I'm very glad I did. Access is a horrible multiuser solution, simply put-- concurrency and corruption issues plague it. The only reasons why developers have a hard time using MSDE is because they a) don't realize it exists, b) don't want to go through the "hassle" of installing MSDE on a client's systems. These are hardly excuses. They're poor development choices.

[quwiltw]

Contrary to what Nerseus said internal reports from Microsoft indicate that Access won't be supported in the near future.

I think you mean that the Jet data engine, the default underneath Access won't be supported in the near future rather than Access -- which will in all likelihood be supported indefinitely? I suspect that MSDE will sometime soon replace Jet as the default data engine under Access.

 

Btw. I agree with Derek (other than the above). If there's a chance you will get more than one user or will ever want to scale up - then MSDE is the answer. Since MSDE is itself based on SQL Server, when/if you outgrow it all stored procs, views, etc. will work no problem meaning there's clear and clean scaling as opposed to Jet which doesn't present an easy scaling solution to full blown SQL Server.

Edited August 1, 2003 by quwiltw

[Answer]

Can MSDE be included in a install file so that a computer newb could install it? For instance, not having to give setup any command line parameters by the user.

 

Second, Should i still use MSDE even for the single user?

 

Third, should i use ADO or .NET data classes?

 

Lastly, MSDE seemed slow to me, as when i did testing, inserting 10,000 records with ado and jet, using a server side cursor took 2.8 seconds. MSDE using sqlcommand object took over 15 seconds to do the same task! Is there a way to speed this up?

 

 

Thanks for the help!

[Derek Stone]

Can MSDE be included in a install file so that a computer newb could install it? For instance, not having to give setup any command line parameters by the user.

Yes. The MSDE installer comes with built-in support for silent installations.

 

Second, Should i still use MSDE even for the single user?

If you have a frontend for the data, then yes, I'd recommend MSDE.

 

Third, should i use ADO or .NET data classes?

.NET. I don't understand why you'd even bother using ADO.

 

Lastly, MSDE seemed slow to me, as when i did testing, inserting 10,000 records with ado and jet, using a server side cursor took 2.8 seconds. MSDE using sqlcommand object took over 15 seconds to do the same task! Is there a way to speed this up?

Use ADO.NET.

[Nerseus]

Inserting 10,000 records can be coded many different ways. For instance, were you recreating the command object on every pass in the loop? Were you recreating the parameters on every pass? How likely is it that this would occur in "real life" (inserting 10,000 records in one big loop)?

 

It sounds like your code is still doing 600+ inserts per second. Is your single (or possibly a few users) going to really be doing this? Or, as you said, they're just doing a simple read/update or delete? Even the poorest of code should read or save a piece of data in well under 1 second.

 

I'd still suggest trying MSDE for a week or so. Getting opinions is good, but having first hand experience is the best.

 

-Nerseus

[jfackler]

If you have a frontend for the data, then yes, I'd recommend MSDE.

 

 

 

Your profile doesnt include what version of .net you are using. All versions of VS except the Standard support a pretty good front end to create dbs and manage permissions. You can use Access as the front end to create your db however, managing the MSDE db requires osql. MSDN has a good tutorial.....I don't have the link at this location. Takes some work, but worth the effort.

 

Jon

[Nerseus]

You can use Visual Studio to create an MSDE database? How do you do that? :)

 

-Nerseus

[quwiltw]

Right-Click on DataConnections and choose "Create New Sql Server Database" in the server explorer.

[Answer]

Here is the code i was using to do my testing....

 

Dim cmd As OleDb.OleDbCommand

cmd = New OleDb.OleDbCommand

cmd.Connection = oOleDbConnection

cmd.CommandText = "insert into products (id,code) values ('333','Hello')"

Performance.Reset()

For x As Integer = 0 To 1000

Try

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Next

MsgBox(Performance.Peek / 1000)

 

Timing starts with the Performance.reset() line and ends with Performance.peek

 

 

The only time my app will be doing mass updates is on one field in a database to change its sorting fields.

And it will probably only be called once in a while.

 

So its not that big of a deal.

[Derek Stone]

Under the rare conditions that more than a few INSERTS would need to be executed you'd create a batch insert, not execute them one by one. A scenario like this is rare though, and I've never had the inkling to even bother creating an insert test.

 

A few tips to keep inserts as fast as possible:

 

• The more indexes on the table the slower inserts will be.
  
• Keep column lengths as narrow as possible.
  
• Use RAID 10 or RAID 1. According to Microsoft employees RAID 5 is very slow on inserts.
  

[Answer]

I have been doing some reading and messing around with MSDE. very cool i must say. ADO.NET once i understood it better also is pretty nifty.

 

 

Thanks for forcing me into :)

 

One question though? is there a way to tell the sqlcommandbuilder to put [] around a column name since its the same as a sql keyword?

 

I know i could set the commands manually, but thats such a pain :)

 

 

this is for writing a dataset back to the db.

[jfackler]

A pain perhaps, but learning the technique is worth the anguish:

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/CommandBuilder.asp

 

 

Jon