4 ms·
One aspect that always comes up is the "inefficiency" of doing a select * from a table with 30 columns when you only need 4 columns. 99% of the time the millis
by mistermann 15y ago
One aspect that always comes up is the "inefficiency" of doing a select * from a table with 30 columns when you only need 4 columns. 99% of the time the millisecond performance difference doesn't matter, and if it does, there is a standard non-default way to handle it in most ORM's.
However, one aspect that is usually conspicuously absent in anti-orm blog posts is that of development time and cost. ORM usage practically guarantees known coded efficiencies, but it lets you implement and pivot really quickly, the time and money saved is easily more than enough to pay for a bump in hardware to overcome the 10% slower code. But to do so is heresy for these people....selecting columns from the database that you do not use is just not done, full stop. Which is cheaper, in dollars, is irrelevant.
- epscylonb 15y agoThis surprised me as well, I was told that in Postgres at least, there is no difference in query speed between selecting a subset of a record and the entire thing.
- chrisb 15y agoThe query speed may be the same (I don't know if this is true or not), but fetching the data from disk and returning it over a network will certainly be slower, especially if the unnecessary columns contain large strings or blobs.
- seldo 15y agoIt depends. In MySQL, if the only columns you select are indexed columns, the entire query will be pulled from the index, which is usually entirely in-memory, so enormously faster: http://dev.mysql.com/doc/refman/5.5/en/where-optimizations.html http://dev.mysql.com/doc/refman/5.5/en/where-optimizations.h... (The columns currently all have to be numeric for this to be true, but that's surprisingly often the case)
- nerfhammer 15y agoShort answer: Any relational database is going to read in the entire row when you select it. You will save network traffic and probably space in some internal buffers only. Longer answer: Any relational database will read the entire page the row resides on when you select a row. This means that tables with more columns will have less efficient pages which will need to be read/buffered more frequently per row. This also means there is an exception: columns that are stored in overflow pages away from the rest of the row like blob or text columns or (in some db's) very long varchars may not necessarily be read if you don't select them. There is an additional, very important exception: if you use a covering index then the db will not need to read the data page the row resides on. For example, if you have an index on (username, user_id) and you select "select user_id from table where username=xxxxx" then it will be able to read the user_id from the leaf node of the index and no bookmark lookup to the data pages will be needed. In some db's the primary key is always "covered" and you never need a bookmark lookup to get it.
- IgorPartola 15y agoForget the speed of the database. What happens when you need to transmit the returned data across a network? Would you rather transmit 32 bytes per row, or 10kB?
- falcolas 15y agoOr, like I had happen recently, 10GB? Of course, that comes down to the scaling problem. The application I'm currently working on can't use ORMs (though we have rolled a few of our own read-only ones), because of 1) the size of the data we're going through, and 2) the lack of support for splitting reads and read/writes to different connections.
- IgorPartola 15y ago(1) is definitely a problem I've seen. SQLAlchemy for example takes forever to initialize objects representing even a single row. Fine if you have less than 100 tables with less than 100 rows each and almost never join, but start scaling, and you run into issues.
- zzzeek 15y ago"forever to initialize a single row" is obviously some pretty severe hyperbole, but the slowness of loading rows into fully formed objects is largely a product of C python's sluggishness in creating new objects. This also has nothing to do with how many rows are in the table, only how many rows you happen to be fetching over the wire for a particular query. Performance is also something SQLAlchemy is obsessive about, so the speed of loading objects in SQLA has increased by orders of magnitude over the course of 0.4, 0.5, 0.6, 0.7. If your opinions are based on earlier versions, your impressions may be out of date.
- IgorPartola 15y agoSorry to be vague. In my benchmarks it takes around 30 ms to initialize an instance of a model as a result of a query using Python 2.6 and sqlalchemy 0.6. This is in contrast to raw MySQLdb query initialized as a dict which takes 2-4 ms.
- Erwin 15y agoI think that would depend on the data type and sizes. A single row in a PG table cannot take up more than page size (8kB). If you have a 100kB TEXT field the row will instead contain a reference to an "overflow" table rather than the data itself. See http://developer.postgresql.org/pgdocs/postgres/storage-toast.html http://developer.postgresql.org/pgdocs/postgres/storage-toas... -- presumably the corresponding toast data is not looked up unless needed (and you can check for e.g IS NULL in the main row) (looking over my database, less than 1% of the 30-40GB size is in overflow tables so unless you routinely store large amounts of data it won't affect you). (I used to think the "toast" tables were temporary tables and wondered when Postgres would delete them).
- absconditus 15y agoIf your database server is on another machine you are now transmitting 30 columns of data over the network instead of 4.
- mistermann 15y agoCorrect. And for most applications running on most networks, without access to programmatic benchmarking tools, you wouldn't be able to tell one app from the other. Everyone likes to think they're writing the next google, Facebook,etc, but the reality is far more humble and boring.
- mattmanser 15y agoWell, it depends if your columns are blobs or not, it makes a difference (well, at least it used to). On the other hand, I don't know which ORM you use, but they don't all behave like this, if you tell them you only want columns Name, Age, DOB, they'll only get those columns.