3 ms·

He's not selecting each row one-by-one. He's getting 48 records back using two queries. The first query gets the 48 id's of the most recently updated posts.

He's not selecting each row one-by-one. He's getting 48 records back using two queries. The first query gets the 48 id's of the most recently updated posts. The second query selects the entire row that is associated with each id: 1) select top 48 Id from Posts 2) select * from Posts where Id in (<id's selected from previous query) Unless the second query is 'abstracted' into 48 individual queries (which would make for an interesting abstraction-article in itself) your accusation is completely baseless. I think Jeff's notation in the SQL queries were a bit confusing, but if you actually read the article his intention is clear and his point is valid. I'm baffled by the retaliation here on HN. Please rebut me and restore my faith in the HN community.

logicalmind 17y ago
He says: "Selecting all columns with the star () operator is expensive, and that's what LINQ to SQL always does by default." Doing a select is expensive relative to what? Assuming your select * where clause has only the index key in it, then select any column in the leaf of the index has the same cost as selecting all columns in the leaf. The expense is the disk read, if necessary. Also, If you select the entire object then linq does a select * because it has to populate every value of the object. If you only need an id column, just select the id column. Then he says: "Now, retrieving 48 individual records one by one is sort of silly, becase you could easily construct a single where Id in (1,2,3..,47,48) query that would grab all 48 posts in one go. But even if we did it in this naive way, the total execution time is still a very reasonable (48 * 3 ms) + 260 ms = 404 ms." He says he could do an IN list, but he never does it. He assumes that selecting on one id is the worst case and then multiplying it by 48 gives him a total. The true time would depend on whether the index pages were in the cache of the database. It also depends on whether the query being run uses parameter versus literals because now compiling/optimizing time is there too. Specifically, what point are you referring to that is valid?
Maro 17y ago
"Now, retrieving 48 individual records one by one is sort of silly, becase you could easily construct a single where Id in (1,2,3..,47,48) query that would grab all 48 posts in one go. But even if we did it in this naive way, the total execution time is still a very reasonable (48 * 3 ms) + 260 ms = 404 ms. That is half the time of the standard select-star SQL emitted by LINQ to SQL!" His "naive way" is the 48 individual SELECTs, and he's claiming that that's faster than one SELECT (star). His point about SQL vs. LINQ may or may not be valid --- I don't know since I don't use LINQ --- but his argument and measurements are certainly flawed. The nice thing about software such as a database is that, if you spend enough time, it's not some magical black box. If you read some books and papers or go to school or read some source code then you can actually have an understanding of what goes on "under the hood" and you don't have to resort to voodoo measurements. So, if you know something about RDMBSs, you know that once a query retrieves some rows from disk they're cached, so any queries issued shortly after will be served from memory. This is probably what happened here, since 3ms is less than the disks' seek time, not counting networking and processing overhead... If you're absolutely unwilling to learn something about how a software works and feel compelled to measure it, then at least create meaningful measurements, including averages, medians, deviations, plots... As a final note, as a developer who's startup is writing a soon-to-be-released high-performance replicated key-value store, if their site is such that performance matters, then these are exactly the type of queries that could be offloaded to some key-value store or even memcached and recomputed asynchronously in the background every minute.
ntoshev 17y ago
He's not selecting each row one-by-one. Yes he does: "even if we did it in this naive way, the total execution time is still a very reasonable". He's getting 48 records back using two queries. This is equally silly. The two queries would be equivalent to the single one if there weren't two round trips instead of one, more traffic (the ids are retrieved twice instead of once and they are also sent to the server) and the overhead from concatenating and then parsing all the ids in the second query.
- bradford 17y ago
  I concede. Got confused with the original posts wording, thanks for clearing it up!

logicalmind 17y ago

He says: "Selecting all columns with the star () operator is expensive, and that's what LINQ to SQL always does by default." Doing a select is expensive relative to what? Assuming your select * where clause has only the index key in it, then select any column in the leaf of the index has the same cost as selecting all columns in the leaf. The expense is the disk read, if necessary. Also, If you select the entire object then linq does a select * because it has to populate every value of the object. If you only need an id column, just select the id column. Then he says: "Now, retrieving 48 individual records one by one is sort of silly, becase you could easily construct a single where Id in (1,2,3..,47,48) query that would grab all 48 posts in one go. But even if we did it in this naive way, the total execution time is still a very reasonable (48 * 3 ms) + 260 ms = 404 ms." He says he could do an IN list, but he never does it. He assumes that selecting on one id is the worst case and then multiplying it by 48 gives him a total. The true time would depend on whether the index pages were in the cache of the database. It also depends on whether the query being run uses parameter versus literals because now compiling/optimizing time is there too. Specifically, what point are you referring to that is valid?

Maro 17y ago

"Now, retrieving 48 individual records one by one is sort of silly, becase you could easily construct a single where Id in (1,2,3..,47,48) query that would grab all 48 posts in one go. But even if we did it in this naive way, the total execution time is still a very reasonable (48 * 3 ms) + 260 ms = 404 ms. That is half the time of the standard select-star SQL emitted by LINQ to SQL!" His "naive way" is the 48 individual SELECTs, and he's claiming that that's faster than one SELECT (star). His point about SQL vs. LINQ may or may not be valid --- I don't know since I don't use LINQ --- but his argument and measurements are certainly flawed. The nice thing about software such as a database is that, if you spend enough time, it's not some magical black box. If you read some books and papers or go to school or read some source code then you can actually have an understanding of what goes on "under the hood" and you don't have to resort to voodoo measurements. So, if you know something about RDMBSs, you know that once a query retrieves some rows from disk they're cached, so any queries issued shortly after will be served from memory. This is probably what happened here, since 3ms is less than the disks' seek time, not counting networking and processing overhead... If you're absolutely unwilling to learn something about how a software works and feel compelled to measure it, then at least create meaningful measurements, including averages, medians, deviations, plots... As a final note, as a developer who's startup is writing a soon-to-be-released high-performance replicated key-value store, if their site is such that performance matters, then these are exactly the type of queries that could be offloaded to some key-value store or even memcached and recomputed asynchronously in the background every minute.

ntoshev 17y ago

He's not selecting each row one-by-one. Yes he does: "even if we did it in this naive way, the total execution time is still a very reasonable". He's getting 48 records back using two queries. This is equally silly. The two queries would be equivalent to the single one if there weren't two round trips instead of one, more traffic (the ids are retrieved twice instead of once and they are also sent to the server) and the overhead from concatenating and then parsing all the ids in the second query.

bradford 17y ago
I concede. Got confused with the original posts wording, thanks for clearing it up!