Post reply

Find Min(), Max() value from a table

  • July 20, 2016 at 1:47 pm

    #311995

    This table has about 5M records and it takes about 300 milliseconds to execute below query. Just wondering if there are other ways do this for better performance?

    SELECT MIN(Date), MAX(Date) FROM Table1

  • July 20, 2016 at 1:48 pm

    #1891150

    Is there an index on date ?

  • July 21, 2016 at 12:54 am

    #1891217

    ocean3300 (7/20/2016)

    This table has about 5M records and it takes about 300 milliseconds to execute below query. Just wondering if there are other ways do this for better performance?

    SELECT MIN(Date), MAX(Date) FROM Table1

    Quick suggestion, post the actual execution plan, the DDL for the table including all indices and the actual query, then we'll have enough information to fully assert the problem.

    😎

  • July 21, 2016 at 8:48 am

    #1891350

    I'm still amazed at the requirement to get better performance when you are already seeing performance that is equivalent to an eye-blink.:crazy:

  • July 21, 2016 at 10:22 am

    #1891384

    No, I don't have an index on date column. However, I do have the index on a combination of three column including date column. When I retrieve the data from this table, the query always use all 3 columns in where condition.

  • July 21, 2016 at 10:23 am

    #1891385

    Will do sometime today. Thanks.

    Eirikur Eiriksson (7/21/2016)

    ocean3300 (7/20/2016)

    This table has about 5M records and it takes about 300 milliseconds to execute below query. Just wondering if there are other ways do this for better performance?

    SELECT MIN(Date), MAX(Date) FROM Table1

    Quick suggestion, post the actual execution plan, the DDL for the table including all indices and the actual query, then we'll have enough information to fully assert the problem.

    😎

  • July 21, 2016 at 10:25 am

    #1891387

    I use this line of code in a stored procedure to pull the report and the sp takes about 1.5 seconds. I am trying to improve it so that it runs 1 second or less. 500 miliseconds to go. Lol

    Smendle (7/21/2016)

    I'm still amazed at the requirement to get better performance when you are already seeing performance that is equivalent to an eye-blink.:crazy:

  • July 21, 2016 at 9:44 pm

    #1891485

    This should be faster (if your WHERE clause indeed includes first columns from the index where Date is mentioned):

    SELECT TOP 1 @MaxDate = Date FROM Table1

    WHERE {Conditions on init cols of the index}

    ORDER BY Date DESC

    SELECT TOP 1 @MinDate = Date FROM Table1

    WHERE {Conditions on init cols of the index}

    ORDER BY Date

    _____________
    Code for TallyGenerator

Viewing 8 posts - 1 through 7 (of 7 total)

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