dslreports logo
 
    All Forums Hot Topics Gallery
spc
Search similar:


uniqs
944

gorrillamcd
Hangin' Out
join:2010-04-01
mexico

gorrillamcd

Member

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.

Steve
I know your IP address

join:2001-03-10
Tustin, CA

Steve

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
Radiant
Premium Member
join:2002-07-10
Port Orchard, WA

drew

Premium Member

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.
dave
Premium Member
join:2000-05-04
not in ohio

dave to Steve

Premium Member

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 Member
join:2000-07-30
Washington, DC
Actiontec GT784WN

DC DSL to gorrillamcd

Premium Member

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.

Steve
I know your IP address

join:2001-03-10
Tustin, CA

Steve

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

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

1 edit

DC DSL

Premium Member

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.

drew
Radiant
Premium Member
join:2002-07-10
Port Orchard, WA

drew

Premium Member

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.

gorrillamcd
Hangin' Out
join:2010-04-01
mexico

gorrillamcd

Member

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.

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

cdru

MVM

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

cdru to drew

MVM

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
Radiant
Premium Member
join:2002-07-10
Port Orchard, WA

drew

Premium Member

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