StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCreating test load for self-referencing table
primarykey
Id
17400338
data
AcceptedAnswerId
17407431
AnswerCount
3
ClosedDate
CommentCount
3
CommunityOwnedDate
CreationDate
2013-07-01T08:38:24.210
FavoriteCount
0
LastActivityDate
2013-07-02T15:35:07.410
LastEditDate
2013-07-01T09:47:21.863
LastEditorUserId
1632534
OwnerUserId
1632534
ParentId
0
PostTypeId
1
Score
0
ViewCount
415
LastEditorDisplayName
text
Body
I have to do some SQL Server 2008 R2 performance testing and it would be very convenient to do it using only SSMS and SQL Server, without additional application support. One of the tests I have to do is querying a self-referencing table (tree-like structure) with unknown content. So, for a start I would have to load something like 100K - 1M randomly parent-child-related rows into this table. <pre><code>CREATE TABLE Test2 ( ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, ParentID int NULL REFERENCES Test2 (ID)) </code></pre> I am currently trying with SSMS and this script to load 10K rows into the table: <pre><code>SET NOCOUNT ON INSERT INTO Test2 (ParentID) VALUES (NULL) DECLARE @n int = 0 ;WHILE(1=1) BEGIN --PRINT @n INSERT INTO Test2 (ParentID) SELECT TOP 1 ID FROM Test2 ORDER BY NEWID() SET @n = @n + 1 IF(@n >= 9999) BREAK END SET NOCOUNT OFF </code></pre> My problem is that it runs something like 2m 45s on my laptop. You can imagine how long it would take to load 100K or even 1M records this way. I would like to have a faster way to load this random tree-like structure into database table using TSQL? EDIT: After Mitch Wheat's suggestion, I replaced <pre><code>SELECT TOP 1 ID FROM Test2 ORDER BY NEWID() </code></pre> with <pre><code>SELECT TOP 1 ID FROM Test2 WHERE ID >= RAND(CHECKSUM(NEWID())) * (SELECT MAX(ID) FROM Test2) </code></pre> Regarding random row selection, results really look uniformly distributed. Execution time falls from 160s to 5s (!) -> this enables me to insert 100K records in ~60s. However, inserting 1M records using my RBAR script is still very slow and I'm still searching for possible set-based expression to fill my table. If it exists. Now, after ~10mins of filling random data I have 1M rows. It is slow but acceptable. However, to copy this data to another table using batch insert it takes <10s. <pre><code>SELECT * INTO Test3 FROM Test2 </code></pre> So, I believe some form of batch insert could speed up the process.
Tags
<performance><tsql><sql-server-2008-r2><self-referencing-table>
Title
Creating test load for self-referencing table
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USOzrenTkalcecKrznaric
UserOwnerUserId
1. USOzrenTkalcecKrznaric
plurals
PostLinksPostIdRelatedPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.