Application execute complex SQL query

Question

My application needs to execute a fairly complicated series of queries against a database. Normally I would just dump everything into a stored procedure and execute it that way.

But I do not have access to the database I'm trying to access so I can't create a stored procedure. Is there a better way of doing this instead of hitting the database 5-6 times to get the results that I need? I could join everything into a single query, but I would like to avoid that if possible since I would need to join about 10 tables.

Answer 1

There's nothing wrong with joining 10 tables if that's ultimately what you need to do. Generally SQL is good at this kind of thing. However, if there isn't that tight of a coupling between your 5-6 queries, then run them separately.

If you choose to break up the query, hitting the DB 5-6 times is fine--absolutely nothing wrong with that. Your access method (e.g. ADO.NET) probably gives you connection pooling for free anyway so the overhead of multiple queries is very small.

Answer 2

If it's possible to join everything into a single query, then in my experience it's almost always more efficient to do so; you should be doing that regardless. Follow that trail first in any case. Only consider alternatives if it's infeasible.

Answer 3

You can always execute the same series of queries in a single shot by separating them by ";" .