Post reply

Derived tables faster?

  • July 10, 2007 at 9:35 am

    #117370

    Ok which one is faster?

    These queries are run off ServerB. The first one gets the value from ServerB and then updates it by adding ServerA's value to ServerB's.

    --Get the total Deposits from the Bank table in ServerB

    INSERT INTO @Totals (TotalDeposits)

    SELECT ISNULL(SUM(CashAmount),0) AS TotalDeposits

    FROM Bingo.dbo.Bank

    WHERE CurrentDate >= @StartDate1 AND CurrentDate < @EndDate1
    AND TransactionType IN (26)

    --UPDATE the total with the ServerB amount plus the amount from ServerA
    UPDATE @Totals
    SET TotalDeposits = TotalDeposits + (SELECT ISNULL(SUM(CashAmount),0) AS TotalDeposits
    FROM ServerA.Bingo.dbo.Bank
    WHERE CurrentDate >= @StartDate1 AND CurrentDate < @EndDate1
    AND TransactionType IN (26))

    Or this one with two derived tables?

    --Get the total Deposits from both Bank tables

    INSERT INTO @Totals (TotalDeposits)

    SELECT t.TotalDeposits + b.TotalDeposits

    FROM (SELECT ISNULL(SUM(CashAmount),0) AS TotalDeposits

    FROM Bingo.dbo.Bank

    WHERE CurrentDate >= @StartDate1 AND CurrentDate < @EndDate1
    AND TransactionType IN (26)) t,
    (SELECT ISNULL(SUM(CashAmount),0) AS TotalDeposits
    FROM ServerA.Bingo.dbo.Bank
    WHERE CurrentDate >= @StartDate1 AND CurrentDate < @EndDate1
    AND TransactionType IN (26)) b

    The Bank tables on ServerA and ServerB have the same columns with different data populated in them. I can give a table definition if anyone needs it...or any other info too.

    Thanks!

  • July 10, 2007 at 10:43 am

    #718354

    i am being thick  probably but I am thinking you could run the 2 queries and tell us which one is faster.

    Everything you can imagine is real.

  • July 10, 2007 at 11:35 am

    #718365

    Try running an execution plan...I'm guessing the one w/ the derived tables might be better since there is no DML operations taking place but then again it might add a table scan or two! Let us know what you find...

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • July 10, 2007 at 11:47 am

    #718375

    The one with the derived tables was faster and didn't do a table scan, it performed an Index Seek. It was only slightly faster with the derived tables on test data but I think it will be faster in production on tables with more data.

    Thanks for your help Ben.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply