|
Is a query for a single column any faster than othersGeneral 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. |
|
SteveI know your IP address
join:2001-03-10 Tustin, CA |
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". |
|
drewRadiant Premium Member join:2002-07-10 Port Orchard, WA |
drew
Premium Member
2012-Nov-9 12:35 am
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
2012-Nov-9 8:03 pm
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 DSLThere's a reason I'm Command. Premium Member join:2000-07-30 Washington, DC Actiontec GT784WN
|
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. |
|
SteveI know your IP address
join:2001-03-10 Tustin, CA |
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 DSLThere's a reason I'm Command. Premium Member join:2000-07-30 Washington, DC Actiontec GT784WN
1 edit |
DC DSL
Premium Member
2012-Nov-10 9:20 am
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. |
|
drewRadiant Premium Member join:2002-07-10 Port Orchard, WA |
drew
Premium Member
2012-Nov-10 3:15 pm
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. |
|
|
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. |
|
cdruGo Colts MVM join:2003-05-14 Fort Wayne, IN |
cdru
MVM
2012-Nov-10 5:29 pm
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 |
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. |
|
drewRadiant Premium Member join:2002-07-10 Port Orchard, WA |
drew
Premium Member
2012-Nov-10 6:28 pm
I would argue that the benefits of using an ORM outweigh the benefits of using SPs. |
|