Sample Header Ad - 728x90

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' enter image description here
Asked by Ross Bush (683 rep)
Jan 4, 2024, 09:02 PM