DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Simple, but very slow query using IN (<list>)

  1. #1
    Tim Guest

    Simple, but very slow query using IN (<list>)

    Thanks for looking,

    I have a simple query that is made up of two parts. One part selects a list
    of indexes from a linked server (Oracle 8i), and feeds it into a very simple
    select query on the SQL Server (7). The problem is that each query when run
    individually returns results in only a second or two, but the query when run
    together, takes 4-6 minutes.

    I'm wondering if the "..IN (..." syntax that I'm using is the problem.
    Here's the query:

    SELECT
    LOCAL.*
    FROM
    <Local Database>.dbo.<local table> as LOCAL
    WHERE
    LOCAL.<field>
    IN
    (SELECT DISTINCT
    REMOTE.<key field>
    FROM
    <remote database>..<Remote catalog>.<remote table> as REMOTE)

    The key field in the local database is indexed, but this query takes what I
    think is an inordinate amount of time.
    I have tried using the IN (... syntax with some fixed values in the query as
    well and it is still very slow.

    Is there something about using the IN (<list>) that causes significant
    processing overhead?


    TIA,
    Tim Cornwell









  2. #2
    davinci Guest

    Re: Simple, but very slow query using IN (<list>)


    Tim,

    Try adding the following where clause to your IN subselect:

    WHERE remote.field = local.field

    If you don't do this you are telling the subquery to return back the entire
    list of remote.fields for comparison against the value you are concerned
    with. Tell it to return only the one value if it's got it. For that matter
    you could turn this into an "exists" type query.

    "Tim" <TC225@.el.closeo.cornwell.edu> wrote:
    >Thanks for looking,
    >
    >I have a simple query that is made up of two parts. One part selects a

    list
    >of indexes from a linked server (Oracle 8i), and feeds it into a very simple
    >select query on the SQL Server (7). The problem is that each query when

    run
    >individually returns results in only a second or two, but the query when

    run
    >together, takes 4-6 minutes.
    >
    >I'm wondering if the "..IN (..." syntax that I'm using is the problem.
    >Here's the query:
    >
    >SELECT
    > LOCAL.*
    >FROM
    > <Local Database>.dbo.<local table> as LOCAL
    >WHERE
    > LOCAL.<field>
    >IN
    > (SELECT DISTINCT
    > REMOTE.<key field>
    > FROM
    > <remote database>..<Remote catalog>.<remote table> as REMOTE)
    >
    >The key field in the local database is indexed, but this query takes what

    I
    >think is an inordinate amount of time.
    >I have tried using the IN (... syntax with some fixed values in the query

    as
    >well and it is still very slow.
    >
    >Is there something about using the IN (<list>) that causes significant
    >processing overhead?
    >
    >
    >TIA,
    >Tim Cornwell
    >
    >
    >
    >
    >
    >
    >
    >



  3. #3
    Steve Jackson Guest

    Re: Simple, but very slow query using IN (<list>)

    I suspect that for EVERY row in the local database (first part of
    SELECT) it is doing a query on the remote database that sweeps the
    ENTIRE remote database - so the remote query that takes a second or
    two is multiplied by the number of rows in the local database,
    resulting in your 4-6 minute time.

    If this is embedded in some program, I wonder if you could first do a
    query against the remote database, and create a temporary table on
    your local DB - it looks like it's only one field per row you need on
    the remote DB. Then just to a simple join on local db and local temp
    db. I'll give it more thought, or perhaps others have a more elegant
    solution.

    Steve Jackson

    On Wed, 13 Feb 2002 11:17:59 -0500, "Tim"
    <TC225@.el.closeo.cornwell.edu> wrote:

    >Thanks for looking,
    >
    >I have a simple query that is made up of two parts. One part selects a list
    >of indexes from a linked server (Oracle 8i), and feeds it into a very simple
    >select query on the SQL Server (7). The problem is that each query when run
    >individually returns results in only a second or two, but the query when run
    >together, takes 4-6 minutes.
    >
    >I'm wondering if the "..IN (..." syntax that I'm using is the problem.
    >Here's the query:
    >
    >SELECT
    > LOCAL.*
    >FROM
    > <Local Database>.dbo.<local table> as LOCAL
    >WHERE
    > LOCAL.<field>
    >IN
    > (SELECT DISTINCT
    > REMOTE.<key field>
    > FROM
    > <remote database>..<Remote catalog>.<remote table> as REMOTE)
    >
    >The key field in the local database is indexed, but this query takes what I
    >think is an inordinate amount of time.
    >I have tried using the IN (... syntax with some fixed values in the query as
    >well and it is still very slow.
    >
    >Is there something about using the IN (<list>) that causes significant
    >processing overhead?
    >
    >
    >TIA,
    >Tim Cornwell
    >
    >
    >
    >
    >
    >
    >
    >



    Steve Jackson, Enterprise Section Leader



  4. #4
    hylton Guest

    Re: Simple, but very slow query using IN (<list>)


    Stephen is right...the solution you are using at this point is working itself
    to death...linked servers can be handy, but many times (and I'm 99% sure
    this is true w/ an Oracle linked server) a linked server queried the way
    you are doing it doesn't make use of the keys/indexes on the linked server...I
    think this is pretty much the case for all linked servers.

    You DEFINITELY want to run an 'openquery' statement to get the data back
    from the linked server first, then deal w/ it on SQL Server...i.e. limit
    the rows returned from the linked server as much as possible...OPENQUERY
    is a pass-thru query...it doesn't get processed by SQL Server...whereas your
    query does...I suspect if you look on Oracle, a table scan is getting performed...and
    likely for every row in your SQL Server table...OPENQUERY passes the SQL
    statement thru to the linked server and lets the linked server do the processing
    natively...

    This should significantly improve your performance...

    Somebody correct me if I'm wrong here...but this is my understanding of linked
    servers...

    Chris


    stephen.t.jackson@lmco.com (Steve Jackson) wrote:
    >I suspect that for EVERY row in the local database (first part of
    >SELECT) it is doing a query on the remote database that sweeps the
    >ENTIRE remote database - so the remote query that takes a second or
    >two is multiplied by the number of rows in the local database,
    >resulting in your 4-6 minute time.
    >
    >If this is embedded in some program, I wonder if you could first do a
    >query against the remote database, and create a temporary table on
    >your local DB - it looks like it's only one field per row you need on
    >the remote DB. Then just to a simple join on local db and local temp
    >db. I'll give it more thought, or perhaps others have a more elegant
    >solution.
    >
    >Steve Jackson
    >
    >On Wed, 13 Feb 2002 11:17:59 -0500, "Tim"
    ><TC225@.el.closeo.cornwell.edu> wrote:
    >
    >>Thanks for looking,
    >>
    >>I have a simple query that is made up of two parts. One part selects a

    list
    >>of indexes from a linked server (Oracle 8i), and feeds it into a very simple
    >>select query on the SQL Server (7). The problem is that each query when

    run
    >>individually returns results in only a second or two, but the query when

    run
    >>together, takes 4-6 minutes.
    >>
    >>I'm wondering if the "..IN (..." syntax that I'm using is the problem.
    >>Here's the query:
    >>
    >>SELECT
    >> LOCAL.*
    >>FROM
    >> <Local Database>.dbo.<local table> as LOCAL
    >>WHERE
    >> LOCAL.<field>
    >>IN
    >> (SELECT DISTINCT
    >> REMOTE.<key field>
    >> FROM
    >> <remote database>..<Remote catalog>.<remote table> as REMOTE)
    >>
    >>The key field in the local database is indexed, but this query takes what

    I
    >>think is an inordinate amount of time.
    >>I have tried using the IN (... syntax with some fixed values in the query

    as
    >>well and it is still very slow.
    >>
    >>Is there something about using the IN (<list>) that causes significant
    >>processing overhead?
    >>
    >>
    >>TIA,
    >>Tim Cornwell
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>

    >
    >
    >Steve Jackson, Enterprise Section Leader
    >
    >



  5. #5
    Tim Guest

    Re: Simple, but very slow query using IN (<list>)

    hylton,

    OPENQUERY works very well - much faster.

    Thanks
    Tim C.



    "hylton" <cchylton@hotmail.com> wrote in message
    news:3c6de02a$1@10.1.10.29...
    >
    > Stephen is right...the solution you are using at this point is working

    itself
    > to death...linked servers can be handy, but many times (and I'm 99% sure
    > this is true w/ an Oracle linked server) a linked server queried the way
    > you are doing it doesn't make use of the keys/indexes on the linked

    server...I
    > think this is pretty much the case for all linked servers.
    >
    > You DEFINITELY want to run an 'openquery' statement to get the data back
    > from the linked server first, then deal w/ it on SQL Server...i.e. limit
    > the rows returned from the linked server as much as possible...OPENQUERY
    > is a pass-thru query...it doesn't get processed by SQL Server...whereas

    your
    > query does...I suspect if you look on Oracle, a table scan is getting

    performed...and
    > likely for every row in your SQL Server table...OPENQUERY passes the SQL
    > statement thru to the linked server and lets the linked server do the

    processing
    > natively...
    >
    > This should significantly improve your performance...
    >
    > Somebody correct me if I'm wrong here...but this is my understanding of

    linked
    > servers...
    >
    > Chris
    >
    >
    > stephen.t.jackson@lmco.com (Steve Jackson) wrote:
    > >I suspect that for EVERY row in the local database (first part of
    > >SELECT) it is doing a query on the remote database that sweeps the
    > >ENTIRE remote database - so the remote query that takes a second or
    > >two is multiplied by the number of rows in the local database,
    > >resulting in your 4-6 minute time.
    > >
    > >If this is embedded in some program, I wonder if you could first do a
    > >query against the remote database, and create a temporary table on
    > >your local DB - it looks like it's only one field per row you need on
    > >the remote DB. Then just to a simple join on local db and local temp
    > >db. I'll give it more thought, or perhaps others have a more elegant
    > >solution.
    > >
    > >Steve Jackson
    > >
    > >On Wed, 13 Feb 2002 11:17:59 -0500, "Tim"
    > ><TC225@.el.closeo.cornwell.edu> wrote:
    > >
    > >>Thanks for looking,
    > >>
    > >>I have a simple query that is made up of two parts. One part selects a

    > list
    > >>of indexes from a linked server (Oracle 8i), and feeds it into a very

    simple
    > >>select query on the SQL Server (7). The problem is that each query when

    > run
    > >>individually returns results in only a second or two, but the query when

    > run
    > >>together, takes 4-6 minutes.
    > >>
    > >>I'm wondering if the "..IN (..." syntax that I'm using is the problem.
    > >>Here's the query:
    > >>
    > >>SELECT
    > >> LOCAL.*
    > >>FROM
    > >> <Local Database>.dbo.<local table> as LOCAL
    > >>WHERE
    > >> LOCAL.<field>
    > >>IN
    > >> (SELECT DISTINCT
    > >> REMOTE.<key field>
    > >> FROM
    > >> <remote database>..<Remote catalog>.<remote table> as REMOTE)
    > >>
    > >>The key field in the local database is indexed, but this query takes

    what
    > I
    > >>think is an inordinate amount of time.
    > >>I have tried using the IN (... syntax with some fixed values in the

    query
    > as
    > >>well and it is still very slow.
    > >>
    > >>Is there something about using the IN (<list>) that causes significant
    > >>processing overhead?
    > >>
    > >>
    > >>TIA,
    > >>Tim Cornwell
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>

    > >
    > >
    > >Steve Jackson, Enterprise Section Leader
    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center