An article and comments SQL query - efficient

Question

Hi there,

What'd be the most effective and efficient sql query to grab an article and the comments?

It'd be two tables (articles, comments.) Would it be best to have it in one SQL query or two SQL querys? Speed is the main concern.

Thanks in advance

Answer 1

Depending on how large your records are to work with, there shouldn't be a huge noticeable difference, if at all negligible, in speed whether its one query or two.

Personally, if it can get done in one query and you don't have to sacrifice readability, I would opt for one query. As a heads up, I would try to worry about performance when it is time to worry about performance.

Answer 2

Making a lot of assumptions here but...

Usually you will get the best performance by doing it with one query with a join. If you have very limited bandwidth between your database and your server, and a great many comments per join (probably a rare case) you could see a situation where two queries will outperform the single-query solution.

Answer 3

Use JOINS to accomplish this, JOINS are faster than writing 2 queries. U can do something like this

SELECT article, comment FROM tbl_article JOIN tbl_comment WHERE tbl_comment.article_id=tbl_article.id

Considering u have primary field id on tbl_article and article_id on tbl_comment. If u make article_id an foreign key will be gr8

Answer 4

Particularly if your database is on a different physical server to your web application (or whatever is doing the quering) it can be that simple network latency between the two servers can become an issue (that is, the ping time from one server to the other is likely measured in milliseconds, whereas the query itself might execute in microseconds.)

I would imagine the most efficient solution would be to put the two queries in a stored procedure and execute that. Only one round-trip to the database, no need for a join and the minimum data required to display the results returned to the client. In addition, you get query parameterization and the query plan for the stored procedure will be more likely cached in the server.

Answer 5

It's hard to imagine a question with any less detail than you provided, but I will try to answer in general terms.

If you are using a modern relational database management system (MySQL, etc) you should expect the database itself to best understand how to get information out of it. Therefore, you should submit almost all queries as single queries to the database and let it determine the optimal plan for getting the data back to you.

The database will perform best if you instruct it to index the fields used to join the data; if you find poor performance most database engines provide an option to have the engine explain the plan that was used to retrieve the information and based on that you can improve your indexing.

For frequent requests (like this one) it's best to encapsulate the query in the database as a VIEW (a stored query definition) and query the view rather than the tables directly.

Answer 6

Some platforms allow you to run multiple queries with one roundtrip to the database.

IE in ADO .Net you can run:

SELECT col1, col2, whatever from Articles WHERE id=xxx
SELECT colx, coly, colz from comments where arcticle_id=xxx

and get back a data set with two data tables.