0

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

9
  • Were you running something else concurrently for the second one? 7mins 14secs of SOS_SCHEDULER_YIELD waits won't help Commented Jan 4 at 21:10
  • 1
    Can you repro the same dismal performance when that isn't running? Or somewhat less dismal? I do tend to avoid INFORMATION_SCHEMA as much more efficient to join on indexed ids than strings but this diff sounds excessive! Commented Jan 4 at 21:15
  • 1
    The execution plan for just select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE is pretty large - but if you look at exec sp_helptext 'INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE' you can see why. Commented Jan 4 at 21:32
  • 1
    @MartinSmith - Ow wow, thanks, it is a bit more transparent now. That server has close to 200 databases with 640 or more tables each.
    – Ross Bush
    Commented Jan 4 at 21:37
  • 2
    You should generally avoid the information_schema views: The case against INFORMATION_SCHEMA views Commented Jan 4 at 22:09

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.