ksh批处理

发布于 2024-10-19 05:28:07 字数 481 浏览 5 评论 0原文

我有一个 shell 脚本，它从文件中逐行读取用户 ID 并将记录插入数据库。但是插入过程很慢，我想通过批量进行优化。我无法将整个文件作为一批使用，因为事务大小超出了专有限制。这是脚本：

for user in `cat $userlist`
do 
echo "processing user $user"
{
    echo "begin"
        i=1
        while [[ $i -le 30 ]] ; do
        echo "insert into usertab values ($user,-1,\"\",-1)" 
        i=$(expr $i + 1)
        done
        echo "commit"
} | propSql userDb -
done

Begin 和 commit 是该数据库的标准关键字。在这里，我不想一次处理一个用户，而是一次处理 10 个用户。有人可以建议所需的改变吗？

原文

I have a shell script that reads the user id's line by line from a file and inserts records into the database. However insertion process is slow and I want to optimize by taking in batches. I cannot use entire file as one batch as the transaction size exceeds the proprietary limits. Here is the script:

for user in `cat $userlist`
do 
echo "processing user $user"
{
    echo "begin"
        i=1
        while [[ $i -le 30 ]] ; do
        echo "insert into usertab values ($user,-1,\"\",-1)" 
        i=$(expr $i + 1)
        done
        echo "commit"
} | propSql userDb -
done

Begin and commit are standard key words for this db. Here, instead of processing one user at a time, I want to process 10 users at a time. Can some one suggest the change needed?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

愿与i 2024-10-26 05:28:07

这展示了一次累积 10 个用户的方法。您需要修改它以适应您需要数据来查找 insert 语句的方式。我保留了内部循环，但将其更改为 for 循环，因为这使得所有控制都发生在一个地方。不过，您可能不需要该循环。

process () {
   # Somewhere in here you'll use the value of "$@"
   echo "begin"
        # you may not need this loop any more
        for ((i=1; i<30; i++))
        do
            echo "insert into usertab values ($user,-1,\"\",-1)" 
        done
    echo "commit"
}

j=0
while read -r user
do 
    echo "processing user $user"
    if ! (( j++ % 10 ))    # every tenth user, do a database operation
    then
        process "$accum" | propSql userDb -
        accum=""
    fi
    accum+=" $user"    # accumulate user names
done < "$userlist"
process "$accum" | propSql userDb -    # one more time to get the remainder

请注意，这是使用 ksh93 语法编写的。如果您使用其他版本，则需要进行一些修改。

This shows a way to accumulate users 10 at a time. You will need to modify it to suit the way you need the data to look for the insert statement. I left the inner loop in place, but changed it to a for loop since that makes all the control happen in one place. You may not need that loop though.

process () {
   # Somewhere in here you'll use the value of "$@"
   echo "begin"
        # you may not need this loop any more
        for ((i=1; i<30; i++))
        do
            echo "insert into usertab values ($user,-1,\"\",-1)" 
        done
    echo "commit"
}

j=0
while read -r user
do 
    echo "processing user $user"
    if ! (( j++ % 10 ))    # every tenth user, do a database operation
    then
        process "$accum" | propSql userDb -
        accum=""
    fi
    accum+=" $user"    # accumulate user names
done < "$userlist"
process "$accum" | propSql userDb -    # one more time to get the remainder

Note that this is written using ksh93 syntax. If you're using another version, you'll need to make some modifications.

回复收藏 0 原文

~没有更多了~