Re: [firebird-support] loading data from a .csv file?
Den 2014-05-18 21:23 skrev Alan Shank li...@notoriousshankbrothers.com [firebird-support] såhär: Is there some way to load data into a table in Firebird, like LOAD DATA INFILE... in Mysql? I have comma-delimited files from Mysql that I want to load into Firebird tables. I have Firebird2.5-super on Ubuntu 14.04, 64-bit. Thomas mentioned external tables. I've tried a couple of other data pump methods, both free and commercial import utilities as well as my own C# import loop (Windows and .Net in that case obviously). They all use a loop of insert statements, one for each .csv file row. While it works fine, it's generally very slow. I've now switched completely to external tables, or in some cases (not so many records) generating insert statements and executing them in one query batch. It does require that you modify the file, but it's extremely fast! I usually modify the format to fixed width with or without separator, matching a FB table with char columns (with an extra dummy column for each separator, if present, and an extra dummy column for the linebreak). It's easiest with a character set with a fixed number of bytes per character like iso 8859-1, ascii or win-1252, because FB expects each column to be the same number of bytes, not characters. I.e. with utf8 format it expects a char(10) column to consist of 40 byte in the file. The problem with that is that with text editors etc. such a string will appear as anything from 10 to 40 characters, depending on what characters you enter. If you enter ten 4 byte characters, that's the same byte length as 40 1 byte characters. So, such a file will not be fixed-width if you count characters. If you convert the format with some utility, you have to pad the strings to 40 byte, taking care to really count byte, not characters. With fixed width character sets, it's much easier. Then use an external table like this (if separators are present and line breaks in your system are 1 single character) create table External external file 'yourfilepath' ( DataCol1 char(10) character set iso88591, Sep1 char(1) character set iso88591, DataCol2 char(25) character set iso88591, Sep2 char(1) character set iso88591, DataCol3 char(7) character set iso88591, Linebreak char(1) character set iso88591 ); Then import into your real table making sure the char columns are appropriately converted: insert into MyTable ( SomeString, -- varchar(10) SomeDate, -- datetime SmallInteger -- int ) select trim(cast(DataCol1 as varchar(10)) SomeString, cast(DataCol2 as datetime) SomeDate, cast(DataCol3 as int) SmallInteger; Good luck! Kjell
Re: [firebird-support] Restore DB with Firebird 1.5 Super Server vs Classic Server
Guten Tag halim1...@yahoo.com [firebird-support], much too few information for any reasonable answer, so just the standart Cache settings ? ar way too low in staddart classic setup try increasing them Hello All, Our database is about 30 GB. We are using Firebird 1.5. When restoring the database using Super server install, the restore takes about 2.5 Hours but when we use Classic Server mode, the restore takes over 8 hours. Our machine is up to date as far as CPU and RAM(64 GB ). Any idea why this difference of restore speed? Thank you, -Halim -- Mit freundlichen Grüssen Hannes Streichermailto:hstreic...@gmx.de
[firebird-support] search table by array value using parameters
//In firebird example database employee is table 'job' //with string[] column 'language_req' // i have some code: fbcmd.CommandText = select language_req from job where job_code=SRep; fbreader = fbcmd.ExecuteReader (); Array array_value; if (fbreader.Read ()) array_value = (Array)fbreader[0]; fbreader.Close (); fbcmd.CommandText = select * from job where language_req = @par1; fbcmd.Parameters.AddWithValue (@par1,array_value); fbreader = fbcmd.ExecuteReader (); // ERROR - Cannot cast from source type to destination type //is this code wrong ? //what is solution for searching table by array values ?
RE: [firebird-support] Restore DB with Firebird 1.5 Super Server vs Classic Server
Thank you for your reply. I used default restore settings and I got over 8 hours of restore time. Increasing the cache size using gfix -b did not help at all. I got the same result. I don't think it helps during the restore process.-Halim To: firebird-support@yahoogroups.com From: firebird-support@yahoogroups.com Date: Sat, 24 May 2014 09:28:01 +0200 Subject: Re: [firebird-support] Restore DB with Firebird 1.5 Super Server vs Classic Server Guten Tag halim1...@yahoo.com [firebird-support], much too few information for any reasonable answer, so just the standart Cache settings ? ar way too low in staddart classic setup try increasing them Hello All, Our database is about 30 GB. We are using Firebird 1.5. When restoring the database using Super server install, the restore takes about 2.5 Hours but when we use Classic Server mode, the restore takes over 8 hours. Our machine is up to date as far as CPU and RAM(64 GB ). Any idea why this difference of restore speed? Thank you, -Halim -- Mit freundlichen Grüssen Hannes Streichermailto:hstreic...@gmx.de
Re: [firebird-support] Restore DB with Firebird 1.5 Super Server vs Classic Server
Hi, In order to give you a good optimization advice, please answer the following questions: http://ib-aid.com/en/articles/required-information-for-firebird-database-performance-optimization/ Regards, Alexey Kovyazin IBSurgeon Thank you for your reply. I used default restore settings and I got over 8 hours of restore time. Increasing the cache size using gfix -b did not help at all. I got the same result. I don't think it helps during the restore process. -Halim To: firebird-support@yahoogroups.com From: firebird-support@yahoogroups.com Date: Sat, 24 May 2014 09:28:01 +0200 Subject: Re: [firebird-support] Restore DB with Firebird 1.5 Super Server vs Classic Server Guten Tag halim1...@yahoo.com [firebird-support], much too few information for any reasonable answer, so just the standart Cache settings ? ar way too low in staddart classic setup try increasing them Hello All, Our database is about 30 GB. We are using Firebird 1.5. When restoring the database using Super server install, the restore takes about 2.5 Hours but when we use Classic Server mode, the restore takes over 8 hours. Our machine is up to date as far as CPU and RAM(64 GB ). Any idea why this difference of restore speed? Thank you, -Halim -- Mit freundlichen Grüssen Hannes Streicher mailto:hstreic...@gmx.de
