May 20, 2014 at 1:29 pm
I think the most I've seen 12 FKs on a table.
Tom
May 20, 2014 at 1:59 pm
Nice, inventive way to form a QotD. Thanks, Andy!
May 20, 2014 at 2:10 pm
Nice question, clear, unambiguous.
But it's kind of interesting .... you know that Chinese proverb (or maybe urban myth?) about interesting times?
The interesting thing here is that 253 is a suggested limit on breadth immediately below a node in a tree all of whose members have the same key, and limts neither the number of nodes in the whole tree nor the numer of nodes at any level not adjacent to the root (root is above? terminology is loony, but that's pretty normal). So for any practical purposes it seems rather meaningless: consider 127 tables referencing table A attribute X, and 127 tables referencing table B attribute X, and attribute X in table A referencing table C attribute X, and attribute X in table B referencing table C attribute X; that delivers in effect 254 tables referencing (indirectly) table C attribute X so the limit as expressed is pretty meaningless. Perhaps there is some real problem with going beyond 253 direct attributes but there's no indication of how that problem arises and whether the same limit applies to indirect references as in the suggested example above, and perhaps there would have to be some pretty bizarre logic to make 253 a performance turning point for direct but not for indirect references.
Tom
May 20, 2014 at 2:22 pm
TomThomson (5/20/2014)
Nice question, clear, unambiguous.But it's kind of interesting .... you know that Chinese proverb (or maybe urban myth?) about interesting times?
The interesting thing here is that 253 is a suggested limit on breadth immediately below a node in a tree all of whose members have the same key, and limts neither the number of nodes in the whole tree nor the numer of nodes at any level not adjacent to the root (root is above? terminology is loony, but that's pretty normal). So for any practical purposes it seems rather meaningless: consider 127 tables referencing table A attribute X, and 127 tables referencing table B attribute X, and attribute X in table A referencing table C attribute X, and attribute X in table B referencing table C attribute X; that delivers in effect 254 tables referencing (indirectly) table C attribute X so the limit as expressed is pretty meaningless. Perhaps there is some real problem with going beyond 253 direct attributes but there's no indication of how that problem arises and whether the same limit applies to indirect references as in the suggested example above, and perhaps there would have to be some pretty bizarre logic to make 253 a performance turning point for direct but not for indirect references.
Interesting, 253 is (2^8) - 3, 127 is (2^7) -1, so for this equation, where has 1 gone?
Good question Andy, made me ponder on the reasons for the suggested limits. I have seen this and a lot worse when the schema has been derived from (read created by) the likes of Entity Framework.
😎
May 20, 2014 at 3:11 pm
I decided to run a quick experiment to see if I could find any limits. Here is the code I used to create a table with lots of foreign keys:
USE tempdb;
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'QotD')
BEGIN;
ALTER DATABASE QotD SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE QotD;
END;
CREATE DATABASE QotD;
GO
USE QotD;
DECLARE @Base nvarchar(MAX) = N'',
@Ref nvarchar(MAX) = N'CREATE TABLE dbo.Ref(PKCol int NOT NULL, ';
DECLARE @Max int = 1000,
@i int = 1;
WHILE @i < @Max
BEGIN;
SET @Base += N'CREATE TABLE dbo.T' + CAST(@i AS nvarchar(20))
+ N'(Col' + CAST(@i AS nvarchar(20)) + N' int NOT NULL PRIMARY KEY); ';
SET @Ref += N'Col' + CAST(@i AS nvarchar(20))
+ N' int NOT NULL DEFAULT(1) REFERENCES dbo.T' + CAST(@i AS nvarchar(20)) + N', ';
SET @i += 1;
END;
SET @Ref += N'PRIMARY KEY(PKCol));'
EXECUTE (@Base);
EXECUTE (@Ref);
/*
-- Execute the above, then select the below and execute to check time, or get estimated plan for fun
SET STATISTICS TIME ON;
INSERT INTO dbo.Ref DEFAULT VALUES;
go
SET STATISTICS TIME OFF;
*/
By changing the value of @max-2, I could specify the number of foreign keys. It will create lots of tables, but you can just drop the QotD database afterwards.
I didn't see a noticeable performance difference between 250 and 260. I noticed a slight slowdown at 500, and a severe slowdown at 750. At a 1000, the query failed with error 701: insufficient system memory in resourcepool 'default' to run the query. I got the same error when requesting the estimated plan, so in spite of the error message, I think that the compilation failed. More memory (I have 8GB installed, 6GB available to SQL Server) might have helped.
I was impressed to see that Management Studio was able to render the graphical plan for up to 250 foreign keys - SQL Sentry Plan Explorer already crashed at "only" 200 foreign keys.
May 20, 2014 at 3:19 pm
Hugo Kornelis (5/20/2014)
I decided to run a quick experiment to see if I could find any limits. Here is the code I used to create a table with lots of foreign keys:
USE tempdb;IF EXISTS (SELECT * FROM sys.databases WHERE name = 'QotD')
BEGIN;
ALTER DATABASE QotD SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE QotD;
END;
CREATE DATABASE QotD;
GO
USE QotD;
DECLARE @Base nvarchar(MAX) = N'',
@Ref nvarchar(MAX) = N'CREATE TABLE dbo.Ref(PKCol int NOT NULL, ';
DECLARE @Max int = 1000,
@i int = 1;
WHILE @i < @Max
BEGIN;
SET @Base += N'CREATE TABLE dbo.T' + CAST(@i AS nvarchar(20))
+ N'(Col' + CAST(@i AS nvarchar(20)) + N' int NOT NULL PRIMARY KEY); ';
SET @Ref += N'Col' + CAST(@i AS nvarchar(20))
+ N' int NOT NULL DEFAULT(1) REFERENCES dbo.T' + CAST(@i AS nvarchar(20)) + N', ';
SET @i += 1;
END;
SET @Ref += N'PRIMARY KEY(PKCol));'
EXECUTE (@Base);
EXECUTE (@Ref);
/*
-- Execute the above, then select the below and execute to check time, or get estimated plan for fun
SET STATISTICS TIME ON;
INSERT INTO dbo.Ref DEFAULT VALUES;
go
SET STATISTICS TIME OFF;
*/
By changing the value of @max-2, I could specify the number of foreign keys. It will create lots of tables, but you can just drop the QotD database afterwards.
I didn't see a noticeable performance difference between 250 and 260. I noticed a slight slowdown at 500, and a severe slowdown at 750. At a 1000, the query failed with error 701: insufficient system memory in resourcepool 'default' to run the query. I got the same error when requesting the estimated plan, so in spite of the error message, I think that the compilation failed. More memory (I have 8GB installed, 6GB available to SQL Server) might have helped.
I was impressed to see that Management Studio was able to render the graphical plan for up to 250 foreign keys - SQL Sentry Plan Explorer already crashed at "only" 200 foreign keys.
+1
😎
May 20, 2014 at 3:56 pm
Good shooting, Hugo1 🙂 I was too lazy to experiment :blush:, so I'm glad to see your results.
edit: "Hugo1"! My left little finger has failed me - that "1" should be a "!".
Tom
May 20, 2014 at 4:00 pm
TomThomson (5/20/2014)
Hugo1
So you finally discovered the existence of my evil twin? (Hugo2)
May 20, 2014 at 4:05 pm
Hugo Kornelis (5/20/2014)
TomThomson (5/20/2014)
Hugo1So you finally discovered the existence of my evil twin? (Hugo2)
(a) See edit to above post.
(b) I'm tempted, but won't.
(c) Well, you know about my evil twin already, so it's only fair.
Tom
May 21, 2014 at 3:21 am
TomThomson (5/20/2014)
Good shooting, Hugo1 🙂 I was too lazy to experiment :blush:, so I'm glad to see your results.edit: "Hugo1"! My left little finger has failed me - that "1" should be a "!".
As Master Oogway says.. "there are no accidents"... It was meant to be as Hugo1 (as in Chosen-one) for this QToD. 😀
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 21, 2014 at 3:24 am
Hugo Kornelis (5/20/2014)
I decided to run a quick experiment to see if I could find any limits. Here is the code I used to create a table with lots of foreign keys...
Thank you Hugo, this is excellent. 🙂
(I was in between of creating such script late night and then one yawn came... then thud)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 21, 2014 at 4:18 am
Thanks Andy for the question...
I have a table with 30 foreign key reference attached to on which I am working on, with on an average 5 lac record being updated daily...
Bhaskar Shetty
May 21, 2014 at 6:32 am
Good question and great followup. Thanks for a good one, Andy.
May 21, 2014 at 6:36 am
Hugo, that's good stuff! Should send a note to the Sentry guys too.
May 21, 2014 at 7:33 am
Nice experiment Hugo. Saves me the effort!!
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply