Re: Question about speed: Weird Behavior

Israel Ben Guilherme Fonseca <israel(dot)bgf(at)gmail(dot)com> Monday 13 of June 2011
05:14:07
> I'm doing some speed tests, and I noticed something very strange.
>
> My test code:
>
> Connection con =
> DriverManager.getConnection("jdbc:postgresql://localhost:5432/tcc","postgre
> s","postgres"); PreparedStatement pst;
> ResultSet rs;
> Timer timer = new Timer();
>
> timer.start();
> pst = con.prepareStatement("select count(*) from Curso");
> rs = pst.executeQuery();
> rs.next();
> timer.stop();
>
> timer.start();
> pst = con.prepareStatement("select * from Curso");
> rs = pst.executeQuery();
> rs.next();
> timer.stop();
>
> At every stop, I print the milliseconds occurred between the begin and the
> end of the operation. The results were basically
>
> 1st operation: 0.05 miliseconds
> 2nd operation: 0.1 miliseconds
>
> That's ok, the strange thing is: When I comment the first operation and
> re-execute the test, the execution time of the 2nd operations increases by
> 50% (going to 0.15 miliseconds)
>
> If i inverse the order of operations, the speed is changed again, the 2nd
> (now 1st) get the 50% percent increase and the count(*) get even faster
> going to 0.005 miliseconds.
It's not stranga, server may keep some data in buffers or in system cache.
Compare your times on only 1st operations, but invoke select * in terminal.

> At first glance, I thought that it could be the statement pre-compiled but
> the two statements are different so it doesn't apply (i think). Using the
> Wireshark tool i could notice that the package sizes get bigger
> automatically (going from 8258 to 16450 and finally to 49218). From my
> readings, the Java Sockets have some configurations about buffer size that
> maybe is doing this.
This is system autotuning - a good solution.

> Even when I close the connection and open a new one, the new queries still
> get faster.
System cache.

> --
>
> Anyway, that speed increases is definitely a good thing, but why this
> happens it's a mystery for me. The problem is that my benchmark tests are
> influenced with this, cause the order of the tests can influence the
> outcome of the total time.
>
> Does someone know what's is this? And how could I disable this (if
> possible)?
>
> Thanks in advance,
>
> Israel