Torgny,

I don't know if (SQL) BETWEEN is optimizable. Apparently, BETWEEN() (the function) *is*.

If you are running with SET DELETED ON and you do not hav a TAG on DELETED() then partial may be the best you can get BUT... (repeat: BUT) that may well be OK!

IF your table has few or no deleted records, then running withOUT a TAG on DELETED() is most likely best, because its overhead is very large and can easily outweigh the benefit when there are few records deleted.

You can TEMPORARILY add a TAG for DELETED() and check the optimization level. If it shows FULL then you will know that the cause of the "PARTIAL" was the missing TAG on DELETED() and then you can REMOVE THAT TAG with confidence.

good luck


>Hi All,
>
>How do i define an indextag to the following table to fully optimize my SQL-view ?
>The table contains about 1 million records so it needs to fully optimized or else
>it becomes to slow, at least over the network. I have tried different definitions
>but at its best i only get partial optimazation.
>

>Table: LogAL100
>Field Name Type Len
>
>cart_num     C   8
>coperator    C   5
>dtime        T   8
>cmachine     C   5
>clopnr       C   6
>copnr        C   3
>cstatus      C   1
>corsak       C   2
>cprgrupp     C   3
>------------------------
>Total record size: 42
>

>

>SQL-view:
>
>
>SELECT *, SUBSTR(LogAL100.cart_num,1,5) AS artsort FROM reportit!logal100 ;
>WHERE Logal100.cart_num = ?vp_Artnum ;
>AND Logal100.coperator = ?vp_Operator;
>AND Logal100.cmachine = ?vp_Machine  ;
>AND Logal100.cprgrupp = ?vp_Prgrupp ;
>AND Logal100.clopnr = ?vp_Lopnr ;
>AND Logal100.dtime BETWEEN ?vp_FromTime AND ?vp_ToTime ;
>ORDER BY 10, Logal100.copnr, Logal100.clopnr
>
>
>
>
>