Data driven programing

slopey

Have programmed a few simple programs (client entry, appointments, reports etc) using VB on a volunteer basis for a couple of local charities.

As these were all small time affairs with only small amounts of data I never worried about what was the optimal programming language, database etc.

Have been asked to look considerablly larger project with potentially very large amounts of data. Database would have approx 10,000 records added per day and would be reports based. Was going to implement a VB program with SQL Server database but is this the optimal solution? Is Java a quicker, more responsive enviroment.

What would be optimal for a data driven program in which user must be able to update and retrive data quickly in a user friendly graphical enviroment?

liamo

slopey wrote:

Was going to implement a VB program with SQL Server database but is this the optimal solution? Is Java a quicker, more responsive enviroment.

What would be optimal for a data driven program in which user must be able to update and retrive data quickly in a user friendly graphical enviroment?

Quicker, more responsive environment
Java can be quick and responsive. So can VB.

update and retrive data quickly
Speed of data updating and retrieval has nothing to do with the language. Concentrate on your database design.

user friendly
Nothing to do with the language. That's down to the programmer.

I applaud your willingness to learn a new language but a large project is not the place to experiment. If VB is what you know, then go with that.

As far as the database is concerned, if this project is also for a charity then costs may be an issue. If that's the case, you may wish to look at one of the many free databases. PostgreSQL is my favourite. Other people will have theirs.

Best of luck with it.

Liam

amen

are your professional programmer doing this in your spare time or just learning programming and doing this in your spare time?
10,000 records a day is a lot of records for a charity based organisation
you need to have a good db design. Don't fogeta about locking, concurrency, updates to existing data.

you can definatley make VB work with this and fairly quickly. Is it all fat client
or do you have a n-tier architecture?
Are you using SQL Server or SQL Express?
At 10,000 records a day and say 100bytes per record your db will be growing by 1MB a day. Don't forget about backups, redundancy etc

If you are not getting paid for this you should be.

bonkey

slopey wrote:

but is this the optimal solution?

I can't remember where I read it, but someone made a very good point recently on exactly this topic. Well, I thought it was a very good point. As with all things IT, you'll find no shortage of people with differing opinions ranging from "mostly right, but..." to "don't listen to that blithering idiot".

Anyway...the comment...paraphrasing

The best language to develop any given solution is the one you know best which is suited to the problem.

If there is no language that you can already "do" which is suitable to the problem at hand, then sure...you probably need to find the right one to learn. However, if one of the languages you know can "do" the thing, then odds are you'll produce a better result, more efficiently using this language than you will with learning a new "better" one.

sharingan

I am requoting this for emphasis:

bonkey wrote:

The best language to develop any given solution is the one you know best which is suited to the problem.

To the OP:
Basically VB is fine for your database app.

Your choice of Database is probably a more significant decision. And yeah, I think SQL Server is fine for your needs, added advantage is that you are probably more familiar with it.

Your tools are fine. You need to focus more on how making a database app at the scales you are talking about is different to how you have traditionally done things.

HelterSkelter

VB will be fine, we use it all the time in our company for large projects. If you are concerned about speed you really need to concentrate on database design. The programming language you use has little if any bearing on data access speed, it's all down to the database. You need to get the table design right and know where to add keys/indexes to speed things up.

We use SQL server but if you are trying to keep costs down you might want to go with MySQL. It is free.

Evil Phil

If they're a registered charity they may be able to get a free SQL Server licence from Microsoft. Dunno who to contact in MS about that though.

Zombrex

slopey wrote:

Have programmed a few simple programs (client entry, appointments, reports etc) using VB on a volunteer basis for a couple of local charities.

As these were all small time affairs with only small amounts of data I never worried about what was the optimal programming language, database etc.

Have been asked to look considerablly larger project with potentially very large amounts of data. Database would have approx 10,000 records added per day and would be reports based. Was going to implement a VB program with SQL Server database but is this the optimal solution? Is Java a quicker, more responsive enviroment.

What would be optimal for a data driven program in which user must be able to update and retrive data quickly in a user friendly graphical enviroment?

It sounds like the database design will be far more important that the language you write the client application in. If you have a badly designed database layout it won't matter how quick your client application is

As a client interface to a database VB is perfectly fine, and you won't notice any major improvements using Java in terms of speed.

Focus on learning how to get the best out of MS SQL Server All the VB client will probably be doing is sending queries to the database, which can be done in an instant. The speed bottleneck will be how quickly the SQL Server can process, retrieve and return the data to the client application.

Get a good book (or a few) on optimising SQL Server design (unfortunately I can't recommend any since I hardly ever use SQL Server). The most important thing will be that you have set up your tables correctly, especially things like indexes. I work on a PostgreSQL database that had been set up before I arrived. The main query was taking 28 seconds to run on a table of about 2 million records. Needless to say my bosses were not happy with this, but they thought it was because there was too much data, or that the client program (written in PHP) was doing something wrong. First thing I did was add an index to the table on the column used to match the search string. The query went from 28 seconds to half a second.

Hobbes

If you don't have the SQL/DB experience I would recommend getting someone in for that volume.

Had something similar with a chartiy about 15 years ago. They had a large volume of people to send out a present too and wanted data put in so they could deliver the items.

It was only after everything was written and the data being processed daily (At the volumes as yours) that they mentioned that they wanted data to be able to be broken down by region/street so that delivery trucks could schedule where to go.

At that point it was a mess as the search alone took 30 minutes for one area.

Badly planned means horror stories later on.

Frontend GUI is the least of your worries imho.

bonkey

HelterSkelter wrote:

If you are concerned about speed you really need to concentrate on database design.

This is, in my opinion, only half-true.

Depending on where your speed is needed, how you access your database will be equally as important.

When I worked with VB, I always "rolled my own" DB-access code. By this I mean that I used prepared statements with bound parameter-variables. I used the right type of cursor (typically read-only, forward-scrolling-only). I did my data-manipulation using INSERT, UPDATE and DELETE statements via the afore-mentioned prepared statements.

Put simply, I used none of the data-binding widgets that come with VB. I used none of the "make life easy" data collection objects, that let you batch-edit a chunk of data, then tell the thing to .Save() everything. I used the simplest, lightest, most performant techniques which - unfortunately for me - involved writing more code.

The upside was that performance was at least an order of magnitude better.

I was at TechEd in 2004, and I remember being really interested in a lecture which was about data-access optimisation under C#. Having heard a lot about the cool new stuff in ADO.Net, but not having had a chance to play with it in anger, I ws looking forward to learning how my life was going to be made so much easier. No more prepared statments for me, I thought to myself.

When I finally got there, I was somewhat disappointed/surprised to learn that what the guy was talking about were the exact same things I did with ADO & VB. My "best performance" techniques under the old were...surprise surprise....still far-and-away the best performance tecnniques under the new, and still by a comparable margin.

UI design is also important when you get to data quantities this size.For example...never retrieve more data than you need to. I've lost count of the number of Apps I've seen retrieve 10,000 or 100,000 rows to populate a grid, leaving the user then to search/scroll for what they want themselves....rather than give them up-front search/filter capability so that only the required (typically less than 100) records are displayed.

I've equally lost track of the number of system redesigns I've seen needed because a system was "proof-of-concepted" with minimal data, and then designed to work against a DB running to hundreds of MB, or into the TB range....only for the devvelopers to discover that filling that list now takes minutes instead of being instantaneous.

Working with lots of data isn't as hard as it sounds. It requires one thing - you bear in mind at all times that you're working with lots of data, and consider the implications.

slopey

Evil Phil wrote:

If they're a registered charity they may be able to get a free SQL Server licence from Microsoft. Dunno who to contact in MS about that though.

Thanks for the heads up, can get an Academic licence for SQL Server 2005 for €350+VAT.

Thanks for all the advice, will start on database design, and ensure I'm getting quick return on queries through the Query analyser. I'd like to get a book to improvem my knowledge. Anyone have any recommendations, spotted these two on Amazon to start off with.
http://www.amazon.co.uk/o/ASIN/0321382188/ref=s9_asin_title_2/202-7862805-1581421
http://www.amazon.co.uk/o/ASIN/0072260939/ref=s9_asin_title_1/202-7862805-1581421

Once I have the database returning results quickly through the Query analyser I'll look at the front end. Only have one piece of the front end that I don't know how to do, want a grid with results from the database to be editable without having to hit an Edit button like with a datagrid in VB. If I can achieve this in VB I'll stick to what I know and develop it all in VB.

Anyone any hints on creating good reporting views within program? Currently using VB 2005 Express Edition and with all reports I just export to a csv file. Do I need to upgrade to a Professional Edition to be able to use Crystal Reports Viewer so that users will be able to view reports in the program and decide then on whether they want to export and save that report?

Thanks for all the help so far.