Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL question

Discussion in 'Information Technology' started by Ron Hinds, Jul 28, 2006.

Tags:
  1. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    I have the following SQL statement. It runs *very* slow, we're talking 5
    minutes or more. TblInventory has a little over 21,000 rows and
    tblVendorQuote has over 25,000 rows. I'm hoping someone has a suggestion on
    how to improve the performance of this statement.

    strSQL = "INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID, quoPrice,
    quoCurrent, quoVendorPartnumber )" _
    & " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2, True AS
    Expr3, TblInventory.invItemID" _
    & " FROM TblInventory" _
    & " WHERE TblInventory.invItemID NOT IN (SELECT quoPartNumber FROM
    tblVendorQuote WHERE quoVendorID='1')"
    db.Execute strSQL
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005
    Question About GGS Questions and Answers Aug 28, 2016
    Marriage And Discrimination Related Questions Blogs Mar 21, 2016
    Sikhi Questioning Faith Sikh Sikhi Sikhism Mar 3, 2016
    Entering into Sikhism: Questions on Reht Maryada. Blogs Oct 22, 2015

  3. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    The NOT IN clause is going to be slow. Better to use a LEFT JOIN, if you can
    make it do what you need. I've tried to rewrite the SQL below, as I believe it
    should be written. Try grabbing the SELECT piece of it and pasting it into the
    QBE. If it returns the data you want, test it for performance against the other
    SQL with the NOT IN () clause.

    INSERT INTO tblVendorQuote (
    quoPartnumber
    , quoVendorID
    , quoPrice
    , quoCurrent
    , quoVendorPartnumber )

    SELECT TblInventory.invItemID, '1', 0, True, TblInventory.invItemID
    FROM TblInventory LEFT JOIN tblVendorQuote
    ON tblVendorQuote.quoPartNumber = TblInventory.invItemID
    WHERE tblVendorQuote.quoPartNumber IS NULL
    AND tblVendorQuote.quoVendorID='1'

    --
    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    "Ron Hinds" <__ron__dontspamme@wedontlikespam_garageiq.com> wrote ...
    >I have the following SQL statement. It runs *very* slow, we're talking 5
    > minutes or more. TblInventory has a little over 21,000 rows and
    > tblVendorQuote has over 25,000 rows. I'm hoping someone has a suggestion on
    > how to improve the performance of this statement.
    >
    > strSQL = "INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID, quoPrice,
    > quoCurrent, quoVendorPartnumber )" _
    > & " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2, True AS
    > Expr3, TblInventory.invItemID" _
    > & " FROM TblInventory" _
    > & " WHERE TblInventory.invItemID NOT IN (SELECT quoPartNumber FROM
    > tblVendorQuote WHERE quoVendorID='1')"
    > db.Execute strSQL
    >
    >
     
  4. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    Thanks Danny. Unfortunately, that statement won't work. That was what was
    there originally, but if the quoPartNumber IS NULL, then so also will be the
    VendorID (the two of them form a unique primary ID). I did however solve the
    speed issue by creating a Query (qryVendorQuote) that selects quoPartNumber
    and quoVendorID WHERE quoVendorID='1'. I then use that in a similar
    statement to the one you proposed and there is no speed issue:

    INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID, quoPrice,
    quoCurrent, quoVendorPartnumber )
    SELECT TblInventory.invItemID, '1' AS VendorID, TblInventory.COST, True AS
    Current, TblInventory.invItemID
    FROM TblInventory LEFT JOIN qryVendorQuote1 ON TblInventory.invItemID =
    qryVendorQuote1.quoPartnumber
    WHERE qryVendorQuote1.quoPartnumber Is Null;

    Again thank you for the effort!

    "Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in message
    news:OuoSswFpGHA.4424@TK2MSFTNGP05.phx.gbl...
    > The NOT IN clause is going to be slow. Better to use a LEFT JOIN, if you

    can
    > make it do what you need. I've tried to rewrite the SQL below, as I

    believe it
    > should be written. Try grabbing the SELECT piece of it and pasting it

    into the
    > QBE. If it returns the data you want, test it for performance against the

    other
    > SQL with the NOT IN () clause.
    >
    > INSERT INTO tblVendorQuote (
    > quoPartnumber
    > , quoVendorID
    > , quoPrice
    > , quoCurrent
    > , quoVendorPartnumber )
    >
    > SELECT TblInventory.invItemID, '1', 0, True, TblInventory.invItemID
    > FROM TblInventory LEFT JOIN tblVendorQuote
    > ON tblVendorQuote.quoPartNumber = TblInventory.invItemID
    > WHERE tblVendorQuote.quoPartNumber IS NULL
    > AND tblVendorQuote.quoVendorID='1'
    >
    > --
    > Danny J. Lesandrini
    > dlesandrini@hotmail.com
    > http://amazecreations.com/datafast
    >
    >
    > "Ron Hinds" <__ron__dontspamme@wedontlikespam_garageiq.com> wrote ...
    > >I have the following SQL statement. It runs *very* slow, we're talking 5
    > > minutes or more. TblInventory has a little over 21,000 rows and
    > > tblVendorQuote has over 25,000 rows. I'm hoping someone has a suggestion

    on
    > > how to improve the performance of this statement.
    > >
    > > strSQL = "INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID,

    quoPrice,
    > > quoCurrent, quoVendorPartnumber )" _
    > > & " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2, True AS
    > > Expr3, TblInventory.invItemID" _
    > > & " FROM TblInventory" _
    > > & " WHERE TblInventory.invItemID NOT IN (SELECT quoPartNumber FROM
    > > tblVendorQuote WHERE quoVendorID='1')"
    > > db.Execute strSQL
    > >
    > >

    >
    >
     
  5. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    TblInventory.invItemID most likely should be indexed if it is not now.

    As indexes slow down inserts, try removing indexes from tblVendorQuote. Keep
    primery keys and unique constraints of course. The tblVendorQuote.quoVendorID
    field may or may not benefit from indexing. One way to tell if an index is
    slowing down an insert is to just run the SELECT statement and see how long
    it takes.

    If a NOT IN statement is slow, often changing it to a NOT EXISTS statement
    speeds things up. See if the following runs quicker than the original select
    statement.

    SELECT TblInventory.invItemID,
    '1' AS Expr1,
    0 AS Expr2,
    True AS Expr3,
    TblInventory.invItemID
    FROM TblInventory
    WHERE NOT EXISTS (SELECT *
    FROM tblVendorQuote as VQ
    WHERE TblInventory.invItemID = VQ.quoPartNumber
    AND VQ.quoVendorID='1') ;


    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "Ron Hinds" wrote:

    > I have the following SQL statement. It runs *very* slow, we're talking 5
    > minutes or more. TblInventory has a little over 21,000 rows and
    > tblVendorQuote has over 25,000 rows. I'm hoping someone has a suggestion on
    > how to improve the performance of this statement.
    >
    > strSQL = "INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID, quoPrice,
    > quoCurrent, quoVendorPartnumber )" _
    > & " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2, True AS
    > Expr3, TblInventory.invItemID" _
    > & " FROM TblInventory" _
    > & " WHERE TblInventory.invItemID NOT IN (SELECT quoPartNumber FROM
    > tblVendorQuote WHERE quoVendorID='1')"
    > db.Execute strSQL
    >
    >
    >
     
  6. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    You're right, Jerry - NOT EXISTS runs much faster than NOT IN! Interesting -
    thank you!

    "Jerry Whittle" <JerryWhittle@discussions.microsoft.com> wrote in message
    news:2235D55D-6BB6-4F0E-BC88-28DE6BE8274A@microsoft.com...
    > TblInventory.invItemID most likely should be indexed if it is not now.
    >
    > As indexes slow down inserts, try removing indexes from tblVendorQuote.

    Keep
    > primery keys and unique constraints of course. The

    tblVendorQuote.quoVendorID
    > field may or may not benefit from indexing. One way to tell if an index is
    > slowing down an insert is to just run the SELECT statement and see how

    long
    > it takes.
    >
    > If a NOT IN statement is slow, often changing it to a NOT EXISTS statement
    > speeds things up. See if the following runs quicker than the original

    select
    > statement.
    >
    > SELECT TblInventory.invItemID,
    > '1' AS Expr1,
    > 0 AS Expr2,
    > True AS Expr3,
    > TblInventory.invItemID
    > FROM TblInventory
    > WHERE NOT EXISTS (SELECT *
    > FROM tblVendorQuote as VQ
    > WHERE TblInventory.invItemID = VQ.quoPartNumber
    > AND VQ.quoVendorID='1') ;
    >
    >
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "Ron Hinds" wrote:
    >
    > > I have the following SQL statement. It runs *very* slow, we're talking 5
    > > minutes or more. TblInventory has a little over 21,000 rows and
    > > tblVendorQuote has over 25,000 rows. I'm hoping someone has a suggestion

    on
    > > how to improve the performance of this statement.
    > >
    > > strSQL = "INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID,

    quoPrice,
    > > quoCurrent, quoVendorPartnumber )" _
    > > & " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2, True AS
    > > Expr3, TblInventory.invItemID" _
    > > & " FROM TblInventory" _
    > > & " WHERE TblInventory.invItemID NOT IN (SELECT quoPartNumber FROM
    > > tblVendorQuote WHERE quoVendorID='1')"
    > > db.Execute strSQL
    > >
    > >
    > >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 10 Jul 2006 12:30:35 -0700, "Ron Hinds"
    <__ron__dontspamme@wedontlikespam_garageiq.com> wrote:

    >I have the following SQL statement. It runs *very* slow, we're talking 5
    >minutes or more. TblInventory has a little over 21,000 rows and
    >tblVendorQuote has over 25,000 rows. I'm hoping someone has a suggestion on
    >how to improve the performance of this statement.
    >
    >strSQL = "INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID, quoPrice,
    >quoCurrent, quoVendorPartnumber )" _
    > & " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2, True AS
    >Expr3, TblInventory.invItemID" _
    > & " FROM TblInventory" _
    > & " WHERE TblInventory.invItemID NOT IN (SELECT quoPartNumber FROM
    >tblVendorQuote WHERE quoVendorID='1')"
    >db.Execute strSQL
    >


    The NOT IN clause can be very poky - apparently the optimizer doesn't
    handle it all that well. You might want to try a "frustrated outer
    join". Make sure that quoPartNumber and invItemID are indexed
    appropriately in their respective tables, and try

    strSQL = "INSERT INTO tblVendorQuote" _
    & " (quoPartnumber, quoVendorID, quoPrice, quoCurrent," _
    & " quoVendorPartnumber)" _
    & " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2," _
    & " True AS Expr3, TblInventory.invItemID" _
    & " FROM TblInventory" _
    & " LEFT JOIN tblVendorQuote " _
    & " ON tblVendorQuote.quoPartNumber = tblInventory.invItemID" _
    & " WHERE TblVendorQuote.quoPartNumber IS NULL" _
    & " AND quoVendorID='1'"
    db.Execute strSQL

    I'm not sure where the quoVendorID = '1' fits - if it's a criterion on
    a field in tblVendorQuote you may need to use a left join to a
    Subquery rather than directly to the quote table.

    John W. Vinson[MVP]
     

Share This Page