Hi Walter,Jim and Mike

Thanks for your help. This LogAL100 table is static, I mean that there is not any records appended to it and there are also
no deleted records. I made one indextag on each of the fields included in the where clause and set deleted off right before
i use the view. Surely this is not the best approach, but it works just fine and is also very fast on our network. Walter, I
dont think that your suggestion about the specialized index for the view will allow me to get hits on partially equally fields ? I mean i can use vp_cart_num="12" and get records with cart_num starting with "12" and the same can be done with the other fields.

/Torgny




>Hi Torgny,
>
>Despite of what many people believe is that partial optimization often is faster than full optimization. In you query (or all possible other queries) you'll have to define what is the most selective component in your WHERE clause.
>
>By selective I mean which constraint does return the least possible records. For example:
>
>

>dtime       contains 200000 different values
>cart_num              75000
>cprgrupp               1000
>clopnr                  500
>coperator                 7
>cmachine                  5
>deleted flag              2
>

>
>In this case its best to have an index on dtime as the index is very selective (a particular dTime value only returns a few records).
>
>Then test and time your query ten times and take the avarage. Then add an index to Cart_num. Again time your query 10 times and so on.
>
>By the time you get by the least selective components you'll see that adding an index would reduce processing time, but could also increase processing time (esspecially on a network), because rushmore has to load indexnode for that particular value from the server to the workstation. In that way the network is getting the bottleneck since the time to get the index information of this particular tag takes more time that it helps.
>
>When adding an index does not make a big performance difference, drop the index (if you don't absolutely need it for other purposes like other queries or setting order) because it might be more of a burden than it helps, esspecially in a networking environment.
>
>To make your test reliable be sure you test in the following manner:
>
>1. Test your query on a workstation while your data is on the server.
>2. Also open the table on another workstation a write some data (a replace or append) to the table. This causes the OS of your workstation to have large readbuffers on your workstation (The Opportunistic locking mechanism of the OS).
>
>To ensure the query to be the fastest you can also have a specialized index for the view, In your case:
>
>

INDEX ON cart_num + coperator + cmachine + cprgrupp + clopnr TAG combVal
>INDEX ON dtime TAG dTime

>
>Your where clasue now should look like:
>
>

WHERE cart_num + coperator + cmachine + cprgrupp + clopnr = ?cCombineValues ;
>     AND Logal100.dtime BETWEEN ?vp_FromTime AND ?vp_ToTime

>
>Now rushmore only has to process two different indexes with a high selectivity. This means:
>- Less network trafic as less index information is requested from the server
>- Less rushmore processing time as there are less indexes to process.
>
>
>
>
>Walter,