Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[udig-devel] [Fwd: Re: [postgis-users] Small speed comparison]
For optimization on the PostGIS datastore side, recognizing and handling 8.0 will provide a speedup.
--- Begin Message --- 
Hi,

I just ran a small test (see attached file) in java, fetching the same
large table from 1.0CVS, 0.9.1 and 0.8.1. The timings are:

Creating JDBC connection to lwgeom... fetching...
Result 0: 83 rows fetched in 22.572s.
Result 1: 83 rows fetched in 22.187s.
Result 2: 83 rows fetched in 21.975s.
Result 3: 83 rows fetched in 23.475s.
Creating JDBC connection to hwgeom09... fetching...
Result 0: 83 rows fetched in 270.259s.
Result 1: 83 rows fetched in 271.803s.
Result 2: 83 rows fetched in 272.83s.
Result 3: 83 rows fetched in 271.672s.
Creating JDBC connection to logigis... fetching...
Result 0: 83 rows fetched in 276.982s.
Result 1: 83 rows fetched in 273.021s.
Result 2: 83 rows fetched in 273.16s.
Result 3: 83 rows fetched in 272.399s.

As expected, 0.9 and 0.8 (which use EWKT as canonical rep) are nearly
equal, while lwgeom using hexwkb is much faster. Due to the tcp overhead
and the java parsing, the difference is not as large as in the
server-side benchmark I posted last week, but I think a speedup factor
of 12 is still notable.

Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@xxxxxxxxxxxxxx | www.logi-track.com

/*
 * LargeGeomSpeed.java
 *
 * (C) 2005 Markus Schaber, schabios@xxxxxxxxxxxxxx
 *
 * $Id: LargeGeomSpeed.java,v 1.5 2005/02/08 14:53:29 mschaber Exp $
 */

package test;

import org.postgis.Geometry;
import org.postgis.PGgeometry;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class LargeGeomSpeed {

    public static void main(String[] args) throws Exception {
        // The databases we connect
        String[] databases = {
            "lwgeom", //PostGIS 1.0.0CVS
            "hwgeom09", //PostGIS 0.9.1
            "logigis", //PostGIS 0.8.1
        };
        String dbprefix = "jdbc:postgresql_postGIS://localhost:5432/";
        String dbuser = "psql";
        String dbpass = "guess";
        // initialize the driver
        Class.forName("org.postgis.DriverWrapper");

        for (int i = 0; i < databases.length; i++) {
            System.out.print("Creating JDBC connection to " + databases[i] + "...");
            Connection conn = DriverManager.getConnection(dbprefix + databases[i], dbuser, dbpass);
            conn.setReadOnly(true);
            conn.setAutoCommit(true);
            Statement statement = conn.createStatement();
            statement.setFetchSize(5);
            System.out.println(" fetching...");
            for (int round = 0; round < 4; round++) {
                int count = 0;
                long start = System.currentTimeMillis();
                ResultSet rs = statement.executeQuery("SELECT geom FROM adminbndy1;");
                while (rs.next()) {
                    Geometry temp = ((PGgeometry) rs.getObject(1)).getGeometry();
                    if (temp == null) {
                        System.out.println("null geometry");
                    }
                    count++;
                }
                long end = System.currentTimeMillis();
                System.out.println("Result " + round + ": " + count + " rows fetched in "
                        + ((end - start) / 1000.0) + "s.");
                rs.close();
                System.gc();
                Thread.sleep(1000);
                System.gc();
            }
            statement.close();
            conn.close();
        }
    }
}

Attachment: signature.asc
Description: OpenPGP digital signature

_______________________________________________
postgis-users mailing list
postgis-users@xxxxxxxxxxxxxxxxxxxxxxx
http://postgis.refractions.net/mailman/listinfo/postgis-users

--- End Message ---

Back to the top