Skip site navigation (1) Skip section navigation (2)

Re: Queries run twice

From: Richard Huxton <dev(at)archonet(dot)com>
To: "erne_dev (at) Bellsouth(dot)net" <erne_dev(at)bellsouth(dot)net>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Queries run twice
Date: 2005-03-11 09:30:58
Message-ID: 42316552.4030306@archonet.com (view raw or flat)
Thread:
Lists: pgsql-odbc
erne_dev @ Bellsouth.net wrote:
> Running version 8.0 server under WinXP, client is Builder/ADO application
> connecting through ODBC. Found a reference to exactly the same problem on
> this msg http://archives.postgresql.org/pgsql-odbc/2005-02/msg00033.php. Is
> this a PostgreSQL bug? Or is it ODBC driver? 

Well, if the reply to that message is right, I think it's an unfortunate 
interaction between ADO/PostgreSQL.

 > My application also works with
> MySQL and this doesn't happen when connecting to a MySQL server so I don't
> think is has to do with the ADO components.

Could you turn query-logging on in MySQL and just check? That would help 
to pin things down precisely.

 > Like the poster of that message
> my query gets executed twice, one as I specified the second time without the
> WHERE clause, so the whole Table is returned causing a lot of network
> traffic and slowing things down...What is the 'parse statement' mentioned on
> that message? Is that a PostgreSQL setting?

There is a "parse statements" option in the ODBC driver's settings
   http://gborg.postgresql.org/project/psqlodbc/genpage.php?doc-config

What I think is happening, is that for some reason the ADO code wants to 
know the types of the columns (even though it presumably already has 
this information in the first query). So - it issues the query without 
the WHERE and then doesn't actually fetch any rows, just checks what 
types are returned.

Now, one difference between PostgreSQL and many other databases is that 
if you ask for 1 million rows, we return all those straight away. Many 
others keep the results server-side and only pass them to the client 
when asked for.

Why return them all at once?
  1. It frees up resources server-side for other clients
  2. If the client asked for 1 million rows, then it presumably wants 
all of them, otherwise it would have said so.

The problem occurs when you have application-generated code which is 
thinking in more of a dbf/file oriented way. It should really do "SELECT 
... LIMIT 0"

Anyway, that's only speculation - if you turn on MySQL statement logging 
and it still shows the second query, that's the problem. If it doesn't 
that suggests it's something in the ODBC driver.

--
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-odbc by date

Next:From: Joost KraaijeveldDate: 2005-03-11 09:45:39
Subject: Re: Queries run twice
Previous:From: erne_dev @ Bellsouth.netDate: 2005-03-11 00:08:26
Subject: Queries run twice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group