SQL：在一个查询中从多个表中进行选择，还是为每个表进行一个查询？
关于 DAL 层部分的后续问题.. 关于处理大型对象<的第一个问题< /a>
我有一个表 (PlacesTable)，它由 6 个其他表（1:Many 和 Many:Many 关系）连接
在我的 DAL 中，我应该放置一个连接 6 个表 + m:m 引用表的大查询（顺便说一句，它生成了我不需要的多个数据请参阅我的旧问题）并放置所有数据在它的特定对象属性中，或者，我应该对每个表使用单个查询并为每个获取的表创建一个对象..最后将它们发送到 UI 吗？
如果我不够清楚，请让我知道您需要了解哪些进一步信息！
预先感谢您的时间和帮助 =)
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 =)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果一个大查询提供太多重复数据，并且 6 次往返很慢...一些选项
你有任何性能差异吗？或者这是猜测驱动的优化？
If one big query gives too much duplicate data, and 6 round trips are slow... some options
Have you any performance differences yet? Or is this guess driven optimization?
您的问题似乎是关于是否应该将相关实体加载到 Places 对象中。听起来您的模型中有一些类，这是一个很好的开始。
仅加载您需要的数据。考虑您的消费者（在本例中为 UI）。您需要显示这些数据吗？仅查询您需要的数据。在 DAL 中构建多个方法以确保当时加载正确的数据。即
GetPlacesSummary()返回以一种方式形成的数据，而GetDetailedPlaces()返回更精细的数据集。研究支持延迟加载的数据访问组件。这意味着只有当您访问这些属性（例如 Customer.Invoices.Count）时，才会从数据库中实际查询数据。
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, whileGetDetailedPlaces()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.
您最好通过单个查询返回 UI 所需的所有信息。
每个数据库连接都会很慢，您必须等待所有连接完成才能为 UI 准备好数据。
应用程序和 SQL Server 之间最慢的事情是建立连接的行为 - 连接越少越好。
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.
一般来说，数据库在连接多个表中的数据方面比其他技术表现得更好。进行一次查询比进行多次查询会获得更好的性能。您还将获得额外的好处，即表和对象之间不必具有一对一的关系。
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.