Performance on full table scans

  • is there any way to improve performance on queries that SELECT * without WHERE conditions? Indexes cant help here can they?

    I've listened to the programmers reasoning for doing the full scan and the situation is compelling (i wont bore you). But lets assume for the sake of argument that this is all we can do with the query; select * from table

    SQL 2012 Standard VPS Windows 2012 Server Standard

  • If all you are doing is a table dump, there is no reason to use an index.

    And, no, there is no way that I know of to improve the performance of just reading every record consecutively.

  • >>Indexes cant help here can they?

    If selecting every row and every column, an index can still help if the SELECT statement has an ORDER BY and the index definition supports the columns being sorted on.

    [Edit] Also, even if all you do is SELECT * from a table, it should still have a clustered index. A table with no clustered index can become a large fragmented heap which will increase dsk I/O times to select all data from it.

     

  • I take that back.

    A clustered index physically arranges the records on the hard drive.  This may improve retrieval times.

  • If you do the scan often, and if the table's not too big, you could consider using DBCC PINTABLE on startup to ensure the table stays in memory. Be warned that this could have bad consequences if the table is too big, though, and is only to be done under expert guidance. And if you do scan the table frequently and it's not too big, chances are the data will be in the cache anyway...

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • Make your table as narrow as possible to be able to fit as many records as possible within one page. And use clustered index.



    Bye
    Gabor

  • Other than adding a primary key (defaults to clustered), there's not much you can do to increase the performance of a SELECT * other than pinning the table (as someone else suggested, could have some dire consequences).

    Unless you are simply dumping a table to something, I've found that "compelling reasons" are normally not so compelling if you analyze the code a bit... could you post the code?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Make sure the table is not fragmented.  You will have to add an index to check and/or adjust fragmentation.  Once the table is compacted, if necessary, you can drop the index.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Are you sure that you need all rows?

    You could do

    Select top X * from

    and use a button to show all datas if users need it

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

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