hi,all,

We have a potenial customer who use mssql now.
The speed of db is the most critical part for them.


Use iSQL, I write a storeprocedure,which can insert 10000 record within 0.6s
on my Dell D610.
But using delphi7 and our ODBC driver, insert 10000 record would cost about
1.4~1.6 second.
I already apply the in-memory license and use -im nw flag to speed up.
The SA version is 11.0.1.2044.

As customer said, they can use MSSQL and C# program to do this within 1
second on their server.
I don't know the hardware of the server.
And the customer said we can a test program and SA11 on that server.
They wish we could show the best performance of SA11.

So, any suggestion to speed up SA 11 would be very helpful.
Can Sybase Open Client API provide a better insert performance?

Database schema
--------------------
CREATE TABLE tData(rid integer,para3 float,para4 float,CONSTRAINT tDataKey
PRIMARY KEY (rid));
--------------------
Delphi7 Code(part)
--------------------

Database1.Open;
Query1.SQL.Text:=('delete from tData;');
Query1.ExecSQL;
Database1.StartTransaction;
Table1.Open;


t1:=Now();
for i:=1 to iCount do
begin
Table1.Insert;
Table1.FieldByName('rid').AsInteger:=i;
Randomize;
Table1.FieldByName('para3').AsFloat:=Random(1);
Table1.FieldByName('para4').AsFloat:=Random(1);
Table1.Post;
end;
if Database1.InTransaction then
Database1.Commit;
t2:=Now();
Memo1.Lines.Add('cost: '+FloatToStr((t2-t1)*24*60*60));

Insert performance will be affected by the following factors:
- how often the database file needs to grow
- how often the transaction log needs to grow
- how often the application causes a COMMIT
- database server cache size
- number of rows inserted per request ("wide" inserts)
The Insert Test example (instest.exe, found in
Samples\SQLAnywhere\PerformanceInsert, can be used to test insert performance
while varying some of the above conditions. Pre-growing the database and log
files, committing less frequently (e.g. every 100,000 rows), and inserting many
(e.g. 200) rows at a time will improve the insert rate. If you are using
in-memory mode, growth of the database and log files is not a factor.

If the data being inserted is coming from a file, you can also use the LOAD
TABLE statement to insert it.

SQL Anywhere Developer Community:
http://www.sybase.com/developer/libr...ere-techcorner


SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs




Ivan.deng wrote:

Thanks Bruce.
The customer already give the limit.
We don't care the db file and db log.
Because the table and data we test is very simple.

The data is come from the running machine, not a file.
So we cant use load table statement.

I already test the 10000 record insert in c#(VS2008).
Using iAnywhere.Data.SQLAnywhere namespace,SAConnect and SACommand.
I am very disappointed for the result.
Without in-memory mode, insert 10000 records cost more that 1 minute.
While it cost 2~3 second to do that using in-memory mode.

Have any good idea?



"Bruce Hay" <h_a_y_@_i_a_n_y_w_h_e_r_e_d_o_t_c_o_m> wrote

I use SATransation.
Now in normal mode(no -im), it cost only 2~3 seconds.
How can it be more fast?

"Ivan.deng" <gdeng (AT) sybase (DOT) com> wrote

Ivan.deng write:

Bruce's advice is still worthwhile:
If you use a "fresh" database and continuously insert lots of data that
data will soon fill the former free space, and new space will have to be
added to the database file. Additionally, the transaction log will
continuously grow.
This will take place automatically by adding small chunks to the file
(IIRC, 256 KB to the database file if running page sizes <= 8 KB).

However, adding file space in incremental chunks is slow and leads to
fragmentation.
Therefore it might be much quicker if you let the database/log file
pre-allocate file space beforehand - cf. the ALTER DBSPACE statement.

(This is just a hint. Without knowledge of the database contents
before/after the tests and the size of the inserted data, incremental
growing may be a problem or not at all.)

HTH
Volker

I can get consistent subsecond performance, without running the server in
memory, on an HP Pavilion dv8000 (Turion 64 ML-32 at 1.8 GHz). I suggest
doing two things differently:

1. Use NativeDB (www.nativedb.com) instead of BDE/ODBC as your middleware.
That will cost some money, but if you use Delphi and SQL Anywhere, it's a
wise investment.

2. Prepare your insert statement once, then execute the prepared statement
each time through your loop. (Though doing this with TQuery.Prepare doesn't
seem to speed your code up any, indicating that the BDE itself is slow.)

Your code, using TTable, takes about 7 seconds to execute on my machine, and
my code takes 0.8 - 0.9 seconds. Here's my code:

procedure TSDIAppForm.Button2Click(Sender: TObject);
var
iCount, i: Integer;
t1, t2: TDateTime;
vpara3, vpara4: Double;
begin
iCount := StrToInt(Edit2.Text);
with AsaSession1 do
begin
LibraryFile := 'dblib11.dll';
LoginEngineName := 'demo11';
LoginUser := 'dba';
LoginPassword := 'sql';
Open;
end;
try
with AsaSQL1 do
begin
Session := AsaSession1;
ExecuteImmediate('truncate table tData');
AsaSession1.StartTransaction;
PrepareExecute('insert into tdata (rid, para3, para4) ' +
' values (?, ?, ?)');
Randomize;
t1:=Now();
for i:=1 to iCount do
begin
vpara3 := Random;
vpara4 := Random;
ExecutePrepared([i, vpara3, vpara4]);
end;
if AsaSession1.InTransaction then
AsaSession1.Commit;
t2:=Now();
Label3.Caption := 'cost: '+FloatToStr((t2-t1)*24*60*60);
end;
finally
AsaSession1.Close;
end;
end;

Note that the Randomize function needs to be called only once, so I do it
outside the loop, and the Random function should be called without
parameters if you want it to return a floating point value. I made the same
changes in the TTable code you provided, so the comparisons between the two
procedures were as fair as I could make them.

Richard


"Ivan.deng" <gdeng (AT) sybase (DOT) com> wrote

Hi,richard,

Your suggestion is great help to me.
I can insert 10000 records in 1s.

But now I have new problem.
http://groups.google.com.hk/group/sy...3447505?hl=en#
Your guide would be very helpful....


"Richard Biffl" <sybaseforums (AT) removvethiisphrraseblacklettersoftware (DOT) com>
wrote in message news:4aae167e$1 (AT) forums-1-dub (DOT) ..