Transaction speed...

Transaction speed...

Post by Peter Hart » Wed, 08 Nov 2006 19:15:29


Hi!

I'm using a transaction to import data from a textfile. The progress
indicates the import speed isn't very linear, instead it goes fast for a
while and then slows down, keeps repeating this behavior... Is this the
normal way an import works when using transaction (I don't see the same
behaviour without transaction)?

More important, the first time I run the import it goes quite slow, could
even be slower than without a transaction, but if I import once again (the
same data or some other data) the speed is _significantly_ faster. What
could be the reason for this? Is it a server caching issue?

I am using Delphi Win32 (BDS2006), and importing data into MSDE or SQL
Server 2000. All queries are parameterized

Thanks in advance,

Peter
 
 
 

Transaction speed...

Post by Oliver Tow » Wed, 08 Nov 2006 19:32:51

> I'm using a transaction to import data from a textfile. The progress

Had you considered bcp? Or some bulk copy command?

Oliver Townshend

 
 
 

Transaction speed...

Post by Peter Hart » Wed, 08 Nov 2006 20:11:25

> Had you considered bcp? Or some bulk copy command?

Actually I havn't investigated if it would work for us, because I don't
think it does. The textfile is of custom format each line is an entry to a
separate table with relations between the tables.

So rowX could relate to rowX-1 where rowX-1 is master and rowX is child. In
the same manner, rowX+1 could be the child of rowX.

Here's a simple example of how the textfile is constructed:

Row, Table, Id, Field data...
1 t1 1 data1 data2
2 t2 1 data1 data2
3 t3 1 data1 data2
4 t2 2 data1 data2
5 t3 1 data1 data2
6 t3 2 data1 data2

7 t1 2 data1 data2
8 t2 1 data1 data2
9 t3 1 data1 data2
10 t3 2 data1 data2

When importing the first row, the id (1) is saved and used when importing
row 2-3. When importing row 3 of table t3 the id of table t2 on row 2 is
used. So t1 is master with t2 as child (one to many relation), and t2 is
master with t3 as child (also one to many rel.).

Is it possible to bcp/bulk copy this kind of data?

Thanks
Peter
 
 
 

Transaction speed...

Post by Ralf Janse » Thu, 09 Nov 2006 05:43:17

What could slow down your import maybe re indexing of the table.
Depending on the size of your table and the number of imported rows deactivating
indexes and activating/recreating them after the import may help.

Another problem could be resizing of the database itself.
If you work with a small auto growth value (1MB or so) resizing could lead to
the nonlinear behavior that you noticed.

If you have a clustered index on the table and your imported data isn't sorted
in a way that it could be simply appended to the end of the table or at least
all rows at the same spot it's possible that you get problems because SQLServer
needs to issue a high amount of page splits. (IIRC perfmon has a counter for
page splits)

IMHO the best approach would be to use Bcp to import the data into a temptable
(temptable needs no indexes and you don't need a long running transaction for
the import) and from there issue a set of 'Insert into Table1 Select from
TempTable where Tablename = "Table1"' statements.



--
Ralf Jansen

deepinvent Software GmbH - Viersen, Germany - http://www.yqcomputer.com/
Blog: http://www.yqcomputer.com/
Archiving E-mails with MailStore: http://www.yqcomputer.com/
 
 
 

Transaction speed...

Post by Viatchesla » Thu, 09 Nov 2006 17:31:44

Increasing time to insert new records may have different reasons. How do you
insert data - from dataset using Append()/Post() or by executing INSERT INTO
command? INSERT command should be faster, specify eoExecuteNoRecords in
ExecuteOptions of TADOCommand.

You may use bulk copy commands with custom data, but this needs special
components (OLEDB Direct from http://www.yqcomputer.com/ can do this, look
sample SQLServer\1 000 000 rows) or low-level OLEDB code (using
IRowsetFastLoad interface).

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.yqcomputer.com/ - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.yqcomputer.com/ - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Peter Hartl" < XXXX@XXXXX.COM >

> Actually I havn't investigated if it would work for us, because I don't>
> think it does. The textfile is of custom format each line is an entry to a>
> separate table with relations between the tables>
> So rowX could relate to rowX-1 where rowX-1 is master and rowX is child.>
> In the same manner, rowX+1 could be the child of rowX>
> Here's a simple example of how the textfile is constructed>
> Row, Table, Id, Field data..>
> 1 t1 1 data1 data>
> 2 t2 1 data1 data>
> 3 t3 1 data1 data>
> 4 t2 2 data1 data>
> 5 t3 1 data1 data>
> 6 t3 2 data1 data>
> 7 t1 2 data1 data>
> 8 t2 1 data1 data>
> 9 t3 1 data1 data>
> 10 t3 2 data1 data>
> When importing the first row, the id (1) is saved and used when importing>
> row 2-3. When importing row 3 of table t3 the id of table t2 on row 2 is>
> used. So t1 is master with t2 as child (one to many relation), and t2 is>
> master with t3 as child (also one to many rel.)>
> Is it possible to bcp/bulk copy this kind of data>
> Thank>
> Pete>
>
 
 
 

Transaction speed...

Post by Peter Hart » Fri, 10 Nov 2006 19:49:16

> command? INSERT command should be faster, specify eoExecuteNoRecords in

We are using TADOQueries with INSERT statements importing into a temporary
table.

Is TADOQuery significantly slower than ADOCommand? I think it uses a
ADOCommand in the end...

qry.SQL.Add( 'command' );
adoqry.ExecSql;

vs.

cmd.ExecuteOptions := [eoExecuteNoRecords];
adocmd.CommandText := 'command';
adocmd.Execute;



I don't think this is an option as we use relations between rows in the file
and also need to check the existing data to see what rows should be updated,
inserted or ignored... I haven't investigated the power of bulk copy but the
name implies it has to do with importing massive amount of data that doesn't
need special checks... correct?

Thanks,

Peter
 
 
 

Transaction speed...

Post by Peter Hart » Fri, 10 Nov 2006 19:50:24

Hi Ralf!

Yes, I realized that the transaction log was kept as small as 512kB and
auto-shrink, so this is definitely one reason for the non-linear behaviour!

Thanks!

Peter

"Ralf Jansen" < XXXX@XXXXX.COM > skrev i meddelandet