LQO - Ambiguous how on earth do you get this working in a grid

[peteconway]

I've searched the the help and all the threads - man, all I need is a clear point-by-point example of how to implement this feature - any assistance appreciated, i have 400,000 record in this table now and it can’t even display a person by surname in under 30 seconds – indexed or not.

[Steve Workings]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Your filter expression needs to match an index expression exactly. There are a few other details but for what you need it should be simple.

If you have an index:

alltrim(surname)

and provide a filter (with myVar being the value):

alltrim(surname) = myVar

you should be in good shape.

Remember to alltrim(myVar) as well before using it.

If this doesn't help enough, how about showing us your index expression and filter attempt?

[peteconway]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Steve - thanks for that I'll give it a go tonight - check out what the help file says:

Use at least one of the same fields used in the query.

If you use multiple fields with different data types, the first field used must be type character,

The data type of the data in the index key must match the data type of the field you are searching.

Use an order expression that contains either a single field, or two fields concatenated using the plus operator (+), such as in the expression Lname+Fname.

Not use the Unique Only option

Not use a filter expression

[csda1]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Hi Pete,

Not use the Unique Only option

Not use a filter expression

Both of the above refer to the definition of the permanent index on the table, not the definition of the query

[peteconway]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Sorry, I'm just totaly lost on this.

I just don't get "what-goes-where" in the GRID.

I have a grid - I want to use the LQO on a field in the table called fullname, i have an index called fullname alltrim(fullname)

When fullname is entered in the search, where the field property is set to fullname, I want the LQO to display the reslult.

When you say set the variabe - where do I do that, how do I get the search property into the variable?


When you say "Index expression" does that mean use or don't use the order function. -

I really am lost on this and it concerns me I may be losing the plot.

[Steve Workings]

Re: LQO - Ambiguous how on earth do you get this working in a grid

If you have the index you describe, and simply build a Search on the Fullname field, you may be all done.

If it's apparent that the query is still taking too long, try using the BeforeSearch event of the Search object, with code something like this:

fullname = alltrim(SearchDataSubmitted.FULLNAME)

When you build an index, you have to choose an Order (ascending or descending) and if you don't Alpha will select the former for you anyway. This part is not a big deal and not a factor in what's causing your grief. Just choose ascending.

[APFCClyde]

Re: LQO - Ambiguous how on earth do you get this working in a grid

I too am having the same problem. Attached screenshots show the setup. When I run a lastname search in the desktop, it takes 7 seconds to return 66 records - the same search on the web takes 50 seconds. Then when I click next to display the next 10 records, it takes another 50 seconds.

What could be taking so long?

[Steve Workings]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Your screenshot doesn't show which table contains the Lastname field.

I don't work with grids based on a View very much, but I strongly suspect that this is the cause of your problem. I'm not sure how Alpha executes this from the web server, but from a desktop it's the equivalent of having a set. LQO doesn't work if you're trying to query a child table in the set.

Try making a grid based only on the table that contains the Lastname field, add a Search feature and see how well that performs.

[APFCClyde]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Actually I just finished doing that & it worked great. The problem is that I need to display info from the other files (ARHIST & POLICY) in the result grid. The FINBUS file has the lastname field - the other two files are linked by AccountNo.

As I said , I created the same set in the desktop & it is much faster. Obviously adding these two files to the view is slowing it down, but I don't understand why since it works well in the desktop.

[Al Buchholz]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Obviously adding these two files to the view is slowing it down, but I don't understand why since it works well in the desktop.

I'm not sure that is the problem.

You could easily test by making a grid and search based only on the FINBUS table and verify performance.

[APFCClyde]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Al, that is what I did earlier. I created one with FINBUS only & it was very fast. As soon as I add the other two tables it slows to a crawl. Of course, I've seen this happen on the desktop also but in this case the desktop set was built like the view & is 10 times faster than the web version. Not sure what to do short of hand coding my own search to pull the FINBUS recs first to a temp table then matching the other two tables for the info I need.

[[email protected]]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Al, that is what I did earlier. I created one with FINBUS only & it was very fast. As soon as I add the other two tables it slows to a crawl. Of course, I've seen this happen on the desktop also but in this case the desktop set was built like the view & is 10 times faster than the web version. Not sure what to do short of hand coding my own search to pull the FINBUS recs first to a temp table then matching the other two tables for the info I need.

How about keeping the search on 1 (one) table but use calculated fields (using lookupc) to provide the missing data for your grid?

[Al Buchholz]

Re: LQO - Ambiguous how on earth do you get this working in a grid

Al, that is what I did earlier. I created one with FINBUS only & it was very fast. As soon as I add the other two tables it slows to a crawl. Of course, I've seen this happen on the desktop also but in this case the desktop set was built like the view & is 10 times faster than the web version. Not sure what to do short of hand coding my own search to pull the FINBUS recs first to a temp table then matching the other two tables for the info I need.

What I'm concerned about is that the linkage from parent to child has the proper index set up for the child.

So, you are saying that a desktop set is in-place with the exact same linkage as the view? Or was that another similar but different situation?

A one to one linkage from parent to child shouldn't add a significant amount of time to the retrieval if the indexing on the child is properly established.

I'd build a set on the desktop with the same linkages as the view. That'll cause the index structure to be correct. I'd also verify the index validity by running a5_checkindexdialog() from the desktop and check the indices of the children tables.

If none of that works, post the tables and web objects so we can test them... What you are experiencing is not consistent with what I find in my tables and views.

(And I hope we don't have to use calculated fields with lookups because that causes many more table opens and closes than necessary.)

[APFCClyde]

Re: LQO - Ambiguous how on earth do you get this working in a grid

First, I had "Is Contained in" set as the search style default which was slowing things down. However, it was still slow using the set/view. So, I built a search table containing all of the info I need for searching/displaying the results grid & now it is almost instantaneous on any search field.

Sometimes it just doesn't pay to fight against the software.