Re: Multiple 'OR' parameters in sql SELECT statement

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 02/23/05

  • Next message: Jack: "Problem with display of check in checkbox retrived from database" 
    Date: Wed, 23 Feb 2005 15:00:12 -0500

    Chris Hohmann wrote:
    >>
    >> If you are passing a lot of values into that IN construct, you may
    >> see a
    >> performance boost from inserting them into a temp table and using an
    > inner
    >> join to that temp table, as opposed to using IN, which forces a scan
    >> to be
    >> used.
    >>
    >
    > As an interesting side note, SQL Server 6.5 did this automatically.
    > Here's a
    > thread:
    > http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/712dd4f655ef9b6e/3563e4708a6dff31
    >
    > There's no mention of this in SQL7.0+, so either it was removed from
    > the
    > engine or simply classified as a low level operation and omitted from
    > the
    > documentation.

    Wow! That IS interesting. I'm going to do a little digging to see if I can
    come up with some more info on this.

    Bob 

    -- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
  • Next message: Jack: "Problem with display of check in checkbox retrived from database"

    Relevant Pages

    • Re: Multiple OR parameters in sql SELECT statement
      ... >> Sorry Luis, I didn't mean to say that there was anything negative ... performance boost from inserting them into a temp table and using an inner ... This email account is my spam trap so I ...
      (microsoft.public.inetserver.asp.db)
    • Re: faster way to write tables?
      ... What I've found to be the fastest way of inserting hundreds/thousands of records into a table which might already contain millions of rows, using ADO in Access/SQLserver is to use RecordSets and insert via a temporary table ... Create a new temp table which exactly matches the structure of the target table... ... For inserting millions of rows (into an Access database from SQLserver) I still haven't found anything which performs as well as... ... According to a random database creation log that I'm looking at, Access can insert 3 million rows in just over 70 seconds ...
      (borland.public.delphi.database.ado)
    • Re: Row Order
      ... but that doesn't guarantee they will be located in the ... IDENTITY property, then can I assume that the IDENTITY property will ... Remember this is a TEMP table I'm ... >> I'm inserting rows into a temp table via a SELECT statement that has an ...
      (microsoft.public.sqlserver.programming)
    • Help with a list variable
      ... then looping through the list and inserting the values into ... the temp table, then selecting all the records from teh temp table. ... DECLARE @startingPosition int ...
      (comp.databases.ms-sqlserver)
    • Re: Row Order
      ... Server will insert the rows wherever it can. ... the rows back out of the table, SQL Server can access them in whatever order ... > I'm inserting rows into a temp table via a SELECT statement that has an ... > ORDER BY clause because I need for the rows to exist in the temp table in ...
      (microsoft.public.sqlserver.programming)