just a real quick question

Posted on 2006-04-26
Last Modified: 2008-03-03
hi there.  this isn't critical at all, i'm actually just looking for other opinions as to how the following could be accomplished most appropriately.  i've got to run the code below on demand in production via procedure.  there are upwards of 88M records in prod, growing daily, and the archival does not run on this particular repository.  so, it's only getting bigger.  (at least for now, as the db design is not going to change)   So, my concern is simply design.  See the WITH (NOLOCK) and MAXDOP, I'm just trying to ensure I am not hindering other active connections when this thing is run.  you know...i don't want to invite any concurrency problems.  Any thoughts?

SELECT Symbol,
'Quantity' = CASE
    WHEN BS = 'S' THEN quantity * (-1)
    WHEN BS = 'B' THEN quantity END, [Time]
WHERE [Time] BETWEEN @start AND @stop
AND EndPoint IN (...........great big IN list.............)
Question by:dbaSQL
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible.
    LVL 21

    Accepted Solution

    maybe you create a temp table with your "great big IN list" as its rows, and join instead of doing IN?

    Index on TIME?

    Just some random thoughts
    LVL 11

    Assisted Solution

    by:Ved Prakash Agrawal
    Hi you can place all in values into temp variable table and then used join to get same result as you can get in IN join .
    In is slow when data is more so always tried to avoid that...
    LVL 17

    Author Comment

    anees, are you referring to MAXDOP?
    kevin/ved, yes there is an idx on time.  and the tmp table, do you feel there's a sufficient gain there?
    LVL 50

    Assisted Solution

    also consider
    Select symbol,
    quantity * x as quantity,[time]
    from ...
     Inner Join (select 'B' as BSType, 1 as x union Select 'S', -1) as Qmod
        on Qmod.BSType = BS
     Inner Join ( endpoint table ) as Ept
       on Ept.Endpt = Endpoint
    where [time] between @start and @stop

    order by [Time]

     Have a covering index of
      Endpoint,[Time],symbol,BS,Quantity on the table..

    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    > anees, are you referring to MAXDOP?

    I  am referring to NOLOCK option,
    LVL 21

    Expert Comment

    Yes....a join will typically outperform an IN list
    LVL 17

    Author Comment

    excellent.  i appreciate all of your feedback.  i will split the points

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now