I got following question: I have a table which i will read with a program, at the meantime this table will be refreshed everyday. But the refresh program is not reliable, it may fail at anytime. I got following solutions:
我得到了以下问题:我有一张桌子,我将用一个程序阅读,同时这张桌子每天都会刷新。但刷新程序不可靠,可能随时失败。我有以下解决方案:
1. The refresh program writes data to a tmp table, after success, i drop the read table, then rename the tmp table to read table.
1.刷新程序将数据写入tmp表,成功后,删除读取表,然后将tmp表重命名为读取表。
2. I create three tables T1, T2, S. T1 and T2 are the data tables. S is the status table recording the table(T1 or T2) for read. The refresh program first query the S for current read table, then another is the write table, loading data into write table, after success, updating S making the write table to be the read table. Every time, the query program first fetch the read table from S, then read data from it.
2.我创建了三个表T1,T2,S。T1和T2是数据表。 S是记录表(T1或T2)以供读取的状态表。刷新程序首先查询S表示当前读表,然后另一个是写表,将数据加载到写表中,成功后,更新S使写表成为读表。每次,查询程序首先从S获取读表,然后从中读取数据。
I want to know which one is better and WHY? Or ur guys have any better solutions. Appreciate for ur help, thanks!
我想知道哪一个更好,为什么?或者你们有更好的解决方案。感谢您的帮助,谢谢!
1 个解决方案
#1
0
It really depends on your data model. Do you have constraints between tables? If so you'll need to delete/insert in the right order. Also which database are you using?
这实际上取决于您的数据模型。表之间有约束吗?如果是这样,您将需要以正确的顺序删除/插入。您还使用哪个数据库?
In your fisrt solution you drop the table. If you are using triggers or stored procedures or any other kind of database stuff it's gonna be invalid after the drop. So you'll need to recompile objects.
在你的fisrt解决方案中,你放弃了桌子。如果您正在使用触发器或存储过程或任何其他类型的数据库内容,那么在丢弃之后它将无效。所以你需要重新编译对象。
Also why you need to delete all rows in your table? You can add a date field, ie dupdated and keep for example a retention of 1 day or 5 days or anyway. Then you purge your data of previous day when process of load is done and validated.
还有为什么你需要删除表中的所有行?您可以添加日期字段,即重复日期,并保留例如1天或5天的保留期或无论如何。然后,在完成并验证加载过程时,清除前一天的数据。
#1
0
It really depends on your data model. Do you have constraints between tables? If so you'll need to delete/insert in the right order. Also which database are you using?
这实际上取决于您的数据模型。表之间有约束吗?如果是这样,您将需要以正确的顺序删除/插入。您还使用哪个数据库?
In your fisrt solution you drop the table. If you are using triggers or stored procedures or any other kind of database stuff it's gonna be invalid after the drop. So you'll need to recompile objects.
在你的fisrt解决方案中,你放弃了桌子。如果您正在使用触发器或存储过程或任何其他类型的数据库内容,那么在丢弃之后它将无效。所以你需要重新编译对象。
Also why you need to delete all rows in your table? You can add a date field, ie dupdated and keep for example a retention of 1 day or 5 days or anyway. Then you purge your data of previous day when process of load is done and validated.
还有为什么你需要删除表中的所有行?您可以添加日期字段,即重复日期,并保留例如1天或5天的保留期或无论如何。然后,在完成并验证加载过程时,清除前一天的数据。