.Or. What?

[Steve Wood]

Sorry about the corny title. Any thoughts on the following...?

I am trying to construct an effecient filter expression which will have multiple .or. operators as in:

Accnt_no="10124" .or. Accnt_no="10125"

Which is fine so far, but the number of OR operatiors is going to be several hundred. It actually works fine in some tests of 150 OR's and is reasonably fast, but I start to run in to maximum charactor allowed for the places I would set my filter, DBF.Filter=, for instance.

I also tried inlist2() function. That works for my 150 test also, but is extremely slow.

Add to this my record set is near 350,000, so I've got a problem.

The two things I have not tried (and don't know if they will work) are

1 - Some Array method
2 - possibly running a query on the table prior to view. I don't think that will work in the web environment.

[NoeticCC]

Re: .Or. What?

This is one of the things that I missed in DBFs but is easy in SQL - the in() function takes care of that :)

[Doug Page]

Re: .Or. What?

I don't know whether the A5_COMBINE_FILTERS() functions will work. Worth a try.

[Mike Reed]

Re: .Or. What?

Hi Steve,
I cannot help you out with this, because I have been faced with the same issue.

So I'm hoping that if a suitable outcome is found that you would post it.

Thanks,
mike

[csda1]

Re: .Or. What?

Sorry about the corny title. Any thoughts on the following...?

I am trying to construct an effecient filter expression which will have multiple .or. operators as in:

Accnt_no="10124" .or. Accnt_no="10125"

Try this instead

wordat(ut(Accnt_no),"10124,10125",",")>0

If the filter expression is still too long, try

compare_string="10124,10125"
wordat(ut(Accnt_no),compare_string,",")>0

I'll also add that INLIST2() is a wrapper function that does a similar result, but is extremely inefficient. WORDAT() is a native function that runs extremely fast.

wordat(ut(Accnt_no),"10124,10125",",")>0 ' .000316952548 seconds
inlist2(Accnt_no,"10124,10125") ' .00189107413 seconds

Inlist2() is 6 times slower. How do I know? Gee, perhaps there is a utility that could accurately measure code execution times :) of code fragments?

Maybe the CSDA Code Utility ?

[Pat Bremkamp]

Re: .Or. What?

Steve, I had a couple thoughts. First, if the filter is a couple hundred or so entries long, then you must have them in a table somewhere. Probably not being selected one-by-one by hand. Second, after you get those records, then you must be plannig to do something with them.

So, this may be a good place to create a temporary table. If both tables are in account number order, or are indexed on account number, you could loop through the selected accounts and copy the records (or maybe just a few needed fields) to the temp table.

Then base your actions on the temp table.

Pat

[Al Buchholz]

Re: .Or. What?

Sorry about the corny title. Any thoughts on the following...?

Yes, you should be.... :D

Add to this my record set is near 350,000, so I've got a problem.

The two things I have not tried (and don't know if they will work) are

1 - Some Array method
2 - possibly running a query on the table prior to view. I don't think that will work in the web environment.

3. Build a table with a record with each value in it and a set that links to the original table... Then you don't have many limitations....

[Steve Wood]

Re: .Or. What?

Ira "Gee, perhaps there is a utility...". I've never thought of you as a 'utility', but yes you are good at measuring this sort of stuff:). OK, OK, I'll break out the CSDA Code Utility. Have to admit, I am getting to the point where I need that more often as my client's databases and expectations are growing. I'll test all of those suggestions and report back. The client has agreed to limit the filter to 50 or so Accounts, which will fall within the limit I found using DBF.filter= on the A5W page.

FYI - As part of this I tested session variable length. Appears to be practically unlimited in size. It must have some limit, but I threw 30,000 characters at it and it retained the value.

Pat - yes the filter parameters are in a database. The structure is such that Accnt#1000 controls all of these other Accounts 1002,1003,1006, etc. The list of controlled accounts can change at any time, so maybe they change the list to 1002,1999,1006, and then the filter still has to work right after that.

Where I have the luxury of running a script, I certainly don't use this filter method, I use a query and fetch like I should. But I want to display all of the "controlled accounts" in a Grid, and that requires a filter expression.

I'm not an SQL expert, but it sounds like what I want is to select the records I want and bring back a Result Set. In A5, that would be similar to what you propose, bring the records over to a temp table, or perhaps summarize them.

[Tim Kiebert]

Re: .Or. What?

Pat - yes the filter parameters are in a database. The structure is such that Accnt#1000 controls all of these other Accounts 1002,1003,1006, etc. The list of controlled accounts can change at any time, so maybe they change the list to 1002,1999,1006, and then the filter still has to work right after that.

Steve,
One way to read this is that you are looking for all accounts in the 1000 range.

If not then
     ignore the rest of this
Else 
     Try left(Accnt_no,1) = "1"
End if

[Doug Page]

Re: .Or. What?

Just another thought...

What about a "filter" field in the table. Reset it it to false and then just run through the table with the filter parameters setting the field to true. Now when you need a the filter expression you just need to check one field for one value. Another benefit would be to set a saved variable (boolean) that would indicate if there have been new account codes added. If not, your table would already have the right values set and save the time and effort of resetting the field values. Depends on how often your list changes. If not often, then this might be a very plausible solution.

[Bill Parker]

Re: .Or. What?

Would be interesting to see what happens when that session variable is overriden with a 30,000 character url parameter!

Bill.

[martinwcole]

Re: .Or. What?

Just tried Ira's approach. I was originally using:

parentform.BaseQueryRun("selfpay=.f. .and. inlist2(systemid,var->gpc2)","fullname")

I tried:
parentform.BaseQueryRun("selfpay=.f. .and. wordat(ut(systemid),var->gpc2,',')>0","fullname")

I prepopulated var->gpc2 with 190 words and ran against a table with about 17,000 records

went from about 3 seconds to about 1/5 second!!! Major improvement.

[csda1]

Re: .Or. What?

Just tried Ira's approach. I was originally using:

parentform.BaseQueryRun("selfpay=.f. .and. inlist2(systemid,var->gpc2)","fullname")

I tried:
parentform.BaseQueryRun("selfpay=.f. .and. wordat(ut(systemid),var->gpc2,',')>0","fullname")

I prepopulated var->gpc2 with 190 words and ran against a table with about 17,000 records

went from about 3 seconds to about 1/5 second!!! Major improvement.

Feel free to send me a check for all the time you save! :D

When ever you have a query/filter expression, efficiency is always #1 unless processing a trivial number of records. If systemid is already trimmed you don't need to use UT(). Even if it isn't, TRIM() might be a slight bit faster, though probably not measureable. WordAt() is case-insensitive, WordAtC() is case-sensitive version

[martinwcole]

Re: .Or. What?

Hey!!!!! I done already bought the supreme version of csda utility!! (Just needed you to use it for me)

[csda1]

Re: .Or. What?

Hi Martin,

(Just needed you to use it for me)

And that's what the check is for, TECH SUPPORT! :D

Actually though, it amazes me how many people run with critical sections of code that could be enhanced by speedups. If that code took 10 minutes to run the query originally and now runs in 1 minute, that's a 9 minute savings each time it's run. Multiply that by the frequency it is run at, and it makes a big cost savings.

It's a service I offer to users and other consultants, but few seem to take advantage of it.:(

The right tweak can change an application from a dog to a cheetah. It seems like the typical range of speed increase ranges from 30% faster for a very well written & conceived piece of code to an amazing 20000% (In other words 200x faster) for some algorithms. 50x is not uncommon for typical code. If your numbers are accurate, you had a 1500% increase (15x), which is not bad.

Sometimes it's code, sometimes it's algorithms, sometimes it's methods (e.g. passing a large blob/string to a function can be high overhead, but pass a pointer to the blob/string and it's virtually instantaneous)