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" <lhorn...@redhat.com>
> To: "Eli Mesika" <emes...@redhat.com>
> Cc: "engine-devel" <engine-devel@ovirt.org>
> Sent: Wednesday, August 28, 2013 1:02:18 PM
> Subject: Re: [Engine-devel] Opimizing Postgres Stored Procedures
>
>
> ----- Original Message -----
> > From: "Eli Mesika" <emes...@redhat.com>
> > To: "Laszlo Hornyak" <lhorn...@redhat.com>
> > Cc: "engine-devel" <engine-devel@ovirt.org>
> > Sent: Wednesday, August 28, 2013 11:45:14 AM
> > Subject: Re: [Engine-devel] Opimizing Postgres Stored Procedures
> >
> >
> >
> > ----- Original Message -----
> > > From: "Laszlo Hornyak" <lhorn...@redhat.com>
> > > To: "Eli Mesika" <emes...@redhat.com>
> > > Cc: "engine-devel" <engine-devel@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" <emes...@redhat.com>
> > > > To: "engine-devel" <engine-devel@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@ovirt.org
> > > > http://lists.ovirt.org/mailman/listinfo/engine-devel
> > > >
> > >
> >
> _______________________________________________
> Engine-devel mailing list
> Engine-devel@ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
>
_______________________________________________
Engine-devel mailing list
Engine-devel@ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-devel
