Slow running Database.tt generation query

[tranceporter]

We updated our SQL server to "Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Mar 6 2017 14:18:16 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)" some time ago. After this, if we save a Database.tt file in VS2015, the SQL query just times out. However, if we run the same query on an environment that has not been upgraded to SQL server 2016, it runs within 15-30 secs. We are using reverse poco generator v2.5.0 and EF6.

EDIT: In the attached query, if we change the LEFT OUTER JOIN to LEFT OUTER MERGE JOIN, it runs in 4 seconds! Is there something that has changed in SQL server 2016 that's causing this issue?

Query is pasted below. Any thoughts please?

faulty sql query.zip

[sjh37]

Try setting IncludeQueryTraceOn9481Flag = true;

See if that makes a difference.

[tranceporter]

Thanks Simon. Is that setting on the .ttinclude file? Will check tomm and report back :)

[tranceporter]

Morning. Just tried setting the flag to true on the Core.ttinclude, and the Database.tt file, no dice. I am going to have a chat with our DBA guys to see if they can knock down the Database compatibility level to 70. After I save the .tt file, VS2015 still hangs. I am running VS2015 in admin mode, and the connection string to DB uses cust_sa. Need to check if that account has elevated privileges.

[sjh37]

Have a look at this page too https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator/wiki/Speed-up-Reverse-generating-by-updating-statistic-on-sys-tables

[tranceporter]

Cheers. We have already run Update statistics on the relevant database. Investigating now. Will report back. On another note, would it be worthwhile changing the generated query so that it uses LEFT OUTER MERGE JOIN instead of LEFT OUTER JOIN on the 2 sub query outputs (if you check the query attached above, there is only 1 LEFT OUTER JOIN I think.

[tranceporter]

Ok. So setting/unsetting the flag does not help. However if you look at the last post on this thread:

https://github.com/aspnet/EntityFramework6/issues/4

Someone has posted a solution that works. Would it be a lot of effort to refactor the code so that it generates a query as mentioned in the solution? That, or now, changing the Left outer join to Left outer merge join works for us. But I feel the solution mentioned in the post above is better. I will look the source code in SchemaReader class (core.include.tt) to see if i can jig up something.

Thanks, Shreyas

[sjh37]

The query I use is different to the query the EF team use, so I can't use the query you posted above. Performing a query plan of the attached sql to the one I use, I get 100% vs 0% (subtree cost 6566 vs 5).

An alternative which I will have a play with at somepoint is to use the EF to reverse engineer the db instead of straight hand-crafted SQL. That way I could reverse engineer other databases such as Oracle, PostgreSQL, etc.

[sjh37]

Changing the EF SQL to use left outer merge join changed the query plan to 62% vs 38% (subtree cost 8.9 vs 5.4) which is vastly improved, however I am not using the EF generated SQL, but my own hand-crafted SQL, which is still better than the EF one, even with the left outer merge join changes.

[tranceporter]

I have attached a different version of .core.ttinclude file. Here are the changes:

1. Changed the TableSQL variable to use the query from here (last post in the thread) https://github.com/aspnet/EntityFramework6/issues/4 I tweaked the query to drop the #TableColumns and #ViewColumns tables at the end Also changed the left outer join to left outer merge join.

2. Changed ForeignKeySQL to do INNER HASH JOIN instead of INNER JOIN on

SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' After the TableSQL query finished in 4 seconds, the ForeignKeySQL was getting stuck. Changing the join above made it finish in a few seconds. I had a fair bit of help from our DB guys of course :)

Can you please try out the .core.ttInclude file below, and please post your thoughts?

EF.Reverse.POCO.Core.ttinclude.zip

[sjh37]

Comparing my SQL with left outer join (original) and changed to 'left outer merge join` gives me 54% vs 46% (5.4 vs 4.6). This is on my company database with 9430 columns over 1137 tables.

So only a slight improvement for me.

Checking other databases:

• AdventureWorks (2012) (44% vs 56%), slightly worse.
• AdventureWorks (2014) (23% vs 77%), a lot worse.
• Northwind (46% vs 54%), slightly worse.

[sjh37]

Here is the SQL I use to generate the table and columns, which takes 1 second for me

SELECT  c.TABLE_SCHEMA AS SchemaName,
        c.TABLE_NAME AS TableName,
        t.TABLE_TYPE AS TableType,
        c.ORDINAL_POSITION AS Ordinal,
        c.COLUMN_NAME AS ColumnName,
        CAST(CASE WHEN IS_NULLABLE = 'YES' THEN 1
                  ELSE 0
             END AS BIT) AS IsNullable,
        DATA_TYPE AS TypeName,
        ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS [MaxLength],
        CAST(ISNULL(NUMERIC_PRECISION, 0) AS INT) AS [Precision],
        ISNULL(COLUMN_DEFAULT, '') AS [Default],
        CAST(ISNULL(DATETIME_PRECISION, 0) AS INT) AS DateTimePrecision,
        ISNULL(NUMERIC_SCALE, 0) AS Scale,
        CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity,
        CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 1
                  WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') = 1 THEN 1
                  WHEN DATA_TYPE = 'TIMESTAMP' THEN 1
                  WHEN DATA_TYPE = 'UNIQUEIDENTIFIER' AND LOWER(ISNULL(COLUMN_DEFAULT, '')) LIKE '%newsequentialid%' THEN 1
                  ELSE 0
             END AS BIT) AS IsStoreGenerated,
        CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS PrimaryKey,
        ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal,
        CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS IsForeignKey
FROM    INFORMATION_SCHEMA.COLUMNS c
        LEFT OUTER JOIN (SELECT u.TABLE_SCHEMA,
                                u.TABLE_NAME,
                                u.COLUMN_NAME,
                                u.ORDINAL_POSITION
                         FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND u.TABLE_NAME = tc.TABLE_NAME
                                       AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                         WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY') pk
            ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA
               AND c.TABLE_NAME = pk.TABLE_NAME
               AND c.COLUMN_NAME = pk.COLUMN_NAME
        LEFT OUTER JOIN (SELECT DISTINCT
                                u.TABLE_SCHEMA,
                                u.TABLE_NAME,
                                u.COLUMN_NAME
                         FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND u.TABLE_NAME = tc.TABLE_NAME
                                       AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                         WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY') fk
            ON c.TABLE_SCHEMA = fk.TABLE_SCHEMA
               AND c.TABLE_NAME = fk.TABLE_NAME
               AND c.COLUMN_NAME = fk.COLUMN_NAME
        INNER JOIN INFORMATION_SCHEMA.TABLES t
            ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
               AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')
      AND c.TABLE_NAME NOT LIKE 'sysdiagram%'
UNION ALL
-- Synonyms
SELECT  sc.name AS SchemaName,
        sn.name AS TableName,
        'SN' AS TableType,
        COLUMNPROPERTY(c.object_id, c.name, 'ordinal') AS Ordinal,
        c.name AS ColumnName,
        c.is_nullable AS IsNullable,
        ISNULL(TYPE_NAME(c.system_type_id), t.name) AS TypeName,
        ISNULL(COLUMNPROPERTY(c.object_id, c.name, 'charmaxlen'), 0) AS [MaxLength],
        CAST(ISNULL(CONVERT(TINYINT, CASE WHEN c.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN c.precision
                                     END), 0) AS INT) AS [Precision],
        ISNULL(CONVERT(NVARCHAR(4000), OBJECT_DEFINITION(c.default_object_id)), '') AS [Default],
        CAST(ISNULL(CONVERT(SMALLINT, CASE WHEN c.system_type_id IN (40, 41, 42, 43, 58, 61) THEN ODBCSCALE(c.system_type_id, c.scale)
                                      END), 0) AS INT) AS DateTimePrecision,
        ISNULL(CONVERT(INT, CASE WHEN c.system_type_id IN (40, 41, 42, 43, 58, 61) THEN NULL
                                 ELSE ODBCSCALE(c.system_type_id, c.scale)
                            END), 0) AS Scale,
        CAST(COLUMNPROPERTY(OBJECT_ID(sn.base_object_name), c.name, 'IsIdentity') AS BIT) AS IsIdentity,
        CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(sc.NAME) + '.' + QUOTENAME(o.NAME)), c.NAME, 'IsIdentity') = 1 THEN 1
                  WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(sc.NAME) + '.' + QUOTENAME(o.NAME)), c.NAME, 'IsComputed') = 1 THEN 1
                  WHEN ISNULL(TYPE_NAME(c.system_type_id), t.NAME) = 'TIMESTAMP' THEN 1
                  WHEN ISNULL(TYPE_NAME(c.system_type_id), t.NAME) = 'UNIQUEIDENTIFIER'
                       AND LOWER(ISNULL(CONVERT(NVARCHAR(4000), OBJECT_DEFINITION(c.default_object_id)), '')) LIKE '%newsequentialid%' THEN 1
                  ELSE 0
             END AS BIT) AS IsStoreGenerated,
        CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS PrimaryKey,
        ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal,
        CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS IsForeignKey
FROM    sys.synonyms sn
        INNER JOIN sys.columns c
            ON c.[object_id] = OBJECT_ID(sn.base_object_name)
        INNER JOIN sys.schemas sc
            ON sc.[schema_id] = sn.[schema_id]
        LEFT JOIN sys.types t
            ON c.user_type_id = t.user_type_id
        INNER JOIN sys.objects o
            ON c.[object_id] = o.[object_id]
        LEFT OUTER JOIN (
                         SELECT u.TABLE_SCHEMA,
                                u.TABLE_NAME,
                                u.COLUMN_NAME,
                                u.ORDINAL_POSITION
                         FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND u.TABLE_NAME = tc.TABLE_NAME
                                       AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                         WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'
                        ) pk
            ON sc.name = pk.TABLE_SCHEMA
               AND sn.name = pk.TABLE_NAME
               AND c.name = pk.COLUMN_NAME
        LEFT OUTER JOIN (
                         SELECT DISTINCT
                                u.TABLE_SCHEMA,
                                u.TABLE_NAME,
                                u.COLUMN_NAME
                         FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND u.TABLE_NAME = tc.TABLE_NAME
                                       AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                         WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY'
                        ) fk
            ON sc.name = fk.TABLE_SCHEMA
               AND sn.name = fk.TABLE_NAME
               AND c.name = fk.COLUMN_NAME;

[tranceporter]

The query above took 24 seconds to run on our database. It's a prett big DB. 24 seconds is not unacceptable by any means, but the query in my .ttinclude (which is refactored from your older v2.5 .core.ttinclude) takes 4 seconds. Can you check how long it takes on your databases please? Also, it could be that my query is only performing well for SQL Server 2016? I unfortunately do not have access to any other SQL server versions. If that is the case, it might be worthwhile, optimizing queries differently based on SQL Server version? Just a thought...

Here is the query from @lukeatron that I am using

DECLARE @p0 nvarchar(4000) = N'TargetDatabase' , @p1 nvarchar(4000) = N'dbo' , @p2 nvarchar(4000) = N'__RefactorLog'

---

-- Table Columns SELECT quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) Id , quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) ParentId , c.COLUMN_NAME Name , c.ORDINAL_POSITION Ordinal , CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) IsNullable , CASE WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)' ELSE c.DATA_TYPE END as TypeName , c.CHARACTER_MAXIMUM_LENGTH MaxLength , CAST(c.NUMERIC_PRECISION as integer) Precision , CAST(c.DATETIME_PRECISION as integer) DateTimePrecision , CAST(c.NUMERIC_SCALE as integer) Scale , c.COLLATION_CATALOG CollationCatalog , c.COLLATION_SCHEMA CollationSchema , c.COLLATION_NAME CollationName , c.CHARACTER_SET_CATALOG CharacterSetCatalog , c.CHARACTER_SET_SCHEMA CharacterSetSchema , c.CHARACTER_SET_NAME CharacterSetName , CAST(0 as bit) as IsMultiSet , CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as IsIdentity , CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as IsStoreGenerated , c.COLUMN_DEFAULT as [Default] INTO #TableColumns FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME AND t.TABLE_TYPE = 'BASE TABLE'

---

-- View Columns SELECT quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) Id , quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) ParentId , c.COLUMN_NAME Name , c.ORDINAL_POSITION Ordinal , CAST(CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) IsNullable , CASE WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)' ELSE c.DATA_TYPE END AS TypeName , c.CHARACTER_MAXIMUM_LENGTH MaxLength , CAST(c.NUMERIC_PRECISION as integer) Precision , CAST(c.DATETIME_PRECISION as integer) as DateTimePrecision , CAST(c.NUMERIC_SCALE as integer) Scale , c.COLLATION_CATALOG CollationCatalog , c.COLLATION_SCHEMA CollationSchema , c.COLLATION_NAME CollationName , c.CHARACTER_SET_CATALOG CharacterSetCatalog , c.CHARACTER_SET_SCHEMA CharacterSetSchema , c.CHARACTER_SET_NAME CharacterSetName , CAST(0 as bit) as IsMultiSet , CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as IsIdentity , CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as IsStoreGenerated , c.COLUMN_DEFAULT [Default] INTO #ViewColumns FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.VIEWS v ON c.TABLE_CATALOG = v.TABLE_CATALOG AND c.TABLE_SCHEMA = v.TABLE_SCHEMA AND c.TABLE_NAME = v.TABLE_NAME WHERE NOT ( v.TABLE_SCHEMA = 'dbo' AND v.TABLE_NAME in('syssegments', 'sysconstraints') AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)

---

SELECT TableAndViewColumns.Ordinal AS C1 , Tables.CatalogName AS CatalogName , Tables.SchemaName AS SchemaName , Tables.Name AS Name , TableAndViewColumns.Name AS C2 , TableAndViewColumns.IsNullable AS C3 , TableAndViewColumns.TypeName AS C4 , TableAndViewColumns.MaxLength AS C5 , TableAndViewColumns.Precision AS C6 , TableAndViewColumns.DateTimePrecision AS C7 , TableAndViewColumns.Scale AS C8 , TableAndViewColumns.IsIdentity AS C9 , TableAndViewColumns.IsStoreGenerated AS C10 , CASE WHEN (Project5.C2 IS NULL) THEN cast(0 as bit) ELSE Project5.C2 END AS C11 FROM
( SELECT quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) Id , TABLE_CATALOG CatalogName , TABLE_SCHEMA SchemaName , TABLE_NAME Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ) Tables INNER JOIN
( SELECT TableColumns.Id AS Id , TableColumns.Name AS Name , TableColumns.Ordinal AS Ordinal , TableColumns.IsNullable AS IsNullable , TableColumns.TypeName AS TypeName , TableColumns.MaxLength AS MaxLength , TableColumns.Precision AS Precision , TableColumns.DateTimePrecision AS DateTimePrecision , TableColumns.Scale AS Scale , TableColumns.IsIdentity AS IsIdentity , TableColumns.IsStoreGenerated AS IsStoreGenerated , 0 AS C1 , TableColumns.ParentId AS ParentId FROM

TableColumns TableColumns

        UNION ALL
        SELECT 
            ViewColumns.Id AS Id
        ,   ViewColumns.Name AS Name
        ,   ViewColumns.Ordinal AS Ordinal
        ,   ViewColumns.IsNullable AS IsNullable
        ,   ViewColumns.TypeName AS TypeName
        ,   ViewColumns.MaxLength AS MaxLength
        ,   ViewColumns.Precision AS Precision
        ,   ViewColumns.DateTimePrecision AS DateTimePrecision
        ,   ViewColumns.Scale AS Scale
        ,   ViewColumns.IsIdentity AS IsIdentity
        ,   ViewColumns.IsStoreGenerated AS IsStoreGenerated
        ,   6 AS C1
        ,   ViewColumns.ParentId AS ParentId
        FROM 
            #ViewColumns AS ViewColumns
) TableAndViewColumns ON 0 = TableAndViewColumns.C1
                         AND Tables.Id = TableAndViewColumns.ParentId
 LEFT OUTER MERGE JOIN  
(   SELECT 
        UnionAll2.Id AS C1
    ,   CAST(1 as bit) AS C2
    FROM 
        (
            SELECT
                quotename(tc.CONSTRAINT_SCHEMA) + quotename(tc.CONSTRAINT_NAME) Id
            ,   quotename(tc.TABLE_SCHEMA) + quotename(tc.TABLE_NAME) ParentId
            ,   tc.CONSTRAINT_NAME Name
            ,   tc.CONSTRAINT_TYPE ConstraintType
            ,   CAST(CASE tc.IS_DEFERRABLE WHEN 'NO' THEN 0 ELSE 1 END as bit) IsDeferrable
            ,   CAST(CASE tc.INITIALLY_DEFERRED WHEN 'NO' THEN 0 ELSE 1 END as bit) IsInitiallyDeferred
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            WHERE 
                tc.TABLE_NAME IS NOT NULL
        ) KeyConstraints
         INNER JOIN  
        (
            SELECT 
                7 AS C1
            ,   KeyConstraintColumns.ConstraintId AS ConstraintId
            ,   TableColumns.Id AS Id
            FROM  
                (
                    SELECT
                        quotename(CONSTRAINT_SCHEMA) + quotename(CONSTRAINT_NAME) ConstraintId
                    ,   quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) + quotename(COLUMN_NAME) ColumnId
                    FROM
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                ) KeyConstraintColumns
                 INNER JOIN 
                #TableColumns TableColumns ON TableColumns.Id = KeyConstraintColumns.ColumnId
            UNION ALL
            SELECT 
                11 AS C1
            ,   Extent7.ConstraintId AS ConstraintId
            ,   ViewColumns2.Id AS Id
            FROM  
                (   SELECT
                        CAST(NULL as nvarchar(1)) ConstraintId
                    ,   CAST(NULL as nvarchar(max)) ColumnId  
                    WHERE 1=2
                ) AS Extent7
                 INNER JOIN 
                #ViewColumns ViewColumns2 ON ViewColumns2.Id = Extent7.ColumnId
        ) UnionAll2 ON  (7 = UnionAll2.C1) 
                        AND (KeyConstraints.Id = UnionAll2.ConstraintId)
    WHERE KeyConstraints.ConstraintType = N'PRIMARY KEY' 
) Project5 ON TableAndViewColumns.Id = Project5.C1

WHERE
NOT ( (Tables.CatalogName LIKE @p0) AND (Tables.SchemaName LIKE @p1) AND (Tables.Name LIKE @p2) )

DROP TABLE #TableColumns
DROP TABLE #ViewColumns

[tranceporter]

As per your comments above, did the v2.5 of Reverse POCO generator use EF generated queries? I have attached our original .core.ttinclude, which we are currently using.

EF.Reverse.POCO.Core.ttinclude.zip

[sjh37]

I originally took the generated SQL from EF as a starting point, and changed/refined it over time to make it human readable, and other users contributions.

[tranceporter]

Ah that makes sense. I that case, I will change the .core.ttinclude o our sides; where we are using EF generated queries in .core.ttinclude files. Hopefully that should sort things out on our side. Thanks for your help :)

Regards, Shreyas

[sjh37]

I ran my query, and got 9430 rows back, ran the EF query, got back 9430 rows, ran your version (in 0 seconds) and get back 8791 rows which is worrying. Not sure why the discrepancy yet.

[tranceporter]

Is it because my version excludes this part:

SELECT DISTINCT FK.TABLE_NAME AS FK_Table, FK.COLUMN_NAME AS FK_Column, PK.TABLE_NAME AS PK_Table, PK.COLUMN_NAME AS PK_Column, FK.CONSTRAINT_NAME AS Constraint_Name, FK.TABLE_SCHEMA AS fkSchema, PK.TABLE_SCHEMA AS pkSchema, PT.COLUMN_NAME AS primarykey FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FK ON FK.CONSTRAINT_CATALOG = C.CONSTRAINT_CATALOG AND FK.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA AND FK.CONSTRAINT_NAME = C.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK ON PK.CONSTRAINT_CATALOG = C.UNIQUE_CONSTRAINT_CATALOG AND PK.CONSTRAINT_SCHEMA = C.UNIQUE_CONSTRAINT_SCHEMA AND PK.CONSTRAINT_NAME = C.UNIQUE_CONSTRAINT_NAME AND PK.ORDINAL_POSITION = FK.ORDINAL_POSITION INNER HASH JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE PT.COLUMN_NAME = PK.COLUMN_NAME ORDER BY FK.TABLE_NAME, FK.COLUMN_NAME";

[tranceporter]

v2.5.0 of .core.ttInclude had 2 queries; one for TableSQL (which I pasted in the previous post), and one for ForeignKeySQL (pasted above). Did you by any change merged those 2 in the refined versions? (> v2.5.0)

[sjh37]

Removing this

AND t.TABLE_TYPE = 'BASE TABLE'

makes it return all the data, including view info

[sjh37]

No merge of those, I still have a separate query for foreign keys.

[tranceporter]

Hmm, interesting. v2.5.0 had that where clause you mentioned for BASe_TABLE. However the query you posted (which you use) does not seem to have that filter. Was it removed in the later versions? Also do you see any performance improvement versus your query?

[sjh37]

Yes, it was removed as it excluded views

[tranceporter]

In that case I will remove that clause in our .core.ttInclude as well. Ideally we should upgrade, but we have about 2 dozen .core.ttInclude files in our massive codebase. Upgrading all will be headache. Apologies. In terms of performance, how does the query I posted fare against the hand crafted one you use? No much difference? Just curious.

[sjh37]

I also now include Synonyms sql, via a UNION ALL

In terms of performance, mine (70%), vs yours (30% cost). Although I can't include the last query, so not like for like yet...

Still, both mine and yours execute in 1 second, although that will be down to sql caching.

Will try the following to clear the cache

DBCC DROPCLEANBUFFERS -- Clears the database cache
DBCC FREEPROCCACHE --Free the procedure cache
DBCC FREESYSTEMCACHE('ALL') --Free the plan cache

[tranceporter]

Ah I see. Looks like we will be missing out on the synonyms unless we upgrade :)

[sjh37]

clearing the cache didn't make any difference, still both are 1 second

[sjh37]

Do you have any synonyms ? I would say most databases don't make use of them

[tranceporter]

Need to double check on Synonyms. We have 40 databases, and I am currently only working on 1 of them. Another option might be to replace our optimized EF query (TableSQL) with your handcrafted one. I will run your query past our DB guys as well. Will take a look at the latest .core.ttInclude on GitHub to see what should go where. Will report back. Cheers!

EDIT: Ran your synonyms query on my database, and no results; so no synonyms in this DB atleast. Without the UNION ALL synonyms query, it takes 11 seconds to run on my db vs the 4 seconds for the optimized EF query.

Sent from my iPhone

On 3 May 2017, at 13:38, Simon Hughes notifications@github.com wrote:

Do you have any synonyms ?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[tranceporter]

Here is the optimized version of your handcrafted query. Basically we moved a bit of expensive lookups into temp tables. This reduced the entire query performance from about 24-27 seconds to 4 seconds. Can you run this query on your side and see how it fares please?

IF ( OBJECT_ID('tempdb..#Columns') IS NOT NULL ) BEGIN DROP TABLE #Columns; END; IF ( OBJECT_ID('tempdb..#PrimaryKeys') IS NOT NULL ) BEGIN DROP TABLE #PrimaryKeys; END; IF ( OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL ) BEGIN DROP TABLE #ForeignKeys; END;

SELECT * INTO #Columns FROM INFORMATION_SCHEMA.COLUMNS C WHERE c.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory') AND c.TABLE_NAME NOT LIKE 'sysdiagram%'

SELECT u.TABLE_SCHEMA, u.TABLE_NAME, u.COLUMN_NAME, u.ORDINAL_POSITION INTO

PrimaryKeys

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA AND u.TABLE_NAME = tc.TABLE_NAME AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

SELECT DISTINCT u.TABLE_SCHEMA, u.TABLE_NAME, u.COLUMN_NAME INTO

ForeignKeys

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA AND u.TABLE_NAME = tc.TABLE_NAME AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

SELECT c.TABLE_SCHEMA AS SchemaName, c.TABLE_NAME AS TableName, t.TABLE_TYPE AS TableType, c.ORDINAL_POSITION AS Ordinal, c.COLUMN_NAME AS ColumnName, CAST(CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END AS BIT) AS IsNullable, DATA_TYPE AS TypeName, ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS [MaxLength], CAST(ISNULL(NUMERIC_PRECISION, 0) AS INT) AS [Precision], ISNULL(COLUMN_DEFAULT, '') AS [Default], CAST(ISNULL(DATETIME_PRECISION, 0) AS INT) AS DateTimePrecision, ISNULL(NUMERIC_SCALE, 0) AS Scale, CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity, CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 1 WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') = 1 THEN 1 WHEN DATA_TYPE = 'TIMESTAMP' THEN 1 WHEN DATA_TYPE = 'UNIQUEIDENTIFIER' AND LOWER(ISNULL(COLUMN_DEFAULT, '')) LIKE '%newsequentialid%' THEN 1 ELSE 0 END AS BIT) AS IsStoreGenerated, CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0 ELSE 1 END AS BIT) AS PrimaryKey, ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal, CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS BIT) AS IsForeignKey FROM #Columns c LEFT OUTER JOIN #PrimaryKeys pk ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA AND c.TABLE_NAME = pk.TABLE_NAME AND c.COLUMN_NAME = pk.COLUMN_NAME LEFT OUTER JOIN #ForeignKeys fk ON c.TABLE_SCHEMA = fk.TABLE_SCHEMA AND c.TABLE_NAME = fk.TABLE_NAME AND c.COLUMN_NAME = fk.COLUMN_NAME INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME WHERE c.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory') AND c.TABLE_NAME NOT LIKE 'sysdiagram%' UNION ALL -- Synonyms SELECT sc.name AS SchemaName, sn.name AS TableName, 'SN' AS TableType, COLUMNPROPERTY(c.object_id, c.name, 'ordinal') AS Ordinal, c.name AS ColumnName, c.is_nullable AS IsNullable, ISNULL(TYPE_NAME(c.system_type_id), t.name) AS TypeName, ISNULL(COLUMNPROPERTY(c.object_id, c.name, 'charmaxlen'), 0) AS [MaxLength], CAST(ISNULL(CONVERT(TINYINT, CASE WHEN c.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN c.precision END), 0) AS INT) AS [Precision], ISNULL(CONVERT(NVARCHAR(4000), OBJECT_DEFINITION(c.default_object_id)), '') AS [Default], CAST(ISNULL(CONVERT(SMALLINT, CASE WHEN c.system_type_id IN (40, 41, 42, 43, 58, 61) THEN ODBCSCALE(c.system_type_id, c.scale) END), 0) AS INT) AS DateTimePrecision, ISNULL(CONVERT(INT, CASE WHEN c.system_type_id IN (40, 41, 42, 43, 58, 61) THEN NULL ELSE ODBCSCALE(c.system_type_id, c.scale) END), 0) AS Scale, CAST(COLUMNPROPERTY(OBJECT_ID(sn.base_object_name), c.name, 'IsIdentity') AS BIT) AS IsIdentity, CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(sc.NAME) + '.' + QUOTENAME(o.NAME)), c.NAME, 'IsIdentity') = 1 THEN 1 WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(sc.NAME) + '.' + QUOTENAME(o.NAME)), c.NAME, 'IsComputed') = 1 THEN 1 WHEN ISNULL(TYPE_NAME(c.system_type_id), t.NAME) = 'TIMESTAMP' THEN 1 WHEN ISNULL(TYPE_NAME(c.system_type_id), t.NAME) = 'UNIQUEIDENTIFIER' AND LOWER(ISNULL(CONVERT(NVARCHAR(4000), OBJECT_DEFINITION(c.default_object_id)), '')) LIKE '%newsequentialid%' THEN 1 ELSE 0 END AS BIT) AS IsStoreGenerated, CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0 ELSE 1 END AS BIT) AS PrimaryKey, ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal, CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS BIT) AS IsForeignKey FROM sys.synonyms sn INNER JOIN sys.columns c ON c.[object_id] = OBJECT_ID(sn.base_object_name) INNER JOIN sys.schemas sc ON sc.[schema_id] = sn.[schema_id] LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id INNER JOIN sys.objects o ON c.[object_id] = o.[object_id] LEFT OUTER JOIN #PrimaryKeys pk ON sc.name = pk.TABLE_SCHEMA AND sn.name = pk.TABLE_NAME AND c.name = pk.COLUMN_NAME LEFT OUTER JOIN #ForeignKeys fk ON sc.name = fk.TABLE_SCHEMA AND sn.name = fk.TABLE_NAME AND c.name = fk.COLUMN_NAME;

[sjh37]

That takes 1 second for me, 9430 rows returned, which is all of them :-)

[tranceporter]

Excellent. So not much change on your side then. It still takes 1 second lol. In our case it was the join with INFORMATION_SCHEMAS that was slowing things down. Moving it into temp table improved things significantly. Returns around 17k rows in our case

[sjh37]

How is it your end, vs the sql in the reverse poco code?

[tranceporter]

Pretty much the same now. This query which is from EF, but with merge outer join also runs in 4 seconds. Try it on your side :) Note that it used TABLE_TYPE IN ('BASE_TABLE', 'VIEW')

SELECT [Extent1].[SchemaName] ,[Extent1].[name] AS TableName ,[Extent1].[TABLE_TYPE] AS TableType ,[UnionAll1].[Ordinal] ,[UnionAll1].[name] AS ColumnName ,[UnionAll1].[IsNullable] ,[UnionAll1].[TypeName] ,ISNULL([UnionAll1].[MaxLength], 0) AS MaxLength ,ISNULL([UnionAll1].[Precision], 0) AS Precision ,ISNULL([UnionAll1].[Default], '') AS [Default] ,ISNULL([UnionAll1].[DateTimePrecision], '') AS [DateTimePrecision] ,ISNULL([UnionAll1].[Scale], 0) AS Scale ,[UnionAll1].[IsIdentity] ,[UnionAll1].[IsStoreGenerated] ,CASE WHEN ([Project5].[C2] IS NULL) THEN CAST(0 AS BIT) ELSE [Project5].[C2] END AS PrimaryKey FROM (SELECT QUOTENAME(TABLE_SCHEMA) + QUOTENAME(TABLE_NAME) [Id] ,TABLE_SCHEMA [SchemaName] ,TABLE_NAME [Name] ,TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW')) AS [Extent1] INNER JOIN (SELECT [Extent2].[Id] AS [Id] ,[Extent2].[Name] AS [Name] ,[Extent2].[Ordinal] AS [Ordinal] ,[Extent2].[IsNullable] AS [IsNullable] ,[Extent2].[TypeName] AS [TypeName] ,[Extent2].[MaxLength] AS [MaxLength] ,[Extent2].[Precision] AS [Precision] ,[Extent2].[Default] ,[Extent2].[DateTimePrecision] AS [DateTimePrecision] ,[Extent2].[Scale] AS [Scale] ,[Extent2].[IsIdentity] AS [IsIdentity] ,[Extent2].[IsStoreGenerated] AS [IsStoreGenerated] ,0 AS [C1] ,[Extent2].[ParentId] AS [ParentId] FROM (SELECT QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id] ,QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId] ,c.COLUMN_NAME [Name] ,c.ORDINAL_POSITION [Ordinal] ,CAST(CASE c.is_nullable WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END AS BIT) [IsNullable] ,CASE WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)' ELSE c.DATA_TYPE END AS [TypeName] ,c.CHARACTER_MAXIMUM_LENGTH [MaxLength] ,CAST(c.NUMERIC_PRECISION AS INTEGER) [Precision] ,CAST(c.DATETIME_PRECISION AS INTEGER) [DateTimePrecision] ,CAST(c.NUMERIC_SCALE AS INTEGER) [Scale] ,c.COLLATION_CATALOG [CollationCatalog] ,c.COLLATION_SCHEMA [CollationSchema] ,c.collation_name [CollationName] ,c.CHARACTER_SET_CATALOG [CharacterSetCatalog] ,c.CHARACTER_SET_SCHEMA [CharacterSetSchema] ,c.CHARACTER_SET_NAME [CharacterSetName] ,CAST(0 AS BIT) AS [IsMultiSet] ,CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS [IsIdentity] ,CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END AS BIT) AS [IsStoreGenerated] ,c.COLUMN_DEFAULT AS [Default] FROM INFORMATION_SCHEMA.columns c INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME AND t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')) AS [Extent2] UNION ALL SELECT [Extent3].[Id] AS [Id] ,[Extent3].[Name] AS [Name] ,[Extent3].[Ordinal] AS [Ordinal] ,[Extent3].[IsNullable] AS [IsNullable] ,[Extent3].[TypeName] AS [TypeName] ,[Extent3].[MaxLength] AS [MaxLength] ,[Extent3].[Precision] AS [Precision] ,[Extent3].[Default] ,[Extent3].[DateTimePrecision] AS [DateTimePrecision] ,[Extent3].[Scale] AS [Scale] ,[Extent3].[IsIdentity] AS [IsIdentity] ,[Extent3].[IsStoreGenerated] AS [IsStoreGenerated] ,6 AS [C1] ,[Extent3].[ParentId] AS [ParentId] FROM (SELECT QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id] ,QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId] ,c.COLUMN_NAME [Name] ,c.ORDINAL_POSITION [Ordinal] ,CAST(CASE c.is_nullable WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END AS BIT) [IsNullable] ,CASE WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)' ELSE c.DATA_TYPE END AS [TypeName] ,c.CHARACTER_MAXIMUM_LENGTH [MaxLength] ,CAST(c.NUMERIC_PRECISION AS INTEGER) [Precision] ,CAST(c.DATETIME_PRECISION AS INTEGER) AS [DateTimePrecision] ,CAST(c.NUMERIC_SCALE AS INTEGER) [Scale] ,c.COLLATION_CATALOG [CollationCatalog] ,c.COLLATION_SCHEMA [CollationSchema] ,c.collation_name [CollationName] ,c.CHARACTER_SET_CATALOG [CharacterSetCatalog] ,c.CHARACTER_SET_SCHEMA [CharacterSetSchema] ,c.CHARACTER_SET_NAME [CharacterSetName] ,CAST(0 AS BIT) AS [IsMultiSet] ,CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS [IsIdentity] ,CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END AS BIT) AS [IsStoreGenerated] ,c.COLUMN_DEFAULT [Default] FROM INFORMATION_SCHEMA.columns c INNER JOIN INFORMATION_SCHEMA.VIEWS v ON c.TABLE_CATALOG = v.TABLE_CATALOG AND c.TABLE_SCHEMA = v.TABLE_SCHEMA AND c.TABLE_NAME = v.TABLE_NAME WHERE NOT ( v.TABLE_SCHEMA = 'dbo' AND v.TABLE_NAME IN ('syssegments', 'sysconstraints') AND SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(20)), 1, 1) = 8 )) AS [Extent3]) AS [UnionAll1] ON (0 = [UnionAll1].[C1]) AND ([Extent1].[id] = [UnionAll1].[ParentId]) LEFT OUTER MERGE JOIN (SELECT [UnionAll2].[id] AS [C1] ,CAST(1 AS BIT) AS [C2] FROM (SELECT QUOTENAME(tc.CONSTRAINT_SCHEMA) + QUOTENAME(tc.CONSTRAINT_NAME) [Id] ,QUOTENAME(tc.TABLE_SCHEMA) + QUOTENAME(tc.TABLE_NAME) [ParentId] ,tc.CONSTRAINT_NAME [Name] ,tc.CONSTRAINT_TYPE [ConstraintType] ,CAST(CASE tc.IS_DEFERRABLE WHEN 'NO' THEN 0 ELSE 1 END AS BIT) [IsDeferrable] ,CAST(CASE tc.INITIALLY_DEFERRED WHEN 'NO' THEN 0 ELSE 1 END AS BIT) [IsInitiallyDeferred] FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE tc.TABLE_NAME IS NOT NULL) AS [Extent4] INNER JOIN (SELECT 7 AS [C1] ,[Extent5].[ConstraintId] AS [ConstraintId] ,[Extent6].[Id] AS [Id] FROM (SELECT QUOTENAME(CONSTRAINT_SCHEMA) + QUOTENAME(CONSTRAINT_NAME) [ConstraintId] ,QUOTENAME(TABLE_SCHEMA) + QUOTENAME(TABLE_NAME) + QUOTENAME(COLUMN_NAME) [ColumnId] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE) AS [Extent5] INNER JOIN (SELECT QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id] ,QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId] ,c.COLUMN_NAME [Name] ,c.ORDINAL_POSITION [Ordinal] ,CAST(CASE c.is_nullable WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END AS BIT) [IsNullable] ,CASE WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)' ELSE c.DATA_TYPE END AS [TypeName] ,c.CHARACTER_MAXIMUM_LENGTH [MaxLength] ,CAST(c.NUMERIC_PRECISION AS INTEGER) [Precision] ,CAST(c.DATETIME_PRECISION AS INTEGER) [DateTimePrecision] ,CAST(c.NUMERIC_SCALE AS INTEGER) [Scale] ,c.COLLATION_CATALOG [CollationCatalog] ,c.COLLATION_SCHEMA [CollationSchema] ,c.collation_name [CollationName] ,c.CHARACTER_SET_CATALOG [CharacterSetCatalog] ,c.CHARACTER_SET_SCHEMA [CharacterSetSchema] ,c.CHARACTER_SET_NAME [CharacterSetName] ,CAST(0 AS BIT) AS [IsMultiSet] ,CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS [IsIdentity] ,CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END AS BIT) AS [IsStoreGenerated] ,c.COLUMN_DEFAULT AS [Default] FROM INFORMATION_SCHEMA.columns c INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME AND t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')) AS [Extent6] ON [Extent6].[Id] = [Extent5].[ColumnId] UNION ALL SELECT 11 AS [C1] ,[Extent7].[ConstraintId] AS [ConstraintId] ,[Extent8].[Id] AS [Id] FROM (SELECT CAST(NULL AS NVARCHAR(1)) [ConstraintId] ,CAST(NULL AS NVARCHAR(MAX)) [ColumnId] WHERE 1 = 2) AS [Extent7] INNER JOIN (SELECT QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id] ,QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId] ,c.COLUMN_NAME [Name] ,c.ORDINAL_POSITION [Ordinal] ,CAST(CASE c.is_nullable WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END AS BIT) [IsNullable] ,CASE WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)' ELSE c.DATA_TYPE END AS [TypeName] ,c.CHARACTER_MAXIMUM_LENGTH [MaxLength] ,CAST(c.NUMERIC_PRECISION AS INTEGER) [Precision] ,CAST(c.DATETIME_PRECISION AS INTEGER) AS [DateTimePrecision] ,CAST(c.NUMERIC_SCALE AS INTEGER) [Scale] ,c.COLLATION_CATALOG [CollationCatalog] ,c.COLLATION_SCHEMA [CollationSchema] ,c.collation_name [CollationName] ,c.CHARACTER_SET_CATALOG [CharacterSetCatalog] ,c.CHARACTER_SET_SCHEMA [CharacterSetSchema] ,c.CHARACTER_SET_NAME [CharacterSetName] ,CAST(0 AS BIT) AS [IsMultiSet] ,CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS [IsIdentity] ,CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END AS BIT) AS [IsStoreGenerated] ,c.COLUMN_DEFAULT [Default] FROM INFORMATION_SCHEMA.columns c INNER JOIN INFORMATION_SCHEMA.VIEWS v ON c.TABLE_CATALOG = v.TABLE_CATALOG AND c.TABLE_SCHEMA = v.TABLE_SCHEMA AND c.TABLE_NAME = v.TABLE_NAME WHERE NOT ( v.TABLE_SCHEMA = 'dbo' AND v.TABLE_NAME IN ('syssegments', 'sysconstraints') AND SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(20)), 1, 1) = 8 )) AS [Extent8] ON [Extent8].[Id] = [Extent7].[ColumnId]) AS [UnionAll2] ON (7 = [UnionAll2].[C1]) AND ([Extent4].[id] = [UnionAll2].[ConstraintId]) WHERE [Extent4].[ConstraintType] = N'PRIMARY KEY') AS [Project5] ON [UnionAll1].[id] = [Project5].[C1] WHERE NOT ([Extent1].[name] IN ('EdmMetadata', '__MigrationHistory'));

[sjh37]

minimum sql columns instead of * for #columns is

SELECT  C.TABLE_SCHEMA,
        C.TABLE_NAME,
        C.COLUMN_NAME,
        C.ORDINAL_POSITION,
        C.COLUMN_DEFAULT,
        C.IS_NULLABLE,
        C.DATA_TYPE,
        C.CHARACTER_MAXIMUM_LENGTH,
        C.NUMERIC_PRECISION,
        C.NUMERIC_SCALE,
        C.DATETIME_PRECISION
INTO    #Columns
FROM    INFORMATION_SCHEMA.COLUMNS C
WHERE   C.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')
        AND C.TABLE_NAME NOT LIKE 'sysdiagram%';

[tranceporter]

So do you reckon it worthwhile to tweak your query to use temp tables? Reducing number of columns from Information_schema.columns will definitely help, as you suggested.

[sjh37]

Made it even faster.

After the insert into #Columns, add this

CREATE NONCLUSTERED INDEX IX_EfPoco_Columns
ON dbo.#Columns (TABLE_NAME)
INCLUDE (TABLE_SCHEMA,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,
DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION);

[tranceporter]

Nice. Knocked it down it from 4 seconds to 3 seconds in my case. This is some good collab :) This should go into next version of Reverse POCO generator. It's definitely going to add lots of performance improvement, especially for chaps with big databases like ours!

[sjh37]

I'll add in this new SQL into the generator, and close this case. Thanks for this!

[tranceporter]

My pleasure. All for the better. Thanks for being active on this thread. Resolved the issues quickly :)

[sjh37]

Do you want me to use tranceporter as your name in the repo comments, or your real name?

[tranceporter]

Are your changes normally labelled as sjh37 or Simon Hughes? This is my first contribution to a public repo, hence the question :) If you label it as sjh37, then tranceporter should do :)

EDIT: Looking at the commit history, it appears under sjh37, so please use tranceporter. It will link to my github account as well.

[ErikEJ]

Maybe test to ensure this does not make perf worse on small dbs (Northwind)

[sjh37]

I am getting this error on using the SQL with northwind

Msg 468, Level 16, State 9, Line 1702 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

which is on this line

ON c.TABLE_SCHEMA = t.TABLE_SCHEMA

[ltippmann]

I'm not sure I'm following this conversation completely. Was my rewritten query helpful?

[tranceporter]

I downloaded Northwind database backup from here:

https://northwinddatabase.codeplex.com/

Restored it on my local SQL server and ran the optimized query in 0 seconds. Returned 190 rows.

Also for AdventureWorks, use this

http://msftdbprodsamples.codeplex.com/releases

The query ran on AdventureWorks in 0 seconds and returned 744 rows.

[tranceporter]

@lukeatron it definitely was, but it was not returning the same number of rows as the original EF query. If you want I can open a separate conversation thread with you :) But your post atleast made think that the POCO query could be optimized for SQL Server 2016. So thanks :)

EDIT: Not sure I can open a new thread of convo. Basically, if you upgrade to the latest version of Reverse POCO generator after Simon has released a new version with the improved query, you should see some performance improvement. Another option is to use the attached query on your big database (which was regenerating POCO's slowly), and see how quickly the query executes :)

efficient query.zip

[tranceporter]

Northwind query results.zip

[ErikEJ]

Simon. Glad you caught the error! The Northwind test results are good.

The error means that the collation of northwind and tempdb mismatch. Potential solution is to explucitly create the temp tables with explicit collation for the varchar columns

[tranceporter]

As Eric says, it's a collation issue. You can also use database_default collation when you are comparing columns. So like this:

SELECT DISTINCT u.TABLE_SCHEMA, u.TABLE_NAME, u.COLUMN_NAME INTO

ForeignKeys

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON u.TABLE_SCHEMA COLLATE database_default = tc.CONSTRAINT_SCHEMA COLLATE database_default AND u.TABLE_NAME = tc.TABLE_NAME AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

[ltippmann]

@tranceporter That's alright. When I have time I want to try to create a patch for the EF6 tooling in VS. It's good to get some confirmation that this might be a good approach. Or at least not a terrible one.