Performance Problem with Inserts

[early]

Performance Problem with Inserts



Hi all, we are using Sybase 11 on a Solaris box along with Sun's C++, and are having performance problems namely with inserts.
The scenerio goes like this :-

1. Receive message from MQ, and insert message into table defined like :-
MSG_ID varchar(34)
MSG text(approx 1K)

MSG_ID is defined as a NONCLUSTERED UNIQUE INDEX

2. I have performed three tests with the above table.
a). Insert into table using a MSG = space(ie. 1 character). This takes around 5 secs to process 1000 messages
b). Insert into table using a MSG equal to around 50 chars, defined as a string(C++). This takes around 5-6 secs.
b). Insert into table using a standard text message of approx 1000 chars, also defined as a string(C++). This takes around 3mins.

I have tried changing the index from Clustered/Nonclustered, deleted the index etc. but to no avail.

Does anyone have any ideas on what I can try next?

What I should also mention is that I have tried to 'chunk' the message up into smaller 255 byte fields, of which also had no impact.(This took approx 3mins to process 1000 as well).

Thanks in anticipation,
Earl Sorensen.

 

[dbmsguy]

you sound like you presume the problem to be with the db... you might try to look else where as well...

if you do the math, 1000 rows at 50 bytes is about 50 Kb or so....

1000 rows at 1000 bytes is 1Mb significantly more....

My recollection of text fields is that it will physically store the data in absolute pages (2k), so 50 byte text will store on 1 page, as will 1000 bytes.

I would start looking at the network side as well...


Just a thought Paul

http://www.dbmsguy.com

 

[dbmsguy]

you might also want to look at your network packet size config parm... if you are sending large size data, it may want to be increased. Paul

http://www.dbmsguy.com