[Engine-devel] Opimizing Postgres Stored Procedures

[Laszlo Hornyak]

Hi Eli,

I wrote a quick benchmark to see if there is any difference when using STABLE modifier on functions running queries the way the engine does it (calling it from JDBC, one function in a single statement)

with a stable function:
create function getKakukk(_id int) returns VARCHAR STABLE as 'select val from kakukk where id = $1' language sql;
and one not marked as stable
create function getKakukk_(_id int) returns VARCHAR as 'select val from kakukk where id = $1' language sql;
the table is this simple:
create table kakukk(id int primary key, val varchar);
and the only content is:
insert into kakukk (id, val) values (1, 'bla bla bla');

Now the benchmark code:

package com.foobar;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class SpeedTest {

    Connection connection;

    @Before
    public void connect() throws SQLException {
        connection = DriverManager.getConnection("jdbc:postgresql://localhost/stabletest", "engine", "engine");
    }

    @After
    public void disconnect() throws SQLException {
        connection.close();
    }

    private long measure(Runnable runnable, int times) {
        final long start = System.currentTimeMillis();
        for (int i = 0; i < times; i++) {
            runnable.run();
        }
        final long end = System.currentTimeMillis();
        return end - start;
    }

    public static class Select implements Runnable {

        public Select(PreparedStatement preparedStatement) {
            super();
            this.preparedStatement = preparedStatement;
        }

        final PreparedStatement preparedStatement;

        public void run() {
            try (
                    ResultSet resultSet = preparedStatement.executeQuery();) {
                while (resultSet.next()) {
                    // nothing, just next
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    @Test
    public void performTest() throws SQLException {
        for (int i = 0; i < 10; i++) {
            try (
                    PreparedStatement stable = connection.prepareStatement("select getKakukk(1)");
                    PreparedStatement notStable = connection.prepareStatement("select getKakukk_(1)");) {
                System.out.println("STABLE: " + measure(new Select(stable), 100000));
                System.out.println("not STABLE: " + measure(new Select(notStable), 100000));
                System.out.println("---");

            }
        }
    }
}


-- 

The results are very similar, seemingly no difference at all.
Therefore, it seems we do not need those STABLE markers for performance reasons.

Thank you,
Laszlo

----- Original Message -----
> From: "Laszlo Hornyak" <lhornyak at redhat.com>
> To: "Eli Mesika" <emesika at redhat.com>
> Cc: "engine-devel" <engine-devel at ovirt.org>
> Sent: Wednesday, August 28, 2013 1:02:18 PM
> Subject: Re: [Engine-devel] Opimizing Postgres Stored Procedures
> 
> 
> ----- Original Message -----
> > From: "Eli Mesika" <emesika at redhat.com>
> > To: "Laszlo Hornyak" <lhornyak at redhat.com>
> > Cc: "engine-devel" <engine-devel at ovirt.org>
> > Sent: Wednesday, August 28, 2013 11:45:14 AM
> > Subject: Re: [Engine-devel] Opimizing Postgres Stored Procedures
> > 
> > 
> > 
> > ----- Original Message -----
> > > From: "Laszlo Hornyak" <lhornyak at redhat.com>
> > > To: "Eli Mesika" <emesika at redhat.com>
> > > Cc: "engine-devel" <engine-devel at ovirt.org>
> > > Sent: Tuesday, August 27, 2013 11:40:27 AM
> > > Subject: Re: [Engine-devel] Opimizing Postgres Stored Procedures
> > > 
> > > Hi Eli,
> > > 
> > > Most of the functions that we have in the DB are doing very simple jobs
> > > like
> > > run a query, insert/update and I see that now you have all QUERY
> > > functions
> > > as STABLE.
> > > My questions:
> > > Is this required for new functions from now on?
> > Yes and a email asking that was posted to engine_devel
> > 
> > > Is this done in order to improve performance?
> > Yes
> 
> Do you have any documents/benchmarks on how and why does this improve
> performance?
> STABLE functions should improve performance if they return the same result
> for the same parameters in the same statement.
> E.g. if you have a stable function like "select foo(x) from y" then the
> function can be invoked only once to evaluate each distinct value of y.x -
> this is kind of useful
> Functions running queries for the ovirt engine are typically invoked from
> client side, therefore they are only ivoked once from the parameters list
> and therefore will be only executed once for that single statement.
> 
> > 
> > > 
> > > Thank you,
> > > Laszlo
> > > 
> > > ----- Original Message -----
> > > > From: "Eli Mesika" <emesika at redhat.com>
> > > > To: "engine-devel" <engine-devel at ovirt.org>
> > > > Sent: Monday, August 26, 2013 11:22:20 AM
> > > > Subject: [Engine-devel] Opimizing Postgres Stored Procedures
> > > > 
> > > > Hi
> > > > 
> > > > I had merged the following patch
> > > > http://gerrit.ovirt.org/#/c/17962/
> > > > 
> > > > This patch introduce usage of the IMMUTABLE, STABLE and STRICT keywords
> > > > in
> > > > order to boost performance of the Postgres SPs.
> > > > 
> > > > Please make sure that your current/and future DB scripts applied that.
> > > > 
> > > > 
> > > > Volatility
> > > > ----------
> > > > * A function should be marked as IMMUTABLE if it doesn't change the
> > > > database,
> > > > and if it doesn't perform any lookups (even for database configuration
> > > > values) during its operation.
> > > > * A function should be marked STABLE if it doesn't change the database,
> > > > but
> > > > might perform lookups (IMMUTABLE is preferable if function meets the
> > > > requirements).
> > > > * A function doesn't need to be marked VOLATILE, because that's the
> > > > default.
> > > > 
> > > > STRICTNESS
> > > > ----------
> > > > A function should be marked STRICT if it should return NULL when it is
> > > > passed
> > > > a NULL argument, and then the function won't even be called if it is
> > > > indeed
> > > > passed a NULL argument.
> > > > 
> > > > 
> > > > I am available for any questions.
> > > > 
> > > > Thanks
> > > > 
> > > > Eli
> > > > _______________________________________________
> > > > Engine-devel mailing list
> > > > Engine-devel at ovirt.org
> > > > http://lists.ovirt.org/mailman/listinfo/engine-devel
> > > > 
> > > 
> > 
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
>