Performance query

[jdpjamesp]

Just wondering if there was a performance hit by doing A over B below? Or are they much of a muchness?

A:

/*Try and work out table using fact that field should start with EntityMnemonicKey*/
FOR FIRST lb-ENT FIELDS(EntDescription) NO-LOCK
    WHERE lb-ENT.EntityMnemonicKey = SUBSTRING(ip-DBFieldName,1,3):
    ASSIGN op-DBTable = lb-ENT.EntDescription.
END.

B:

lv-EntityMnemonicKey = SUBSTRING(ip-DBFieldName,1,3).
/*Try and work out table using fact that field should start with EntityMnemonicKey*/
FOR FIRST lb-ENT FIELDS(EntDescription) NO-LOCK
    WHERE lb-ENT.EntityMnemonicKey = lv-EntityMnemonicKey:
    ASSIGN op-DBTable = lb-ENT.EntDescription.
END.

 

[RealHeavyDude]

AFAIK using functions in a query where prevents the compiler from selecting an appropriate index bracket and therefore will result in a table scan. I think I have it seen documented somewhere but can't remember right now where it was.

Heavy Regards, RealHeavyDude.

 

[jdpjamesp]

Yeah that's what I thought too. Thanks RHD. Thankfully in this case it's not a difficult fix
Finding some howlers at the moment. Some of them are harder to fix. Such as a Query for 3 or 4 tables all found no-lock. That's fine, but then later we FIND CURRENT query EXCLUSIVE-LOCK in order to update one table. So let's lock all the records to do it!

 

[rzr]

AFAIK using functions in a query where prevents the compiler from selecting an appropriate index bracket and therefore will result in a table scan. I think I have it seen documented somewhere but can't remember right now where it was.

Heavy Regards, RealHeavyDude.​

I recall reading this too, but can't find the pdf right now.... anyways I was trying the below ... and..

[FONT=Courier New]DEFINE VARIABLE cName AS CHARACTER NO-UNDO.

FOR FIRST Customer NO-LOCK
    WHERE Customer.Name = SUBSTRING(cName,1,10) :

    DISPLAY Customer.Cust-Num Customer.Name.
END. [/FONT]

XREF for above code shows the compiler *using* "Name index" from Customer table.

c:\temp\1.p c:\temp\1.p 1 COMPILE c:\temp\1.p
c:\temp\1.p c:\temp\1.p 1 CPINTERNAL ISO8859-1
c:\temp\1.p c:\temp\1.p 1 CPSTREAM ISO8859-1
c:\temp\1.p c:\temp\1.p 1 STRING "cName" 5 NONE UNTRANSLATABLE 
c:\temp\1.p c:\temp\1.p 3 STRING "Customer" 8 NONE UNTRANSLATABLE 
c:\temp\1.p c:\temp\1.p 3 ACCESS sports2000.Customer Name 
c:\temp\1.p c:\temp\1.p 3 SEARCH sports2000.Customer Name
c:\temp\1.p c:\temp\1.p 5 ACCESS sports2000.Customer Name 
c:\temp\1.p c:\temp\1.p 5 STRING "x(30)" 5 NONE TRANSLATABLE  FORMAT 
c:\temp\1.p c:\temp\1.p 6 STRING "Name" 4 LEFT TRANSLATABLE 
c:\temp\1.p c:\temp\1.p 6 STRING "Name" 4 NONE UNTRANSLATABLE 
c:\temp\1.p c:\temp\1.p 6 STRING "--------------------------------" 32 NONE UNTRANSLATABLE 
c:\temp\1.p c:\temp\1.p 6 STRING "Name" 4 NONE UNTRANSLATABLE

So if a valid index is used it would'nt result in a full table scan.. would it ?

 

[RealHeavyDude]

Maybe we should start a "worst practice" forum where we could post such examples in order to enlighten those who are responsible for them

For sure I could add a lot ...

Heavy Regards, RealHeavyDude.

 

[RealHeavyDude]

There might be another aspect but I am not 100% positive - so please bear with me:

Having a closer look it I would appear to me that the function needs to be evaluated during each iteration of the FOR EACH loop. If that is true then, although the compiler is able to pick an index, the database engine is not able to appropriately use an index bracket because it is only valid for a single iteration.

Heavy Regards, RealHeavyDude.

 

[cj_brandt]

Function based indexes are not supported. I created an enhancement request for them and was shot down.

 

[jdpjamesp]

*loads rifle*

What was the main ammunition used to shoot? Hopefully not silver bullets?

 

[rzr]

here's another FYI from the good old Kb

 

[jdpjamesp]

Thanks rzr