Query Execution Performance

  • If a Select Statement takes more than 5seconds to Execute.

    Steps needs to be followed to reduce the time interval or execution time

    To execute the statement in 2 seconds

    Simple Select to a Single Table not multiple tables.

    Select * from Table1

  • I'm not sure I follow you completely.

    As for your example query - select * from table - there's no way to optimise that. It's asking for all rows of the table, hence SQL has to scan the entire table and return all the rows, all the columns. Are all rows really necessary.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Is their any Steps to be followed for changing the Server Settings

    Or any changes need to be made to the table.

  • If you're requesting all the rows, all the columns from a table there is no practical way to optimise the query. It may take some time if the table is large. How many rows are in that table? Why are you selecting every single column, every single row from that table? Do you really need all of that?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Based on the very limited information provided so far: reduce the number of rows in the table or in your select statement (the former by archiving data and the latter by using a where clause with appropriate covering index).

    If none of the above is an option please provide a more detailed information (how many rows, how many columns, total size of the table, is the time measure using SSMS on the SQL Server or is there a network in between, maybe even a frontend that might be the cause for the delay).



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 1) Select fewer columns, and use a well tuned covering index

    2) Select fewer rows, and use a well tuned (covering) index

    3) Make sure that table has a clustered index, and rebuild the index

    4) Defrag the storage at the OS level and the SAN level

    5) Tune the fillfactor of the clustered index to make sure the table takes up as few pages as possible when this query is likely to run

    6) Revisit the business requirement

    7) Buy a new set of disks, short-stroke them, and use them solely for this table

    8) Buy a new set of flash SSD's and put this table on them

    9) Buy more RAM so this table is likely to stay in the buffers

    10) Buy a new set of DRAM or SRAM SSD's and put this table on them

    11) Buy a faster machine, faster IO, faster network, faster storage, faster everything.

  • Use Microsoft Time and you will get 2 sec or whatever you like without much of hassle 😀

    _____________________________________________
    "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]

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

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