View Incident Report

Serious

Reported By: J. Pieter Vos
Reported On: 5/9/2002
For: Version 3.10 Build 1

# 1127 Using a Filter or SQL WHERE Clause with Multiple Optimized Conditions Can Cause Un-Optimized Results

When filtering one field indexed field (see below) it is fast, another indexed field also, but if I filter the two fields at the same time, it's quite slow. You tould me that it's a problem regarding positioning the first field in the filter condition. My partners connected a linux machine with a packet monitoring software and discovered that if we filter with one condition only filtered rows are transmitted via ethernet, but if we filter two fields (Both indexed) it transmits the complete table (And some are quite huge).

Fast: Traspassada=false
Fast: Anulat=false 

Slow:  (Traspassada=false) and (Anulat=false)

Comments

The problem was caused by an internal bug in the optimizer that incorrectly handled the second condition as un-optimized, causing all records to be evaluated in the second condition when the engine should have just used the index. The second indexed condition was satisfying more than 90% of the values in the table for that column, and so the engine was kicking out the condition as un-optimized. However, this type of processing should only occur for source tables of a canned query, hence the bug.

Resolution

Fixed Problem on 5/10/2002 in version 3.11 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap