Siva Palanisamy wrote:
Hi All. I basically have 3 tables. One being the core table and the
other 2 depend on the 1st. I have the requirement to add upto 70000
records in all the tables. I do have constraints (primary & foreign
keys, index, unique etc) set for the tables. I can't go for bulk
import (using COPY command) as there is no standard .csv file in
requirement, and the mapping is explicitly required plus few
validations are externally applied in a C based programming file. Each
record details (upto 70000) will be passed from .pgc (an ECPG based C
Programming file) to postgresql file. It takes less time for the 1st
few records and the performance is turning bad to the latter records!
The result is very sad that it takes days to cover upto 20000! What
are the performance measures could I step in into this? Please guide me.
------------------------------------------------------------------------
::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its
affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily
reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure,
modification, distribution and / or publication of
this message without the prior written consent of the author of this
e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender
immediately. Before opening any mail and
attachments please check them for viruses and defect.
-----------------------------------------------------------------------------------------------------------------------
Hi Siva,
Look like every insert cause update their indexes.
My first suggestion is
Drop all indexes related to the three tables before starting insert your
70k records. You can add the indexes back after insert complete.
If it does not improve, I will create three new temp tables without any
constrain and index. Add the constrain and indexes and rename the three
original tables to something *_old. Then you can rename the three temp
table to be the production table.
--
Best regards,
Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
alai@xxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
