Josh Berkus wrote:
ZZ,
One of the big problems with the performance numbers I came up with is
the way the data was inserted. When creating the table and adding the
records I only did "Select * from qclog" from the MSSql where the data
is not organized in chronologically with respect to InspecNum. So
PostgreSql has to jump around allot to put the data in order. This made
the page caching worthless. Second given the size of the dataset being
requested the buffer is always over written with each new query.
PostgreSql had to use the disk
First off, this is PostgreSQL on windows, right?
Yes its Windows XP Pro sp2Second, what's your work_mem setting?
Default for both MSSQL and PostgreSql no performance turning was done to the configurations
Third: data in MSSQL is spooled, and therefore by default in the order it
was inserted into the table. So if the timestamp is related to how the
data was inserted in the table, it may actually be ordered in MSSQL and
not in PostgreSQL.
MSSql does not spool data out in the order it appears in the table for several reasons.
Also, by default the time returned by MSSQL is the time it *began*
returning rows, whereas PostgreSQL gives you the time it *finished*. How
are you measuring that time?
I'm not using the server time but the clients time from the start of the query and time to return the resulting dataset to Foxpro. The code is on Channel 9, Its just a simple loop to see have fast it runs. That being said, it's certainly possible that PostgreSQL sorting is slower
on Windows than SQL Server is. We are optimized for Linux and FreeBSD,
and your test is pretty much a raw sort speed test.
I was not looking at the server response time but what the client/user will experience. To me how the fast the server does something kinda meaningless if User can't see the added speed because one of the other layers is slowing things down.
Now i did compare it to the production server. The time to get the records into Foxpro was 1.2 seconds.
Finally, given your overall times I see that stuff is *very* slow on
VMware. I'd expect that query to return in milleseconds on both
databases!
I stated the test setup on channel 9 this is all running on my HP zd7000 laptop 3.2 gigahertz p4, 54000 rpm hard drive.
My development environment runs in VMware 5.0 it slows things down a bit but not allot.