February 15, 2012 at 1:47 am
Apart from using NOLOCK
February 15, 2012 at 3:10 am
First of all - NOLOCK should not be considered as a 'go faster' switch. That's a misconception that abounds the forums for no good reason - http://itknowledgeexchange.techtarget.com/sql-server/what-does-with-nolock-actually-mean/. It avoids locks on data and may retrieve the data using a different process ('NOLOCK and faster query processing' - SQL Server MVP Deep Dives - and check the warning at the end of that section) but the data may not be as accurate as required.
'SELECT *' against one table and without any JOIN, WHERE clause or suchlike (which I'm assuming is your full question) is telling SQL Server to retrieve every row from the specified table, so it will read every row (using a Table Scan). There is very little that can be done via a query to make such a thing any faster.
Structurally there are some things that can be done. One example is spreading the table across multiple files in a file group, in which case SQL will attempt to spread the reading across the files evenly (See Read Ahead - Microsoft SQL Server 2008 Internals for the correct description). But make sure you have a full understanding of it before you submit that as an answer to anyone.
February 15, 2012 at 3:12 am
Nolock is not an optimisation technique.
SELECT * FROM table means get me the entire table. Short of faster hardware, there's no way to speed that up. For that to be slow, the table will likely be a reasonable size, and the question then is why the entire table?
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
February 15, 2012 at 3:59 am
Thank you guys.
If there had been a where clause i could have increased the performance using indexes and statistics.
Now i am contended to know that its not possible to reduce the time 🙂
February 15, 2012 at 4:02 am
I'll try to group them into files and thank you guys for the knowledge about nolock.
I had the misconception about them.
February 15, 2012 at 4:09 am
Wicked (2/15/2012)
I'll try to group them into files
That is an inordinate amount of work in order to retrieve all rows back quickly. To repeat Gail's question - why do you need every row returned? I assume we're talking about a large number of rows, so the impact on the system could be quite noticable.
February 15, 2012 at 4:33 am
Wicked (2/15/2012)
I'll try to group them into files
I wouldn't recommend that unless you intend to spend days at the least designing the file layout based on data usage, query design and more. Again, it's not a magic bullet for performance
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply