Perl DBI, PostgreSQL performance question

Поиск
Список
Период
Сортировка
От Benjamin Franks
Тема Perl DBI, PostgreSQL performance question
Дата
Msg-id 20011214073150.N30736-100000@crimea.dzhan.com
обсуждение исходный текст
Ответы Re: Perl DBI, PostgreSQL performance question  (Frank Finner <postgresql@finner.de>)
Re: Perl DBI, PostgreSQL performance question  (Lincoln Yeoh <lyeoh@pop.jaring.my>)
Список pgsql-general
I'm using the Perl DBI to interact with my PostgreSQL 7.1.3 database.  I had a section of code that looked something
likethe 
following (it's only pseudocode):

**************
foreach
    foreach
        eval {
            prepare first select statement;
            execute first select;
            prepare first update or insert;
            execute first update or insert;

            prepare second select statement;
            execute second select;
            prepare second update or insert;
            execute second update or insert;

            commit;
        };
        if ($@) {
            rollback;
        }
    }
}
***************

I realized some of those statements did not need to be inside the loops
and so figure if I changed the code to the following, it would speed up:

***************
prepare first select statement;
prepare first update;
prepare first insert;
foreach
    eval {
        execute first select statement;
        execute first update or insert;
        commit;
    };
    if ($@) {
        rollback;
        next;
    }

    prepare second select statement;
    prepare second update;
    prepare second insert;

    foreach
        eval {
            execute second select;
            execute second update or insert;
            commit;
        };
        if ($@) {
            rollback;
        }
    }
}
***************

The results are the same in the database either way. From what I can tell, it did not speed up.  In fact it actually
slowed
down.  The SQL statements haven't changed at all and I haven't changed the database schema, version, configuration
options,
etc..  I would have imagined the second sequence would have been much faster because needless SQL isn't being done
insidethe 
inner loops.  Does anyone have any ideas as to why this would be the case?  Could it have to do with moving from a
singleeval 
block to two eval blocks with some statements outside the eval?  What about multiple commits--could they be expensive
operations?

I appreciate any info you may be able to provide.
Thanks,
--Ben

-------------------------------------------------------------------------
"From causes which appear similar, we expect similar effects.  This is the
 sum total of all our experimental conclusions." --David Hume



В списке pgsql-general по дате отправления:

Предыдущее
От: Thomas Lockhart
Дата:
Сообщение: Re: SQL QUERIES
Следующее
От: Tom Lane
Дата:
Сообщение: Re: initdb, separate fileystem?