sql执行速度非常慢 [英] Sql execution speed very slow

问题描述

我有for循环，并在该循环内我已经使用INSERT INTO命令75000+值。当我运行它消耗更多的时间。如何提高插入速度，...

在此先感谢...
rgs
tharindu

<如果你有一个循环与75K插入，你做错了

基于您的意见（

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $; ）OVER（ORDER BY c.id） - 1 AS rownum
FROM sys.columns c CROSS JOIN start_number c2 CROSS JOIN start_number c3
）
SELECT
c.rownum + m。 start_number
FROM
mastertable m
CROSS JOIN
cNumbers c
WHERE
c.rownum< = no_of_items

有更好的方法来生成行，但是这会更好的循环75k次。

编辑：同样的想法适用于大多数的RDBMS，除了没有窗口功能的MySQL ...
在这种情况下，我将有一个数字表填写1-100000的例子

I have for loop and within that loop I have used INSERT INTO command for 75000+ values. when i am running it consumes more time. how can I improve insertion speed,...

thanks in advance... rgs tharindu

解决方案

If you have a loop with 75k inserts, you're doing it wrong

Based on your comments, you need something to gererate rows for you.

;WITH cNumbers AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY c.id) - 1 AS rownum
    FROM sys.columns c CROSS JOIN start_number c2 CROSS JOIN start_number c3
)
SELECT
    c.rownum + m.start_number
FROM
    mastertable m
    CROSS JOIN
    cNumbers c
WHERE
    c.rownum <= no_of_items

There are better ways to generate rows but this will be better then looping 75k times.

Edit: the same idea applies to most RDBMS except MySQL which doesn't have Windowing functions... in which case I'd have a Numbers table filled with 1-100000 for examplke

这篇关于sql执行速度非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！