While attempting to find better ways to parse table schema data, the INFORMATION_SCHEMA
articles were tested.
There must be way more going on behind the scenes then meets the eye. Using sys.* articles, being more verbose, are all reliably fast.
Can anyone explain why using the INFORMATION_SCHEMA
articles produce a query plan the size of a Texas roadmap (see paste a plan)?
Also, the storing the output of INFORMATION_SCHEMA.TABLES
into a #temp or @temp table reduces the query time from 8 minutes to 6 seconds when joined against INFORMATION_SCHEMA.TABLE_CONSTRAINTS
and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
.
Here is a paste the plan -> https://www.brentozar.com/pastetheplan/?id=SyRUqcEd6
The first query takes 2 seconds. The second query takes over 8 minutes.
DECLARE @X TABLE(TableName NVARCHAR(300))
INSERT INTO @X
SELECT T.TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.TABLES T
----2 Seconds 740 Rows
SELECT
CU.COLUMN_NAME AS ColumnName,
T.TableName AS TableName
FROM
@X T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CS ON CS.TABLE_NAME = T.TableName
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON CU.CONSTRAINT_NAME = CS.CONSTRAINT_NAME AND CS.CONSTRAINT_TYPE = 'PRIMARY KEY'
GO
----8:47 Seconds 740 Rows
SELECT
CU.COLUMN_NAME AS ColumnName,
T.TABLE_NAME AS TableName
FROM
INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CS ON CS.TABLE_NAME = T.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON CU.CONSTRAINT_NAME = CS.CONSTRAINT_NAME AND CS.CONSTRAINT_TYPE = 'PRIMARY KEY'
SOS_SCHEDULER_YIELD
waits won't helpINFORMATION_SCHEMA
as much more efficient to join on indexed ids than strings but this diff sounds excessive!select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
is pretty large - but if you look atexec sp_helptext 'INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE'
you can see why.