1

I've got two different SQL queries that I need to run at once. But since I am using inner joins in both of them, I can't just join them. Is there a way to do this? Also, do you think that much normalization could cause problems?

SELECT S.SchoolName,Sd.DepartmentName,E.GraduationDate 
FROM   Education E 
           INNER JOIN SchoolDepartment Sd ON Sd.DepartmentID = E.DepartmentID 
           JOIN School S ON S.SchoolID = Sd.SchoolID

SELECT c.CompanyName,dt.DepartmentName, pl.PositionLevelName 
FROM   Employee Ee 
           INNER JOIN Position P ON Ee.PositionID = P.PositionID  
           JOIN Company c ON c.CompanyID = P.CompanyID 
           JOIN Department De ON De.DepartmentID=P.DepartmentID 
           JOIN DepartmentType dt ON dt.DepartmentName = De.DepartmentTypeID  
           JOIN PositionLevel pl ON pl.PositionLevelID=P.PositionLevelID
Improve this question
1
  • Absolutely nothing wrong with multiple queries on a page. Those 2 queries will probably take less than a second. But if you really want to save the time, go with JonH and do a UNION Commented Feb 23, 2012 at 22:17

5 Answers 5

Reset to default
3

It seems like you're attempting to get two distinct sets of data, so it's not the worst thing in the world to run two queries.

Per your comment: I'm sure Facebook queries several stores when they load your profile page, but they're making heavy use of data caching, so they may cache your profile object(s) to reduce database hits.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

but before they cache they do get the data from database.Caching is another thing but to cache it i need to retrieve those and i'm asking should i create to procedures that open/retrieve/close connection or is there any better way to do that?
It depends on what technologies you are using to retrieve the data, but I would open a single connection and use that to retreive both sets of data, then close it. You can run multiple statements on the same connection.
What I'm trying to say is that yes, doing multiple queries may be more expensive in most cases than doing a single query, but that doesn't mean you should create some terribly designed concoction to avoid doing two queries when it clearly makes sense. As long as you share the same connection, that's going to be adequate.
1

You haven't indicated how they both are connected to a profile and whether each set is going to have many rows which are unrelated to each other. If each just returns a single row and both are related to the profile (related in the sense of relational database - attributes "related" to a key - perhaps your profile), there there probably is a realistic way to combine them into a single result set (i.e. a relation, or table).

There are a few strategies you can use if the sets on not really related in that sense.

  1. Make a single stored procedure or batch both in the same command which returns both result sets in sequence and use ADO.NET's feature to get the next result set on your reader.

  2. Make two sequential calls to the database.

  3. Use ASP and ADO's asynchronous features to make two simultaneous calls to the database and handle the completions - this can be useful for high-volume sites, since control is returned to the page once they are completed, freeing up threads.

Improve this answer

Comments

0

If the fields are of the same data type (the columns in query 1 match the same data type of query 2) you can use UNION to combine both queries, otherwise no its not possible if you cannot join them.

Improve this answer

3 Comments

i mean how do facebook retrieve all user information at once when you load the page?i need something like that
@tfeseas - Don't compare your work with facebook's work. Facebook has hundreds of employees. In any event I don't work for facebook so I don't know what their database looks like. They may store profile information in various tables but have a linkage via a pk fk relationship. And whose to say they don't make multiple calls to the database at various times - they probably do.
I work on some pages with dozens of queries... it's just a fact of life.
0

No, it is not possible on one connection. You need to open two connections and have a server powerfull enough to handle that (which means more than one core, which you mostl likely have).

Note that I answer your question - most others dont seem to.

I've got two different SQL queries that I need to run at once

That is not how it works in SQ LServer. If you writee thm into one batch, they still execute one after the other, not at the same time. Not sure it makes sense for them to run at the same time, but that is what you ask for.

But I have software doing hundreds of SQL at the same time given a powerfull enough applkication server (24 cores), a powerfull enough Oracle RAC (48 cores on 2 machines with hyperthreading = 96 parallel threads).

Improve this answer

Comments

0

I am trying to do something similar. I am actually trying to group lines by multiple criteria that are different into one data set. What I am doing is querying all of the data into a temp table then querytin that temp table into the different views then unioning it back. This way it shows the different views. For example. Line 1 includes cat1 cat2 and cat3. Line 2 is just cat 2 and cat 3. And line 3 is Cat1 - cat8. The data is all the same just different slices of it. So multiple trips to the database to query it isn't worth the overhead. I can query the temp multiple times to get my slices then union them together into one view. Alternatively if you need these views and they change you can create a table with the groupings you want in a one to many. With that three tiered approach you can join and get the sums in one step.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.