表变量上的SQL记录集...好吗? [英] SQL Recordsets on table variable... O.K?

问题描述

在进行所有培训和代码审查等过程中，始终提醒我们避免在数据库代码中使用记录集，因为这会影响性能.

首先，我想知道为什么.

其次，如果我在表变量上创建记录集，它是否会消耗相同的性能? (例如@myTable).我认为不会，因为正在访问的数据位于RAM中(在我的情况下，这只是一手记录，因此RAM的使用量应该很小)

During all my training and code reviews etc. we are always reminded to avoid Record Sets in our DB code because it hampers performance.

Firstly, I would like to know why.

Secondly, does it have the same performance drain if I create a Record Set on a table variable? (e.g. @myTable). I''d think no since the data being accessed is located in the RAM (And in my case it''s only a hand full of records, so the amount of RAM used should be very small)

Comments?

推荐答案

尝试一下.如果不确定时间安排，请创建相同代码的两个版本，然后使用秒表类 [ ^ ]将它们的时间设置为1000次迭代.
然后，您将有一些数字作为决策依据，您可以在下一次代码审查时提出这些决策...

Try it. If you aren''t sure about timings, create two versions of the same code and use the Stopwatch class[^] to time them over say, 1000 iterations.
Then you will have numbers to base a decision on, which you can bring up at your next code review...

我不确定记录集的含义.您是说在DB中吗?子查询确实会影响性能，因为它们会创建一个嵌套循环，因此外循环上的每次迭代都会再次遍历内循环.但是，无论如何，您的记录都存在于RAM中.是的，如果您的表永远不会很大，那么问题可能就不存在了.但是，当客户最终使用大型数据集时，千万不要犯用较小数据集进行测试的错误.

I''m not sure what you mean by record sets. Do you mean with in the DB ? Subqueries do hamper performance, b/c they create a nested loop, so each iteration on the outer loop, runs through the inner loop again. But, no matter what, your records live in RAM. And yes, if your tables are not going to be very big, ever, then the problem is probably non-existant. But, never make the mistake of testing with smaller data sets when your client will end up with large ones.

这篇关于表变量上的SQL记录集...好吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！