Thread: PHP SQL

Hi All

I am designing a data loader script that can transform data in one table schema into a new table schema in Postgres for reporting purposes.

I wanted to ask, on a performance issue.
Which is more efficient, in reading in a row of data, and assigning individual column values to variables, and then inserting a new row into the new table.

pg_fetch_row
pg_fetch_assoc
pg_fetch_object
pg_fetch_result
pg_fetch_array

Are there performance differences, which are noticeable?

Kind Regards,
Shaun Clements

On Tue, Mar 22, 2005 at 01:25:30PM +0200, Shaun Clements wrote:

> Which is more efficient, in reading in a row of data, and assigning
> individual column values to variables, and then inserting a new row into the
> new table.
>
> pg_fetch_row
> pg_fetch_assoc
> pg_fetch_object
> pg_fetch_result
> pg_fetch_array
>
> Are there performance differences, which are noticeable?

The best way to determine this would be to benchmark each method
in your own environment and observe whether there are significant
differences.

Have you considered using a server-side function to avoid passing
the data back and forth between the client and the server?  Are you
familiar with constructs like CREATE TABLE AS and INSERT...SELECT?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Excellent idea. Yes, I would prefer to write in plpgsql.
My next concern then, is, does Postgres, perform some kind of Load Balancing.
I dont want the stored procedure once run, to DOS other users, who are running reports.. or perform queries using some application.

The reason for asking, is I plan on taking, 350 000 rows, and transforming them, into hopefully about 50% smaller number (in a new schema)

It is critical, that I dont DOS the database server, because it is being used in a PRODUCTION enviroment

Kind Regards,
Shaun Clements

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: 22 March 2005 02:01 PM
To: Shaun Clements
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PHP SQL

On Tue, Mar 22, 2005 at 01:25:30PM +0200, Shaun Clements wrote:

> Which is more efficient, in reading in a row of data, and assigning
> individual column values to variables, and then inserting a new row into the
> new table.
>
> pg_fetch_row
> pg_fetch_assoc
> pg_fetch_object
> pg_fetch_result
> pg_fetch_array
>
> Are there performance differences, which are noticeable?

The best way to determine this would be to benchmark each method
in your own environment and observe whether there are significant
differences.

Have you considered using a server-side function to avoid passing
the data back and forth between the client and the server?  Are you
familiar with constructs like CREATE TABLE AS and INSERT...SELECT?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Shaun Clements wrote:
> Excellent idea. Yes, I would prefer to write in plpgsql.
> My next concern then, is, does Postgres, perform some kind of Load
> Balancing.

Well, you can have multiple clients running queries at the same time.

> I dont want the stored procedure once run, to DOS other users, who are
> running reports.. or perform queries using some application.
> The reason for asking, is I plan on taking, 350 000 rows, and transforming
> them, into hopefully about 50% smaller number (in a new schema)
> It is critical, that I dont DOS the database server, because it is being
> used in a PRODUCTION enviroment

Well, if you need to lock resources used by other users then they could
be delayed then. Otherwise, you'll need to test and see if your
production server has enough capacity to run your simultaneous queries
fast enough.

--
   Richard Huxton
   Archonet Ltd

> pg_fetch_row
> pg_fetch_assoc
> pg_fetch_object
> pg_fetch_result
> pg_fetch_array
>
> Are there performance differences, which are noticeable?


I doubt you'll see much real difference. Your real bottleneck will be
I/O.

You might be able to save something by using COPY out and then back in.
If it's a lot of data I would definately try that, because it's got a
lot more chance of helping you. Or you could use a server side function
rather than doing the work in the client. contrib/dblink may be able to
help you a lot.

However, your question made it sound like the tables were in the same DB
already and just in different schemas? If that's the case, you can just
select one table into another and avoid all that unecessary overhead.

Regards,
    Jeff Davis

Thanks for your response.
It is as you mentioned, within the same database.
I will get working on a stored procedure this morning.

Thanks for your response.

Kind Regards,
Shaun Clements

-----Original Message-----
From: Jeff Davis [mailto:jdavis-pgsql@empires.org]
Sent: 22 March 2005 10:48 PM
To: Shaun Clements
Cc: PgSQL General List
Subject: Re: [GENERAL] PHP SQL

> pg_fetch_row
> pg_fetch_assoc
> pg_fetch_object
> pg_fetch_result
> pg_fetch_array
>
> Are there performance differences, which are noticeable?

I doubt you'll see much real difference. Your real bottleneck will be
I/O.

You might be able to save something by using COPY out and then back in.
If it's a lot of data I would definately try that, because it's got a
lot more chance of helping you. Or you could use a server side function
rather than doing the work in the client. contrib/dblink may be able to
help you a lot.

However, your question made it sound like the tables were in the same DB
already and just in different schemas? If that's the case, you can just
select one table into another and avoid all that unecessary overhead.

Regards,
        Jeff Davis