pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedSpeed of Postgres with Java
4+ messages / 4 participants
[nested] [flat]
* Speed of Postgres with Java
@ 1999-12-04 03:11 ALPESH KOTHARI <alpeshkothari@yahoo.com>
1999-12-04 10:24 ` Re: [INTERFACES] Speed of Postgres with Java Brad Pepers <brad@linuxcanada.com>
0 siblings, 1 reply; 4+ messages in thread
From: ALPESH KOTHARI @ 1999-12-04 03:11 UTC (permalink / raw)
To: pgsql-interfaces
Hello,
I am using Postgres-6.5 as my database. I have to
create and query db using Java (with JDBC). I am using
P-III with 128 MB of RAM.
Consider that following data structure is being stored
in db.
(int4,int4,varchar(50),int2,int4)
To store such 1000 data it takes as large as 90
seconds. I don't have any pre-experience using db.
So, is this much time OK?
I expect someone to comment who have used db with Java
thoroughly.
Thanking You,
With Reards,
Alpesh
=====
KOTHARI ALPESH D.
STUDENT M. TECH.
CEDT
INDIAN INSTITUTE OF SCIENCE
BANGALORE-560 012
INDIA
__________________________________________________
Do You Yahoo!?
Thousands of Stores. Millions of Products. All in one place.
Yahoo! Shopping: http://shopping.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] Speed of Postgres with Java
1999-12-04 03:11 Speed of Postgres with Java ALPESH KOTHARI <alpeshkothari@yahoo.com>
@ 1999-12-04 10:24 ` Brad Pepers <brad@linuxcanada.com>
1999-12-04 17:07 ` Re: [INTERFACES] Speed of Postgres with Java Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Brad Pepers @ 1999-12-04 10:24 UTC (permalink / raw)
To: ALPESH KOTHARI <alpeshkothari@yahoo.com>; +Cc: pgsql-interfaces
ALPESH KOTHARI wrote:
>
> Hello,
>
> I am using Postgres-6.5 as my database. I have to
> create and query db using Java (with JDBC). I am using
> P-III with 128 MB of RAM.
> Consider that following data structure is being stored
> in db.
> (int4,int4,varchar(50),int2,int4)
> To store such 1000 data it takes as large as 90
> seconds. I don't have any pre-experience using db.
> So, is this much time OK?
> I expect someone to comment who have used db with Java
> thoroughly.
You've turned off auto-commit right and just do one commit at
the end? Otherwise its doing a lot of work for each record
added. In general when you are doing bulk inserts, you want
to turn off some of the database features to gain speed.
Make sure auto-commit is off and then post the results. I
bet they are *way* faster!
--
Brad Pepers
Linux Canada Inc. Home of Linux products in Canada!
http://www.linuxcanada.com Proud supporter of Cyclades, Red
brad@linuxcanada.com Hat, and Caldera.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] Speed of Postgres with Java
1999-12-04 03:11 Speed of Postgres with Java ALPESH KOTHARI <alpeshkothari@yahoo.com>
1999-12-04 10:24 ` Re: [INTERFACES] Speed of Postgres with Java Brad Pepers <brad@linuxcanada.com>
@ 1999-12-04 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 1999-12-04 17:07 UTC (permalink / raw)
To: Brad Pepers <brad@linuxcanada.com>; +Cc: ALPESH KOTHARI <alpeshkothari@yahoo.com>; pgsql-interfaces
Brad Pepers <brad@linuxcanada.com> writes:
> ALPESH KOTHARI wrote:
>> To store such 1000 data it takes as large as 90
>> seconds. I don't have any pre-experience using db.
>> So, is this much time OK?
> You've turned off auto-commit right and just do one commit at
> the end? Otherwise its doing a lot of work for each record
> added. In general when you are doing bulk inserts, you want
> to turn off some of the database features to gain speed.
Other commonly used tricks for speeding up bulk inserts are:
1. Use "COPY FROM STDIN" to load all the records in one command, instead
of a series of INSERT commands. This reduces parsing, planning, etc
overhead a great deal. (If you do this then it's not necessary to fool
around with autocommit.)
2. If you are loading a freshly created table, the fastest way is to
create the table, bulk-load with COPY, then create any indexes needed
for the table. Creating an index on pre-existing data is quicker than
updating it incrementally as each record is loaded. This isn't useful
for adding to an existing table, of course.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: [INTERFACES] Speed of Postgres with Java
@ 1999-12-07 07:11 Peter Mount <petermount@it.maidstone.gov.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Mount @ 1999-12-07 07:11 UTC (permalink / raw)
To: 'Brad Pepers' <brad@linuxcanada.com>; ALPESH KOTHARI <alpeshkothari@yahoo.com>; +Cc: pgsql-interfaces
Don't forget to commit() before closing the connection :-)
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Brad Pepers [mailto:brad@linuxcanada.com]
Sent: Saturday, December 04, 1999 10:25 AM
To: ALPESH KOTHARI
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Speed of Postgres with Java
ALPESH KOTHARI wrote:
>
> Hello,
>
> I am using Postgres-6.5 as my database. I have to
> create and query db using Java (with JDBC). I am using
> P-III with 128 MB of RAM.
> Consider that following data structure is being stored
> in db.
> (int4,int4,varchar(50),int2,int4)
> To store such 1000 data it takes as large as 90
> seconds. I don't have any pre-experience using db.
> So, is this much time OK?
> I expect someone to comment who have used db with Java
> thoroughly.
You've turned off auto-commit right and just do one commit at
the end? Otherwise its doing a lot of work for each record
added. In general when you are doing bulk inserts, you want
to turn off some of the database features to gain speed.
Make sure auto-commit is off and then post the results. I
bet they are *way* faster!
--
Brad Pepers
Linux Canada Inc. Home of Linux products in Canada!
http://www.linuxcanada.com Proud supporter of Cyclades, Red
brad@linuxcanada.com Hat, and Caldera.
************
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-12-07 07:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-04 03:11 Speed of Postgres with Java ALPESH KOTHARI <alpeshkothari@yahoo.com>
1999-12-04 10:24 ` Brad Pepers <brad@linuxcanada.com>
1999-12-04 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 07:11 RE: [INTERFACES] Speed of Postgres with Java Peter Mount <petermount@it.maidstone.gov.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox