Обсуждение: query executions
Why would a query in the form of an embedded string in a PHP page perform about the same as a prepared statement and significantly faster than the same query in the form of a stored procedure – all called from PHP pages? (I’m only interested in the database aspect)
An example run:
Duration for 17 native embedded queries : 0.15884709358215 seconds
Duration for 17 native prep-stmt queries : 0.13436007499695 seconds
Duration for 17 native stored-proc queries : 3.0338139533997 seconds
I tried these in sets with different data for each call, opening the connection only once for all sets. I would expect the embedded to be the slowest since I believe the query would have to be planned every time. We’re running Suse 10.2, PG 8.1.5, and the DB and app live on the same box.
-- Stored Procedure:
CREATE OR REPLACE FUNCTION TestStoredProc(integer, text, text) RETURNS integer AS $$
BEGIN
PERFORM a.id,
EXTRACT(EPOCH FROM ad.start_time),
EXTRACT(EPOCH FROM ad.end_time - ad.start_time) + 1 as duration,
a.code, a.description, a.severity
FROM alert_data AS ad
INNER JOIN alerts AS a
ON ad.alert_id = a.id
WHERE ad.asset_id = $1
AND ad.start_time >= $2
AND ad.start_time < $3;
RETURN 0;
END
$$ LANGUAGE 'plpgsql';
PHP:
$query = "Select TestStoredProc(710006, '2008-09-01', '2008-09-10');";
$result = pg_query($dbconn, $query);
-- Prepared Statement:
$prep1 = "SELECT a.id,
EXTRACT(EPOCH FROM ad.start_time),
EXTRACT(EPOCH FROM ad.end_time - ad.start_time) + 1 as duration,
a.code, a.description, a.severity
FROM alert_data AS ad
INNER JOIN alerts AS a ON ad.alert_id = a.id
WHERE ad.asset_id = $1 AND ad.start_time >= $2 AND ad.start_time < $3;";
pg_prepare($dbconn, 'prep1', $prep1);
$data = array(710006, '2008-09-01', '2008-09-10');
$result = pg_execute($dbconn, 'prep1', $data);
Table structure:
civet=> \d alerts
Table "public.alerts"
Column | Type | Modifiers
-------------+-----------------------+----------------------------------------------------------
id | integer | not null default nextval(('alerts_seq'::text)::regclass)
code | character varying(30) | not null
description | text | not null default 'NO DESCRIPTION PROVIDED'::text
severity | character varying(5) | not null default 0
Indexes:
"alerts_pkey" PRIMARY KEY, btree (id)
civet=> \d alert_data
Table "public.alert_data"
Column | Type | Modifiers
------------+--------------------------+--------------------------------------------------------------
id | integer | not null default nextval(('alert_data_seq'::text)::regclass)
asset_id | integer | not null
alert_id | integer | not null
start_time | timestamp with time zone | not null
end_time | timestamp with time zone |
Indexes:
"alert_data_pkey" PRIMARY KEY, btree (id)
"alert_data_idx1" btree (end_time, start_time, asset_id) CLUSTER
"alert_data_idx2" btree (asset_id, start_time, end_time)
Foreign-key constraints:
"alert_data_fkalert_id" FOREIGN KEY (alert_id) REFERENCES alerts(id) MATCH FULL
"alert_data_fkasset_id" FOREIGN KEY (asset_id) REFERENCES assets(id) MATCH FULL