-- content removed by administrator --

Asif,

You cannot post solutions in the discussion thread. If you would like to submit a solution, please use the 'submit' link on the page.

Hi,

Which version(s) of SQL? 2005/2008/2008r2?

Regards David

The evaluation software runs on a SQL Server 2008 R2 instance. However, you can write a solution that runs on any version of SQL Server - SQL Server 2008 R2 or earlier versions.

The dearth of comments here suggests that this particular forum thread might be a touch harder to find than usual.
Normally, there is a link to the thread in the challenge writeup...

I see your point. The new challenge page has a comment section in the footer. You can write a comment directly from the comment page and it will be posted to this thread. It also has a link "view comments" which will take you directly to this thread.

So it does; I'm not sure how I missed that originally.
That said, posting direct from the challenge might lead to a lot of duplicates questions, especially if there is not a list of recent/upvoted comments on the main page. We'll see how it goes...

OK, well, provided I've understood the requirements, the following should be able to generate reasonably repeatable load test data for the challenge. Please feel free to correct me if I am wrong...

TRUNCATE TABLE tc1_birds
TRUNCATE TABLE tc1_fruits
TRUNCATE TABLE tc1_grains

DECLARE 
     @seed INT
    ,@birds INT
    ,@max_fruits_per_bird INT
    ,@max_grains_per_bird INT
    ,@empty_food_chance FLOAT
    ,@seed_random FLOAT
    ,@loop INT

-- PARAMETERS -------------------------------------------------------------------------------------
SELECT
     @seed = 1
    ,@birds = 1000
    ,@max_fruits_per_bird = 100
    ,@max_grains_per_bird = 100
    ,@empty_food_chance = 0.1
---------------------------------------------------------------------------------------------------

PRINT('Populating TC1_BIRDS, TC1_FRUITS and TC1_GRAINS with load test data')

-- INSERT BIRDS -----------------------------------------------------------------------------------
PRINT('...Inserting ' + CONVERT(VARCHAR(MAX), @birds) + ' birds into TC1_BIRDS')
;WITH cte_birds AS (
    SELECT 
    	 code = n
    	,name = 'B#' + CONVERT(VARCHAR(MAX), n)
    	,rn = ROW_NUMBER() OVER (ORDER BY NEWID())
    FROM tsqlc_tally t
    WHERE n BETWEEN 1 AND @birds
)
INSERT INTO tc1_birds (code, name)
SELECT code, name FROM cte_birds ORDER BY rn
---------------------------------------------------------------------------------------------------

-- INSERT GRAINS/FRUITS ---------------------------------------------------------------------------
CREATE TABLE #tmp_grains(code INT, grain VARCHAR(10))
CREATE TABLE #tmp_fruits(code INT, fruit VARCHAR(10))
SELECT @loop = 1, @seed_random = RAND(@seed)

WHILE @loop BETWEEN 1 AND @birds BEGIN
    IF @empty_food_chance > RAND() BEGIN
    	PRINT('...Bird #' + CONVERT(VARCHAR(MAX), @loop) + ' receives no fruits or grain')
    	SET @loop = @loop + 1
    	CONTINUE
    END

    PRINT('...Inserting up to ' + CONVERT(VARCHAR(MAX), @max_grains_per_bird) + ' grains and ' +
    	  CONVERT(VARCHAR(MAX), @max_fruits_per_bird) + ' fruits into TC1_GRAINS and TC1_FRUITS for bird #' +
    	  CONVERT(VARCHAR(MAX), @loop))

    ;WITH cte_grains AS (
    	SELECT 
    		 code = @loop
    		,grain = 'G#' + CONVERT(VARCHAR(MAX), n) + '.' + CONVERT(VARCHAR(MAX), @loop)
    		,rn = ROW_NUMBER() OVER (ORDER BY NEWID())
    	FROM tsqlc_tally
    	WHERE n BETWEEN 1 AND (RAND() * @max_grains_per_bird)
    )
    INSERT INTO #tmp_grains (code, grain)
    SELECT code, grain FROM cte_grains ORDER BY rn

    ;WITH cte_fruits AS (
    	SELECT 
    		 code = @loop
    		,fruit = 'F#' + CONVERT(VARCHAR(MAX), n) + '.' + CONVERT(VARCHAR(MAX), @loop)
    		,rn = ROW_NUMBER() OVER (ORDER BY NEWID())
    	FROM tsqlc_tally
    	WHERE n BETWEEN 1 AND (RAND() * @max_fruits_per_bird)
    )
    INSERT INTO #tmp_fruits (code, fruit)
    SELECT code, fruit FROM cte_fruits ORDER BY rn

    SET @loop = @loop + 1
END

;WITH cte_grains AS (
    SELECT code, grain
    	,rn = ROW_NUMBER() OVER (ORDER BY NEWID())
    FROM #tmp_grains
)
INSERT INTO tc1_grains (code, grain)
SELECT code, grain FROM cte_grains ORDER BY rn

;WITH cte_fruits AS (
    SELECT code, fruit
    	,rn = ROW_NUMBER() OVER (ORDER BY NEWID())
    FROM #tmp_fruits
)
INSERT INTO tc1_fruits (code, fruit)
SELECT code, fruit FROM cte_fruits ORDER BY rn

DROP TABLE #tmp_grains
DROP TABLE #tmp_fruits
---------------------------------------------------------------------------------------------------

DECLARE @summary VARCHAR(MAX)
SELECT @summary = ('Done; inserted ' + (SELECT CONVERT(VARCHAR(MAX), COUNT(1)) FROM tc1_birds) + ' birds, ' + 
    			  (SELECT CONVERT(VARCHAR(MAX), COUNT(1)) FROM tc1_grains) + ' grains and ' + 
    			  (SELECT CONVERT(VARCHAR(MAX), COUNT(1)) FROM tc1_fruits) + ' fruits')
PRINT(@summary)

SELECT 'tc1_birds', * FROM tc1_birds
SELECT 'tc1_fruits', * FROM tc1_fruits
SELECT 'tc1_grains', * FROM tc1_grains

Edit #1: Forms that require JavaScript to ensure that your post is formatted correctly can go die in a fire...

Edit #2: Made it more likely that a row with NULL fruit and NULL grain would appear...

Edit #3: Really made it more likely that a row with NULL fruit and NULL grain would appear...

I understand.

That said, posting direct from the challenge might lead to a lot of duplicates questions, especially if there is not a list of recent/upvoted comments on the main page. We'll see how it goes...

You are right. Let us see how it goes. One option is to show the comment box on the challenge page only if there are no other comments in the thread. So this will be convenient to post the first comment. After we get some feedback we can get back to it and rework a little :-)

Thanks for the load testing data. I will pass it to the evaluation team who will be very happy to review this.

Using the test harness I get the following stats

(61160 row(s) affected) Table 'TC1BIRDS'. Scan count 5, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC1GRAINS'. Scan count 5, logical reads 137, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC1_FRUITS'. Scan count 5, logical reads 141, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.