7:17 p.m.
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(a)redhat.com>
To: "Eli Mesika" <emesika(a)redhat.com>
Cc: "engine-devel" <engine-devel(a)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(a)redhat.com>
> To: "Laszlo Hornyak" <lhornyak(a)redhat.com>
> Cc: "engine-devel" <engine-devel(a)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(a)redhat.com>
> > To: "Eli Mesika" <emesika(a)redhat.com>
> > Cc: "engine-devel" <engine-devel(a)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(a)redhat.com>
> > > To: "engine-devel" <engine-devel(a)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(a)ovirt.org
> > > http://lists.ovirt.org/mailman/listinfo/engine-devel
> > >
> >
>
_______________________________________________
Engine-devel mailing list
Engine-devel(a)ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-devel