Re: timeout on updating entity model

[Tim Martin]

I discovered that the query that is running a monster query (pasted below). It seems that the complexity of the query depends in some way on the number of fields in the table being imported. I have some tables that just refuse to import due to this problem.

SELECT
`Project14`.`C11` AS `C1`,
`Project14`.`C5` AS `C2`,
`Project14`.`C6` AS `C3`,
`Project14`.`C4`,
`Project14`.`C2` AS `C5`,
`Project14`.`C8` AS `C6`,
`Project14`.`C9` AS `C7`,
`Project14`.`C7` AS `C8`,
`Project14`.`C3` AS `C9`,
`Project14`.`C1` AS `C10`,
`Project14`.`Name`,
`Project14`.`Id`,
`Project14`.`C10` AS `C11`
FROM (SELECT
`Extent1`.`Id`,
`Extent2`.`Name`,
`UnionAll1`.`Ordinal` AS `C1`,
`UnionAll3`.`Name` AS `C2`,
`UnionAll4`.`Name` AS `C3`,
`UnionAll5`.`Name` AS `C4`,
`UnionAll5`.`Catalog` AS `C5`,
`UnionAll5`.`Schema` AS `C6`,
`UnionAll6`.`Name` AS `C7`,
`UnionAll6`.`Catalog` AS `C8`,
`UnionAll6`.`Schema` AS `C9`,
CASE WHEN (`Extent1`.`DeleteRule` = 'CASCADE') THEN (cast(1 as decimal(0,0))) WHEN (NOT `Extent1`.`DeleteRule` = 'CASCADE') THEN (cast(0 as decimal(0,0))) END AS `C10`,
1 AS `C11`
FROM (
SELECT
CONCAT('`', CONSTRAINT_SCHEMA, '`.`', TABLE_NAME, '`.`', CONSTRAINT_NAME, '`') AS `Id`,
UPDATE_RULE AS `UpdateRule`,
DELETE_RULE AS `DeleteRule`
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
) AS `Extent1` INNER JOIN (
SELECT /* Constraints */
CONCAT('`', CONSTRAINT_SCHEMA, '`.`', TABLE_NAME, '`.`', CONSTRAINT_NAME, '`') AS `Id`,
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `ParentId`,
CONSTRAINT_NAME AS `Name`,
CONSTRAINT_TYPE AS `ConstraintType`,
0 AS `IsDeferrable`,
0 AS `IsInitiallyDeferred`
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE != 'CHECK' AND TABLE_SCHEMA=schema()
) AS `Extent2` ON `Extent1`.`Id` = `Extent2`.`Id` INNER JOIN (((SELECT
`Extent3`.`Ordinal`,
0 AS `C1`,
`Extent3`.`ConstraintId`,
6 AS `C2`,
`Extent3`.`FromColumnId`,
6 AS `C3`,
`Extent3`.`ToColumnId`
FROM (
SELECT
CONCAT('`', FC.CONSTRAINT_SCHEMA, '`.`', FC.CONSTRAINT_NAME, '`.`', FC.ORDINAL_POSITION, '`') AS `Id`,
CONCAT('`', PC.TABLE_SCHEMA, '`.`', PC.TABLE_NAME, '`.`', PC.COLUMN_NAME, '`') AS `ToColumnId`,
CONCAT('`', FC.TABLE_SCHEMA, '`.`', FC.TABLE_NAME, '`.`', FC.COLUMN_NAME, '`') AS `FromColumnId`,
CONCAT('`', FC.CONSTRAINT_SCHEMA, '`.`', FC.TABLE_NAME, '`.`', FC.CONSTRAINT_NAME, '`') AS `ConstraintId`,
FC.ORDINAL_POSITION AS `Ordinal`
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PC /* PRIMARY KEY COLS*/
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PC.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PC.CONSTRAINT_NAME
AND RC.REFERENCED_TABLE_NAME = PC.TABLE_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FC /* FOREIGN KEY COLS*/
ON RC.CONSTRAINT_SCHEMA = FC.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FC.CONSTRAINT_NAME
AND RC.TABLE_NAME = FC.TABLE_NAME
AND PC.ORDINAL_POSITION = FC.ORDINAL_POSITION
) AS `Extent3`) UNION ALL (SELECT
`Extent4`.`Ordinal`,
9 AS `C1`,
`Extent4`.`ConstraintId`,
10 AS `C2`,
`Extent4`.`FromColumnId`,
10 AS `C3`,
`Extent4`.`ToColumnId`
FROM (
SELECT /* View foreign keys */
NULL AS `Id`,
NULL AS `ToColumnId`,
NULL AS `FromColumnId`,
NULL AS `ConstraintId`,
0 AS `Ordinal`
) AS `Extent4`)) AS `UnionAll1` INNER JOIN ((SELECT
0 AS `C1`,
`Extent5`.`Id`
FROM (
SELECT
CONCAT('`', CONSTRAINT_SCHEMA, '`.`', TABLE_NAME, '`.`', CONSTRAINT_NAME, '`') AS `Id`,
UPDATE_RULE AS `UpdateRule`,
DELETE_RULE AS `DeleteRule`
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
) AS `Extent5` INNER JOIN (
SELECT /* Constraints */
CONCAT('`', CONSTRAINT_SCHEMA, '`.`', TABLE_NAME, '`.`', CONSTRAINT_NAME, '`') AS `Id`,
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `ParentId`,
CONSTRAINT_NAME AS `Name`,
CONSTRAINT_TYPE AS `ConstraintType`,
0 AS `IsDeferrable`,
0 AS `IsInitiallyDeferred`
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE != 'CHECK' AND TABLE_SCHEMA=schema()
) AS `Extent6` ON `Extent5`.`Id` = `Extent6`.`Id`) UNION ALL (SELECT
9 AS `C1`,
`Project4`.`C1` AS `C2`
FROM (SELECT
NULL AS `C1`
FROM (SELECT
cast(1 as decimal(0,0)) AS `X`) AS `SingleRowTable1`) AS `Project4`
WHERE cast(1 as decimal(0,0)) = cast(0 as decimal(0,0)))) AS `UnionAll2` ON (`UnionAll1`.`C1` = `UnionAll2`.`C1`) AND (`UnionAll1`.`ConstraintId` = `UnionAll2`.`Id`) LEFT OUTER JOIN ((SELECT
6 AS `C1`,
`Extent7`.`Id`,
`Extent7`.`Name`,
3 AS `C2`,
`Extent7`.`ParentId`
FROM (
SELECT /* Table columns */
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`.`', COLUMN_NAME, '`') AS `Id`,
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `ParentId`,
COLUMN_NAME AS `Name`,
ORDINAL_POSITION AS `Ordinal`,
CASE IS_NULLABLE WHEN 'YES' THEN 1 ELSE 0 END AS `IsNullable`,
IF(LEFT(COLUMN_TYPE,10) = 'tinyint(1)', 'bool',
IF (LEFT(COLUMN_TYPE,10) = 'binary(16)' OR LEFT(COLUMN_TYPE,8) = 'char(36)', 'guid',
IF (INSTR(COLUMN_TYPE, 'unsigned') = 0, DATA_TYPE,
CONCAT('u', DATA_TYPE)))) AS `TypeName`,
IF (CHARACTER_MAXIMUM_LENGTH > 2147483647, 2147483647, CHARACTER_MAXIMUM_LENGTH) AS `MaxLength`,
NUMERIC_PRECISION AS `Precision`,
0 AS `DateTimePrecision`,
NUMERIC_SCALE AS `Scale`,
NULL AS `CollationCatalog`,
NULL AS `CollationSchema`,
COLLATION_NAME AS `CollationName`,
NULL AS `CharacterSetCatalog`,
NULL AS `CharacterSetSchema`,
CHARACTER_SET_NAME AS `CharacterSetName`,
0 AS `IsMultiSet`,
CASE WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS `IsIdentity`,
CASE WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS `IsStoreGenerated`,
IF (COLUMN_TYPE = 'tinyint(1)', -128, COLUMN_DEFAULT) AS `Default`
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=schema()
) AS `Extent7`) UNION ALL (SELECT
10 AS `C1`,
`Extent8`.`Id`,
`Extent8`.`Name`,
12 AS `C2`,
`Extent8`.`ParentId`
FROM (
SELECT /* View columns */
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`.`', COLUMN_NAME, '`') AS `Id`,
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `ParentId`,
COLUMN_NAME AS `Name`,
ORDINAL_POSITION AS `Ordinal`,
CASE IS_NULLABLE WHEN 'YES' THEN 1 ELSE 0 END AS `IsNullable`,
DATA_TYPE AS `TypeName`,
IF (CHARACTER_MAXIMUM_LENGTH > 2147483647, 2147483647, CHARACTER_MAXIMUM_LENGTH) AS `MaxLength`,
NUMERIC_PRECISION AS `Precision`,
0 AS `DateTimePrecision`,
NUMERIC_SCALE AS `Scale`,
NULL AS `CollationCatalog`,
NULL AS `CollationSchema`,
COLLATION_NAME AS `CollationName`,
NULL AS `CharacterSetCatalog`,
NULL AS `CharacterSetSchema`,
CHARACTER_SET_NAME AS `CharacterSetName`,
0 AS `IsMultiSet`,
CASE WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS `IsIdentity`,
CASE WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS `IsStoreGenerated`,
COLUMN_DEFAULT AS `Default`
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=schema()
) AS `Extent8`)) AS `UnionAll3` ON (`UnionAll1`.`C3` = `UnionAll3`.`C1`) AND (`UnionAll1`.`ToColumnId` = `UnionAll3`.`Id`) LEFT OUTER JOIN ((SELECT
6 AS `C1`,
`Extent9`.`Id`,
`Extent9`.`Name`
FROM (
SELECT /* Table columns */
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`.`', COLUMN_NAME, '`') AS `Id`,
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `ParentId`,
COLUMN_NAME AS `Name`,
ORDINAL_POSITION AS `Ordinal`,
CASE IS_NULLABLE WHEN 'YES' THEN 1 ELSE 0 END AS `IsNullable`,
IF(LEFT(COLUMN_TYPE,10) = 'tinyint(1)', 'bool',
IF (LEFT(COLUMN_TYPE,10) = 'binary(16)' OR LEFT(COLUMN_TYPE,8) = 'char(36)', 'guid',
IF (INSTR(COLUMN_TYPE, 'unsigned') = 0, DATA_TYPE,
CONCAT('u', DATA_TYPE)))) AS `TypeName`,
IF (CHARACTER_MAXIMUM_LENGTH > 2147483647, 2147483647, CHARACTER_MAXIMUM_LENGTH) AS `MaxLength`,
NUMERIC_PRECISION AS `Precision`,
0 AS `DateTimePrecision`,
NUMERIC_SCALE AS `Scale`,
NULL AS `CollationCatalog`,
NULL AS `CollationSchema`,
COLLATION_NAME AS `CollationName`,
NULL AS `CharacterSetCatalog`,
NULL AS `CharacterSetSchema`,
CHARACTER_SET_NAME AS `CharacterSetName`,
0 AS `IsMultiSet`,
CASE WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS `IsIdentity`,
CASE WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS `IsStoreGenerated`,
IF (COLUMN_TYPE = 'tinyint(1)', -128, COLUMN_DEFAULT) AS `Default`
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=schema()
) AS `Extent9`) UNION ALL (SELECT
10 AS `C1`,
`Extent10`.`Id`,
`Extent10`.`Name`
FROM (
SELECT /* View columns */
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`.`', COLUMN_NAME, '`') AS `Id`,
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `ParentId`,
COLUMN_NAME AS `Name`,
ORDINAL_POSITION AS `Ordinal`,
CASE IS_NULLABLE WHEN 'YES' THEN 1 ELSE 0 END AS `IsNullable`,
DATA_TYPE AS `TypeName`,
IF (CHARACTER_MAXIMUM_LENGTH > 2147483647, 2147483647, CHARACTER_MAXIMUM_LENGTH) AS `MaxLength`,
NUMERIC_PRECISION AS `Precision`,
0 AS `DateTimePrecision`,
NUMERIC_SCALE AS `Scale`,
NULL AS `CollationCatalog`,
NULL AS `CollationSchema`,
COLLATION_NAME AS `CollationName`,
NULL AS `CharacterSetCatalog`,
NULL AS `CharacterSetSchema`,
CHARACTER_SET_NAME AS `CharacterSetName`,
0 AS `IsMultiSet`,
CASE WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS `IsIdentity`,
CASE WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS `IsStoreGenerated`,
COLUMN_DEFAULT AS `Default`
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=schema()
) AS `Extent10`)) AS `UnionAll4` ON (`UnionAll1`.`C2` = `UnionAll4`.`C1`) AND (`UnionAll1`.`FromColumnId` = `UnionAll4`.`Id`)) ON ((0 = `UnionAll1`.`C1`) AND (`Extent1`.`Id` = `UnionAll1`.`ConstraintId`)) AND (`UnionAll2`.`Id` = `Extent1`.`Id`) LEFT OUTER JOIN ((SELECT
3 AS `C1`,
`Extent11`.`Id`,
`Extent11`.`Name`,
`Extent11`.`Catalog`,
`Extent11`.`Schema`
FROM (
SELECT /* Tables */
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `Id`,
TABLE_CATALOG AS `Catalog`,
TABLE_SCHEMA AS `Schema`,
TABLE_NAME AS `Name`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA=schema()
) AS `Extent11`) UNION ALL (SELECT
12 AS `C1`,
`Extent12`.`Id`,
`Extent12`.`Name`,
`Extent12`.`CatalogName`,
`Extent12`.`SchemaName`
FROM (
SELECT /* Views */
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `Id`,
TABLE_CATALOG AS`CatalogName`,
TABLE_SCHEMA AS `SchemaName`,
TABLE_NAME AS `Name`,
VIEW_DEFINITION AS `ViewDefinition`,
CASE IS_UPDATABLE WHEN 'YES' THEN 1 ELSE 0 END AS `IsUpdatable`
FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA=schema()
) AS `Extent12`)) AS `UnionAll5` ON (`UnionAll3`.`C2` = `UnionAll5`.`C1`) AND (`UnionAll3`.`ParentId` = `UnionAll5`.`Id`) LEFT OUTER JOIN ((SELECT
3 AS `C1`,
`Extent13`.`Id`,
`Extent13`.`Name`,
`Extent13`.`Catalog`,
`Extent13`.`Schema`
FROM (
SELECT /* Tables */
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `Id`,
TABLE_CATALOG AS `Catalog`,
TABLE_SCHEMA AS `Schema`,
TABLE_NAME AS `Name`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA=schema()
) AS `Extent13`) UNION ALL (SELECT
12 AS `C1`,
`Extent14`.`Id`,
`Extent14`.`Name`,
`Extent14`.`CatalogName`,
`Extent14`.`SchemaName`
FROM (
SELECT /* Views */
CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `Id`,
TABLE_CATALOG AS`CatalogName`,
TABLE_SCHEMA AS `SchemaName`,
TABLE_NAME AS `Name`,
VIEW_DEFINITION AS `ViewDefinition`,
CASE IS_UPDATABLE WHEN 'YES' THEN 1 ELSE 0 END AS `IsUpdatable`
FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA=schema()
) AS `Extent14`)) AS `UnionAll6` ON (3 = `UnionAll6`.`C1`) AND (`Extent2`.`ParentId` = `UnionAll6`.`Id`)
WHERE ((((((((((`UnionAll6`.`Schema` LIKE 'mydbname') AND (`UnionAll6`.`Name` LIKE 'my_table3')) OR ((`UnionAll6`.`Schema` LIKE 'mydbname') AND (`UnionAll6`.`Name` LIKE 'albums'))) OR ((`UnionAll6`.`Schema` LIKE 'mydbname') AND (`UnionAll6`.`Name` LIKE 'my_table6'))) OR ((`UnionAll6`.`Schema` LIKE 'mydbname') AND (`UnionAll6`.`Name` LIKE 'artists'))) OR ((`UnionAll6`.`Schema` LIKE 'mydbname') AND (`UnionAll6`.`Name` LIKE 'my_table4'))) OR ((`UnionAll6`.`Schema` LIKE 'mydbname') AND (`UnionAll6`.`Name` LIKE 'my_table5'))) OR ((`UnionAll6`.`Schema` LIKE 'mydbname') AND (`UnionAll6`.`Name` LIKE 'my_table2'))) OR ((`UnionAll6`.`Schema` LIKE 'mydbname') AND (`UnionAll6`.`Name` LIKE 'my_table1'))) OR ((`UnionAll6`.`Schema` LIKE 'mydbname') AND (`UnionAll6`.`Name` LIKE 'tracks'))) AND ((((((((((`UnionAll5`.`Schema` LIKE 'mydbname') AND (`UnionAll5`.`Name` LIKE 'my_table3')) OR ((`UnionAll5`.`Schema` LIKE 'mydbname') AND (`UnionAll5`.`Name` LIKE 'albums'))) OR ((`UnionAll5`.`Schema` LIKE 'mydbname') AND (`UnionAll5`.`Name` LIKE 'my_table6'))) OR ((`UnionAll5`.`Schema` LIKE 'mydbname') AND (`UnionAll5`.`Name` LIKE 'artists'))) OR ((`UnionAll5`.`Schema` LIKE 'mydbname') AND (`UnionAll5`.`Name` LIKE 'my_table4'))) OR ((`UnionAll5`.`Schema` LIKE 'mydbname') AND (`UnionAll5`.`Name` LIKE 'my_table5'))) OR ((`UnionAll5`.`Schema` LIKE 'mydbname') AND (`UnionAll5`.`Name` LIKE 'my_table2'))) OR ((`UnionAll5`.`Schema` LIKE 'mydbname') AND (`UnionAll5`.`Name` LIKE 'my_table1'))) OR ((`UnionAll5`.`Schema` LIKE 'mydbname') AND (`UnionAll5`.`Name` LIKE 'tracks')))) AS `Project14`
ORDER BY
`Name` ASC,
`Id` ASC,
`C1` ASC