Re: Queries run twice - Mailing list pgsql-odbc
From | erne_dev @ Bellsouth.net |
---|---|
Subject | Re: Queries run twice |
Date | |
Msg-id | FIEALGGOPOGCKJEBMHJIAEHCCHAA.erne_dev@bellsouth.net Whole thread Raw |
In response to | Re: Queries run twice (Richard Huxton <dev@archonet.com>) |
List | pgsql-odbc |
Ok guys this is part of a MySQL log while running the relevant code... 050311 12:50:42 3 Connect a1_admin@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@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Richard Huxton Sent: Friday, March 11, 2005 4:31 AM To: erne_dev @ Bellsouth.net Cc: pgsql-odbc@postgresql.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
pgsql-odbc by date: