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 [sig][/sig]

 

[Karl Blessing]

why not try

sSQL$ = "SELECT * From Master where PAYEMP='" & Num "'"

then you'll get back a recordset with ONLY the records matching the where criteria.
[sig]<p>Karl<br><a href=mailto:kb244@kb244.com>kb244@kb244.com</a><br><a href=

http://kb244.com>

</a><br>Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)<br>

http://www.brainbench.com/transcript.jsp?pid=629151

[/sig]

 

[dellyjm]

I'd recommend getting a SQL book to see the type of power that's available to you.

Delton Phillips.
dphilips@gleanerjm.com [sig][/sig]

 

[NickBulka]

Did you know that there's a Visual Basic Database Forum? [sig]<p>nick bulka<br><a href=mailto: > </a><br><a href=

http://www.bulka.com>

</a><br>Get your technical books at Bulka's Books<br>

http://www.vstorebooks.com/bulkasbooks

[/sig]

 

[dellyjm]

That I didn't know. SQL is the key to utilizing databases.

If you don't know SQL you're not going to code efficiently thus your code will cost the client alot.

We cannot have a customer waiting more than 10 seconds for his/her customer balance when he's given you his account number. Calls should be no longer than 30 secs average 10. [sig][/sig]

 

[dellyjm]

That I didn't know. SQL is the key to utilizing databases.

If you don't know SQL you're not going to code efficiently thus your code will cost the client alot.

We cannot have a customer waiting more than 10 seconds for his/her customer balance when he's given you his account number. Calls should be no longer than 30 secs average 10.

Delton Phillips
dphilips@gleanerjm.com [sig][/sig]

 

[Catrina]

Kb244...I used the where statement first, and it was also slow.

dellyjm..I have an SQL book. Hence the SELECT statement. I am using SQL, but the database is still slow. I am getting help from another board, but thanks anyway.

[sig][/sig]

 

[dellyjm]

The database probably needs to indexed on the Num field and set as the primary key. That should make the process run much quicker if it hasn't been done already.

A 'WHERE' clause i'm sure would be quicker especially if it's on the primary key. So it'd be WHERE PAYEMP = &quot;'&quot; & Num & &quot;'&quot;.

Delton Phillips
dphilips@gleanerjm.com
[sig][/sig]

 

[MichaelRed]

If the &quot;Num&quot; field is indexed, it would be MUCH faster using the &quot;SEEK&quot; method from vb. Not sure the sql approach will be very different w/o specifying an index.

Also, to &quot;hide&quot; fields from certain users, you can just set the visible property of the control, rather than the manual population. It is easier, safer and probably somewhat faster.

An alternative could be to execute different queries depending on the user, where the user should not see the field data, just return a Null in the field, with the alais set to the field name. [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[TNN]

Catrina,

I am working on exactly the same project you are working on. Converting an old DOS payroll software to VB6.0 using access 2000 database for the backend.

I have a table with 225 fields and 123 records and am having the same slow access response. I am using sql select with where clause. Am considering reducing the size of this table by either splitting the fields or the records.

Would like to chat with you.

My E-Mail TNPAYROLL@AOL.COM

TOM

 

[DanTheVBman]

Am i right in thinking your using a large flat file database? I recently normalised a database that was around 35000 records in size and the results were quite amazing. Theres a wizard in access 2000 to normalise but i think it sux especially if you have to go through 2000 suggested corrections like it asked me too. so i did it manually.

 

[MichaelRed]

Dan is on at least one 'right track'. Ms. Access has a limit on the number of fields in a recordset (256?). This follows current 'thinking' on data base design which favors &quot;deep&quot; (Many Records) as opposed to &quot;Wide&quot; (fewer records) but more redundant information. Regardless of how you go about the normalization - you SHOULD go about the normalization.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over