Bug #46411 SQLPrepare causes Prefetch of table
Submitted: 27 Jul 2009 16:43 Modified: 27 Dec 2011 21:11
Reporter: Cyrill Müller Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S4 (Feature request)
Version:5.1/3.51 OS:Linux
Assigned to: Lawrenty Novitsky CPU Architecture:Any
Tags: Prefetch, SQLNumResultCols, SQLPrepare

[27 Jul 2009 16:43] Cyrill Müller
Description:
Using HSODBC from Oracle, connecting to a MySQL Database via ODBC Driver from MySQL. A describe to a View/Table which is not small (~30Mio Rows) does in the SQLPrepare a "select * from table" this causes a long initialization, which is not acceptable.
Tried with several option in MySQL odbc.ini

How to repeat:
ODBC Connection from Linux to MySQL. Describe table.

Suggested fix:
No FTS at SQLPrepare (where 1=2) would be a solution. Is SQLCancel in implemented inn ODBC Driver on Linux?
[27 Jul 2009 16:45] Cyrill Müller
Sorry forgotten the version from the odbc driver.
[29 Jul 2009 6:51] Tonci Grgin
Hi Cyrill and thanks for your report.

As there is *no* test case attached, I'll have to fish for solution. I'd start from result-set caching, so please activate "Don't cache ..." and "Forward-only cursors ... " options and see if it helps.
[29 Jul 2009 7:24] Cyrill Müller
Hello
Thanks for your information. We tested alread with the following options:
(FLAG_FIELD_LENGTH,FLAG_FOUND_ROWS,FLAG_NO_CACHE,FLAG_FORWARD_CURSOR)
I try to create a testcase.
regards
Cyrill
[29 Jul 2009 7:26] Tonci Grgin
Thanks Cyrill and please do not forget to attach ODBC DM trace for test case too.
[29 Jul 2009 12:22] Cyrill Müller
Hi Tonci

Following testcase:
Create hsodbc connection from oracle
(Using SLES 10 SP2 / mysql-connector-odbc-5.1.5-linux-x86-32bit.tar.gz / unixODBC-32bit-2.2.11-21.4)

MySQL Table: Create table test

TestConnection from Oracle:

desc "globi"@mysql
=> Works well

Insert some data:
declare
k varchar2(10);
begin
for i in 1..10000000 LOOP
  k:=i;
  insert into "globi"@mysql ("name","id") values(k,i);
end loop;
end;
/

Close Database Link (close ODBC Connection).

desc "globi"@mysql
=> takes much longer

We tried the following Options
option = 3145731
=> FLAG_FIELD_LENGTH,FLAG_FOUND_ROWS,FLAG_NO_CACHE,FLAG_FORWARD_CURSOR

If we don't set FLAG_NO_CACHE we receive "MySQL client ran out of memory" 

Also I've tried to enable trace
option = 4
=> seems not to work anymore

Enabled odbc dm trace. uploaded

regards
Cyrill
[29 Jul 2009 12:22] Cyrill Müller
odbc dm trace

Attachment: sqtraceodbc.trc (text/plain), 21.80 KiB.

[29 Jul 2009 12:37] Tonci Grgin
Thanks for attaching trace Cyrill.

Now for the problem, I was more thinking of *standalone* test case, one *not* involving 3rd party ODBC bridges. Are you able to repeat the same problem without Oracle bridge?
[29 Jul 2009 14:18] Cyrill Müller
Hi Tonci
I can't create a standalone testcase without third party tools.
sorry
regards
Cyrill
[30 Jul 2009 6:47] Tonci Grgin
Cyrill, tracing is left to DM for quite some time now, so that option is not in effect nor it is needed.
As for stand alone test case it is really necessary because I need to distinguish between MySQL server problem, connector problem and 3rd party tool problem. For example, Microsoft does not recognize any column bigger than 2^32 and so on. Usually, I discard reports based solely on 3rd party tools problems but since you've attached ODBC DM trace, I'll take a look at it first.
In the meantime, I strongly suggest you try to come up with scenario not using HSODBC bridge.
[30 Jul 2009 10:15] Bogdan Degtyariov
test case

Attachment: bug46411.c (text/plain), 3.91 KiB.

[30 Jul 2009 10:23] Bogdan Degtyariov
Cyrill,

Please try to compile and run the test case (bug46411.c) attached above then copy/paste the program output as a comment for the current bug report.

Also, try adding OPTION=1048576 to the connection string (parameter 1).

Because SQLPrepare does not execute the query being prepared, I suspect the delay occurs in SQLNumResultCol(). The number of resulting columns can be only obtained after executing the query (and buffering the result unless you use OPTION=1048576).

Thanks.
[30 Jul 2009 12:15] Cyrill Müller
Hi Bogdan and Tonci 

Sorry at the moment I'm a bit confused (compiled 32bit). When I try with your testcase everything works as expected with OPTION=1048576 it is fast. I tried also with a DSN (exactly the same as I use for Oracle Connection) and it works also as expected. But not with Oracle, also when I set OPTION=1048576 it is slow... The OPTION are read as when I set (4+524288) /tmp/myodbc.sql is written (see below).

regards
Cyrill

/tools/oracle/tmp/bug46411/bug46411_32 "DRIVER={MySQL5};SERVER=lnx99912;USER=test;PASSWORD=manager;DATABASE=test;PORT=33005" "SELECT * FROM globi"

Trying to connect...Connected
SQLDriverConnect() takes   0.00 seconds.
Preparing "SELECT * FROM globi"
SQLPrepare() takes   0.00 seconds.
Getting result colums count...[2]
SQLNumResultCols() takes   3.22 seconds.
Describing columns...
Column #: 1, Column Name: name, Data Type: 12, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Column #: 2, Column Name: id, Data Type: 4, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Disconnecting...

/tools/oracle/tmp/bug46411/bug46411_32 "DRIVER={MySQL5};SERVER=lnx99912;USER=test;PASSWORD=manager;DATABASE=test;PORT=33005;OPTION=1048576" "SELECT * FROM globi"

Trying to connect...Connected
SQLDriverConnect() takes   0.00 seconds.
Preparing "SELECT * FROM globi"
SQLPrepare() takes   0.00 seconds.
Getting result colums count...[2]
SQLNumResultCols() takes   0.00 seconds.
Describing columns...
Column #: 1, Column Name: name, Data Type: 12, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Column #: 2, Column Name: id, Data Type: 4, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Disconnecting...

OPTION=0
/tools/oracle/tmp/bug46411/bug46411_32 "DSN=mysql" "SELECT * FROM globi"

Trying to connect...Connected
SQLDriverConnect() takes   0.00 seconds.
Preparing "SELECT * FROM globi"
SQLPrepare() takes   0.00 seconds.
Getting result colums count...[2]
SQLNumResultCols() takes   3.22 seconds.
Describing columns...
Column #: 1, Column Name: name, Data Type: 12, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Column #: 2, Column Name: id, Data Type: 4, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Disconnecting...

OPTION=1048576
/tools/oracle/tmp/bug46411/bug46411_32 "DSN=mysql" "SELECT * FROM globi"

Trying to connect...Connected
SQLDriverConnect() takes   0.00 seconds.
Preparing "SELECT * FROM globi"
SQLPrepare() takes   0.00 seconds.
Getting result colums count...[2]
SQLNumResultCols() takes   0.00 seconds.
Describing columns...
Column #: 1, Column Name: name, Data Type: 12, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Column #: 2, Column Name: id, Data Type: 4, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Disconnecting...

HSODBC Bridge (OPTION=1572868 / 4+524288+1048576):
cat /tmp/myodbc.sql
-- Query logging
--
--  Driver name: MySQL ODBC 5.1 Driver  Version: 05.01.0005
-- Timestamp: 090730 15:52:33

COMMIT;
select * from `globi`;
SHOW KEYS FROM `globi`;
COMMIT;

With your testCase (OPTION=1572868 / 4+524288+1048576):
cat /tmp/myodbc.sql
-- Query logging
--
--  Driver name: MySQL ODBC 5.1 Driver  Version: 05.01.0005
-- Timestamp: 090730 15:54:29

SELECT * FROM globi;
[30 Jul 2009 19:20] Cyrill Müller
Hi
I've reread your post. Now my questions is, when I do a SQLPrepare() and NO execution of the statement but only do a SQLNumResultCol(), why is the Statment not a bit changed (rewritten), it could by something like "where 1=2" which will fast return no rows selected but the Number of Columns correct? But for sure this should only be done, when no SQLExecute() is done before SQLNumResultCol(). What I've missed?
thanks and regards
Cyrill
[6 Aug 2009 18:45] Cyrill Müller
Hi
As the cycle of HSODBC for a statement execution is always the same and a "select * from table" never comes to an execution, as at least the projection is changed (* will be replaced to all columns). I've implemented for me a dirty workaround in MySQLPrepare. If a "select * from table" is received it will be changed to "select * from table where 1=2" which will be executed for SQLNumResultCols(). So this workaround works only as HSODBC will never send a "select * from table" directly to the driver. But I think there has to be a better fix for SQLNumResultCols() for not executed statements.
thanks a lot for your help!
regards
Cyrill
[7 Aug 2009 6:39] Tonci Grgin
Cyrill, we've been thinking of this report and here's what we came up with...

  o I can verify this as a feature request (S4) if you agree since, theoretically, getting metadata with empty result-set should improve c/ODBC performance.
  o Implementing this will require additional man-power and is bound to cause other problems. We might not see it now but they will surely surface.

So, can I change it to S4?
[10 Aug 2009 19:18] Cyrill Müller
Hi Tonci and Bogdan
Thanks a lot for your help again. great job! thanks! :-)
Changed severity to S4.
Just my thought, in our env if I couldn't change this behaviour it would be a performance killer as an init (SQLNumResultCols) over a table with >100Mio rows would never end and it would also not be for a single serial session. But for sure you are right if you say this is coming from the way HSODBC is working. And you are right that this could have some side effects, I've already thought of that and there was also some kind of solution (adding an additional queryWithoutRows to tagSTMT where there is an extra condition 1=2 (NoRows) and this queryWithoutRows is executed in SQLNumResultCols when there was no execution) But sure this is the end... 
Thanks and regards
Cyrill
[11 Aug 2009 6:20] Tonci Grgin
I will verify this now on the basis of entire discussion and my opinion that this functionality is important to have in driver.
[20 Jan 2010 7:44] Tonci Grgin
I have assigned this for Jess to fix but, unfortunately, he left...

Assigning to Lawrentiy now.
[19 Mar 2010 21:25] Lawrenty Novitsky
Patch for the bug. Uses statement MAX_ROWS attribute

Attachment: patch46411.diff (text/x-diff), 2.29 KiB.

[19 Mar 2010 21:51] Lawrenty Novitsky
My first intention was to use prepared statement resultset metadata. And then I (re)discovered Jess' branch lp:~myodbc-developers/myodbc/ssps_result_metadata that does exactly what i want. I merged latest revisions to that branch, but prepared statement use should be optional - either by connection option, or by server version(or both). So that branch require some more work. And proposed patch will be used anyway. if it gets approval :)

There is no actual testcase for the patch, but I added some code to the t_max_rows test to ensure that STMT_ATTR_MAX_RESULT functionality isn't affected.

p.s. "-  return rc;
+  return rc != 0;" is just an unrelated warning fix
[23 Mar 2010 20:44] Jim Winstead
This will address the performance issue with queries that generate a lot of data, but there's still a potential for delays with complex queries. (But dealing with that would require re-writing the query to add an impossible WHERE clause, so probably not worth it relative to this easy fix which addresses the most common problem.)
[24 Mar 2010 9:30] Lawrenty Novitsky
My bet is still to start using prepared statements here(and then in c/odbc prepared statements in general). However idea of writing query parser seizes me regulary(and i even started to write one while worked on some bug). It could be useful in many places. But then I think that ice is to thin to step on it- the problem is probably more complex than it appears at the moment i thinking "i'm gonna write it in couple of hours", and the result will be many bug reports.
and yes - this solution is good for most cases.

pushed as rev#880. if stars are aligned properly, it will go to next(current is 5.1.6) release(5.1.7?)
[24 Mar 2010 16:59] Tony Bedford
An entry has been added to the 5.1.7 changelog:

Describing a view or table caused SQLPrepare to prefetch table data. For large tables this created an intolerable performance hit.
[12 Dec 2011 16:16] Lawrenty Novitsky
merged to 3.51 tree as rev#772, next release is supposed to be 3.51.30
[27 Dec 2011 21:11] Philip Olson
The 3.51.30 entry has been added.