I am wondering how a filter on a query component works and if there is any advantage of using this.  

Currently I have a query that I can't seem to get optimized no matter what I do with the sqll.  Brackets on, case insensitive, upper etc, all make no difference.  The query that has the problem has 3 fields that are being used, all have required indexes as well but I always get "Partially Optimized".

However, if I just use 2 of the criteria I can get an optimized result and I was wondering if it would make sense to just do the 3rd criteria on a filter on the query rather than on with SQL with partially optimized results.

Any opinion?

Cheers
David.

David


Without more information its difficult to answer but my guess would be that performance would be worse.

It would be worth posting the table structure, query and execution plan to let people comment

Roy Lambert

David

<<
Currently I have a query that I can't seem to get optimized no matter what I do with the sqll.  Brackets on, case insensitive, upper etc, all make no difference.  The query that has the problem has 3 fields that are being used, all have required indexes as well but I always get "Partially Optimized".

However, if I just use 2 of the criteria I can get an optimized result and I was wondering if it would make sense to just do the 3rd criteria on a filter on the query rather than on with SQL with partially optimized results.
>>

I would go to execute a query in an optimized way, IOW, with just 2 of criteria to get optimized result and after that I all apply filter property to redefine the result set with a fewer records. For example if the table has 1,000 records and the query (optimized) result in 200 records the filter will be applied only in the 200 records and the performance should be better (or not). The decision could be made after some tests.

Regards
Eduardo

Jose


Fully support the idea of doing tests.

I'm doubtful about performance with criteria split. If a sensitive (forgotten the term for DBISAM) result set is produced AND there is a suitable index for the filter it might be better. If a canned result set is produced then any filter will have to be brute force so I'd guess slower.

Real life test will show what happens.

Roy Lambert

That is what I was hoping would happen, if I do a query and only get 200 results then run the filter just on those 200 results to make it even smaller.  However I would prefer to just optimize the query.

If I run this query it is only partially optimised, even though all fields are indexed.  However If I only do it on StudyCompDate and Department it is optimized, but I was wondering if I could then use a filter to remove the cancelled ones?

Thanks
David.

================================================================================
SQL statement (Executed with 4.25 Build 5)
================================================================================

Select ContractNumber,Company,Contract_Date,CoyID,ContractID from Scheduling a
Inner Join Contract b ON (a.ContractId=b.ContractID) join Company c On
(a.CoyId=c.ID)  Where StudyCompDate = null and StudyStatus <>'Cancelled' and
Department <> 'PM Charge'Group by ContractNumber order by Company Asc,
ContractNumber Desc

Tables Involved
---------------

Scheduling (a) table opened shared, has 32995 rows
Contract (b) table opened shared, has 5806 rows
Company (c) table opened shared, has 1459 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

ContractNumber

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

Company ASC
ContractNumber DESC

WHERE Clause Execution
----------------------

The expression:

StudyCompDate = null and StudyStatus <> 'Cancelled' and Department <> 'PM Charge'

has been rewritten and is PARTIALLY-OPTIMIZED, covers 1992 rows or index keys,
costs 1252171 bytes, and will be applied to the Scheduling table (a) before any
joins

Join Ordering
-------------

The driver table is the Scheduling table (a)


The Scheduling table (a) is joined to the Contract table (b) with the INNER
JOIN expression:


a.ContractId = b.ContractID


The Scheduling table (a) is joined to the Company table (c) with the INNER JOIN
expression:


a.CoyId = c.ID

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

Optimized Join Ordering
-----------------------

The driver table is the Company table (c)


The Company table (c) is joined to the Scheduling table (a) with the INNER JOIN
expression:


c.ID = a.CoyId


The Scheduling table (a) is joined to the Contract table (b) with the INNER
JOIN expression:


a.ContractId = b.ContractID

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

c.ID = a.CoyId

is OPTIMIZED

The expression:

a.ContractId = b.ContractID

is OPTIMIZED

================================================================================
>>>>> 398 rows affected in 1.731 seconds
================================================================================

So have tried this is DBSys and if I just filter on the first column which is optimized and then put the other two criteria into the filter applied to the query the costs reduce to just 24557 bytes which is a lot smaller.

What I would like to know is, is the filter on the SQL applied on the server or is the filter just done locally within the client?

David

Reading that you will need an index on

Scheduling ContactId
Contract ContractID
Company ID
Scheduling CoyId

don't know the table but you'll also need these columns indexed
StudyCompDate
StudyStatus
Department
ContractNumber

The execution plan is telling you why its not optimised

<<Result set will be grouped by the following column(s) using a temporary index:
ContractNumber>>

<<Result set will be ordered by the following column(s) using a case-sensitive
temporary index:
Company ASC
ContractNumber DESC>>

I'm guessing that Company and ContractNumber are from different tables so you'll always get a canned dataset and a not fully optimised query.

Try switching to a SELECT INTO memory table and add an index for company and contactnumber after selecting - it may be faster

Roy

Hi Roy.

All of the fields you mentioned have an index on them, the only one that was different was ContractNumber which didn't have a decending index on it.  I removed the Desc operator and re-ran the query but it still comes up as partially optimized.

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

ContractNumber

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

Company ASC
ContractNumber ASC

WHERE Clause Execution
----------------------

The expression:

StudyCompDate = null and StudyStatus <> 'Cancelled' and Department <> 'PM Charge'

has been rewritten and is PARTIALLY-OPTIMIZED, covers 1949 rows or index keys,
costs 1225141 bytes, and will be applied to the Scheduling table (a) before any
joins

David

<<
StudyCompDate = null and StudyStatus <> 'Cancelled' and Department <> 'PM Charge'
>>

Always the condition without equal sign (>, >=, <, <=, <>) will result in PARTIALLY-OPTIMIZED

If possible stay away of condition like that and try to use just equal sign (=) in condition criteria.

Regards
Eduardo

First of all I would like to thank you all for taking the time to read and give your advice, it is appreciated.

I have simplified the query and removed the join for now as well as trying What Jose said and using = rather than being lazy and using <>.

Select * from scheduling

Where (StudyCompDate = null and Department = 'EM') and (StudyStatus = 'Active' or StudyStatus = 'OnHold')

costs 1225141 bytes  but is still only partially optimized.

However if I create one index that has StudyCompDate,Department and StudyStatus and re-run the query I can get it optimized,

costs 1440855 bytes

Why are the costs higher?

I think that part of the problem is I have an index on all of the fields, but it is one index per field type that I have done rather than multiple fields on one index.

How would I go about having one index for this table using StudyCompDate,Department and StudyStatus, but on another query I might only use StudyCompDate only?