I created a search form that can take several different parameters (ie. name, SS#, phone, etc). The user can choose one or several criteria. Rather than adding all of the tables involved to the form's DE, I created a view and just added it with NoDataOnLoad as .T. I then made the beginner's mistake of having the search button build a string composed of the search criteria, setting it as the filter, and REQUERYing the view. It's slow!
Now, I want to do this right, but I suspect that since the number of conditions in the view's WHERE will vary, using fixed parameters in the view definition won't work. In other words, if the user isn't searching for a name, how could I deal with "name = lcName" being in the view?
It seems that I'm left with two options: either do this with tables, or us a view and define the parameter list using DBSetProp during run time before REQUERY(). Is that a correct conclusion? Is this a case where a view would be more trouble to use than tables?
Thank you.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Yes, the underlying tables do have indexes. The WHERE clause can contain up to 15 items although that is unlikely.
I have created this form to offer many options when it comes to searching. The user has the option to search for an exact match, a match that starts with their criteria, or a match that contains the criteria. The FIELD = VAR that results from the supplied criteria can take different forms (ie. FIELD = VAR, FIELD == VAR, VAR $ FIELD)
That makes it tricky, it would be counter productive to create that many indexes. I think I would try to come up with a way to do the initial select with just one or two criteria in the Where clause so that you can grab a smaller data set initially into a cursor, then set filters on the cursor for the remaining criteria. If the initial select is optimized, then the filter with non-optimizable criteria only has to apply to a small data set, it should be fairly efficient.
The way I would do it is to add a SQL-SELECT command and you build the WHERE clause dynamically during runtime.
cFilter
IF NOT EMPTY(cSS)
cFitler = cFilter ' AND table1.ss="' + ALLTRIM(cSS) + '"'
ENDIF
IF NOT EMPTY(cName)
cFitler = cFilter ' AND table1.name="' + ALLTRIM(cName) + '"'
ENDIF
and so on ...
SELECT table1.field1, table2.field2, ... , tablen.fieldn;
FROM table1, table2, ..., tablen;
WHERE table1.field1 = table2.field2 AND ... table9.field9 = tablen.field9 &cFilter;
INTO CURSOR query
Two approaches: A you can make use of SET EXACT OFF or more special to SQL SET ANSI OFF. Yes OFF, because then a filter like name='' does not filter all empty names, but any name, so it is NOT filtering. That way you can use filter parameters like name=?lcName and when not wnating to filter for names simply set lcName=''.
But this has it's limits. I'd also optimize how many tables to join, in some cases you may not need to join all tables, as some of them aren't in the filter condition. Last not least the result may not always need the same fields.
So all in all you'd rather want a dynamically created SQL and you can of course do that with VFP. If you want the advantages of views, to be updatable, to cause no problems with grid when requerying etc, you could still generate a view on the fly.
Just make sure you still use parameters, even though with a concrete input of eg 'Cyril' as a name you could write WHERE name='Cyril', biut if someone enters ' OR _vfp.docmd('set step on') you'd assemble WHERE name = '' OR _vfp.docmd('set step on') In short, prevent sql injection attacks by still using name= ?lcName and setting lcName to the entered string.
Bye, Olaf.
Business Accounts
Answer for Membership
by: jgbreedenPosted on 2009-06-26 at 17:18:39ID: 24725717
To me, the question is not whether views or tables are faster, it is how complex does the WHERE clause get, and do the underlying tables have indexes that will take advantage of each condition in the Where.
Do you have indexes on the tables?