Slow database search

[Catrina]

Before I start, I am new to VB programming. I am converting a DOS payroll program to VB 6.0. I am using an Access 2000 database with 132 fields.

Finding an employee to display all their data is extremely slow. There must be a faster way to do it, or I'm going to have major problems.

Here is how I am currently searching for the employee and displaying the data:


public Sub FindEmp(Num as string)
ErrFl$ = ""

sSQL$ = "SELECT * From Master"
rsAccess.Open sSQL$, Access, adOpenKeyset, adLockOptimistic

With rsAccess
.MoveFirst
.Find "PAYEMP='" & Num & "'", , , 0
End With
If rsAccess.EOF = true then
ErrFl$ = "Y"
Exit Sub
End If
""then set textboxes to the appropriate fields





Is this the only way I can get all the fields to
a record? It runs so slowly on my machine, and I have a fairly quick system. I do not want to bind the text boxes, because certain fields will not be displayed to all users. Any advice would be appreciated.

Thanks
Catrina

[Chris Eastwood]

You need to place the search criteria inside your 'SELECT' statement to get full advantage of the database.

eg.


public Sub FindEmp(Num as string)
ErrFl$ = ""
sSQL$ = "SELECT * From Master WHERE PAYEMP = " & Num
rsAccess.Open sSQL$, Access, adOpenKeyset, adLockOptimistic
'
' etc
'




If you want even better speed, make sure you have an index in the Access Database on the 'PAYEMP' field of the 'Master' table.

>I am using an Access 2000 database with 132 fields.

That also sounds like overkill - I can't see how having 132 fields in a table is a good use of resources (your Access Database will certainly be 'hammered' by searches) - have you 'normalized' your database design ?




Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

[Catrina]

That is the way I was searching originally(WHERE PAYEMP=), and it was slow also, so I tried to use the find statement.

I have the PAYEMP field set as the Primary Key. If that is what you mean by index

I have 132 fields to hold all the different data for each employee used in the payroll program (deductions, rates, personal info, etc, etc)

I'm not sure what you mean by normalizing the database. Can you explain a bit(new to database programming). Thanks

Catrina

[Chris Eastwood]

The 'select .... where ... = ....' method will always be quicker than the 'find' method as it will only return those records where the match is made.

You might also want to consider just selecting those columns from the table that you are interested in. In 99% of database selects, selecting the actual columns is usually around 60% quicker than doing a 'select *' (for many reasons that I shan't go into here).

Database Normalization has nothing to do with Visual Basic - it's covered in most DB programming books in a couple of chapters (as there are different 'levels' of normalization (called 'forms') that you can go to.

Let's assume that you have absolutely all the employee information in one table (I assume that's what your 'master' table is) :

employees:
----------
ID (or PAYEMP)
Name
Address Line 1
Address Line 2
Age
Department
PayScale

a simple couple of rows from that table could be :

1, Chris, Here, There, 30, Some Big Department, Not enough
2, Someone Else, Elsewhere, somewhere, Some Big Department, Still Not Enough

Now having huge amounts of text in each row for a table is never a good idea - it makes the 'sql' parser / database engine do a lot of work to build the recordset.

You can see that some data is repeated many times in the table (department for instance). Think about it, is it really worth having 'personel' or 'Some Big department' repeated throughout the table for each record ? This can take up a huge amount of space.

This is where 'normalization' comes into effect. It's the process of identifying repeated or 'redundant' data that can be shifted off to other tables.

In my (theoretical) example, we can now split out the 'employees' table into two separate tables :

employee table
department table

The department table can be as simple as :

ID
Department Name

eg:

1, Some Big Department
2, Personnel
3, Admin

etc.

Now your employee table can contain a 'Department ID' column instead of a 'department' column that contains the full text.

Just think about how much space this simple change will make to your database - and that's just for one column.

Of course, normalizing a database does put extra effort onto the developer to get the required information from the database in a select statement. This is where normalization can get 'beyond it's use'. There's no point in normalizing absolutely everything as your 'select' statements get very complex in trying to retrieve the data that you actually need.

You'll need to learn about 'joins' in tables to get the data back from the database in the form you are used to. A join is simply a select on two tables where each table has criteria that match for a record. In this case, you'd want to read the employee table and get the department record for that employee's department id.

Hope I haven't confused you too much - but once you start reading up on this, you'll start to see the big picture and how much space you can actually save in your database (and the performance increases will probably be 10x if not more).




Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

[Fraggle]

Hi,

I´m not a VB pro myself, but you might want to read the Database FAQ I found on this site:
http://codeguru.earthweb.com/vb/openfaq/database_faq.shtml
It covers some DB performance issues. Hope it helps.

cu
Fraggle