pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstored procedures for complex SELECTs
3+ messages / 3 participants
[nested] [flat]
* stored procedures for complex SELECTs
@ 2006-01-18 23:17 alex-lists-pgsql@yuriev.com
0 siblings, 1 reply; 3+ messages in thread
From: alex-lists-pgsql@yuriev.com @ 2006-01-18 23:17 UTC (permalink / raw)
To: pgsql-sql
Are there performance advantages that can be achieved by wrapping a
complex SELECT into a stored procedure?
Alex
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: stored procedures for complex SELECTs
@ 2006-01-18 23:39 Michael Glaesemann <grzm@myrealbox.com>
parent: alex-lists-pgsql@yuriev.com
0 siblings, 0 replies; 3+ messages in thread
From: Michael Glaesemann @ 2006-01-18 23:39 UTC (permalink / raw)
To: alex-lists-pgsql@yuriev.com; +Cc: pgsql-sql
On Jan 19, 2006, at 8:17 , alex-lists-pgsql@yuriev.com wrote:
> Are there performance advantages that can be achieved by wrapping a
> complex SELECT into a stored procedure?
I believe it depends on the procedural language. If it's SQL, I think
it may be inlined, so you'd have overhead due to the stored procedure
rather than the select itself would probably be minimal. In any other
language, there would be additional parsing overhead, I believe, so I
don't think it would be faster than the select itself.
However, why don't you test it? Test each case in an EXPLAIN ANALYZE
or run some other benchmark to see if there's a performance
difference. Then you'll know for sure—and have numbers to back it up.
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Fw: stored procedures for complex SELECTs
@ 2006-01-25 03:01 ipv@tinet.org
0 siblings, 0 replies; 3+ messages in thread
From: ipv@tinet.org @ 2006-01-25 03:01 UTC (permalink / raw)
To: pgsql-sql
For better performance, try to utilize prepared statements;
example:
PREPARE CPrep_Clients (varchar(20)) AS
SELECT * FROM client WHERE nom = $1;
EXECUTE CPrep_Clients('Francio');
In my system, over 51ms difference between standard select (prepared
by-pass
planification/optimization)
Regards
> ----- Original Message -----
> From: <alex-lists-pgsql@yuriev.com>
> To: <pgsql-sql@postgresql.org>
> Sent: Thursday, January 19, 2006 12:17 AM
> Subject: [SQL] stored procedures for complex SELECTs
>
>
>>
>> Are there performance advantages that can be achieved by wrapping a
>> complex SELECT into a stored procedure?
>>
>> Alex
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-01-25 03:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-18 23:17 stored procedures for complex SELECTs alex-lists-pgsql@yuriev.com
2006-01-18 23:39 ` Michael Glaesemann <grzm@myrealbox.com>
2006-01-25 03:01 Fw: stored procedures for complex SELECTs ipv@tinet.org
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