a question?

[Mih_Flyer]

hello guys,
i'm gonna make a program which deals with data base, this data base will contain like 50,000 records, so if i do it it will take like ages to search some thing, what is the best way to do a very fast database,,,especially in search function,,thanx

[Paul Warren]

Mih_Flyer -

There's loads of things you need to consider to improve database performance. First you need to know the technology you're going to be using i.e. SQL Server, Access, Oracle ?

Broadly speaking the following techniques can be used for all the above :

1) Use indexes - put indexes on those fields which you will use as search criteria. This is a tricky one because indexes add an overhead to adding/removing records and too many can grind things to a halt. Read the documentation with the DBMS you choose and see what the supplier recommends. Ignore Microsoft's claim that you can have as many indexes as you want in Access, be sensible.

2) Use stored procedures - these can be set off whilst the user is doing something else, fooling them into thinking more is happening than really is. It's the same principle as splash screens.

3) Read some books on database design. It's not just about normalising the data, you need to modify the normalised design to improve performance given the needs of your system. An example might be if you want to search all your current products for those that meet some criteria. To improve performance of this big search you could create a table for "archived" products which are no longer available but still supported. This reduces the number of records and removed one criteria from the search ( is the product current ? )

In the end it's a tricky process to get the balance right and you can spend over long worrying about it. Do you then need to consider netowkr bandwidth, bottlenecks, disk stripping, etc, etc, etc. Like I said, find a decent book and decide on the level you want to go to.

Hope this helps you make some decisions.

[Mih_Flyer]

thanx alot Paul
my program is very simple, no networks, its just a singers and albums database, it only has four functions, add, remove, search and report, so i think its a simple, I know how to do all of them but search.. but the database gonna be big one, so i want you to tell me these thing:
1. Which technology should I use??
2. Any search Code you have that will help me very much..
3. How to make a fast search function..
Please help me..
thanx

DB with 50k records is fairly small.
As for search - just use "like" command in where clause of select statement - will give string search results. If that is what you are looking for. Any select statement will run pretty quick on such a small Db.

[Paul Warren]

Mih_Flyer - jazzy_josh is pretty well right, you don't need to worry about response times too much when the db is sat on your local hard drive. Performance becomes as major issues when networks are involved and/or distributed applications are used. Adding the right indexes will still make a difference, after all there's no point waiting when you don't have to. As far as technology goes, Access 2000 should do the trick quite happily. If you're using VB 6 you will need SP4 to make the two talk properly.

As for code ? Well, you will need to create a recordset which contains the results of an SQL query. The query itself is just 'SELECT * FROM Table1 WHERE x=1 AND y=2' and the recordset contains a list of those records which match the criteria. In ADO you can create a recordset directly, without the need for a separate ADOconnection object. In DAO ( VB 5 & VB 6 ) you'll need to open a database and then create the recordset. The two technologies are pretty similar in this area and moving between the two shouldn't be too difficult. I could go on for ages giving you a full tutorial but unfortunately I don't have the time. There's plenty of info. out there and on the MSDN CD so have a read and then have a play. All these acronyms ( ADO, DAO, RDO, ODBC, etc ) might seem confusing at the start but once you grasp one then the others are just variations on the same theme.

Stick with it and put posts here if you get stuck.

[Mih_Flyer]

Thanx alot man, you really helped me alot, thanx again,
i'm good in programming, but not in db, simple coz i hate it, don't know why but i do hate it. but know i think i can do this little program,,,expect a few questions in the coming dayz..