哪种方法更好？ [英] Which is the better approach?

问题描述

大家好，


我有一个数据库，我正在为我的ASP页面提取数据。


我有4个表，课程，功能，目标和先决条件。最后一个

三个都包含课程产品代码和文本列 - 课程

产品代码将行链接到课程表。


因此，我可能在课程中有1个条目，但在功能中可能有3个，在目标中有5个
和2个在PreRequisite中。


我使用我的存储过程获取所有这些，使用LEFT JOIN'的
返回多行，我想知道的是我是否更好

返回说20行数据，然后不得不迭代

确定功能开始/结束的位置，目标开始/结束和

PreRequisites开始/结束（即，它们在数据中重复）或 - 无论是否使用存储过程打击每个表格，b / b
会更有效，

使用相同的连接，从而获得只需1道菜，然后3

功能迭代，然后5个目标迭代，然后2

先决条件迭代通过 - 所有这些都将显示在页面上

。


后一个选项听起来更清晰，显然代码更容易 -

但是我只是想知道是否会有很大的差异表现

明智 - 似乎我要么在一次点击中获得大量行并离开

SQL Server让网络服务器完成工作，或者我在网上做的更少
服务器并多次点击SQL Server ....


如有任何建议，将不胜感激。


祝你好运


Rob

解决方案

Rob Meade写道：

大家好，

我有一个数据库，我正在为我的ASP页面提取数据。

我有4个表，课程，功能，目标和先决条件。
最后三个都包含课程产品代码和文本列 -
课程产品代码将行链接到课程表。

所以，我可能有课程中有1个条目，但在功能中可能有3个，在目标中有5个，在PreRequisite中有2个。


嗯，我可能已经用这些表做了一个表，也许

调用表CourseAttributes。它将有三列：产品

代码，属性，属性类型（功能，目标或

PreRequisite）

我使用我的存储过程获取所有这些，使用LEFT
JOIN'返回多行，我想知道的是我是否更好返回说20数据行，然后必须通过确定功能开始/结束的位置，目标
开始/结束和PreRequisites开始/结束（即，它们在
数据中重复）或 - 使用相同的连接打击每个表格是否更有效，因此只获得1个课程，然后3个要素迭代，然后5个/ br / >迭代的目标，然后迭代2个先决条件 - 所有这些都将显示在页面上。

后一个选项听起来更清晰，显然是更简单的代码
明智 - 但我只是想知道是否会有很多明显的性能差异 - 似乎我要么在一次点击中获得大量行并离开SQL Server并让网络流行服务器做的工作，或者我在网络服务器上做的少，并且几次点击SQL Server ....

任何建议都将不胜感激。

最好的问候

Rob

我的意图是尽可能少地访问数据库（进程外）

尽可能。

两个选项：

1使用记录集的

NextRecordset方法从过程中返回多个结果集移动到下一个记录集。

2.使用联合查询返回一个结果集，其结构为上面的CourseAttributes表建议的结构

- 你可以创建一个视图。


Bob Barrows

-

Microsoft MVP - ASP / ASP。 NET

请回复newsgrou页。我的From

标题中列出的电子邮件帐户是我的垃圾邮件陷阱，因此我不经常检查它。通过发布到新闻组，您将获得更快的回复。

" Bob Barrows [MVP]"写了......

嗯，我可能已经用这些表制作了一个表，也许是调用表CourseAttributes。它将有三列：产品
代码，属性，属性类型（特征，目标或
预先要求）


好的 - 此时似乎数据结构设置得很好，也就是说，我不知道需要一类特征的额外项目。仅用于

示例 - 但是如果有的话我可能不得不采用现有的

结构 - 所以我可以做出这个改变，因为你'' ve

建议。

我的意图是尽可能少地访问数据库（进程外）
。


你会定义一个旅行吗？作为连接，还是执行命令？我总是认为这是因为连接的数量可能会对服务器产生很大的影响（特别是如果你不关闭'em he he he $ $ $ $ $ $ he he he he he he he he he he he he he he he he $

：oD），但我想也许是否有一个连接解雇了我在我的另一篇文章中提到的那些可能并不那么糟糕的那个4

SP'。 ..我猜

的执行时间可能略长一些？

两个选项：
1使用记录集'返回多个结果集br /> NextRecordset方法移动到下一个记录集。


我从未在ASP中尝试过这种方法 - 我在.net中使用

数据集/数据表做了类似的事情 - 我想它类似是吗？

2.使用联合查询返回一个结果集，其结构为上面的CourseAttributes表建议的结构
- 你可以从中查看
。 / blockquote>


如果我这样做鲍勃，那么这不就是我现在从SP获得的价值吗？也就是说，每个课程大约20行，其中行中的大多数列具有相同的数据（即来自Course表的数据），因为它

不能为空列？我仍然需要迭代一个更大的

记录集并查找差异，以了解我何时会获得所有

功能或所有功能目标不是吗？


感谢您的回复Bob - 以及更多信息。


问候

Rob

Rob Meade写道：

" Bob Barrows [MVP]"写了......

嗯，我可能已经用这些表制作了一个表，也许是调用表CourseAttributes。它将有三列：
产品代码，属性，属性类型（功能，
目标或PreRequisite）
确定 - 此时似乎数据结构设置得很好，也就是说，我不知道某种类型的特征需要额外的项目。仅举例 - 但如果有的话我可能不得不采用现有的结构 - 所以我可以根据你的建议进行改变。
< blockquote class =post_quotes>我倾向于尽可能少地访问数据库
（进程外）。

你会定义一个跳闸"作为一个连接，或执行一个
命令？

我指的是执行命令，这需要命令

在进程外发送到数据库，结果将被整理回来

进程。

我总是认为它是相信的是否可能对服务器产生重大影响的连接数量（特别是如果你不关闭'em <<

oD），但我想也许有一个连接解雇了我在我的另一篇文章中提到的那些可能不是那么糟糕的4个SP ...我猜
执行时间可能稍长一点？
< blockquote class =post_quotes>两个选项：
1从过程中返回多个结果集，使用
记录集的NextRecordset方法移动到下一个记录集。

<我从未在ASP中尝试过这种方法 - 我在.net中完成了类似的工作他的数据集/数据表 - 我猜它的相似之处是什么？

非常。具有多个select语句的存储过程或批量集合b / b $ b的查询将返回多个结果集，可以使用

set rs = rs.NextRecordset处理。

但是，这实际上只比使用多个调用更有效

来分隔程序，因为下一个结果集的请求必须是
发送到缓存结果的数据库。这是缓存

产生差异。

2.使用联合查询返回单个结果集，其结构为
对于上面的CourseAttributes表 - 你可以从中得到一个
视图。
如果我这样做鲍勃，这不是我现在从
SP得到的虽然？即，每个课程大约20行，其中行中的大多数列具有相同的数据（即来自课程表的数据），因为它不能有空列？

嗯，你可以这样做：


选择''课程''作为Source，courseid，coursecolint，coursecolvarchar，

'''作为属性

来自Course where = ...

union all

选择''功能''， courseid，null，''''，功能

来自功能，其中courseid = ...

等

我还有通过更大的记录集迭代并查找差异，以便知道何时我拥有所有功能或所有目标
不是吗？

迭代20行的成本可能很小，特别是如果在执行此操作时断开与数据库的连接。

如何Course表中有很多列是我们的好吗？如果只有少数，

不要担心。如果有大量列，那么我可能会倾向于使用两个结果集：一个包含来自Course

表的数据，另一个包含属性。


只有测试可以判断哪种方法更好。


Bob Barrows


-

Microsoft MVP - ASP / ASP.NET

请回复新闻组。这个电子邮件帐户是我的垃圾邮件陷阱所以我

不经常检查它。如果您必须离线回复，请删除

无垃圾邮件

Hi all,

I have a databse which I''m pulling the data from for my ASP page.

I have 4 tables, Course, Feature, Objective, and PreRequisite. The last
three all contain a course product code and a text column - the course
product code links the row(s) to the Course table.

So, I might have 1 entry in Course, but perhaps 3 in Feature, 5 in Objective
and 2 in PreRequisite.

I grab all of these using my stored procedure which, using the LEFT JOIN''s
returns a multitude of rows, what I''m wondering is whether I am better
returning say 20 rows of data, and then having to iterate through
determining where the Features start/end, Objectives start/end and
PreRequisites start/end (ie, they are repeated in the data) or - whether it
would be more effecient to hit each of the tables with a stored procedure,
using the same connection and thus getting just the 1 course, then 3
features to iterate through, then 5 objectives to iterate through and then 2
pre-requisites to iterate through - all of which would then get displayed to
the page.

The latter option sounds "clearer", and obviously a bit easier code wise -
but I just wondered whether there would be much of a difference performance
wise - seems that I either get lots of rows in one hit and walk away from
SQL Server and let the web server do the work, or I do less on the web
server and hit the SQL Server several times....

Any suggestions would be appreciated.

Best regards

Rob

解决方案

Rob Meade wrote:

Hi all,

I have a databse which I''m pulling the data from for my ASP page.

I have 4 tables, Course, Feature, Objective, and PreRequisite. The
last three all contain a course product code and a text column - the
course product code links the row(s) to the Course table.

So, I might have 1 entry in Course, but perhaps 3 in Feature, 5 in
Objective and 2 in PreRequisite.
Hmm, I would probably have made a single table out of these, perhaps
calling the table CourseAttributes. It would have three columns: product
code, Attribute, Attribute Type (either "Feature", "Objective" or
"PreRequisite")

I grab all of these using my stored procedure which, using the LEFT
JOIN''s returns a multitude of rows, what I''m wondering is whether I
am better returning say 20 rows of data, and then having to iterate
through determining where the Features start/end, Objectives
start/end and PreRequisites start/end (ie, they are repeated in the
data) or - whether it would be more effecient to hit each of the
tables with a stored procedure, using the same connection and thus
getting just the 1 course, then 3 features to iterate through, then 5
objectives to iterate through and then 2 pre-requisites to iterate
through - all of which would then get displayed to the page.

The latter option sounds "clearer", and obviously a bit easier code
wise - but I just wondered whether there would be much of a
difference performance wise - seems that I either get lots of rows in
one hit and walk away from SQL Server and let the web server do the
work, or I do less on the web server and hit the SQL Server several
times....

Any suggestions would be appreciated.

Best regards

Rob

My inclination is to make as few trips to the database (out-of-process)
as possible.
Two options:
1 Return multiple resultsets from the procedure, using the recordset''s
NextRecordset method to move to the next recordset.
2.Use a union query to return a single resultset with the structure
suggested for the CourseAttributes table above - you could make a view
out of this.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.

"Bob Barrows [MVP]" wrote ...

Hmm, I would probably have made a single table out of these, perhaps
calling the table CourseAttributes. It would have three columns: product
code, Attribute, Attribute Type (either "Feature", "Objective" or
"PreRequisite")
ok - at this time it seems that the data structure is pretty set, ie, I have
no knowledge of additional items being need for a type of "Feature" only for
example - but if there were I''d probably have to adopt the existing
structure at that point - so I could make this change now that you''ve
suggested.
My inclination is to make as few trips to the database (out-of-process)
as possible.
Would you define a "trip" as a connection, or the execution of a command? I
was always lead to believe that it was the number of connections that could
have a big impact on the server (especially if you dont close ''em - tee hee
:oD), but I thought perhaps if there was one connection that fired those 4
SP''s I mentioned in my other post that might not be so bad...I guess the
execution time might be slightly longer?
Two options:
1 Return multiple resultsets from the procedure, using the recordset''s
NextRecordset method to move to the next recordset.
I''ve never tried that in ASP - I''ve done something similar in .net with the
dataset/datatables - I guess its similar is it?
2.Use a union query to return a single resultset with the structure
suggested for the CourseAttributes table above - you could make a view
out of this.

If I do this Bob, isn''t that going to be exactly what I get from the SP
right now though? ie, about 20 rows per course where most of the columns in
the rows have the same data (ie the data from the Course table) because it
cant have the empty columns? I''d still have to iterate through a larger
record set and look for the differences to know when I''d got all of the
features or all of the objectives wouldn''t I?

Thanks for your reply Bob - and further information appreciated.

Regards

Rob

Rob Meade wrote:

"Bob Barrows [MVP]" wrote ...

Hmm, I would probably have made a single table out of these, perhaps
calling the table CourseAttributes. It would have three columns:
product code, Attribute, Attribute Type (either "Feature",
"Objective" or "PreRequisite")
ok - at this time it seems that the data structure is pretty set, ie,
I have no knowledge of additional items being need for a type of
"Feature" only for example - but if there were I''d probably have to
adopt the existing structure at that point - so I could make this
change now that you''ve suggested.

My inclination is to make as few trips to the database
(out-of-process) as possible.

Would you define a "trip" as a connection, or the execution of a
command?

I''m referring to the execution of a command, which requires the command to
be sent out-of-process to the database, and results to be marshalled back
into process.
I was always lead to believe that it was the number of
connections that could have a big impact on the server (especially if
you dont close ''em - tee hee

oD), but I thought perhaps if there was one connection that fired
those 4 SP''s I mentioned in my other post that might not be so bad...I guess
the execution time might be slightly longer?

Two options:
1 Return multiple resultsets from the procedure, using the
recordset''s NextRecordset method to move to the next recordset.

I''ve never tried that in ASP - I''ve done something similar in .net
with the dataset/datatables - I guess its similar is it?

Very. A stored procedure with multiple select statements, or a batched set
of queries, will return multiple resultsets which can be processed by using
set rs = rs.NextRecordset.
But, this is really only a little more efficient than using multiple calls
to separate procedures, since the request for the next resultset has to be
sent to the database which has the results cached. It''s the caching that
makes the difference.

2.Use a union query to return a single resultset with the structure
suggested for the CourseAttributes table above - you could make a
view out of this.
If I do this Bob, isn''t that going to be exactly what I get from the
SP right now though? ie, about 20 rows per course where most of the
columns in the rows have the same data (ie the data from the Course
table) because it cant have the empty columns?

Well, you could do something like:

select ''Course'' as Source, courseid, coursecolint, coursecolvarchar,
'''' as Attribute
from Course where courseid = ...
union all
select ''Feature'', courseid,null,'''', Feature
from Feature where courseid = ...
etc.
I''d still have to
iterate through a larger record set and look for the differences to
know when I''d got all of the features or all of the objectives
wouldn''t I?

The cost of iterating through 20 rows is likely to be small, especially if
you disconnect from the database while doing it.
How many columns from the Course table are we talking about? If only a few,
don''t worry about it. If there are a large number of columns, then I might
lean toward using two resultsets: one containing the data from the Course
table, and the other containing the attributes.

Only testing can tell which approach is better.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don''t check it very often. If you must reply off-line, then remove the
"NO SPAM"

这篇关于哪种方法更好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！