May 23, 2005 at 11:22 pm
Problem Regarding analysis of Results...
------------------------------------------
I have a Multi-Threaded program in java that creates 150 threads...
where each thread is calling a stored procedure A (through jdbc) which is inserting a row into a specific table.
Each thread makes a connection to the Database(Sql Server 2000) Using Type 2 Driver keeping "Use ANSI nulls,padding and warnings" option unchecked while creating DSN .
Moreover, one of the field to be inserted by A is determine by another procedure B (called internally within A) which checks whether the name already exists or not :
The procedure B returns two output parameters one the name and another is namechange flag.
a) if the passed name to the procedure A is not in the table (this test is done through full table scan) the procedure B returns the same name passed to it by procedure A alomg with namechange flag set to 'N'.
b) else proc B append a specific sequences value with the name and return this new name,alomg with namechange flag set to 'Y' .
if the flag namechange is 'Y' the value returned by B is inserted else the same name is inseted
Now the time taken by one thread to execute stored procedure A , is calculated and entered into a file for analysis.
on analysing the file this is showing some strange behaviour- some rows are getting inserted with in 10 to 20 milisec while some are getting inserted in 3543 or 5567 or 9544 milisec
I am not understanding why its happening..
Thanx in advance
May 24, 2005 at 1:35 am
If you watch the locks using Enterprise Manager while your program is running do you seen anything significant?
Are the long running inserts being blocked by the shorter ones?
Does your logging process record make the distinction between an insert and an update?
What indexes are on your table. Are you trying to insert out-of-sequence values into a clustered index?
What happens if you halve the number of threads?
May 25, 2005 at 3:02 pm
You need to try to separate the cause:
--the returned time is big because of the "time to get connection to SQL server"
or
--the retuned time is big because of the time needed to exec the sp on the server
Vasc
May 27, 2005 at 8:00 am
Thanx for reply
actually there are nonclusteed indexes associated with the various coloum of the table.....actually its not Full table scan.........but the indexes are showing abnormal behaviour some times its fast some times its slow........the field(name) which is generated by the proc b is of varchar(255) type...icoud not understand why its showing abnormal behaviour
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply