General Question

manuel_alarcon's avatar

What is better: One big SQL query or several?

Asked by manuel_alarcon (294points) February 27th, 2013

Hi, I have relational database and I want to make the most of the data across several tables; for example, a table with sales data and the table with the prices, and another table linking both; I wonder if I want to display that data, and adding counting the data, or any other info I can get from the tables, what’s the best way to do it: build a big fat messy query or several queries, each one depending on the previous one? I don’t want the site to get sloppy

Observing members: 0 Composing members: 0

7 Answers

tom_g's avatar

It’s difficult without examples. But what you describe seems like a simple join.

jerv's avatar

At least as an intermediate step, it may be best to nest them; have a few small queries and a “meta-query” that unifies them. That makes debugging easier. Once you get all the small parts working, then unify them. Make the parts, then assemble them.

I find a modular approach makes it easier to build and maintain than “spaghetti code”.

manuel_alarcon's avatar

Yes, an example would be listing product names, with their buyers and the prices (I say this as a close example, otherwise I would have to explain a lot). I made the query joining tables; but what if I have another table with, say, the stores that have those products; then what would be best; add it to the query that already has a join? so far I came up with this, as a rough and quick solution:

<table>(1st joined query)while…<tr><td>xxx</td><td>yyy</td><td>(second joined query using the ID as variable from the first query)fetch…zzz</td></tr>...end while</table>

wundayatta's avatar

Doesn’t it depend on the output you want? What kind of report do you want? Where are you going with all this? Are you counting sales by store and product and price? Are you summing sales by store and product? Do you want to know average price per product per store?

My suggestion is that you look at the final product or products and then build your joins in order to enable those final products. Look at your goal. In academic terms, your research question. Everything else is designed in order to meet those requirements.

In a situation like this you need clarity and in my world, the research question provides the clarity. I suspect the business world isn’t all that different.

Cupcake's avatar

It doesn’t appear that you’re doing complicated analysis… but I do and whenever I try to make one big query it takes FOREVER to run, so I tend to do several small queries.

ragingloli's avatar

Several. One single one is a bitch to debug.

manuel_alarcon's avatar

thanks! so i was on a good path after all :)

Answer this question




to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
Knowledge Networking @ Fluther