Select Statement Performance

Question

Select Statement Performance

barani dharan

Grasshopper

Points: 22
More actions
August 1, 2009 at 9:01 am

#156050

hi,
In our project we use auto generated CRUD Stored procs for Database Tables.
The Read proc, just selects all the columns from the table.
But in reality, we don't require all the columns from a table.
So is it really a performance hit when we execute the Read proc to a custom proc that just selects the columns we need.
in a nut sheel will,
Select Col1, Col12, Col15 FROM [Table]
improve performance to this statement
Select Col1, Col 2, Col 3 ......., Col 25 FROM [Table]
if we need only col1,col12,col15 from [Table].

Viewing 5 posts - 1 through 5 (of 5 total)

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

In a nutshell, yes. More rows will fit on a page if you are selecting less columns, which means less memory requirement and possibly less I/O. Especially true if you have to ORDER BY, or use a query in a derived table, or build a temp table. Using few columns also makes satisfying the query by means of a covering index practical. In addition, less data being pushed to the calling application means less work. This may not be very noticeable in the context of a single transaction or report, but when multiplied by everything going on in the database at once, it is significant.

Moving a fraction of the data takes a fraction of the effort for the system.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

barani (8/1/2009)
in a nut sheel will,
Select Col1, Col12, Col15 FROM [Table]
improve performance to this statement
Select Col1, Col 2, Col 3 ......., Col 25 FROM [Table]

Maybe.

If you're got indexes that are covering for the smaller column list then you should see improved performance from the first. Also, if any of those queries are Large Object columns (text, ntext, image, varchar(max), nvarchar(max), varbinary(max),xml), then not selecting those will reduce the number of page reads necessary. Reducing the amount of data queried may improve network transport time.

Best thing is to test in your environment and see.

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

barani dharan Grasshopper Points: 22 More actions · Answer 3

thanks for ur response.

But i would like to know, how the Server would process this select statement and justify that selecting less columns would always improve performance.

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 4

Just from a practical standpoint, the less you select, the fewer bytes need to make it across the pipe, go through implicit conversions, etc, etc. If you don't need certain columns, then I wouldn't include them in the CRUD. Less is more in this case. Think about it... if you reduce the amount of data transmitted in packets across the network by 40%, the complete set of data you've requested will return quicker. If absolutely nothing else mattered, reducing the amount of data returned will allow the entire set of requested data to return more quickly.

If you Google for ways to increase performance, you'll find dozens of articles that say "return only what you need" and "keep transactions as short as possible".

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