INFORMATION_SCHEMA tables take so long when used in certain ways
0
votes
0
answers
296
views
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'

Asked by Ross Bush
(683 rep)
Jan 4, 2024, 09:02 PM