Query very slow, bad code, poor index or anything else the culprit?

[MikeData]

Form is based on a set of 3 tables
the main /master form has 14 indexes.
-----------------------------
U date d
fin_date d
prod c 40
mfg c 10
utype c 40
tmr c 20


gdte1 gdte2 global d
gMsc1 gMsc2 gMsc3 global c
--------------------------------------
Above is The make of the variables involved.
Every field above is indexed in the main form.
Below is the code to get records by a query.

1. Is this, the best way to write a code for a query ?
2. Can it be simplified to be fassssster? Like combining fields or any other syntax.
3. Besides the code, is there anywhere else to optimize?

------------------------------
(( Var->gMISC1 $ prod ) ) .and.
(u_date >= Var->Gdte1 ) .and.
(u_date <= Var->Gdte2 ) .and.
(( Var->gMISC2 $ mfg ) ) .and.
(( Var->gMISC3 $ tmr ) ) .and.
isblank("utype") .and.
isblank("fin_date")

[Tom Cone Jr]

Re: Query very slow, bad code, poor index or anything else the culprit?

1) Be glad this works. It's pretty complex.

2) Is the query running against the primary table in the set? Code not shown. Context not described.

3) The substring inclusion operator has to do a lot of work. So it's apt to be pretty slow.

4) Unlikely that your indexes are being any help at all.

5) You seem to be mixing various data types. Can't see the actual filter expression you're using. Code not shown.

Mike, some of my confusion may be the terms you're using. Forms don't have indexes. Tables do. When you say "main form" are you really trying to say "primary table in the set"?

-- tom

[Mike Wilson]

Re: Query very slow, bad code, poor index or anything else the culprit?

Mike,
I too am not believing it does work. For the line - isblank("fin_date") - I am pretty darn sure this won't work because I am almost certain the isblank() doesn't work for a date field (see image)

[csda1]

Re: Query very slow, bad code, poor index or anything else the culprit?

Hi Mike W.,

Mike,
I too am not believing it does work. For the line - isblank("fin_date") - I am pretty darn sure this won't work because I am almost certain the isblank() doesn't work for a date field (see image)

vfilter=isblank("fin_date") is wrong on two counts. vfilter is dim'ed as C (Character type), but ISBLANK() returns a logical. Also, fin_date field is not accessible from the interactive window unless you have changed the context of the interactive window.

I have found that trim(cdate(date_field))=="" is the most reliable comparison of any date field or date value.

Tom,

The $ operator is pretty fast. I don't think that would slow down any aspect of the query. However, LQO and indexes could help if specified correctly, but there is too little info here. The u_date range could allow a range if using the between with embedded constants (variables are inserted into filter string to look like date constants. Also fin_date and utype could also be used for LQO with the correct format. It also depends on the criteria that eliminates the most records to be the best choice for LQO, which depends upon data trends.

[Tom Cone Jr]

Re: Query very slow, bad code, poor index or anything else the culprit?

Ira,

It's good to hear from you.

I'm not privy to the implementation details for the substring inclusion operator but it seems to me that each time it's used it may have to do (n/2 + x-1) comparisons . (Where 'n' is the number of characters in the string being searched and 'x' is the number of characters in the search key.)

-- tom

[csda1]

Re: Query very slow, bad code, poor index or anything else the culprit?

Hi Tom,

I'm not privy to the implementation details for the substring inclusion operator but it seems to me that each time it's used it may have to do (n/2 + x-1) comparisons . (Where 'n' is the number of characters in the string being searched and 'x' is the number of characters in the search key.)

I don't have implementation details either, but I suspect it searches 1st for the 1st character match, then the extra characters. Here is my test code and results. It indicates that it searches from the beginning of the string, and that extra matches slow it down a small bit.

dim a1 as c
dim a2 as c
' ReplicateX() is enhanced CSDA code.  
' Built-In Replicate() will run very slowly or hang on many systems with large repeats
' Creates a 1 million character string
a1=replicateX("00",500000)
a2=replicateX("ab",500000)
dim b1 as c
dim b2 as c
dim b3 as c
dim b4 as c
b1="abc"+a1
b2=a1+"abc"
b3="abc"+a2
b4=a2+"abc"
' Search for "abc" at beginning, characters match once
?"abc" $ b1	' .00842 seconds
' Search for "abc" at end, characters match once
?"abc" $ b2	' .01007 seconds
' Search for "abc" at beginning, 1st 2 characters match repeatedly
?"abc" $ b3	' .00932 seconds
' Search for "abc" at end, 1st 2 characters match repeatedly
?"abc" $ b4	' .01193 seconds

[MikeData]

Re: Query very slow, bad code, poor index or anything else the culprit?

Thanks for the overwhelming response. I am in the road today and I have to digest all the responses.