How to Import a Datatable as fast as possible? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

How to Import a Datatable as fast as possible?

How to Import a Datatable as fast as possible?# Database - 数据库

n*u2008-09-11 07:09

1 楼

Sorry I can not write Chinese here.
In MySQL, I have two databases: newdb and olddb
table1 in newdb, table2 and table3 in olddb
table1 has userid, nickname, msn
table2 has userid, nickname
table3 has userid msn
First I use NaviCat to import table2 into table1, very fast! 320,000 records
in 2 mins!
Then I tried to import table 3 into table1, now I have to check userid first
, then UPDATE table1. Very slow. 50,000 records in 12 hours
Anyway I can make it fast?
Thanks

n*u2008-09-11 07:09

2 楼

If I innerjoin table2 and table3 first, will it be faster?

t*i2008-09-11 07:09

3 楼

yes you should join first then import.
My estimate is the total time would be comparable to only import table2.

n*u2008-09-11 07:09

4 楼

How long will it take to inner join table2 and table 3 (each has 320,00
records)

【在 t*********i 的大作中提到】

: yes you should join first then import.
: My estimate is the total time would be comparable to only import table2.

t*i2008-09-11 07:09

5 楼

That really depend on your system, how much memory, how much undo space ( I
am from Oracle side, but mysql should have have similar structure), do you
have indexes on tables or not. You need to talk to your DBA to know that.
If I need to do this, I probably will do the join and insert in chunks(id in
certain range is good candidate; again, you need to have good index in
place). commit in chunks will reduce requirement to system resources.
.........................................................

n*u2008-09-11 07:09

6 楼

Thanks for your reply.
I test INNER JOIN:
INNER JOIN table 2 and table 3, 40 seconds (not sure it's good or bad for
320,000 records)
So I started to work on my real task
INNER JOIN table 2, table 3 and table 4, 3 mins (320,000 records)
export result to dbf file, 10 mins
import dbf into Table1, 10 mins
Finally I finished my task in half hour, instead of 3 days.
Now how can I improve export and import process?
PS. I am a .net programmer, but team leader asked me to migrate database.
The next task

B*g2008-09-11 07:09

7 楼

1. check if table2, table3 has primary key userid.
if not, add.
2.
insert into tables1(userid, nickname, msn)
select a.userid, a.nickname, b.msn
from table2 a, table3 b
where a.userid = b.userid.
1. check if table1 has primary key userid.
if not, add.
Note: IF userid is not unique in table 2, table3, kick the person who design
these tables.

records
first

【在 n****u 的大作中提到】

: Sorry I can not write Chinese here.
: In MySQL, I have two databases: newdb and olddb
: table1 in newdb, table2 and table3 in olddb
: table1 has userid, nickname, msn
: table2 has userid, nickname
: table3 has userid msn
: First I use NaviCat to import table2 into table1, very fast! 320,000 records
: in 2 mins!
: Then I tried to import table 3 into table1, now I have to check userid first
: , then UPDATE table1. Very slow. 50,000 records in 12 hours

B*g2008-09-11 07:09

8 楼

tell them to start to use sql server, hehe

【在 n****u 的大作中提到】

: Thanks for your reply.
: I test INNER JOIN:
: INNER JOIN table 2 and table 3, 40 seconds (not sure it's good or bad for
: 320,000 records)
: So I started to work on my real task
: INNER JOIN table 2, table 3 and table 4, 3 mins (320,000 records)
: export result to dbf file, 10 mins
: import dbf into Table1, 10 mins
: Finally I finished my task in half hour, instead of 3 days.
: Now how can I improve export and import process?

n*u2008-09-11 07:09

9 楼

I love you both tangyuanlai and Beijing! hehe
Yes, I tried this way, it's sooooooo fast!
Insert new table
select
from inner join of old tables
21.891 ms (migrated 320,000 records)
Previously I used C# code, it would take me 1 week
Then I used NaviCat import wizard, it would take me 3 days
Yesterday I exported query result and imported into new table, it took me 30
mins
Now, 21.891 ms haha
Thank you very much!!!

B*g2008-09-11 07:09

10 楼

check record number in 3 tables see if they match.

30

【在 n****u 的大作中提到】

: I love you both tangyuanlai and Beijing! hehe
: Yes, I tried this way, it's sooooooo fast!
: Insert new table
: select
: from inner join of old tables
: 21.891 ms (migrated 320,000 records)
: Previously I used C# code, it would take me 1 week
: Then I used NaviCat import wizard, it would take me 3 days
: Yesterday I exported query result and imported into new table, it took me 30
: mins

n*u2008-09-11 07:09

11 楼

Thank you reminding me.
If table 2 has an id=5, but table 3 does not have this id, what can I do?

【在 B*****g 的大作中提到】

: check record number in 3 tables see if they match.
:
: 30

n*u2008-09-11 07:09

12 楼

I check the number
Table2: 319,928
Table3: 319,927
Table4: 319,927
So my result has 319,927 records
How to find this missing one?

【在 B*****g 的大作中提到】

: check record number in 3 tables see if they match.
:
: 30

B*g2008-09-11 07:09

13 楼

SELECT CASE
WHEN a.userid IS NULL
THEN b.userid
ELSE a.userid
END AS userid,
a.nickname,
b.msn
FROM table2 a FULL OUTER JOIN table3 b ON a.userid = b.userid

【在 n****u 的大作中提到】

: Thank you reminding me.
: If table 2 has an id=5, but table 3 does not have this id, what can I do?