What are the best (cleaner, less resource expensive) ways to pass one-to-many relationships from a db server to a client?
将一对多关系从数据库服务器传递到客户端的最佳(更干净,资源更少)方法是什么?
Imagine that I have a Author table and a Book table. I want to to retrieve all the authors whose name starts with "a" and all the books they've written. Then, client-side, generate an array of objects "Author" whose "Books" field is an array of objects "Books".
想象一下,我有一个Author表和一个Book表。我想要检索名称以“a”开头的所有作者以及他们所写的所有书籍。然后,在客户端生成一个对象数组“作者”,其“书籍”字段是一个对象数组“书籍”。
Two poor man solutions that come to my mind are:
我想到的两个穷人解决方案是:
- Retrieve all the authors, cycle through them on the client and execute an additional query to get all the books
- "SELECT a.* FROM author a, book b WHERE a.name like 'A%' and b.author_id = a.id"
检索所有作者,在客户端上循环浏览它们并执行其他查询以获取所有书籍
“SELECT a。* FROM author a,book b WHERE a.name like'A%'和b.author_id = a.id”
The first solution is really database-side intensive (if i have 1000 authors, i have to execute 1001 queries).
第一个解决方案实际上是数据库方面的密集型(如果我有1000个作者,我必须执行1001个查询)。
The second requires some intensive work client-side, as the program would parse the result as it has the data common to Author repeated on each row.
第二个需要客户端的一些密集工作,因为程序将解析结果,因为它具有每行重复的Author共同的数据。
Another solution would be to return multiple record sets from a stored procedure. I've never handled multiple record sets and I'm not sure that all the languages / adaptor classes support them.
另一种解决方案是从存储过程返回多个记录集。我从来没有处理过多个记录集,我不确定所有的语言/适配器类都支持它们。
Of course the situation can get worse if any author can have books and essays and every book can have sample pages and so on.
当然,如果任何作者可以拥有书籍和论文,并且每本书都可以有样本页面等,情况就会变得更糟。
Any idea? Thanks
任何的想法?谢谢
EDIT: I'm using .net, so ado's datarelations are an option. Are they supported my oracle and mysql?
编辑:我正在使用.net,所以ado的datarelations是一个选项。他们支持我的oracle和mysql吗?
5 个解决方案
#1
Your two questions here. One is how to most efficiently gather the data, the other is how to deal with the data you receive.
你这两个问题。一个是如何最有效地收集数据,另一个是如何处理您收到的数据。
First how to gather it:
首先如何收集它:
SELECT a.* FROM Author a INNER JOIN Book b ON a.id=b.author_id WHERE left(a.name,1)='a'
Like is very expensive, avoid it if you can. Inner joins are your least expensive way to compare tables - especially if you have indexes on the Primary keys (id) and Foreign keys(author_id) If there are authors without books (wouldn't be much of an author I guess), use a LEFT JOIN (same syntax just change 'inner' to 'left')
喜欢非常昂贵,如果可以,请避免使用它。内部联接是比较表的最便宜的方式 - 特别是如果你在主键(id)和外键(author_id)上有索引如果有作者没有书(我猜不会是作者),请使用LEFT JOIN(相同的语法只是将'inner'改为'left')
Second. If you are talking about 1000s of records you might want to think of gathering the data in two separate pulls (like your first choice I think) unless you want the user to wait forever for the page to load. For example: the user chooses all authors that begin with a and receives a list back - you could even have a count of the articles from that author.
第二。如果你正在讨论1000条记录,你可能会想到以两个单独的方式收集数据(比如你认为的第一选择),除非你希望用户永远等待页面加载。例如:用户选择以a开头并收到列表的所有作者 - 您甚至可以计算该作者的文章。
SELECT a.Name count(b.author_id) titles
FROM Author a INNER JOIN Book b ON a.id=b.author_id
WHERE left(a.name,1)='a'
GROUP BY a.Name
They'd see
John Adam: 35 Titles
Jane Acaba: 18 Titles
Jim Allan: 3 Titles
Then the user clicks on the author, load the list of books for that author.
然后用户点击作者,加载该作者的书籍列表。
Jim Allan's Titles:
Froggy went a court'n
Death on the Nile
Life in Africa
If you want it to appear like it's all done with one pull, but you want it to come over really fast use xmlhttp or ajax to show the list of authors. That is a whole other topic. :)
如果你希望它看起来像是全部用一次拉动完成,但你希望它快速使用xmlhttp或ajax来显示作者列表。这是另一个主题。 :)
I also should add that stored procedures are a must. The will add significantly to the portability of the app as well as to the performance in most databases.
我还应该补充说,存储过程是必须的。这将显着增加应用程序的可移植性以及大多数数据库的性能。
HTH
#2
I think the latter is probably closer to a good solution than the former.
我认为后者可能比前者更接近一个好的解决方案。
That said, you might want to introduce a layer between the client and the database - on the database machine - that can filter and process the rows from the DB into the data structure(s) of your choosing.
也就是说,您可能希望在客户端和数据库之间引入一个层 - 在数据库机器上 - 可以过滤并处理从DB到您选择的数据结构的行。
#3
It may depend on the choice of RDBMS, some of them may support certain feature that enables your desired function, although the feature may not be portable to another RDBMS.
它可能取决于RDBMS的选择,其中一些可能支持某些能够实现所需功能的功能,尽管该功能可能无法移植到另一个RDBMS。
As an example, Microsoft SQL Server, starting from SQL 2000, supports SELECT FOR XML. This, when the XML schema is crafted carefully (e.g. FOR XML EXPLICIT), will do exactly what you want to do -- return an XML that gives you the hierarchy of the data. If you also use ADO.NET, and choose to load it into a DataSet by using SqlDataAdaptor, the result DataSet will actually have all data already set into DataTables, with correct DataRelations set up between DataTables. However, this is probably a MS-SQL-specific feature, and working in another RDBMS may require some other techniques.
例如,Microsoft SQL Server从SQL 2000开始支持SELECT FOR XML。当仔细精心制作XML模式(例如FOR XML EXPLICIT)时,这将完全按照您的意愿执行 - 返回一个XML,它为您提供数据的层次结构。如果您还使用ADO.NET,并选择使用SqlDataAdaptor将其加载到DataSet中,则结果DataSet实际上已将所有数据设置到DataTable中,并在DataTable之间设置正确的DataRelations。但是,这可能是特定于MS-SQL的功能,而在另一个RDBMS中工作可能需要一些其他技术。
#4
We use multiple recordsets. If you have another entity linked to author, just add another recordset. Not sure about Oracle and MySQL, but if corresponding implementations of IDataReader have NextResult() not throwing NotSupportedException, this should work for them.
我们使用多个记录集。如果您有另一个实体链接到作者,只需添加另一个记录集。不确定Oracle和MySQL,但是如果IDataReader的相应实现让NextResult()没有抛出NotSupportedException,那么这应该适用于它们。
#5
Assuming a typical case, your data volume will be largely in the "Books" table. Take the simplest solution, for which the penalty is small for any display size that you would want to inflict upon a user - join the two together and return a single rowset with some amount of redundancy in "Authors" colum data. Other solutions require multiple queries, which in the overall scheme of things is in most cases both more expensive and complex.
假设一个典型案例,您的数据量将主要位于“书籍”表中。采用最简单的解决方案,对于您希望对用户造成的任何显示大小,惩罚很小 - 将两者连接在一起并返回单个行集,其中“Authors”列数据中包含一定量的冗余。其他解决方案需要多个查询,在整个方案中,在大多数情况下,这些查询既昂贵又复杂。
My order of priority generally is: Deliver the content required for the best UI experience, structured for the simplest emplementation (with the normal disclaimers about unusual circumstances dictating unusual designs).
我的优先顺序通常是:提供最佳UI体验所需的内容,为最简单的实现而构建(与正常的免责声明有关异常设计的异常情况)。
This probably also does the best job of decoupling your data strategy from changes in your UI design, because all the content is in one easily navigated result set.
这可能也最有效地将您的数据策略与UI设计中的更改分离,因为所有内容都在一个易于导航的结果集中。
#1
Your two questions here. One is how to most efficiently gather the data, the other is how to deal with the data you receive.
你这两个问题。一个是如何最有效地收集数据,另一个是如何处理您收到的数据。
First how to gather it:
首先如何收集它:
SELECT a.* FROM Author a INNER JOIN Book b ON a.id=b.author_id WHERE left(a.name,1)='a'
Like is very expensive, avoid it if you can. Inner joins are your least expensive way to compare tables - especially if you have indexes on the Primary keys (id) and Foreign keys(author_id) If there are authors without books (wouldn't be much of an author I guess), use a LEFT JOIN (same syntax just change 'inner' to 'left')
喜欢非常昂贵,如果可以,请避免使用它。内部联接是比较表的最便宜的方式 - 特别是如果你在主键(id)和外键(author_id)上有索引如果有作者没有书(我猜不会是作者),请使用LEFT JOIN(相同的语法只是将'inner'改为'left')
Second. If you are talking about 1000s of records you might want to think of gathering the data in two separate pulls (like your first choice I think) unless you want the user to wait forever for the page to load. For example: the user chooses all authors that begin with a and receives a list back - you could even have a count of the articles from that author.
第二。如果你正在讨论1000条记录,你可能会想到以两个单独的方式收集数据(比如你认为的第一选择),除非你希望用户永远等待页面加载。例如:用户选择以a开头并收到列表的所有作者 - 您甚至可以计算该作者的文章。
SELECT a.Name count(b.author_id) titles
FROM Author a INNER JOIN Book b ON a.id=b.author_id
WHERE left(a.name,1)='a'
GROUP BY a.Name
They'd see
John Adam: 35 Titles
Jane Acaba: 18 Titles
Jim Allan: 3 Titles
Then the user clicks on the author, load the list of books for that author.
然后用户点击作者,加载该作者的书籍列表。
Jim Allan's Titles:
Froggy went a court'n
Death on the Nile
Life in Africa
If you want it to appear like it's all done with one pull, but you want it to come over really fast use xmlhttp or ajax to show the list of authors. That is a whole other topic. :)
如果你希望它看起来像是全部用一次拉动完成,但你希望它快速使用xmlhttp或ajax来显示作者列表。这是另一个主题。 :)
I also should add that stored procedures are a must. The will add significantly to the portability of the app as well as to the performance in most databases.
我还应该补充说,存储过程是必须的。这将显着增加应用程序的可移植性以及大多数数据库的性能。
HTH
#2
I think the latter is probably closer to a good solution than the former.
我认为后者可能比前者更接近一个好的解决方案。
That said, you might want to introduce a layer between the client and the database - on the database machine - that can filter and process the rows from the DB into the data structure(s) of your choosing.
也就是说,您可能希望在客户端和数据库之间引入一个层 - 在数据库机器上 - 可以过滤并处理从DB到您选择的数据结构的行。
#3
It may depend on the choice of RDBMS, some of them may support certain feature that enables your desired function, although the feature may not be portable to another RDBMS.
它可能取决于RDBMS的选择,其中一些可能支持某些能够实现所需功能的功能,尽管该功能可能无法移植到另一个RDBMS。
As an example, Microsoft SQL Server, starting from SQL 2000, supports SELECT FOR XML. This, when the XML schema is crafted carefully (e.g. FOR XML EXPLICIT), will do exactly what you want to do -- return an XML that gives you the hierarchy of the data. If you also use ADO.NET, and choose to load it into a DataSet by using SqlDataAdaptor, the result DataSet will actually have all data already set into DataTables, with correct DataRelations set up between DataTables. However, this is probably a MS-SQL-specific feature, and working in another RDBMS may require some other techniques.
例如,Microsoft SQL Server从SQL 2000开始支持SELECT FOR XML。当仔细精心制作XML模式(例如FOR XML EXPLICIT)时,这将完全按照您的意愿执行 - 返回一个XML,它为您提供数据的层次结构。如果您还使用ADO.NET,并选择使用SqlDataAdaptor将其加载到DataSet中,则结果DataSet实际上已将所有数据设置到DataTable中,并在DataTable之间设置正确的DataRelations。但是,这可能是特定于MS-SQL的功能,而在另一个RDBMS中工作可能需要一些其他技术。
#4
We use multiple recordsets. If you have another entity linked to author, just add another recordset. Not sure about Oracle and MySQL, but if corresponding implementations of IDataReader have NextResult() not throwing NotSupportedException, this should work for them.
我们使用多个记录集。如果您有另一个实体链接到作者,只需添加另一个记录集。不确定Oracle和MySQL,但是如果IDataReader的相应实现让NextResult()没有抛出NotSupportedException,那么这应该适用于它们。
#5
Assuming a typical case, your data volume will be largely in the "Books" table. Take the simplest solution, for which the penalty is small for any display size that you would want to inflict upon a user - join the two together and return a single rowset with some amount of redundancy in "Authors" colum data. Other solutions require multiple queries, which in the overall scheme of things is in most cases both more expensive and complex.
假设一个典型案例,您的数据量将主要位于“书籍”表中。采用最简单的解决方案,对于您希望对用户造成的任何显示大小,惩罚很小 - 将两者连接在一起并返回单个行集,其中“Authors”列数据中包含一定量的冗余。其他解决方案需要多个查询,在整个方案中,在大多数情况下,这些查询既昂贵又复杂。
My order of priority generally is: Deliver the content required for the best UI experience, structured for the simplest emplementation (with the normal disclaimers about unusual circumstances dictating unusual designs).
我的优先顺序通常是:提供最佳UI体验所需的内容,为最简单的实现而构建(与正常的免责声明有关异常设计的异常情况)。
This probably also does the best job of decoupling your data strategy from changes in your UI design, because all the content is in one easily navigated result set.
这可能也最有效地将您的数据策略与UI设计中的更改分离,因为所有内容都在一个易于导航的结果集中。