数据集与datareader [英] Dataset vs datareader

问题描述

关于2002年，.Net为我们提供了DataAdapter / DataSet和DataReader。我们当时需要知道的是DataReader是最快的。

这是14年之后在企业生产环境中我们应该在所有争用防止SQL注入之后使用存储过程之后。 ......顺便说一下，这是一个Windows服务，没有任何网页附件。

有时我们需要动态生成SQL，所以没有办法使用存储过程，但在这种情况下，我也许可能。这将是一个麻烦，但我可以做到......有限制...因为有了DataReader，当我找到足够的进程时，我会停止读取行，这有一定的标准。之后，随着不同的记录被赋予不同的优先级（计划特征），这将在未来变得更加重要。我怀疑，如果数据库管理员发现我正在使用带有SQL字符串文字的DataReader，那么他就会翻出来（他很挑剔）。

所以我的问题是

（1）有关使用我应该注意的DataReader的任何新的含义。

（2）如果这个家伙出发，我如何能够捍卫我需要灵活性的决定？



谢谢，M



嗯。你可以创建一个存储过程，它将获取一个SQL文字语句并运行它，但这仍然不是我需要的解决方案，这真的会让他失望。



我尝试了什么：



真的，我已经制作了存储过程，但是已经定义了它。 DataReader让我可以灵活地选择符合条件的记录数量，然后停止。

What was it, about 2002 that .Net gave us the DataAdapter/DataSet and the DataReader. All we needed to know then was that the DataReader was fastest.
Well this is 14 years later in a corporate production environment after we're supposed to be using stored procedures after all the fighting over preventing SQL injection. ... This is a Windows Service by the way, no web attachment anywhere.
Sometimes we need to dynamically generate SQL, so there is no way to use a stored procedure, but in this case, I potentially could, maybe. It would be a hassle, but I could do it... with limits... because with the DataReader I stop reading rows when I have found enough to process, that have certain criteria. Later, this will become even more important in the future as different records are given different priority (planned feature). I suspect that when/if the DB Admin figures out I am using a DataReader with a SQL string literal, he's gonna flip out (he's picky).
So my question would be
(1) are there any new implications to consider about using the DataReader that I should pay attention to.
(2) How can I defend my decision that I need the flexibility, if the guy goes off?

Thanks, M

Hmmm. You can make a stored procedure that will take a SQL literal statement and run it, but that is still not the solution I need and that would really flip him out.

What I have tried:

Really, I have the stored procedure made, but defined it out. The DataReader gives me the flexibility to pick out the number of records I want that fit the criteria, then I stop.

推荐答案

DataReader不是更快，它只是推迟记录的下载，直到你要求它们，当完整的记录集合并返回到你的代码时，DataTable / DataSet方法返回。 DataReader需要相同的时间 - 甚至更长的时间来处理所有记录，因为它会分散DataTable方法预先设置的所有时间。此外，它需要打开更长时间的连接（读取器的生命周期）和DataTable方法立即释放的hogs服务器资源。这就是你的DBA要翻转的原因。


如果你只想要前20个记录，那么只能使用<$检索前20行c $ c> SELECT TOP 20 MyColumn1，MyColumn2，... 语法。那是有效率和快速的！

The DataReader isn't "faster", it just defers the "download" of the records until you ask for them, which the DataTable / DataSet approach returns when the complete set of records have been assembled and returned to your code. The DataReader takes the same time - or even longer - to process all records because it spreads the time that the DataTable approach puts all up front. In addition, it requires a connection open for longer (the life of the Reader) and "hogs" Server resources which the DataTable approach releases immediately. Which is why your DBA is going to "flip out".

And if you only want the top twenty records, then only retrieve the top twenty rows using the SELECT TOP 20 MyColumn1, MyColumn2, ... syntax. That's efficient and faster!

引用：

我们需要动态生成SQL，所以没有办法使用存储过程

we need to dynamically generate SQL, so there is no way to use a stored procedure

从什么时候开始？ SP仍然可以使用EXEC来执行动态生成的SQL ...

Since when? SP's can still use EXEC to execute dynamically generated SQL...

使用EntityFramework，它应该为您提供所需的东西。它涵盖了实现您的请求并为您执行所需的动态SQL，从而抽象出所有实际的数据访问内容。它基本上使你的SQL表在代码中表现得像对象一样（过度简化，但你明白了）。这意味着抛弃SP。

Use EntityFramework, it should give you the things you need. Under the covers it works out the dynamic SQL needed to fulfil your request and executes for you, abstracting away all the actual data access stuff. It basically makes your SQL tables behave like objects\lists in your code (oversimplification, but you get the idea). It would mean ditching the SPs though.

就我个人而言，我认为拆分逻辑是一个坏主意，并且在处理数据库层时使用存储过程而不是出于性能原因处理很多行，原因有两个：

1）我使开发和更改更加困难，你必须按摩你的逻辑才能在存储过程中工作（这不是它的设计目的）。

2）你被锁定在你的供应商数据库中，无法轻易移动到另一个。

3）至少在我的测试中使用正常的sql代码更快而不是在任何db post 2005中使用存储过程。

Personally I think it is a bad idea to split your logic, and put some in your database layer and use stored procedures other than for performance reasons when processing a lot of rows, for a couple of reasons:
1) I makes development and changes harder and you have to "massage" your logic to work in stored procedures (which is not what it was designed for).
2) You are locked in to your vendor database and can't easily move to another.
3) At least in my tests using normal sql code was faster than using stored procs in any db post 2005.

这篇关于数据集与datareader的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！