Thread: about speed

about speed

From
"Vovk G. Grigoriy"
Date:
Can any tell my, what will fastly -  "SELECT ..." statement  or function
doing "SELECT..."?
I do query whith storage procedure in MS SQL, and what about postgresql?

Vovk G. Grigoriy.


Stored Procedures/Functions (was [GENERAL] about speed)

From
chewie@wookimus.net
Date:
("[n]" denotes a footnote.)

Vovk!

I believe your question was directed toward finding an equivalent to
Micro$oft's Stored Proceedures, correct?  In short, yes, Postgresql does
have an equivalent to M$'s stored procedures.  Micro$oft cooked up a
procedural language called TSQL.  Postgresql has a number of options
where Microsoft only had one.  With Postgresql, you can use the PL that
is packaged with the engine called PL/pgSQL [1].  If you know Tcl, you
can use PL/Tcl [2].  And if you feel real adventurous and would like to
use functions and triggers written using programming language functions
in C or C++ rather than procedural (interpreted) language functions, you
can use the libpg library or the esql library (embedded SQL).  Just
check out the Programmer's Manual in the documentation [3].

I couldn't comment on the speed of things, as I haven't yet tried any of
these methods (it's only a matter of time -- a very short time -- before
I do, though.)  I do understand that PL/pgSQL was created with the same
byte-compiling methods that TSQL was.  Here's a little snippet from the
PL/pgSQL section of the Programmer's Manual[1]:

    For all expressions and SQL statements used in the function, the
    PL/pgSQL bytecode interpreter creates a prepared execution plan
    using the SPI managers SPI_prepare() and SPI_saveplan()
    functions. This is done the first time, the individual statement
    is processed in the PL/pgSQL function. Thus, a function with
    conditional code that contains many statements for which
    execution plans would be required, will only prepare and save
    those plans that are really used during the entire lifetime of
    the database connection.

This looks VERY similar to M$SQL Server's stored procedure
implementation.  I looked at the differences between the two languages,
and I don't think there are too many that they cannot be overcome with a
little familiarity.  In fact, I think there is more logic to the
PL/pgSQL language than TSQL.  Take for example how you assign variables.

In TSQL you must use the following statement:

    SELECT @<identifier>=<expression>

Now, doesn't that seem a bit odd?  Using the SELECT statement to assign
values to identifiers is certainly a way to confuse the programmer, and
I'm willing to bet that such use of the SELECT statement is not SQL92
compliant.  (see question about standards below, please)

In PL/pgSQL you use the following:

    <identifier> := <expression>

And that looks like just a small sample of the language.  I'm getting
very anxious to try a bit more. ;-)

Oh, and now on to my question: where can one find an on-line copy of
these often quoted standards, such as SQL92, SQL3, etc?

Chad

Footnotes:
-----------------------------------------------------------------------
[1] http://www.postgresql.org/docs/programmer/xplang1501.htm
[2] http://www.postgresql.org/docs/programmer/xplang1771.htm
[3] http://www.postgresql.org/docs/programmer/index.html

--------------------------------------------------------------------------
Chad Walstrom                                   mailto:chewie@wookimus.net
a.k.a ^chewie, gunnarr                         http://wookimus.net/~chewie

  Gnupg fingerprint = B4AB D627 9CBD 687E 7A31  1950 0CC7 0B18 206C 5AFD
--------------------------------------------------------------------------


Attachment

Re: [GENERAL] about speed

From
Peter Eisentraut
Date:
On 1999-11-25, Vovk G. Grigoriy mentioned:

> Can any tell my, what will fastly -  "SELECT ..." statement  or function
> doing "SELECT..."?

I am not sure, but it would seem that a function doing SELECT will do just
that: call SELECT. The only difference you would have is the function call
overhead. I'm not sure though to what extent the SQL in the function is
preparsed, but in general I never have seen this mentioned as a
performance-tuning measure.

> I do query whith storage procedure in MS SQL, and what about postgresql?

We have user-defined functions, which cover some areas of stored
procedures, but not others. For example, functions can not return a set of
tuples. There are "stored procedure" threads all the time on these mailing
lists -- you are encouraged to look through them for the detailed answers.
Also, some of those things are being worked on for 7.0.

--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden