Question

I am asking a question that is related to Execute multiple SQL commands in one round trip but not exactly the same because I am having this problem on a much bigger scale:

I have an input file with many different SQL commands (ExecuteNonQuery) that I have to process with a .net application. Example:

INSERT INTO USERS (name, password) VALUES (@name, @pw); @name="abc"; @pw="def";
DELETE FROM USERS WHERE name=@name; @name="ghi";
INSERT INTO USERS (name, password) VALUES (@name, @pw); @name="mno"; @pw="pqr";

所有的指挥都有参数,因此我喜欢蚊帐提供的参数机制。但我的申请必须阅读这些声明,并在可接受的时限内执行。可在一份单一档案中填入多万。

我首先想的是利用Skcoand的参数,因为这确实是适当做到这一点的途径(参数被蚊帐越走),但我不得不等待每名指挥部的50msec(与亚洲开发银行服务器的网络通信......)。我需要掌握指挥系统。

我的第二个想法是逃脱并插入一些参数,因此,我可以在一个部族中把多个指挥结合起来:

INSERT INTO USERS (name, password) VALUES ( abc ,  def ); DELETE FROM USERS WHERE name=@name; @name= ghi ; INSERT INTO USERS (name, password) VALUES ( mno ,  pqr );

然而,我确实对这一解决办法感到不安,因为如果没有预先确定的职能,我不想回避自己的意见。

你会做些什么? 感谢克里斯·克里斯托先生的回答。

Answer 1

假设投入中的所有内容都是有效的,我这样做是:

Parse out the parameter names and values
Rewrite the parameter names so they are unique across all queries (i.e., so you would be able to execute two queries with a @name parameter in the same batch)
Group together a bunch of queries into a single batch and run the batches inside a transaction

为什么你(如你)在单批打中能够开车,是因为在单批打中,参数限制为2100(至少当我与服务器做同样的事时);视你的业绩而定,你想要打上批号。 250-500人为我的工作量做了最佳工作;YMMV。

我将做的一件事情是多面的。如果投入是任意的,如果业务次序很重要,方案就没有任何想法;因此,你可以开始将询问分开,同时处理。

诚然,只要你能够以某种方式向服务器提出询问,你就会重新出现。只有“数千”发言,整个进程赢得了很长的胜利。 (我写过的申请必须作数以百万计的发言。)

Answer 2

Interesting dilemma.. I would suggest any of these:

Have control over sql server? create a stored procedure that loads the file and do the work
Use sqlcommand, but then cache the parameters, and read only command type (delete, insert, etc) and the values from each line to execute. Parameter caching examples here, here, and here.
Use multiple threads.. A parent thread to read the lines and send them over to other threads: one to do the inserts, another to do the deletion, or as many as needed. Look at Tasks

友情链接