Bug #39562 SQLForeignKeys unacceptable performance getting imported FK
Submitted: 20 Sep 2008 18:40 Modified: 23 Jul 2010 14:12
Reporter: Farid Zidan (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.1.5 OS:Microsoft Windows (XP SP3)
Assigned to: Lawrenty Novitsky CPU Architecture:Any
Tags: performance, qc, regression, SQLForeignKeys

[20 Sep 2008 18:40] Farid Zidan
Description:
Executing SQLForeignKeys to get imported FK for tables takes very long time. For example, getting imported foreign keys for 252 tables to determine parent/child dependencies tabes about 3 minutes and 14 seconds for 5.1.5 driver whereas it takes 3 seconds for 3.5x.x driver.

How to repeat:
// execute code fragment for 252 different tables

const TCHAR *szTableCat = "replace with your cat name";
const TCHAR *szTableSchem = "";
const TCHAR *szTableName  = "replace with your table name";

    size_t  cbTableCat   = strlen( szTableCat );
    size_t  cbTableSchem = strlen( szTableSchem );
    size_t  cbTableName  = strlen( szTableName );

    SQLRETURN rc = ::SQLForeignKeys( 
                            pSQL->Gethstmt(),
                            NULL, 0,
                            NULL, 0,
                            NULL, 0,
                            ( SQLCHAR * )   ( *szTableCat ? szTableCat : NULL ),
                            ( SQLSMALLINT ) cbTableCat,
                            ( SQLCHAR * )   ( *szTableSchem ? szTableSchem : NULL ),
                            ( SQLSMALLINT ) cbTableSchem,
                            ( SQLCHAR * )   szTableName,
                            ( SQLSMALLINT ) cbTableName );

// to do: fetch resultset

Suggested fix:
Optimize SQLForeignKeys to match 3.5x.x driver performance by looking at 3.5x.x code for the same function. I am using both drivers against the same 5.x server database and the 3.5x.x driver is about 60 times faster than the 5.1.5 driver
[29 Sep 2009 12:28] Peter Piechutzki
S1 critical severity scince Sept. 2008. Anyone looking into this? This slows down our programming efforts in Powerbuilder extremely. Opening the SQL source of a datawindow takes 30 seconds and more depending on included tables.

regards

Peter
[24 Nov 2009 15:12] ezequiel alvarez
There is any date when this is going to be solved?
[25 Feb 2010 13:24] Lawrenty Novitsky
Patch for Bug#51422 is supposed to significantly improve SQLForeignKeys performance. I can expect that on "real" systems dependency won't be linear, but anyway improvement should be noticeable. I don't close this bug report yet.
[22 Mar 2010 15:59] Peter Piechutzki
On  25 Feb 14:24 Lawrin wrote
<quoute>Patch for Bug#51422 is supposed to significantly improve SQLForeignKeys performance.</quote>

So may I ask when at last will the Patch mentioned in Bug#51422 which was Targeted to 5.1.6 or any other solution be implemented? 

The poor performance takes alot of our productive time which i'd rather spend on programming than waiting für SQLForeignkeys to return data.  

Also I don't understand why severity of this Bug has S1, this bugs thread then references to a solution in another bug with severity S3. Can anyone explain?

Keep Up the good work!

Regards 

Peter

Peter
[22 Mar 2010 16:07] Tonci Grgin
Hi Peter and thanks for your kind words.

> So may I ask when at last will the Patch mentioned in Bug#51422 which was Targeted to 5.1.6 or any other solution be implemented? 

Probably in next release. There is a process to follow.

> Also I don't understand why severity of this Bug has S1, this bugs thread then references to a solution in another bug with severity S3. Can anyone explain?

Severity is set by reporter and I'm not at liberty to change it as it represents *personal* view on severity. Changing that would be just rude. However, I can state my opinion in triage and I did, D3 - medium.
[23 Mar 2010 20:56] Jim Winstead
To address this issue, we should add an option to use the old code path (which has problems of its own -- it can return incorrect and/or incomplete results, and only supports foreign keys on InnoDB tables). Hopefully the performance of the server's INFORMATION_SCHEMA continues to improve so that getting the data directly from the server isn't slow (and the fixes for Bug #51422 certainly help, in that the driver is using a more efficient query to get the data from the server).
[23 Jun 2010 10:11] Lawrenty Novitsky
Patch adding check of the no_information_schema option to select which algorithm to use. it agreed  to be the only solution

Attachment: bug39562mega.patch (application/octet-stream, text), 451 bytes.

[23 Jun 2010 10:13] Lawrenty Novitsky
Uploaded the patch . It's very simple and the solution is already agreed by everyone to be the best possible one at the moment.
So tick that review field - it feels so good :)
[5 Jul 2010 14:15] Lawrenty Novitsky
pushed as rev#899. will go to 5.1.7 if nothing changes.
[23 Jul 2010 14:12] Tony Bedford
An entry has been added to the 5.1.7 changelog: 

Executing SQLForeignKeys to get imported foreign keys for tables took an excessively long time. For example, getting imported foreign keys for 252 tables to determine parent/child dependencies took about 3 minutes and 14 seconds for the 5.1.5 driver, whereas it took 3 seconds for the 3.5x.x driver.
[15 Nov 2010 23:22] Ankit Shah
I don't think this is fixed.  I tried it with 5.1.8 and for each table it takes about 30 seconds for the query to finish if you have a large number of tables in database.

Part of the problem is that none of the tables in information_schema have any indexes.  I saw following query running while i tried to get the foreign key for one of the tables:

SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM,A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM,A.TABLE_NAME AS FKTABLE_NAME,A.COLUMN_NAME AS FKCOLUMN_NAME,A.ORDINAL_POSITION AS KEY_SEQ,CASE WHEN R.UPDATE_RULE = 'CASCADE' THEN 0 WHEN R.UPDATE_RULE = 'SET NULL' THEN 2 WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE = 'SET RESTRICT' THEN 1 WHEN R.UPDATE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,CASE WHEN R.DELETE_RULE = 'CASCADE' THEN 0 WHEN R.DELETE_RULE = 'SET NULL' THEN 2 WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4 WHEN R.DELETE_RULE = 'SET RESTRICT' THEN 1 WHEN R.DELETE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE,A.CONSTRAINT_NAME AS FK_NAME,(SELECT CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = REFERENCED_TABLE_SCHEMA AND TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,7 AS DEFERRABILITY 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A 
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME,TABLE_NAME) 
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND R.TABLE_NAME = B.TABLE_NAME AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA) 
WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND A.TABLE_SCHEMA = 'db_name' AND A.TABLE_NAME = 'table_name' 
ORDER BY FKTABLE_CAT, FKTABLE_NAME, KEY_SEQ, PKTABLE_NAME
[17 Nov 2010 15:32] Lawrenty Novitsky
Ankit, yes, using information_schema can be slow. thus we introduced NO_I_S connections option that makes driver to engage algorithm that doesn't use I_S. It supposed to work faster, but quality of information may be lower.
[17 Nov 2010 16:59] Ankit Shah
Hi Lawrin,

Thanks for your reply.  I realize this is not the place for it but while i have your attention, would you mind pointing me to the documentation for the flag.  I tried to look for it here: http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-parameters....
but don't see this option.
[18 Nov 2010 17:08] Lawrenty Novitsky
Ankit, 
Sorry, documentation on that option is missing indeed. thank you for pointing that out.
[30 May 2011 12:18] Peter Piechutzki
Thank you!

The FLAG_NO_INFORMATION_SCHEMA works fine for us using ODBC Connector 5.1.8 32bit on a Win7 64bit Machine with Powerbuilder 12.1 Build 6807. Powerbuilders Data Source Information opens so much faster now! It's like a dream and ups our productivity by a mile!

regards

Peter
Senior Powerbuilder Developer