blocks|key|3282187|text|按照我读到的方式，您是说在用户按下"GO“并启动脚本之前，模式中甚至不存在插入数据的表？然后创建创建表的嵌入式SQL语句，并在循环中插入行1×1？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3282188|那是..。好吧，这么说吧，我不会这样做。|3282189|您不知道模式会是什么样子吗？如果这样做，然后根据该表绘制数据存储，并使用ds_1.Update()生成INSERT语句。使用数据窗口来实现它的好处。|3282190|如果这是不可能的，并且必须使用嵌入式SQL，那么至少每1000行执行一次提交。否则，SQLServer就会在表上建立撤消日志，以防出了问题而不得不回滚。|3282191|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

The way I read this, you're saying that the table that the data is being inserted into doesn't even exist in the schema until the user presses "GO" and initiates the script? And then you create embedded SQL statements that create the table, and insert rows 1 by 1 in a loop?

That's... Well, let's just say I wouldn't do it this way. 

Do you not have any idea what the schema will look like ahead of time? If you do, then paint the datastore against that table, and use ds_1.Update() to generate the INSERT statements. Use the datawindow for what it's good for.

If that's not possible, and you must use embedded SQL, then at least perform a COMMIT every 1000 rows or so. Otherwise, SQLServer is building up UNDO logs against the table, in case something goes wrong and they have to be rolled back.

blocks|key|2592277|text|不确定它是否会更快，但您可以将数据存储保存在标签分隔的文件中，然后通过Sql进行批量插入。有点像|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2592278|从“c：\csvtest.txt”开始大容量插入c：\csvtest.txt(+FIELDTERMINATOR+=+'\t'，ROWTERMINATOR+=+'\n‘)+GO|2592279|您可以尝试通过ds.object.datawindow.data语法将数据存储内容保存到字符串变量中，然后将其保存到文件中，然后执行SQL。|2592280|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Not sure it would be faster but you could save the data from the datastore in a tab delimited file then do a BULK INSERT via Sql. Something like

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
GO

You can try saving the datastore contents into a string variable via ds.object.datawindow.data syntax then save that to a file then execute the SQL.

blocks|key|2893730|text|其他想法..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2893731|在更新时禁用更新表上的触发器(如果可能的话)|unordered-list-item|2893732|使用PB管道对象，它具有提交的设置--可能会更快，但不会太多。|2893733|最好的主意。在服务器端做点什么。我尝试为您的40K插入创建SQL语句，并调用一个存储过程，发送所有40k+insert/update语句，并让存储过程处理插入/更新。|2893734|创建一个包含几个列的虚拟表，其中一个列是长文本，使用前面提到的SQL语句块对其进行更新，并有一个分隔和执行sql语句的进程。|2893735|以上的一些变体，但使用批量插入，如马特提到的。大容量插入是插入许多行的最快方法。|2893736|也许可以尝试一些自动提交的方法，以便您只在最后提交，或者像已经有人提到的那样每10k行提交一次。|2893737|PB在事务对象(connection)中有一个异步选项，也许您可以让更新在后台进行，让用户继续。这并不适用于所有数据库，在您的情况下也可能不起作用。我在使用异步选项时运气不佳。|2893738|2893739|2893740|您的进程如此缓慢的原因是PB分别执行每个更新，因此您不断地访问网络和数据库。在update表上可能会有触发器，这些触发器也会被锤击。在服务器上猛击它们可以消除网络延迟，而且速度要快得多。使用大容量加载比以往任何时候都要快，因为它没有运行触发器，并且消除了许多数据库管理开销。|2893741|扩展将SQL语句发送到过程的思想，您可以很容易地通过执行dw_1.saveas(+SQL！)来创建sql。(语法不正确)，并立即将其发送到服务器。让服务器解析它并运行SQL。|2893742|通过过程将类似的内容发送到服务器，它应该更新得相当快，因为它只是一条语句：|2893743|Update+TABLE+set+(col1,+col2)+values+('a',+'b')%7CUpdate+TABLE+set+(col1,+col2)+values+('a',+'b')%7CUpdate+TABLE+set+(col1,+col2)+values+('a',+'b')|code-block|syntax|javascript|2893744|在程序上：|2893745|解析sql语句并运行它们。很简单。|2893746|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1A|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|1B|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|1C|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|1D|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|1E|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|1F|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1J|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1K|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1L|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|10|7|1M|8|@]|9|@]|A|$11|12]]|$1|13|3|14|5|6|7|1N|8|@]|9|@]|A|$]]|$1|15|3|16|5|6|7|1O|8|@]|9|@]|A|$]]|$1|17|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]]|18|$]]

Other ideas...

<ul>
<li>Disable triggers on the updated table while it is being updated (if possible)</li>
<li>Use the PB Pipeline object, it has settings for commit- might be faster but not much.</li>
<li>Best idea. Do something on the server side. I'd try to create SQL statements for your 40K inserts, and call a stored procedure sending all 40k insert/update statements and let the stored procedure handle the inserts/updates.</li>
<li>Create a dummy table with a few columns, one being a long text, update it with a block of SQL statements like mentioned in last idea and have a process that delimits and executes the sql statements.</li>
<li>Some variant of above but using bulk insert as mentioned by Matt. Bulk insert is the fastest way to insert many rows.</li>
<li>Maybe try something with autocommit so that you commit only at the end, or every 10k rows as mentioned by someone already.</li>
<li>PB has an async option in the transaction object (connection) maybe you could let the update go in the background and let the user continue. This doesn't work with all databases and may not work in your situation. I haven't had much luck using async option.</li>
</ul>

The reason your process is so slow is that PB does each update separately, so you are hitting the network and database constantly. There may be triggers on the update table and those are getting hammered too. Slamming them in on the server eliminates network lag and is much faster. Using bulk load is ever faster yet because it doesn't run triggers and eliminates a lot of the database management overhead. 

Expanding on the idea of sending SQL statements to a procedure, you can create the sql very easily by doing a dw_1.saveas( SQL! ) (syntax is not right) and send it to the server all at once. Let the server parse it and run the SQL.

Send something like this to the server via procedure, it should update pretty fast as it is only one statement:

<pre><code>Update TABLE set (col1, col2) values ('a', 'b')|Update TABLE set (col1, col2) values ('a', 'b')|Update TABLE set (col1, col2) values ('a', 'b')
</code></pre>

In procedure:

Parse the sql statements, and run them. Easy peasy.

blocks|key|2592388|text|虽然马特的回答可能是最好的，但我还有另一个选择。(选择是好的，对吗？)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2592389|我不知道为什么要回避datastore.Update()方法。我假设这是因为在更新时模式不存在。如果这是唯一的原因，那么仍然可以使用它，从而消除了40,000个字符串操作实例来生成有效的SQL。|2592390|要做到这一点，首先要创建表。然后，使用datastore.SyntaxFromSQL()创建绑定到表的数据存储。可能需要几个修改()语句才能使数据存储更新成为可能。然后将数据从原始数据存储移到可更新的绑定数据存储。(看看RowsMove()或点表示法。)之后，Update()语句生成所有SQL，而不需要字符串解析和循环开销。|2592391|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

While Matt's answer is probably best, I have another option. (Options are good, right?)

I'm not sure why you're avoiding the datastore.Update() method. I'm assuming it's because the schema doesn't exist at the time of the update. If that's the only reason, it can still be used, thus eliminating 40,000 instances of string manipulation to generate valid SQL.

To do it, you would first create the table. Then, you would use datastore.SyntaxFromSQL() to create a datastore that's bound to the table. It might take a couple of Modify() statements to make the datastore update-able. Then you'd move the data from your original datastore to the update-able, bound datastore. (Look at RowsMove() or dot notation.) After that, an Update() statement generates all of your SQL without the overhead of string parsing and looping.

We have a datastore (powerbuilder datawindow's twin sister) that contains over 40.000 rows, which takes more than 30 minutes to insert into a Microsoft SQL Server table. 

Currently, I am using a script generator that generates the sql table definition and an insert command for each row. At the end, the full script to sql server for execution.

I have already found that script generation process consumes more than 97% of the whole task.

Could you please help me finding a more efficient way of copying my client's data to sql server table?

Edit1 (after NoazDad's comments):

Before answer, please bear in mind that:

<ul>
<li>Tabel structure is dynamic;</li>
<li>I am trying to avoid using datastore.Update() method;</li>
</ul>

What is the fastest way of copying a data from a DataWindow/DataStore to SQL Server table using Powerbuilder

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我们有一个数据存储(powerbuilder datawindow的孪生姐妹)，它包含40.000行，它需要超过30分钟才能插入Microsoft 表。目前，我正在使用脚本生成器来生成sql表定义和每个行的insert命令。最后，将完整的脚本提交给sql server执行。我已经发现脚本生成过程消耗了整个任务的97%以...

问使用Powerbuilder将数据从DataWindow/DataStore复制到Server表的最快方法是什么
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Powerbuilder将数据从DataWindow/DataStore复制到Server表的最快方法是什么EN