Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    54

    Unanswered: performance problem urgent

    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","20293")
    Basically i ran the subquery and replaced 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 on clone but still no improvement.Please help............. I cannot have indexes on no_sale and active because they are bit datatype.

    Thanks

    subhas

  2. #2
    Join Date
    Apr 2003
    Posts
    54
    As an update I have tried to force a query for the clone table i.e the clustered index but still the first query does not return any data back.

  3. #3
    Join Date
    Apr 2003
    Posts
    54
    I meant I forced an index to the query. Sorry about that..........

  4. #4
    Join Date
    Nov 2003
    Posts
    1
    Originally posted by jaideep
    I meant I forced an index to the query. Sorry about that..........

    try to use a stored procedure for teh subquesry and exeute it from the query

  5. #5
    Join Date
    Apr 2003
    Posts
    54
    Actally I am already doing that. I think I have found out the problem. It is not the SP or the query but some erroneous data in one of the tables.

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •