Get minimum data without using MIN() function

  • I'm about to start through Itzik Ben-Gan's "T-SQL Querying".

    If I run across an explanation, I'll let you know.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline (3/25/2010)


    I'm about to start through Itzik Ben-Gan's "T-SQL Querying".

    It's not in there 🙁

    Something I read on one of the SQL Server Team blogs once upon a time. Frustrating that I can't remember enough about it to find it again. The reason might even have been locking/correctness concerns...don't know, but it is bugging me.

    Good book by the way.

  • Improved recursive solution:

    WITH [Recursive]

    AS (

    SELECT TOP (1)

    T.AssetID,

    T.Date

    FROM #T1 T

    WHERE T.Date < T.InceptionDate

    ORDER BY

    T.AssetID,

    T.InceptionDate

    UNION ALL

    SELECT SQ.AssetID, SQ.Date

    FROM (

    SELECT T.AssetID,

    T.Date,

    rn = ROW_NUMBER() OVER (ORDER BY T.AssetID, T.Date)

    FROM Recursive R

    JOIN #T1 T

    ON T.AssetID > R.AssetID

    WHERE T.Date < T.InceptionDate

    ) SQ

    WHERE SQ.rn = 1

    )

    SELECT R.AssetID, R.Date

    FROM Recursive R;

  • Paul White NZ (3/25/2010)


    What do you mean here? Having an index prevents a seek???

    I guess optimizer will not use seek for HAVING MIN(Date).What do you think ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (3/25/2010)


    Paul White NZ (3/25/2010)


    What do you mean here? Having an index prevents a seek???

    I guess optimizer will not use seek for HAVING MIN(Date).What do you think ?

    I see. It wasn't clear what you meant from your first post. The query you are referring to didn't even compile, so it's hard to say 😉

    The HAVING clause with a MIN that I posted earlier in the thread did seek on an index...so the answer is 'it depends'.

  • I used

    1) rownumber() over (partition ....order by ....)

    2) TOP & CROSS APPLY

    methods.

    But I didn't see any difference in the execution time.

    karthik

  • karthikeyan-444867 (3/26/2010)


    But I didn't see any difference in the execution time.

    Please post your COMPLETE table structure, including triggers and keys.

    All indexes too, please.

    Then post your execution plan in "sqlplan" xml format for investigation.


    N 56°04'39.16"
    E 12°55'05.25"

  • select AssetID, Date

    from t1

    where Date < InceptionDate

    group by AssetID

    Having Date = Min(Date)

  • I removed a function which was used in the WHERE clause

    say for example

    select <columns>

    from <tablename>

    where <column_name> = dbo.fngetreportfield('Estimated','Final')

    i modified the above query as

    Declare @reportfield int

    select @reportfield = dbo.fngetreportfield('Estimated','Final')

    select <columns>

    from <tablename>

    where <column_name> = @reportfield

    It is taking only 4 seconds.

    will the usage of function in the WHERE clause hit performance issue?

    karthik

  • karthikeyan-444867 (3/29/2010)


    will the usage of function in the WHERE clause hit performance issue?

    Yes.

  • Maybe off-topic but this is an alternative.

    Select top 1 myDate

    from myTable

    group by myDate desc

    Franky L.

  • Paul White NZ (3/29/2010)


    karthikeyan-444867 (3/29/2010)


    will the usage of function in the WHERE clause hit performance issue?

    Yes.

    Actually I thought that MIN() function caused the performance issue. Thats why i asked get the minimum date without using MIN() function.

    I just implemented and tested ROWNUMBER() method, TOP & CROSS APPLY methods. It is useful for me.

    Thanks for all !

    karthik

  • Paul White NZ (3/29/2010)


    karthikeyan-444867 (3/29/2010)


    will the usage of function in the WHERE clause hit performance issue?

    Yes.

    In the past 3 years I have made more money consulting helping clients with performance problems due to UDFs than any single other issue - that should say all you need to know about how bad UDFs can be (actually almost always are is a better way to put it)!! :hehe:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (3/30/2010)


    In the past 3 years I have made more money consulting helping clients with performance problems due to UDFs than any single other issue - that should say all you need to know about how bad UDFs can be (actually almost always are is a better way to put it)!! :hehe:

    Scalar T-SQL UDFs = Very often evil

    Multi-statement T-SQL UDTVFs = Very often evil

    In-line T-SQL UDTVFs = Hugely useful and cool

    CLR scalar UDFs = not as bad as the T-SQL ones...it depends

    CLR streaming TVFs = Hugely useful and cool

  • Paul White NZ (3/30/2010)


    TheSQLGuru (3/30/2010)


    In the past 3 years I have made more money consulting helping clients with performance problems due to UDFs than any single other issue - that should say all you need to know about how bad UDFs can be (actually almost always are is a better way to put it)!! :hehe:

    Scalar T-SQL UDFs = Very often evil

    Multi-statement T-SQL UDTVFs = Very often evil

    In-line T-SQL UDTVFs = Hugely useful and cool

    CLR scalar UDFs = not as bad as the T-SQL ones...it depends

    CLR streaming TVFs = Hugely useful and cool

    IIRC the two you mention as being useful and cool still carry the downside of preventing parallel query plans at a minimum.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 16 through 30 (of 30 total)

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