Import of Text File with ~ 220000 records very slow
Posted: 27.02.2009, 21:51
Hello!
I´m using:
SQLite Admin 0.8.3.2 shows SQLite 3.5.1 in the statusbar
I try to import a Text File structured like this:
Fieldesc.:
PK|Fullpath|Filename|Extension|Size|MD5Complete|MD5Audio|PUID|TrackType|Album|Artist|Tracknumber|Title|AlbumSoundEx|ArtistSoundEx|TitleSoundEx|FilenameSoundEx|FileFormatValid|FileNameValid
Sample Data:
1|J:\mp3\Album\#\101 Strings Orchestra - Cigar Aficionado Smooth Sax\101 Strings Orchestra - 01 - Feelings.mp3|101 Strings Orchestra - 01 - Feelings|.mp3| 3439012|53C460ABB2266CB1AFDAC3F1A772AE94|884afccd44ed2779cd8a1718a119c384||Album|Cigar Aficionado Smooth Sax|101 Strings Orchestra|1|Feelings|C26612532532200000000000000|123652226223600000000|F4522000 |1236522262236145220000000000000000000|Y|Y
Each Field has his own index, and the table was created right from scratch.
The Import is VERY slow on a lets say decent PC running W2k8 x64.
Both the DB and the import file is on a Raid5 Disksubsystem which is capable to do around 300 MB/s.
CPU Usage is about 1%
The Textfile which i try to import has about 80 MB
When i look to the taskmanager i can´t believe my eyes:
i´ve turned on I/O Byte Read and I/O Bytes write Column and the SQLite Admin is at roughly 90% complete with the import process.
SQLite Admin has read so far: 1.8 GB
SQLite Admin has write so far: 11.5 GB
Also i notice that the HDD led is turned on like a powerled!
The system is not swapping and SQLite Admin use about 100MB of RAM
For the record: Import file has around 80MB, DB File has about 410 MB
Why SQL Admin write about 28 times the amount of data that should be imported?
Regards
Martin
I´m using:
SQLite Admin 0.8.3.2 shows SQLite 3.5.1 in the statusbar
I try to import a Text File structured like this:
Fieldesc.:
PK|Fullpath|Filename|Extension|Size|MD5Complete|MD5Audio|PUID|TrackType|Album|Artist|Tracknumber|Title|AlbumSoundEx|ArtistSoundEx|TitleSoundEx|FilenameSoundEx|FileFormatValid|FileNameValid
Sample Data:
1|J:\mp3\Album\#\101 Strings Orchestra - Cigar Aficionado Smooth Sax\101 Strings Orchestra - 01 - Feelings.mp3|101 Strings Orchestra - 01 - Feelings|.mp3| 3439012|53C460ABB2266CB1AFDAC3F1A772AE94|884afccd44ed2779cd8a1718a119c384||Album|Cigar Aficionado Smooth Sax|101 Strings Orchestra|1|Feelings|C26612532532200000000000000|123652226223600000000|F4522000 |1236522262236145220000000000000000000|Y|Y
Each Field has his own index, and the table was created right from scratch.
The Import is VERY slow on a lets say decent PC running W2k8 x64.
Both the DB and the import file is on a Raid5 Disksubsystem which is capable to do around 300 MB/s.
CPU Usage is about 1%
The Textfile which i try to import has about 80 MB
When i look to the taskmanager i can´t believe my eyes:
i´ve turned on I/O Byte Read and I/O Bytes write Column and the SQLite Admin is at roughly 90% complete with the import process.
SQLite Admin has read so far: 1.8 GB
SQLite Admin has write so far: 11.5 GB
Also i notice that the HDD led is turned on like a powerled!
The system is not swapping and SQLite Admin use about 100MB of RAM
For the record: Import file has around 80MB, DB File has about 410 MB
Why SQL Admin write about 28 times the amount of data that should be imported?
Regards
Martin