Using ALLTRIM() on idexes is a bad idea, the full field is always better and faster, because VFP does not have to preprocess the value for searching, for adding a new value and for reindexing.
El lun., 19 de marzo de 2018 23:29, Bill Anderson billand88@gmail.com escribió:
Mike,
I believe ALLTRIM()s in SQL doesn't "defeat" optimization, it's just that you can't have ragged (meaning, varying length) indexes in VFP.
A tag on ALLTRIM(field) is padded out to the full length of the field.
Bill Anderson
On Mon, Mar 19, 2018 at 12:55 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
-----Original Message-----
From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Sunday, March 18, 2018 2:34 PM To: ProFox profox@leafe.com Subject: Testing tells the tale (SYS 3054) -- "BETWEEN(a,b,c)" equivalent to "a BETWEEN b and c"
VFP9SP2 WestWind WebConnection App on WinServer 2012
I was looking at a notoriously slow query to see about optimizing it. Using SYS(3054,11,"cmemvar"), I was able to do so, but during my testing, I found that using MyDate BETWEEN Arg1 and Arg1 was the same optimization-wise as BETWEEN(MyDate,Arg1,Arg2). Somewhere in history, I thought that using VFP's native BETWEEN(field,d1,d2) was NOT Rushmore friendly. However, testing with SYS(3054) showed otherwise!
Just an interesting find this weekend for me whilst debugging.
Carry on! --Mike
Also found that the ALLTRIMs in some SQLs were defeating optimization as well. Just a tip for others here! I don't code that way but somebody in the history of this app did.
[excessive quoting removed by server]