3 ms·

There's no way fetching IDs then fetching each row one-by-one is faster than SELECT * with the the same WHERE clause, especially taking into account roundtrip t

by Maro 17y ago

There's no way fetching IDs then fetching each row one-by-one is faster than SELECT * with the the same WHERE clause, especially taking into account roundtrip times and whatnot. His measurements are wrong (eg. due to caching) and/or he's confused.

mrduncan 17y ago
I completely agree. Unless the database is doing something odd (ok, not out of the question I guess) like ignoring an index on the id column, it just doesn't seem logical that performing 48 more queries would be faster than allowing the database engine to run it all at once.
- logicalmind 17y ago
  There are so many levels to the performance that measuring the surface and making a generalization is practically useless. I find the comments on the article even more baffling. Here are a couple relevant questions: 1. What is the scope of the data context used for these queries? Caching that is keyed by id is done within the scope of the data context: http://blogs.msdn.com/dinesh.kulkarni/archive/2008/07/01/linq-to-sql-tips-9-understanding-datacontext-s-internal-caching.aspx http://blogs.msdn.com/dinesh.kulkarni/archive/2008/07/01/lin... 2. Is the code being run co-located with the database server? If so, this is atypical and these times do not include network overhead. There are many more questions...
deleted 17y ago
[deleted]
bradford 17y ago
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.

by Maro 17y ago

mrduncan 17y ago

I completely agree. Unless the database is doing something odd (ok, not out of the question I guess) like ignoring an index on the id column, it just doesn't seem logical that performing 48 more queries would be faster than allowing the database engine to run it all at once.

logicalmind 17y ago
There are so many levels to the performance that measuring the surface and making a generalization is practically useless. I find the comments on the article even more baffling. Here are a couple relevant questions: 1. What is the scope of the data context used for these queries? Caching that is keyed by id is done within the scope of the data context: http://blogs.msdn.com/dinesh.kulkarni/archive/2008/07/01/linq-to-sql-tips-9-understanding-datacontext-s-internal-caching.aspx http://blogs.msdn.com/dinesh.kulkarni/archive/2008/07/01/lin... 2. Is the code being run co-located with the database server? If so, this is atypical and these times do not include network overhead. There are many more questions...

deleted 17y ago

[deleted]

bradford 17y ago

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.