Grid vs Browse for lookup only 1

[yert33]

I've been learning about grids. They are nifty. I have an app where the users are entering as much or as little of a person's first name, last name, dob, etc and my app should search for all close matches and display them in a grid-like arrangment. For small tables a grid populated from an SQL statement into a cursor performs fine.

But when the table gets bigger, the SQL query time goes up. So I've been perusing this forum in hopes of finding the quickest method for record retrieval into the "grid-like" arrangment.

Then I remembered my old MFoxPLus days: why not just use a browse window. I want nothing but to display the records, nothing else. I don't have to wait for an SQL query to build, etc.

Is browse my best answer here? Is it more "native" to the table than the SQL/cursor/grid method?

Thanks to all.

 

[yert33]

My question is really:

What's the fastest way to retrieve a set of records?

Here's my SQL statement

select county, countycode, certnumber, lastname, ;
firstname, permaddr, permcity, dob ;
from mcounty ;
where mcounty.lastname=alltrim(mlastname) and ;
mcounty.firstname=alltrim(mfirstname) and ;
mcounty.permaddr=alltrim(mpermaddr) and ;
mcounty.permcity=alltrim(mpermcity) ;
order lastname, firstname, dob
into cursor crsTemp NOFILTER

With a table that has approx one million records this takes up to 20 seconds or so (P4 1.6gHz, tables and code on local drives) which is bordering on the unaccceptable for the client....

 

[yert33]

You think you know everything!



Thank you SO MUCH for your reply. Good points all and will undertake to implement each and every one of them. Thank you again. Will report back on results.

The data modeling is inherited and the whole project will be moot in 3 months time....with 10 days left to complete development. Can you say 'critial yet ad hoc'??

Thanks, Mike.

 

[yert33]

...and the real world for this will actually be with all the lookup.dbf's on the local drive. Wierd, I agree, but true.

 

[yert33]

YAHOOOO!!!!!

Dayum, I just did your #'s 1 and 2 and man, oh man - fast as heck.

That's some answer Mike. I appreciate you taking the time to give me that little iist of hot rod instructions. Now on to the rest.

 

[yert33]

On the entity topic, are you saying I should index unique on these fields, copy them to a separate table with a key field and then relate to them from the original file?

 

[JRB-Bldr]

Not that it will address your response issue, but you might want to look at thread184-141633 to handle your partial entry search issues.

Good Luck,
JRB-Bldr

 

[yert33]

Thanks to you, JRB-Bldr. I will go down that rabbit hole soon.

 

[yert33]

Stumbled on new territory for me this morning. I made y code changes to my office dev machine and it was not as fast as what I experienced last night on my laptop. So i reenabled the SYS(3054,1) code and saw that Rushmore Opt was none.

Which has led me to discover that there are codepage diffs on my dev machine. There aren't on my laptop...So now I used cpzero to change all DBF codepages to 437 to match VFP 9 and now am deleting all index tags and index ing again.

Thanks for the tips again. I feel that I'm on my way to getting all the performance I can out of this app. You really helped me.

Trey Harris

 

[yert33]

Still scratching my head over a remaning speed difference between laptop and desktop (after changing codepage on all desktop dbf's to match 437).

Is 437 the codepage of choice? Or is 1252?

we have config.fpw's on these machines that were installed from a commercial app. It has codepage = 437 in it. I'm assuming that it has overidden the default of 1252 when VFP 9 starts up. sound reasonable??

Thanks again.