Good point! The query prepare can take lot of time.
Do you have any idea about how to do the same thing via script? Suppose that
you do not have IBO or another set of components and want to do this with
isql.... I wonder if it is possible...
Ales

> -----Original Message-----
> From: Svein Erling Tysvaer
> [mailto:svein.erling.tysvaer@...]
> Sent: Wednesday, October 30, 2002 2:30 PM
> To: ib-support@yahoogroups.com
> Subject: Re: [ib-support] Simple challenge
>
>
> Clément,
> first a simple question: Why are you using TimeStamp and not
> Date when you
> do not insert any time?
>
> Your inserts are bound to lose the competition, every
> statement has to be
> prepared before executed and prepare is time consuming
> (assuming it to take
> one second each it would have taken one and a half week. It
> may not take
> that long, but you are definitely talking about days for your
> script to
> execute). What I would do, would be to write a very simple
> program like
> (I'm normally using IBO with Delphi):
>
> TIB_DSQL.IB_Transaction:=TIB_Transaction1;
> Count:=0;
> Try
> TIB_DSQL1.SQL.Text:='INSERT INTO TB_LargeTable VALUES (:OneID,
> :OneStatus, :OneDate)';
> TIB_DSQL1.Prepare; //Query or Cursor will be slower.
> repeat
> read and parse each line - that bit is up to you to do
> efficiently.
> TIB_DSQL.Params[0].AsInteger:=ClementOneID;
> //ParamByName is too time
> consuming
> TIB_DSQL.Params[1].AsString:=ClementOneStatus;
> TIB_DSQL.Params[2].AsDate:=ClementOneDate; //This one
> you have of
> course set to an acceptable date format.
> TIB_DSQL.Execute;
> inc(Count);
> if Count MOD 20000 = 0 then
> TIB_Transaction1.Commit;
> until eof(myfile)
> Finally
> if TIB_Transaction1.TransactionIsActive then
> TIB_Transaction1.Commit;
> end;
>
> I normally use a transaction isolation of tiCommitted, but I
> do not know
> whether the isolation does matter when it comes to speed. You
> may also want
> a connect and disconnect to your TIB_Connection and I set 20000 just
> randomly - it may be that 10000 or 100000 will give even better
> performance. Remember to tell us how "we" did in the competition!
>
> HTH,
> Set
>
> At 12:01 30.10.2002 +0000, you wrote:
> >Hi all,
> >
> >My customer has challenged me and other 3rd part developers
> to prepare
> >some tests for different DBMS.
> >They have given us a sort of 'pseudo query SQL statement'
> that we must
> >implement and run against the databases we are choosing...
> >
> >This one is very simple... insert 1 milion records.
> >
> >Here is the table
> >
> >create table TB_LargeTable (
> > OneID INTEGER,
> > OneStatus char(1),
> > OneDate TimeStamp
> > );
> >
> >
> >they sent us a script with a milion rows of:
> >
> > insert into TB_LargeTable values ( 1 , 'A' , '20020101' );
> > insert into TB_LargeTable values ( 2 , 'C' , '20020101' );
> > insert into TB_LargeTable values ( 3 , 'A' , '20020102' );
> > .... until a milion
> > insert into TB_LargeTable values ( 1000000 , 'T' , '20020821' );
> >
> >What would be the best way to run this script?
> >Will FB understand this date format YYYYMMDD?
> >( Of course I have tried to run the script. After 2 hours nothing has
> >been done, I figured out that I was missing something.)
> >Is there a native tool (ie. isql) that could handle file this large?
> >How can I use it?
> >
> >Please don´t get me wrong , but others has ran this same script for
> >different RDMS and they run just fine in under an our. I guess I must
> >use the right tool to run this script.
> >All the systems are the same. Win2k Pro 512 MB, 60 Gbytes.
> >All the RDBMS are installed locally, so we are running the script on
> >the same machine where the SQL Server is.
> >
> >
> >Best regards,
> >Clément
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~-->
> Get 128 Bit SSL Encryption!
> http://us.click.yahoo.com/JjlUgA/vN2EAA/kG8FAA/67folB/TM
> --------------------------------------------------------------
> -------~->
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/