Re: Transactions

Hi,

On 6/27/05, operationsengineer1(at)yahoo(dot)com <operationsengineer1(at)yahoo(dot)com> wrote:
> i have the following two queries i would like to run.
>
> $sql_product = 'SELECT product_id, product_number FROM
> t_product ' .
> 'ORDER BY product_number ASC';
>
> $sql_employee = 'SELECT employee_id, employee_name
> FROM t_employee ORDER BY LOWER(employee_name) ASC';
>
> $rs_product = $db->Execute($sql_product);
> $rs_employee = $db->Execute($sql_employee);
>
> i read somewhere that a transaction will speed up the
> process b/c only one db
> connection/hit/request/whatever is made (i may have
> said it wrong, but hopefully you get the meaning)
> instead of two.

If you don't specify any transactions, PostgreSQL will execute each
sent query in a single transaction block. For instance

$conn->Execute("SELECT 1");
$conn->Execute("SELECT 2");

will be executed by PostgreSQL as

BEGIN; SELECT 1; COMMIT;
BEGIN; SELECT 2; COMMIT;

But if you'd specify your own transaction blocks:

$conn->StartTrans();
$conn->Execute("SELECT 1");
$conn->Execute("SELECT 2");
$conn->CompleteTrans();

This time it'll be executed as:

BEGIN;
SELECT 1;
SELECT 2;
END;

As you can realize, we removed the repeatation of transactions and
combined them as one. Therefore, last one will be executed faster when
compared to first example.

For more information, you can take a look at ADOdb's transactions
documentation [1]. Furthermore, I strongly encourage you to read ADOdb
documentation [2] before starting to use ADOdb. You'll be introduced
lots of cool stuff for your own applications.

[1] http://phplens.com/adodb/tutorial.smart.transactions.html
[2] http://phplens.com/adodb/

> how do i implement the BEGIN and COMMIT in php? i saw
> an example on php.net, but it didn't apply to adodb.
> in the example, begin and commit were transmitted to
> the db via the the pg_query function.

You can achieve above $conn->StartTrans(); and $conn->CompleteTrans();
functionality by using $conn->Execute("BEGIN"); and
$conn->Execute("COMMIT"); too. But gor portability and ADOdb'ish way,
you should prefer the first one.

Furthermore, if your queries are used more than once in a single
connection session, you can use PREPARE [3] to reduce query execution
times.

[3] http://phplens.com/adodb/reference.functions.prepare.html

Regards.