INDEX ON .. FOR &(cCondition) -> Error DBFCDX / 1054

[Yakano]

Hi everyone 

A couple of years ago, I discovered how to remove the slow "Set Filter" from my code, thanks to the publication [http://xthefull.blogspot.com/2014/02/harbouracelerando-al-viejo-set-filter-to. html] by Rafa Carmona (thefull). Thanks Rafa! 

Over time I have been using this method for both new and old features and everything has gone well for me, but now ... 

Maybe I'm being too ambitious and the problem arises because the condition used in the "For" option of the "Index On" command is 561 characters long (!). This causes the error "DBFCDX error / 1054 Data width error". 

Can someone tell me what is the maximum length allowed ??? 

 Thanks!!! . 

P.S.

I insert the code fragment where the query is made on a dbf that contains information about who, when, how long and for what a user has entered to do wich task on which customer. As you can imagine, this dbf can contain hundreds of thousands of records...

// I establish the condition (initial and final values ​​for each field)

   cUsuCon := 'USUARIO>="'+cUsuIni+'".and.USUARIO<="'+cUsuFin+'"'

   cPueCon := 'PUESTO>="'+cPueIni+'".and.PUESTO<="'+cPueFin+'"'

   cDisCon := 'DISTINGO>="'+cDisIni+'".and.DISTINGO<="'+cDisFin+'"'

   cCliCon := 'CLIENTE>="'+cCliIni+'".and.CLIENTE<="'+cCliFin+'"'

   cNomCon := 'NOMBRE>="'+cNomIni+'".and.NOMBRE<="'+cNomFin+'"'

   cYeaCon := 'YEAR>="'+cYeaIni+'".and.YEAR<="'+cYeaFin+'"'

   cTarCon := 'TAREA>="'+cTarIni+'".and.TAREA<="'+cTarFin+'"'

   cDInCon := 'DtoS(DATEIN)>="'+DtoS(cDInIni)+'".and.DtoS(DATEIN)<="'+DtoS(cDInFin)+'"'

   cTInCon := 'TIMEIN>="'+cTInIni+'".and.TIMEIN<="'+CTInFin+'"'

   cDOuCon := 'DtoS(DATEOUT)>="'+DtoS(cDOuIni)+'".and.DtoS(DATEOUT)<="'+DtoS(cDOuFin)+'"'

   cTOuCon := 'TIMEOUT>="'+cTOuIni+'".and.TIMEOUT<="'+cTOuFin+'"'

   cCondicion := cUsuCon + '.and.' + cPueCon + '.and.' + cDisCon + '.and.' + ;

cCliCon + '.and.' + cNomCon + '.and.' + cYeaCon + '.and.' + ;

cTarCon + '.and.' + cDInCon + '.and.' + cTInCon + '.and.' + ;

cDOuCon + '.and.' + cTOuCon

   // I Index TEMPORARY.dbf (it will contain the records that meet the condition)

   USE &(cFileDbf) ALIAS TEMPORAL EXCLUSIVE NEW

   INDEX ON (Usuario)  TAG 1 TO &(cFileCdx)

   INDEX ON (Distingo) TAG 2 TO &(cFileCdx)

   INDEX ON (Cliente)  TAG 3 TO &(cFileCdx)

   INDEX ON (Nombre)   TAG 4 TO &(cFileCdx)

   INDEX ON (Year)     TAG 5 TO &(cFileCdx)

   INDEX ON (Tarea)    TAG 6 TO &(cFileCdx)

   INDEX ON (DateIn)   TAG 7 TO &(cFileCdx)

   CLOSE TEMPORAL

   USE &(cFileDbf) INDEX &(cFileCdx) ALIAS TEMPORAL EXCLUSIVE NEW

   

   // I open the query database

   Select SHERLOCK

   dbGoTop()

   // Testing index in memory

   nSecIni := Seconds()

   cIndexKey := IndexKey()

   INDEX ON &(cIndexKey) TAG _TEMP_ TO tHarbourt FOR &(cCondicion) ADDITIVE MEMORY &&(aqui da error de longitud; Len(cCondicion)=561 !!!)

   OrdSetFocus( "_TEMP_" )

   dbGoTop()

   // I fill TEMPORARY with records from SHERLOCK

   do while Eof()==.F.

      if &cCondicion

         Select TEMPORAL

         dbAppend()

         for i:=1 to fCount()

            FieldPut(i, SHERLOCK->(FieldGet(i)))

         next

      endif

      Select SHERLOCK

      dbSkip()

   enddo

   // Testing index in memory

   OrdDestroy("_TEMP_")

   OrdSetFocus( "1" )

   nSecFin := Seconds()

   

   // Processing time

   Alert('Consulta procesada en Segundos... '+AllTrim(Str(nSecFin-nSecIni)))

[Francesco Perillo]

You may test yourself, removing the last .AND., then the one before.

Then you may shorten it by creating a function myIN:

cUsuCon := 'USUARIO>="'+cUsuIni+'".and.USUARIO<="'+cUsuFin+'"'

becomes

cUsuCon := 'myIN(USUARIO,"'+cUsuIni+'","'+cUsuFin+'")'

saving several chars, but making it slower.

You may need to REQUEST myIN....

You may also add some logic, but it depends if it feasible for the program:

if cUsuIni == cUsuFin

  cUsuCon := ".T."    // this is stupid but gives the idea, you check....

endif

Also if you have a field that can go from 0 to 9999, and the user asks from 0 to 9999, you can remove the line since he asks for all the values.

I'd like to ask you why you create an index then copy the values and not use a COPY TO/SET FILTER... probably the code you pasted is a test, but I suppose that creating the index takes time and has to retrieve the full DBF anyway...

Unless you are using some sort of letodb(f).

--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/78080a8a-5635-4fbe-a35b-ef95d3cf980fn%40googlegroups.com.

[Auge & Ohr]

hi,

> Can someone tell me what is the maximum length allowed ???

as i can say CDX Indexkey() Len can be 240 or 120 Sign maximal

---

SET FILTER can be very slow so i would be better to use Index and SCOPE
if you want to search "inside" from String you can use Index / OrdWildSeek()

   DO WHILE ORDWILDSEEK( "*" + ALLTRIM( cSeek ) + "*", .T. )
      AADD( aSelect, { cTEXT, RECNO() } )
   ENDDO

Jimmy

[CV]

Hi Yakano

For things like this:

cPueCon := 'PUESTO>="'+cPueIni+'".and.PUESTO<="'+cPueFin+'"'

You can use:

function RANGE(Var, from, to)

return (Var >= from .and. Var <= to)  // adapt it to your needs

So you would write the same expression as:

cPueCon := Range("PUESTO", cPueIni, cPueFin)

Thus saving some chars in your final index expression.

The max length for an index expression is 240 chars.

My 2cents.

Regards

---

Claudio Voskian

Buenos Aires - Argentina

[Yakano]

Hello again 

It seems that the problem is that when you use "For", that condition is stored in the "Bag" of the "Cdx" file, (hey!) what you write after "For", but NOT de returned value, wich is used only to evaluate if the record has to be included for the index.

Your comments made it much easier for me and in the previous code, you just have to change the "Index On" line and add the corresponding function. Like that...

[...]

   INDEX ON &(cIndexKey) TAG _TEMP_ TO tHarbourt FOR UserFilter(cCondicion) ADDITIVE MEMORY

[...]

FUNCTION  UserFilter (cCondicion)

RETURN(&(cCondicion))

Loooove simplicity !!!

In the first tests (pending production version), the time used for the query is 20% ( 80% reduction... Amazing!!! ).

fperillo, I use a temporary Dbf and I create its indexes while it is still empty, which takes very little time (null?). This database is displayed after making the query ("Browsed") and it allows the user to display it, indexed by each one of the indexes that were created at the beginning. Then when "printing" (pdf,xls,etc) the report, he can get it ordered by the same index he selected previosly. It is only pure DBFCDX, not LetoDbf. 

Jimmy, I have no experience using "Scope", but I thought I could only set a minimum and maximum value (TOPSCOPE- BOTTOMSCOPE in "ord.ch") for a simple index, that uses a single field. Can it handle complex conditions involving multiple fields? Perhaps when adding several "Scope", each one adds a filter on the previous one?

Claudio, that was the path I followed, but more extreme.

Thank you so much fperillo, Jimmy, Claudio !!! 

Kind regards.

[Angel Pais]

I don't like macros on indexes because macros are context depèndant and you never know where are you using an index.

--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/5aa75abf-b645-4dcc-b3fa-65f624bf2d98n%40googlegroups.com.

[Yakano]

Never? Mmm... I think I *allways* know were I use each index.

Well, it's a temporary index for a temporary simple query.

Original data in DbfCdx's are keeping their original indexes.

[CV]

Your function will be safe provided that there are no two (or more) temp indexes used (open) at the same time.

Regards

---

Claudio Voskian

Buenos Aires - Argentina

[Francesco Perillo]

On Fri, Oct 15, 2021 at 12:05 PM Yakano <gfc...@gmail.com> wrote:

It seems that the problem is that when you use "For", that condition is stored in the "Bag" of the "Cdx" file, (hey!) what you write after "For", but NOT de returned value, wich is used only to evaluate if the record has to be included for the index.

Yes, I found there is space for 240 char maximum.

Your comments made it much easier for me and in the previous code, you just have to change the "Index On" line and add the corresponding function. Like that...

[...]

   INDEX ON &(cIndexKey) TAG _TEMP_ TO tHarbourt FOR UserFilter(cCondicion) ADDITIVE MEMORY

[...]

FUNCTION  UserFilter (cCondicion)

RETURN(&(cCondicion))

Loooove simplicity !!!

I think you can gain some more speed, but only if you NEVER have more than 1 UserFilter active...  if I remember correctly, RETURN(&(cCondicion)) "compiles" the string in cCondicion every time. The time spent may be negligible but is done once per record, and it may sum up.

fperillo, I use a temporary Dbf and I create its indexes while it is still empty, which takes very little time (null?). This database is displayed after making the query ("Browsed") and it allows the user to display it, indexed by each one of the indexes that were created at the beginning. Then when "printing" (pdf,xls,etc) the report, he can get it ordered by the same index he selected previosly. It is only pure DBFCDX, not LetoDbf. 

Ok, so the code snippet you posted in the first message is real code? From INDEX ON to OrdDestroy is real code?

If it is real code, I think you can shave some more ms. You don't need to test the IF &(cCondition) and you may try a COPY TO.

[Auge & Ohr]

hi,

general you can use SCOPE for every X < Y < Z
when you have a lot "Case" you must modify you Index

but in your "Case" you have "numeric" Value so you can´t use OrdWildSeek() this Way.
you only can use it on "Primary" Index but it will help a lot while you have less Records to compare "Rest" with "FILTER".

Jimmy

[Yakano]

Yes, Claudio, this method by indexing on memory using the for option, uses only one index that I destroy after I get results, it exists just to accelerate the process. I never try to use more than one temporary index.

But... (always but) I also use this method when editing data bases "filtered" by some condition without set filter. And I can confirm... it's fast, very fast and so robust, keeping and updating the permanent and temporary index at same time, as usual, no matter if the temporary index is on memory , anyway I destroy it when I leave (no problems since I start removing Set filter using this Rafa Carmona's method 2-3 years ago)

The first blok that creates the temporary filter is for replacing "set filter to ...etc" and the destroy block replaces "set filter to", thats all, no more changes in your code and you get the same, but faster.

Even I think that more than one temporary index on memory may works, but I don't need that...

Regards!

[Yakano]

Yes, fperillo, it's real code (just the gets for condition, some messages and other internal library calls has been removed), and it works... If you want to chek it, I can send you.

And yes, some millisecs can be shaved from this "if" (I saw it), it was in the original code I'm improving speed and I keep it for "safety" (no problems with index on memory, but this "if" helps if some error appears on it, or thats I hope)

[Yakano]

OK, Jimmy, all values are character (some date too) and I'm not sure "Scope" could help me (at least I don't know how)

[Auge & Ohr]

hi,

"think SQL" where you use a "primary" Index and "Where" as "Filter"
IHMO you should think about using SQL when have "Filter" like this

---

"SET FILTER" do SKIP and "test" all Record if they match
when have a (primary) Index and SCOPE you can reduce it a lot when run "Filter"

so look at your "Filter" which Part will give most "matching" and use that FIELD in a (primary) Index with SCOPE.

Jimmy

[Yakano]

Hi, Jimmy

Sure that changing to Sql helps, but I have no experience in handling it. : '(

Thanks