Sql-server – Slow INFORMATION_SCHEMA query

performancequerysql server

We have a .NET Windows application that runs the following query on login to get some information about the database:

SELECT t.TABLE_NAME, ISNULL(pk_ccu.COLUMN_NAME,'') PK, ISNULL(fk_ccu.COLUMN_NAME,'') FK 
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk_tc 
ON pk_tc.TABLE_NAME = t.TABLE_NAME 
AND pk_tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_ccu 
ON pk_ccu.CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME 
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk_tc 
ON fk_tc.TABLE_NAME = t.TABLE_NAME 
AND fk_tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_ccu 
ON fk_ccu.CONSTRAINT_NAME = fk_tc.CONSTRAINT_NAME

Usually this runs in a couple seconds, but on one server running SQL Server 2000, it is taking over four minutes to run. I ran it with the execution plan enabled, and the results are huge, but this part caught my eye (it won't let me post an image):

http://img35.imageshack.us/i/plank.png/

I then updated the statistics on all of the tables that were mentioned in the execution plan:

update statistics sysobjects
update statistics syscolumns
update statistics systypes
update statistics master..spt_values
update statistics sysreferences

But that didn't help. The index tuning wizard doesn't help either, because it doesn't let me select system tables. There is nothing else running on this server, so nothing else could be slowing it down. What else can I do to diagnose or fix the problem on that server?

Best Answer

Left joins are notorious for generating massive recordsets if circumstances permit. I've occasionally run into this sort of problem and I don't have a quick and easy answer. I've found that playing around with the query, e.g. by building up the query one join at a time, is the best way to work out which join is casuing the problem.

Have you tried doing a select count(*) from the tables in the query to see if one of them has an unexpectedly high number off rows?

JR

More thoughts: it isn't clear so far whether the problem lies with the server or if the query is pathological and would kill any server. I suppose you could copy the database to a different server and try the query again.

How about modifying the query to be something like:

SELECT t.TABLE_NAME, ISNULL(pk_ccu.COLUMN_NAME,'') PK, ISNULL(fk_ccu.COLUMN_NAME,'') FK 
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN 
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_ccu
  ON pk_ccu.CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME 
)
 ON pk_tc.TABLE_NAME = t.TABLE_NAME AND pk_tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
LEFT JOIN
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_ccu
  ON fk_ccu.CONSTRAINT_NAME = fk_tc.CONSTRAINT_NAME
)
 ON fk_tc.TABLE_NAME = t.TABLE_NAME AND fk_tc.CONSTRAINT_TYPE = 'FOREIGN KEY'

I think the inner joins ought to be safe since the tc and ccu tables ought to have matching records. Does doing the query this way improve the execution time?

Related Topic