|
|
Posted by cphite on 09/19/07 20:21
> I know that in order to reduce network traffic you should always select only
> the columns you require but I have a stored procedure which must **always**
> return all columns from a specific table.
Reducing traffic really isn't the main reason.
> When returning all columns from a table is there any performance gain (or
> any other reason) for using SELECT col1, col2 etc... instead of just SELECT
> * ?
Simply put, when you are writing production code you should KNOW for
certain exactly how and why everything works. You should know exactly
which columns are going to be returned; even if it happens to be all
of them.
> I can immediately think of a disadvantage which is that if you include named
> columns in your SELECT then you must update the stored procedure each time a
> column is added to the table, using SELECT * gets around this so the stored
> procedure will never need to be modified.
Yes, you will have to modify the procedure in that case. However,
while that does require that you spend time actually doing work, it is
still preferable to a situation where somebody changed a table
somewhere and your stored procedure keeps "working" despite returning
the wrong data. If all they did is add a column you might be okay -
what if they changed the order of some columns? What if they dropped
a column your application was expecting?
While there may not be a significant performance gain, there might
well be gains in terms of not getting phone calls in the middle of the
night because some application crashed, or because some job failed
when a table was changed earlier in the day.
> Would anyone care to share their thoughts on the above?
Using SELECT * in a procedure, view, or whatever is generally just
lazy and sloppy.
Navigation:
[Reply to this message]
|