如何正确和有效地重用C#.NET(SQL Server)中的准备好的语句? [英] How to correctly and efficiently reuse a prepared statement in C# .NET (SQL Server)?
问题描述
我看了很多问题,但是很明显我的 SO-fu 不能胜任这项任务,所以我来了。我试图有效地使用准备好的语句,而不仅仅是指对单个语句进行参数化,而是要对其进行多次编译以供重用。我的问题在于参数和重用以及如何正确实现。
I looked at lots of questions but evidently my SO-fu isn't up to the task, so here I am. I am trying to efficiently use prepared statements, and I don't just mean parameterizing a single statement, but compiling one for reuse many times. My question lies around the parameters and reuse and how to implement that correctly.
通常,我遵循此过程(人为的示例):
Generally I follow this procedure (contrived example):
SqlConnection db = new SqlConnection(...);
SqlCommand s = new SqlCommand("select * from foo where a=@a", db);
s.Parameters.Add("@a", SqlDbType.VarChar, 8);
s.Prepare();
...
s.Parameters["@a"] = "bozo";
s.Execute();
超级好用。但是,我不想每次运行此查询时都执行所有这些步骤(或后四个步骤)。这似乎抵消了准备好的语句的整个想法。在我看来,我只需要更改参数并重新执行,但问题是如何执行?
Super, that works. However, I don't want to do all of these steps (or the latter four) every time I run this query. That seems like it's counteracting the whole idea of prepared statements. In my mind I should only have to change the parameters and re-execute, but the question is how to do that?
我尝试了 s。 Parameters.Clear()
,但这实际上会删除参数本身,而不仅仅是值,因此我基本上需要重新添加
并再次准备
,这似乎也打破了重点。
I tried s.Parameters.Clear()
, but this actually removes the parameters themselves, not just the values, so I would essentially need to re-Add
the parameters and re-Prepare
again, which would seem to break the whole point as well. No thanks.
这时,我需要遍历 s.Parameters
并将它们全部设置为null或其他一些价值。 这对吗?不幸的是,在我当前的项目中,我有约15个参数的查询,每次运行都需要执行约10,000次。我可以将这种迭代分解为一个方法,但是想知道是否有更好的方法(没有存储的proc)。
At this point I am left with iterating through s.Parameters
and setting them all to null or some other value. Is this correct? Unfortunately in my current project I have queries with ~15 parameters which need to be executed ~10,000 times per run. I can shunt this iteration off into a method but was wondering if there is a better way to do this (without stored procs).
我目前的解决方法是扩展方法, SqlParameterCollection.Nullify
,将所有参数设置为null,这对于我的情况很好。我只是在执行后运行它。
My current workaround is an extension method, SqlParameterCollection.Nullify
, that sets all the parameters to null, which is fine for my case. I just run this after an execute.
我发现了一些几乎相同但(IMHO)未解决的问题:
I found some virtually identical but (IMHO) unanswered questions:
SQLite / C#连接池和预准备的语句混淆(Serge非常接近回答!)
SQLite/C# Connection Pooling and Prepared Statement Confusion (Serge was so close to answering!)
我能找到的最佳答案是(1)以上常识和(2)此页面:
The best answer I could find is (1) common sense above and (2) this page:
http://msdn.microsoft.com/en-us/magazine/cc163799.aspx
推荐答案
重新使用准备好的SqlCommand时,您肯定需要做的就是将参数值设置为新值?使用完后,您无需清除它们。
When re-using a prepared SqlCommand, surely all you need to do is set the parameter values to the new ones? You don't need to clear them out after use.
对于我自己来说,最近十年来我还没有看到DBMS的产生,从准备中可以得到任何明显的好处一条语句(我想如果DB Server可能达到其CPU的极限,但这不是典型的)。您确定必须进行准备吗?
For myself, I haven't seen a DBMS produced in the last 10 years which got any noticeable benefit from preparing a statement (I suppose if the DB Server was at the limits of its CPU it might, but this is not typical). Are you sure that Preparing is necessary?
除非您从外部来源上传,否则运行〜10,000次每次运行相同的命令对我来说有点难闻。在这种情况下,批量加载可能会有所帮助?每次跑步在做什么?
Running the same command "~10,000 times per run" smells a bit to me, unless you're uploading from an external source. In that case, Bulk Loading might help? What is each run doing?
欢呼-
这篇关于如何正确和有效地重用C#.NET(SQL Server)中的准备好的语句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!