CODEWITHSUNDEEP
asp.netsqlsql-serverperformancedatabase-design
Mazen Elkashef
Mazen Elkashef

Reputation: 3499

SQL: Selecting from multiple tables in one query or a query for each table?

A follow up question concerning the DAL layer part .. The first question about dealing with large objects

I have one table (PlacesTable) that is connected by like 6 other tables (1:Many and Many:Many relations)

In my DAL should I put a big query that join the 6 tables + the m:m reference tables (btw it produces multiple data that I don't need Please refer to my old question) and place all the data in it's specific object property or, should I use a single query for each table and create an object for each fetched table .. and finally send those to the UI ?

If I wasn't clear enough please let me know what I further information do you need to know!

Thanks in advance for your time and help =)

Upvotes: 0

Views: 1952

Answers (4)

gbn
gbn

Reputation: 432667

If one big query gives too much duplicate data, and 6 round trips are slow... some options

  • Use a stored proc to return 6 recordsets in one DB call
  • Do some joins on the server to reduce both duplicate data and roundtrips
    • do both

Have you any performance differences yet? Or is this guess driven optimization?

Upvotes: 2

p.campbell
p.campbell

Reputation: 100637

Your question seems to be about whether you should load related entities into your Places object. It sounds like you've got a few classes in your model, which is a great start.

Load only the data you require. Consider your consumer(in this case, the UI). Do you need to display this data? Only query for the data you need. Build multiple methods in your DAL to ensure that the right data is loaded at the time. i.e. GetPlacesSummary() returns your data shaped one way, while GetDetailedPlaces() returns a more granular data set.

Look into a data access component that supports lazy loading. This means that only when you access those properties (like Customer.Invoices.Count) will the data actually be queried from the database.

Upvotes: 1

sstendal
sstendal

Reputation: 3236

In general, the database performs much better at joining data from several tables than other technologies. You will get better performance if you make one query than if you make several queries. You will also get the added benefit of not having to have a one-to-one relationship between your tables and your objects.

Upvotes: 0

Oded
Oded

Reputation: 499352

You are better off with a single query returning all the information required for the UI.

Each database connection will be slow and you will have to wait for all of them to finish before having your data ready for the UI.

The slowest thing between the application and the SQL server is the act of establishing connections - the less of them, the better.

Upvotes: 0

Related Questions