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,