Bug #52095 performance of sqlDescribeCol is bad
Submitted: 16 Mar 2010 13:59 Modified: 24 Mar 2010 14:38
Reporter: the 6campbells Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1.6 OS:Any
Assigned to: CPU Architecture:Any

[16 Mar 2010 13:59] the 6campbells
Description:
the elapsed time for sqlDescribeCol can be significant (minutes) as it appears that MySQL is executing the query. that can be confirmed by simply adding a conjunct to the predicate testing if 1=0 which results in a fast API call.

sqlPrepare and sqlDescribeCol have to be fast API calls for dynamic SQL applications. executing the query during these calls is not acceptable in general and especially not for queries which are slow etc. further, it means that the application is incurring a 2x cost as it will subsequently execute a sqlExecute for the query.

How to repeat:
create table T1 ( C1 integer, C2 integer ) 
insert into T1 ( 1, 1) , (2, 1) (3, 1) .... ( 1000000, 1)

create table T2 ( C1 integer, C2 integer)
insert into T2 ( 1, 1) (2, 1), (3, 1) .... ( 10000000, 1) 

use ODBCtest or custom program
sqlPrepare select T1.C1, T1.C2, T2.C1, T2.C2 from T1 join T2 on T1.C2=T2.C2
sqlDescribeCol All

while sqlPrepare is fast sqlDescribeCol will be based on how quickly the query executes. Add and 1=0 to the statement and repeat.

Suggested fix:
[24 Mar 2010 14:38] Lawrenty Novitsky
this is duplicate of the Bug#46411