Adding records

[Damon Pillinger]

Hi guys,
I assume I just have it all wrong.
I am using this code to add records

  dim d as date
  dim ts,ts1 as double
  dim da as VDatabase
  dim f as FolderItem
  dim tblPerson as VTable
  dim i as integer

  f=GetFolderItem("test.vdb")

  da =new VDatabase

  if f.exists then
    da.Open(f)
  else
    da.Create(f)
    tblPerson = da.CreateTable("Person")
    call tblPerson.CreateVarCharField( "cCustomer", 1022,
EVFlag.fNullable)
    call tblPerson.CreateVarCharField( "cDate", 1022, EVFlag.fNullable)
    call tblPerson.CreateVarCharField( "cDuration", 1022,
EVFlag.fNullable)
    call tblPerson.CreateVarCharField( "cCalled", 1022,
EVFlag.fNullable)
  end if

  d=new date
  ts=Ticks

  tblPerson = da.Table( "Person" )

  for i= 1 to 10000
    d.month=rnd*12
    d.day=rnd*28
    d.hour=rnd*24
    d.minute=rnd*60
    d.second=rnd*60
    
    tblPerson.SetBlank()
    tblPerson.Field( "cCustomer" ).SetString(
format(rnd*100+40000,"#####") 
    tblPerson.Field( "cDate" ).SetString( d.SQLDateTime )
    tblPerson.Field( "cDuration" ).SetString( format(rnd*1000,"###") )
    tblPerson.Field( "cCalled" ).SetString( "03
"+format(rnd*10000+95920000,"########") )

    call tblPerson.AddRecord()
  next

  ts1=Ticks
  da.Close()
  da=nil
  msgbox format((ticks-ts)/50,"####.000")+" seconds
"+format((ticks-ts1)/50,"####.000")+" seconds"


But the fastest it will go is 10,000 records in 3.4 seconds 100,000 in
34 seconds

PS I figured out EVFlag.fNullable is 20% quicker than fIndexed which
made me happy.

Am I doing it the wrong way or is the code incorrect or is this the
fastest (not saying it is slow but Ruslan said I should be able to add
100,000 in 3.5 seconds) ?

RB Win 2005r1 Val 2.2 you should be able to cut and paste the above into
a pushbutton to test the second time in seconds is the time to close the
database.

Also found a faster SQL way than 

query = "INSERT INTO T1(f1,f2,f3) VALUES (?,?,?)"

Which is 

query = "INSERT INTO T1 VALUES (?,?,?)"

you just have to ensure the values are in the same order as the create
statement and it is 25% faster.

Also have looked at example Examples\API_way\Records_AddDeleteUpdate but
either I have an old version or it isn't very good as with RB2005 for 10
records it takes 35 seconds.

Thanks

Best regards

Damon L. Pillinger
Director Daisychain EDi
Suite 3
4 Winston Court
Moorabbin 3189
Phone : +61 3 9532 1220
Fax : +61 3 9532 1280


-----Original Message-----
From: Damon Pillinger [mailto:daisychain at iinet.net.au] 
Sent: Sunday, 5 February 2006 9:39 AM
To: 'Valentina Developers'
Subject: RE: Adding records

Thanks Ruslan.

Much appreciated.

Best regards

Damon L. Pillinger
Director Daisychain EDi
Suite 3
4 Winston Court
Moorabbin 3189
Phone : +61 3 9532 1220
Fax : +61 3 9532 1280


-----Original Message-----
From: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] On Behalf Of Ruslan
Zasukhin
Sent: Friday, 3 February 2006 7:34 PM
To: valentina at lists.macserve.net
Subject: Re: Adding records

On 2/3/06 4:28 AM, "Damon Pillinger" <daisychain at iinet.net.au> wrote:

Hi Damon,

> What is the fastest way to add records?

AP way   table.AddRecord

 
> Val 2.1
> RB 5.5.5 Win
> 
> I am using SQL INSERT INTO at the moment and can get 8.12 seconds for
> 10,000 records but wondered if there is a faster way?

Yes. API way can add 100,000 records in 3.5 seconds.

Records have 13 fields in this test.
 
> Also is there a "Begin transaction" as this seems to speed up SQLite
> significantly.

No, SqlLite is special here :-)
All  normal dbs become slower on transactions.
    
    SqlLite is faster with transaction because EACH transaction
    open/close disk file. IT do not have cache at all.

    So if you self start transaction then you avoid thousands
    of open/close in SqlLite.
    
IF you want use SQL and not Api, then the best way to use parameters:

    query = "INSERT INTO T1(f1,f2,f3) VALUES (?,?,?)"

Look into SQL_Way/Bind example


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]


_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina