dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
892
share rss forum feed


gorrillamcd
Hangin' Out

join:2010-04-01
mexico

Is a query for a single column any faster than others

General database performance question here. Is a query for a single column( SELECT 'table.column' FROM 'table' ) any faster than selecting the entire table ( SELECT '*' FROM 'table')? Of course, for small databases, it would be negligible in most cases. I'm just curious.
--
I'm an IT technician with a lot to learn, taught by viewers like you!



Steve
I know your IP address
Consultant
join:2001-03-10
Foothill Ranch, CA
kudos:5

It's a poor idea to ask for data you don't really need, as it wastes resources and will confuse the next guy down the road who reads your code and wonders "Why are they asking for * when all they need is one column? There must be something I'm missing, so I'll dig in a while".



drew
Automatic
Premium
join:2002-07-10
Port Orchard, WA
kudos:6

It absolutely is a bad idea to write it explicitly like that when you know. That being said, ORMs do exactly that in a lot of cases...

I'd also argue about the whole "premature optimization.. blah blah blah" mentality, but the bottom line is that from a pure technical standpoint, yes it's slower. Everything else depends on who/what/when/where/why/etc.
--
flickr | 'Cause I've been waiting, all my life just waiting
For you to shine, shine your light on me


dave
Premium,MVM
join:2000-05-04
not in ohio
kudos:8
Reviews:
·Verizon FiOS
reply to Steve

said by Steve:

.... will confuse the next guy down the road ...

I prefer to regard my programming practices as "job creation".

said by david parnas :
One bad programmer can easily create two new jobs a year.


DC DSL
There's a reason I'm Command.
Premium
join:2000-07-30
Washington, DC
kudos:2
reply to gorrillamcd

It's definitely NOT a Best Practice for a production app, regardless of the size of the database. If this is a web application, you must remember that you are doubling the amount of setup/teardown, plus moving the data over the wire to the requesting web server, then at least temporarily holding it in server memory until your process disposes of it. Exceedingly inefficient and a hindrance to good scalability.

Even better would be to use row returning stored procedures (or whatever the equivalent if you aren't using SQL Server). That is even less behind-the-curtain work.
--
"Dance like the photo isn't being tagged; love like you've never been unfriended; and tweet like nobody is following."



Steve
I know your IP address
Consultant
join:2001-03-10
Foothill Ranch, CA
kudos:5

said by DC DSL:

Even better would be to use row returning stored procedures (or whatever the equivalent if you aren't using SQL Server). That is even less behind-the-curtain work.

Whoa: it's more behind-the-curtain work, not less, because "behind-the-curtain" means hidden-from-view, not server-side.

Stored procedures are great when they provide useful isolation of business rules, but if you just need a simple SELECT, it's not at all obvious that creating a stored procedure increases performance, readability, or maintainability.

Steve
--
Stephen J. Friedl | Unix Wizard | Security Consultant | Orange County, California USA | my web site


DC DSL
There's a reason I'm Command.
Premium
join:2000-07-30
Washington, DC
kudos:2

1 edit

Behind the curtain meaning server-side processing. Not programmer's convenience.

Stored procedures execute faster than "live" queries because they are precompiled...eliminating the interpretation step. And if there is any user input involved, parameterized SPs make injection attacks all but impossible. While this may not be readily apparent to someone doing a simple "SELECT myfield FROM thetable" it will be apparent to their dba and admins if it's executed frequently enough.

Just because something will do the job doesn't mean we should recommend it as gospel. If we're dishing out advice, we should try to make sure it follows best practice guidelines. I can't tell you how many database compromises and other fusterclucks I have come across that could have been prevented by doing just a bit more work by the programmers.

--
"Dance like the photo isn't being tagged; love like you've never been unfriended; and tweet like nobody is following."



drew
Automatic
Premium
join:2002-07-10
Port Orchard, WA
kudos:6

I've been reading a lot of chatter recently where people are running tests with regards to stored procs vs. normal queries and the results are surprising. The performance gain is, a lot of the time, either non-existent OR so marginal that it's irrelevant.
--
flickr | 'Cause I've been waiting, all my life just waiting
For you to shine, shine your light on me



gorrillamcd
Hangin' Out

join:2010-04-01
mexico
reply to gorrillamcd

Thanks for the ideas. I'd figured that it would be slower, but since I'm just about 1 year into learning web development, I wasn't exactly sure why. I've seen ORM's do this a lot (I'm learning rails), though you can explicity select only the columns you want if necessary.

I'll have to research stored procedures. I doubt my app will grow to the size where it would need that kind of optimization, but it'd be good to know anyways.
--
I'm an IT technician with a lot to learn, taught by viewers like you!



cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7

said by gorrillamcd:

I've seen ORM's do this a lot (I'm learning rails), though you can explicity select only the columns you want if necessary.

Depending on what you are doing, the ORM might do it because it doesn't know what you are going to do with your data. Do you just need one column to show a product name? Or every column for exporting a product data feed to Google?

There are advantages and disadvantages, and both not necessarily all from the same vantage point, of requesting only limited fields or all fields. Regardless though, even if you request all fields, don't SELECT * FROM..., spell out all the fields for any production code.


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7
reply to drew

said by drew:

The performance gain is, a lot of the time, either non-existent OR so marginal that it's irrelevant.

So in other words, a lot of the time, SPs performed just as well (no worse), or better (although marginal). And the occasional time, they performed better (more than marginal).

I can't think of an significant disadvantage to using a SP versus the query inline in the code. I can think of advantages to SPs that inline code does not afford.


drew
Automatic
Premium
join:2002-07-10
Port Orchard, WA
kudos:6

I would argue that the benefits of using an ORM outweigh the benefits of using SPs.