June 27, 2006 at 3:28 pm
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
June 27, 2006 at 3:35 pm
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.
June 27, 2006 at 3:37 pm
>>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.
June 27, 2006 at 3:38 pm
I take that back.
A clustered index physically arranges the records on the hard drive. This may improve retrieval times.
June 27, 2006 at 5:49 pm
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
June 28, 2006 at 8:12 am
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
June 28, 2006 at 8:32 am
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
Change is inevitable... Change for the better is not.
June 28, 2006 at 12:37 pm
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".
June 28, 2006 at 8:35 pm
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