在实时环境中有效地将数据从一个 SQL 数据库转换到另一个 SQL 数据库
我们的数据库情况有点混乱。
我们的主要后台系统是用 Visual Fox Pro 编写的，带有本地数据（是的，我知道！）
为了有效地处理我们网站中的数据，我们选择定期将数据导出到 SQL 数据库。 然而，执行此操作的过程基本上每次都会清除表并重新插入。
这意味着我们有两个 SQL 数据库 - 一个用于 FoxPro 导出过程写入，另一个用于我们的网站读取。
这个问题涉及从一个 SQL 数据库到另一个 SQL 数据库的转换（SqlFoxProData -> SqlWebData）。
对于特定的表（我们的主要应用程序表之一），由于在此过程中会发生各种数据转换，因此它不是使用自连接的简单 UPDATE、INSERT 和 DELETE 语句，而是我们必须使用游标（我知道！）
这已经工作了好几个月，但现在我们开始在更新时遇到性能问题（这可能在白天定期发生）
基本上当我们从 SqlFoxProData.ImportantTable 更新 SqlWebData.ImportantTable 时，它会导致实时网站上偶尔会出现连接超时/死锁/其他问题。
我一直在努力优化查询、缓存等，但现在我正在寻找另一种策略来更新数据。
我想到的一个想法是拥有两个重要表的副本（A和B），其中一个表当前处于“活动”状态的一些概念，更新非活动表，然后切换当前的活动表，
即网站从重要表A读取，同时我们正在更新ImportantTableB，然后我们切换网站以从ImportantTableB 中读取。
问题是，这可行并且是个好主意吗？ 我以前做过类似的事情，但我不相信它一定有利于优化/索引等。
欢迎任何建议，我知道这是一个混乱的情况......长期目标是让我们的 FoxPro 应用程序指向SQL。
（如果有帮助的话，我们正在使用 SQL 2005）
我应该补充一点，数据一致性在实例中并不是特别重要，因为数据总是稍微过时
We have a bit of a messy database situation.
Our main back-office system is written in Visual Fox Pro with local data (yes, I know!)
In order to effectively work with the data in our websites, we have chosen to regularly export data to a SQL database. However the process that does this basically clears out the tables each time and does a re-insert.
This means we have two SQL databases - one that our FoxPro export process writes to, and another that our websites read from.
This question is concerned with the transform from one SQL database to the other (SqlFoxProData -> SqlWebData).
For a particular table (one of our main application tables), because various data transformations take places during this process, it's not a straightforward UPDATE, INSERT and DELETE statements using self-joins, but we're having to use cursors instead (I know!)
This has been working fine for many months but now we are starting to hit upon performance problems when an update is taking place (this can happen regularly during the day)
Basically when we are updating SqlWebData.ImportantTable from SqlFoxProData.ImportantTable, it's causing occasional connection timeouts/deadlocks/other problems on the live websites.
I've worked hard at optimising queries, caching etc etc, but it's come to a point where I'm looking for another strategy to update the data.
One idea that has come to mind is to have two copies of ImportantTable (A and B), some concept of which table is currently 'active', updating the non-active table, then switching the currenly actice table
i.e. websites read from ImportantTableA whilst we're updating ImportantTableB, then we switch websites to read from ImportantTableB.
Question is, is this feasible and a good idea? I have done something like it before but I'm not convinced it's necessarily good for optimisation/indexing etc.
Any suggestions welcome, I know this is a messy situation... and the long term goal would be to get our FoxPro application pointing to SQL.
(We're using SQL 2005 if it helps)
I should add that data consistency isn't particularly important in the instance, seeing as the data is always slightly out of date
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
有很多方法可以给这只猫剥皮。
我会首先解决锁定问题。 我很少使用游标，我认为改进性能和锁定行为可能会解决您的很多问题。
我希望我可以通过使用两个单独的临时表来解决它。 一种用于 SQL 中的 FoxPro 导出，另一种并行转换为 SQL 中的最终格式。 然后，使用 sp_rename 将最终表交换为生产，或者简单地使用 3 个 INSERT/UPDATE/DELETE 事务将最终表中的所有更改应用到生产。 无论哪种方式，都会有一些锁定，但我们谈论的是多大呢？
There are a lot of ways to skin this cat.
I would attack the locking issues first. It is extremely rare that I would use CURSORS, and I think improving the performance and locking behavior there might resolve a lot of your issues.
I expect that I would solve it by using two separate staging tables. One for the FoxPro export in SQL and one transformed into the final format in SQL side-by-side. Then either swapping the final for production using sp_rename, or simply using 3 INSERT/UPDATE/DELETE transactions to apply all changes from the final table to production. Either way, there is going to be some locking there, but how big are we talking about?
您应该能够为网站维护一个数据库，然后从另一个 SQL 数据库表复制到该表。
这是假设您不更新网站本身的任何数据。
You should be able to maintain one db for the website and just replicate to that table from the other sql db table.
This is assuming that you do not update any data from the website itself.
“对于特定的表（我们的主要应用程序表之一），因为在此过程中会发生各种数据转换，所以它不是使用自连接的简单 UPDATE、INSERT 和 DELETE 语句，但我们必须使用游标来代替（我知道！）”
我想不出需要使用游标执行插入、更新或删除的情况。 如果可以为游标编写选择，则可以将其转换为插入、更新或删除。 您可以在这些语句中连接到其他表，并使用 case 语句进行条件处理。 花时间以基于集合的方式执行此操作可能会解决您的问题。
如果您有大量数据需要移动，您可以考虑一件事。 我们偶尔会创建一个所需数据的视图，然后有两个表 - 一个处于活动状态，另一个用于将数据加载到其中。 当数据加载完成后，作为流程的一部分，运行一个简单的命令将视图使用的表切换到您刚刚完成加载的表。 这样用户最多只会宕机几秒钟。 当他们在您加载时尝试访问数据时，您不会产生锁定问题。
您还可以考虑使用 SSIS 来移动数据。
"For a particular table (one of our main application tables), because various data transformations take places during this process, it's not a straightforward UPDATE, INSERT and DELETE statements using self-joins, but we're having to use cursors instead (I know!)"
I cannot think of a case where I would ever need to perform an insert, update or delete using a cursor. If you can write the select for the cursor, you can convert it into an insert, update or delete. You can join to other tables in these statements and use the case stament for conditional processing. Taking the time to do this in a set -based fashion may solve your problem.
One thing you may consider if you have lots of data to move. We occassionally create a view to the data we want and then have two tables - one active and one that data will be loaded into. When the data is finsihed loading, as part of your process run a simple command to switch the table the view uses to the one you just finshed loading to. That way the users are only down for a couple of seconds at most. You won't create locking issues where they are trying to access data as you are loading.
You might also look at using SSIS to move the data.
您是否可以选择使更新更加原子化，而不是声明的“清除并重新插入”？ 我认为 Visual Fox Pro 支持触发器，对吗？ 对于您的关键表，您是否可以向更新/插入/删除添加触发器以捕获更改的记录的 ID，然后仅移动（或删除）这些记录？
或者将所有更改写入脱机数据库并让 SQL Server 复制负责同步怎么样？
[抱歉，如果我有足够的声誉，这将是一条评论！]
Do you have the option of making the updates more atomic, rather than the stated 'clear out and re-insert'? I think Visual Fox Pro supports triggers, right? For your key tables, can you add a trigger to the update/insert/delete to capture the ID of records that change, then move (or delete) just those records?
Or how about writing all changes to an offline database, and letting SQL Server replication take care of the sync?
[sorry, this would have been a comment, if I had enough reputation!]
根据您上面对 Ernie 的回复，您询问了如何复制数据库。 以下是 Microsoft 关于 SQL2005 中复制的操作方法。
但是，如果你问的是复制以及如何做，这对我来说表明你对 SQL Server 的经验有点浅。 话虽如此，很容易把事情搞砸，虽然我完全赞成通过经验学习，但如果这是关键任务数据，那么您最好聘请 DBA 或至少测试 #$@#$在实际实施之前，先从其中取出 %。
Based on your response to Ernie above, you asked how you replicate databases. Here is Microsoft's how-to about replication in SQL2005.
However, if you're asking about replication and how to do it, it indicates to me that you are a little light in experience for SQL server. That being said, it's fairly easy to muck things up and while I'm all for learning by experience, if this is mission critical data, you might be better off hiring a DBA or at the very least, testing the #$@#$% out of this before you actually implement it.