Home Forums SQL Server 2005 T-SQL (SS2K5) TSQL Return start/end price and start/end datetime by product name. RE: TSQL Return start/end price and start/end datetime by product name.

  • June 3, 2010 at 6:10 am

    #1175872

    I couldn't believe your performance figures and I've desided to test myself.

    After loading 140707 records into PowerItem table and adding index on PowerUpdateID (which I'm sure you have, otherwise your view will be always slow) I have run the "quirky" update and got 267 rows as results in 3 seconds. This result matches Bhuvnesh's 267 rows and one row short of Mark-101232 268 rows.

    Where this extra row comes from? It does come from situation when the same price change happens multiple times: from 1750 to 1700 then from 1700 to 1750 and then again from 1750 to 1700. Therefore small fix in "quirky" update is required in the final query:

    select Name

    ,NewPrice AS StartPrice

    ,MIN(NewDate) AS StartDate

    ,PrevPrice AS EndPrice

    ,PrevDateAS EndDate

    from #Work

    group by Name, NewPrice, PrevPrice, PrevDate

    order by Name, StartDate ASC

    I am running tests in Express edition on local pc, so my tempdb is on the same drive as main db. In real life, tempdb usually located on dedicated disk, which make additional performance benefits in relation of temp tables.

    Looking into similar perfomance figures Mark CTE version is defenetly a winner as it's more elegant.

    BTW. Version with OUTER APPLY is really slow in this case due to use of non-equal conditions and view which has no helpful indices.

    Cheers,

    Eugene

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]