Hi folks,
I have seen something unusual. I am posting two queries with basically
different approach to the same thing. One is fast and the other is
slow.
Approach one which we are using in our web search
------------------------------------------------------
select distinct a.clone_id,b.collection_name,a.source_clone_id,a.i mage_clone_id,c.library_name,c.vector_name,
c.host_name,d.plate,d.plate_row,d.plate_column,a.c atalog_number,a.acclist,a.vendor_id,b.value,c.spec ies,e.cluster
from clone a,collection b,library c,location d, sequence e
where a.collection_id = b.collection_id
and a.library_id = c.source_lib_id
and a.clone_id = d.clone_id
and a.clone_id = e.clone_id
and b.short_collection_type='cDNA'
and b.is_public = 1
and a.active = 1
and a.no_sale = 0
and e.cluster in (select cluster from master_xref_new where
type='CLONE' and id='LD10094')

This approach is slow and the serach times out..........
Approach two-------------select distinct
a.clone_id,b.collection_name,a.source_clone_id,a.i mage_clone_id,c.library_name,c.vector_name,
c.host_name,d.plate,d.plate_row,d.plate_column,a.c atalog_number,a.acclist,a.vendor_id,b.value,c.spec ies,e.cluster
from clone a,collection b,library c,location d, sequence e
where a.collection_id = b.collection_id
and a.library_id = c.source_lib_id
and a.clone_id = d.clone_id
and a.clone_id = e.clone_id
and b.short_collection_type='cDNA'
and b.is_public = 1
and a.active = 1
and a.no_sale = 0
and e.cluster in ("Dm.19182","Dm.20293")
Basically i ran the subquery and replace the subquery with the result.
and it is fast.The first query is doing a table scan while the second
query is using the clustered index. I have run update all statistics
but still no improvement.Please help............. I cannot have
indexes on no_sale and active because they are bit datatype.

Thanks in Advance

I guess that the reason why the optimizer does this is one
of these two:
- It evaluates that the subqery
select cluster from master_xref_new
where type='CLONE' and id='LD10094'
would return many values.
- The "total density" for sequence.cluster is bad (too high),
so ASE estimates that many sequence's rows are selected
for each value of cluster.

Traceflags 302 and 310 may confirm you whether any of
those theories is correct. For the first case, accurate
statistics on master_xref_new's type and id may help.
For the second, the only idea which comes to my mind
is patching "total density" statistics value with
optdiag (input mode).

Regards,
Mariano Corral

Subhas wrote:
> I have seen something unusual. I am posting two queries with basically
> different approach to the same thing. One is fast and the other is
> slow.
> Approach one which we are using in our web search
> ------------------------------------------------------
> select distinct a.clone_id,b.collection_name,a.source_clone_id,a.i mage_clone_id,c.library_name,c.vector_name,
> c.host_name,d.plate,d.plate_row,d.plate_column,a.c atalog_number,a.acclist,a.vendor_id,b.value,c.spec ies,e.cluster
> from clone a,collection b,library c,location d, sequence e
> where a.collection_id = b.collection_id
> and a.library_id = c.source_lib_id
> and a.clone_id = d.clone_id
> and a.clone_id = e.clone_id
> and b.short_collection_type='cDNA'
> and b.is_public = 1
> and a.active = 1
> and a.no_sale = 0
> and e.cluster in (select cluster from master_xref_new where
> type='CLONE' and id='LD10094')
>
> This approach is slow and the serach times out..........
> Approach two-------------select distinct
> a.clone_id,b.collection_name,a.source_clone_id,a.i mage_clone_id,c.library_name,c.vector_name,
> c.host_name,d.plate,d.plate_row,d.plate_column,a.c atalog_number,a.acclist,a.vendor_id,b.value,c.spec ies,e.cluster
> from clone a,collection b,library c,location d, sequence e
> where a.collection_id = b.collection_id
> and a.library_id = c.source_lib_id
> and a.clone_id = d.clone_id
> and a.clone_id = e.clone_id
> and b.short_collection_type='cDNA'
> and b.is_public = 1
> and a.active = 1
> and a.no_sale = 0
> and e.cluster in ("Dm.19182","Dm.20293")
> Basically i ran the subquery and replace the subquery with the result.
> and it is fast.The first query is doing a table scan while the second
> query is using the clustered index. I have run update all statistics
> but still no improvement.Please help............. I cannot have
> indexes on no_sale and active because they are bit datatype.