Re: Queries run twice

Ok guys this is part of a MySQL log while running the relevant code...

050311 12:50:42 3 Connect a1_admin(at)ERNE on A1Credit
3 Query select database()
3 Field List CUSTOMER
3 Field List CUSTOMER_ACCOUNT
3 Field List TERM
3 Field List IDCONTROL
050311 12:50:43 3 Field List AUDIT
3 Field List USER_SETTINGS
3 Field List MAILING_VAR
3 Query SELECT COUNT(*) FROM TERM WHERE TERM_ID = 0
3 Query SELECT * FROM TERM
3 Query SELECT CanCreateCustomers, CanCreateTerms,
CanCreateAccounts,CanEditCustomers, CanEditTerms,
CanEditAccounts,CanViewCustomers, CanViewTerms,
CanViewAccounts,CanDoUserMaintenance, CanDoCustomerMailing FROM
USER_SETTINGS WHERE USER_ID = 'a1_admin'
3 Query INSERT INTO AUDIT (USER_ID, ACTION, ACTION_TIME,
OBJECT1_ID, OBJECT2_ID, EVENT_TYPE) VALUES('a1_admin', 'User successfully
Logged into A1Credit', NOW(), 0, 0, 0)
050311 12:50:44 3 Query select * from CUSTOMER
050311 12:50:48 3 Query select * from TERM
3 Query SELECT * FROM CUSTOMER_ACCOUNT WHERE CUSTOMER_ID=48
050311 12:50:55 3 Query INSERT INTO AUDIT (USER_ID, ACTION,
ACTION_TIME, OBJECT1_ID, OBJECT2_ID, EVENT_TYPE) VALUES('a1_admin', 'User
successfully Logged off from A1Credit', NOW(), 0, 0, 0)
3 Quit

Results are mixed, let me explain. As you can see the 'SELECT * FROM
CUSTOMER_ACCOUNT WHERE CUSTOMER_ID=48' is only executed once. But a similar
problem is found when executing 'SELECT COUNT(*) FROM TERM WHERE TERM_ID =
0'. The following 'SELECT * FROM TERM' was not issued by my code it was
probably ADO trying to do what Richard said.
The 'select * from CUSTOMER' is ok, this is the grid that shows all
customers. Right now I have a sample data with 10K clients and it takes 3
seconds to open up, in both PostgreSQl and MySQL. Quite acceptable...The
following 'select * from TERM' was me as well. The TERM table is supposed to
be quite small and I cache data used on the Customer form. On PostgreSQL a
'SELECT * FROM CUSTOMER_ACCOUNT' follows the 'SELECT * FROM CUSTOMER_ACCOUNT
WHERE CUSTOMER_ID=48' and that table is 150K records so it takes 7 or 8
seconds for the Customer form to display.

Also when using the 'Parse Statements' settings PostgreSQL is as fast or
faster than MySQL.

-----Original Message-----
From: pgsql-odbc-owner(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org]On Behalf Of Richard Huxton
Sent: Friday, March 11, 2005 4:31 AM
To: erne_dev @ Bellsouth.net
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] Queries run twice

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
--
Internal Virus Database is out-of-date.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.6.1 - Release Date: 3/4/2005

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.7.2 - Release Date: 3/11/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.7.2 - Release Date: 3/11/2005