Re: Permance issues with migrated db

On Tue, 2007-05-22 at 13:10 -0400, Tom Lane wrote:
> Robert Fitzpatrick <lists(at)webtent(dot)net> writes:
> > On Tue, 2007-05-22 at 17:21 +0100, Richard Huxton wrote:
> >> Your query seems to produce 41.8 million rows. Are you sure MS-SQL is
> >> returning that many rows in a few seconds?
>
> > I thought the same thing. While I'm not a MS SQL guru, I put 'TOP 100
> > PERCENT' after SELECT in the query. The Enterprise Manager does not
> > indicate how many rows come back. I save it as a VIEW in MS SQL and do a
> > 'select count(*)...' and, yes, it comes back 42164877 records.
>
> > Just to be sure MS SQL hasn't done something to the structure (I noticed
> > dbo prefixes, etc.), I pasted back into pgadmin, took off 'top 100
> > percent'. Then saved as a view and did a count(*) in pgsql, got
> > 41866801.
>
> How much time do the two select count(*) operations take? That would be
> a reasonably fair comparison of the query engines, as opposed to
> whatever might be happening on the client side (in particular, I wonder
> whether the MS client is actually fetching all the rows or just the
> first few).

Takes 25K ms in pgsql, don't see a timer in MS Ent Manager, but only 5
seconds clocked. Maybe I should put together a php script to operate on
each to be using the exact same client. I am doing all this all on the
same server with PostgreSQL 8.2 loaded in Windows Server 2003 also with
MS SQL server 2000.

--
Robert