At 07:08 PM 29/08/2007, you wrote:

>Let me explain the complete functionality. I have a Swing component,
>which can take the backup of data from db to .csv files and also
>restores the data from .csv files to .fdb file.
>
> I have one User table which contains all User
>information(uId, name, password, nickname, role, code, email,
>designation, description). Some users are grouped together and they will
>be given group name. This information is stored in Groups table(Groupid,
>groupname, uid). There exist one-many relationship between users and
>group table(i.e. one user can belongs to any number of groups). I'm
>using iBatis as the data mapper between my Java code and Firebird Db.

The big hole in this information is this data mapper. You clearly
need to ask on the Firebird-Java list as to whether others have used
it and what it might be doing at the application interface level to
cause this slowdown. The maintenance of the relationship you
describe above could (and SHOULD) be handled by a BEFORE INSERT OR
UPDATE trigger on the User table (which should be named "User", with
the double quotes, since USER is a reserved word.

> I have tested the component with 15,000 user
>records where each user belongs to 20 groups. The following is the flow
>
>1. Before creating the user, it will check in the db whether user exists
>or not. If user exists n the db, it will update the record otherwise a
>new record will be inserted.
>
>2. Checks for the existence of the group, if group exists, then the user
>will be added to the group otherwise a new group record is created and
>the user will be added to the group.

This flow looks incomplete. What about the situation where the
existing user already belongs to the nominated group?

Besides the hole in the logic, this workflow causes far too many
round-trips from application to data mapper to database. The
application should perform at most one read from the input file per
record, passing all of the needed fields into a prepared
parameterised INSERT statement and then simply allowing the trigger
to do its work. Even if you were processing all 15,000 records in
the set, the execution should be a few seconds at most.

>It is taking considerable amount of time to insert the records into db
>in a single processor machine. But when it comes to multiprocessing
>machine, the component is taking more than 1 hour. I hope now you can
>get a clear idea.

If "considerable amount of time" means more than 1 or 2 seconds then
you have some serious fixing on the agenda. If I were encountering
this, and my workflow and processing strategy were sound, I would
definitely want to take a hard look at that data mapping layer - what
it is doing and how it is doing it.

As for the SMP conditions, the likelihood of processor see-saw
happens specifically on Windows with Superserver (including
Embedded). As long as the server is configured with CPUAffinity to
one processor, you won't get see-saw.

>Following is the hardware configuration of the machine
>
>Server PC
>
>=========
>
>OS :WindowsServer2003R2_Sp1
>Mounting memory :256MB

This is not much memory for a database server. How many other
applications are competing with it? Does this noisy data-mapping
layer compete with it? Are you also running a Java VM on the same
machine to service the iBatis functionality?

>CPU :Intel(R) Pentium4 CPU 2.40GHz

I thought you told us you were using an SMP machine....

You don't mention the disk resources.

You should also say where this large input text file is located
relative to the host machine, and how the data mapping layer accesses
it....besides the exorbitant number of visits to the databases, you
possibly have a heck of a lot of seeking and re-seeking in this input
file, i.e., 15,000 records in an indexed Firebird table is nothing,
but 15,000 records in a .csv file is a lot of text to plough through
- even on a system with adequate resources.

./heLen