Performance on full table scans

Question

Post reply

Performance on full table scans

Johnny B

SSCrazy

Points: 2965
More actions
June 27, 2006 at 3:28 pm

#114565

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

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

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

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 1

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.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

>>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.

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 3

I take that back.

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

stax68 SSChampion Points: 11711 More actions · Answer 4

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

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 6

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)

ScottPletcher SSC Guru Points: 101122 More actions · Answer 7

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".

Claudia-343424 Ten Centuries Points: 1080 More actions · Answer 8

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