Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 79 - Finding the Islands

33   Comments   

Unsubscribe from Notifications
  • Can we assume that dates always will be in ascending order, in scope of same Patient?

    commented on Apr 4 2012 2:06AM  .  Report Abuse This post is not formatted correctly
    Adam Tokarski
    58 · 3% · 1021
  • You cannot assume any order in the input data.

    commented on Apr 4 2012 3:31AM  .  Report Abuse This post is not formatted correctly
    dishdy
    17 · 10% · 3263
  • That is my script for mass data.

    It's full randomized, I am going, in near future, to prepare version with not so randomly spread to easy stats compare.

    SET NOCOUNT ON
    DECLARE @MaxCnt INT,
        	@iCnt INT,
        	@DateZero DATETIME,
        	@StartDate DATETIME,
        	@EndDate DATETIME,
        	@PatientId INT
    SET @MaxCnt = 10000
    SET @iCnt = 0
    SET @DateZero = '20010101'
    SET @StartDate = @DateZero
    SET @PatientId = ROUND(RAND(CAST(NEWID() AS BINARY(2))), 4) * 10000 
    
    WHILE @iCnt < @MaxCnt 
    BEGIN
    SET @iCnt = @iCnt + 1
    
    SET @EndDate = DATEADD(day, ROUND(RAND(CAST(NEWID() AS BINARY(2))), 2) * 100, @StartDate)
    
    INSERT INTO TC79(PatientID,AdmissionDate,DischargeDate,Cost)
    SELECT @PatientId,@StartDate,@EndDate,ROUND(RAND(CAST(NEWID() AS BINARY(2))), 4) * 100 
    
    SET @StartDate = DATEADD(day, ROUND(RAND(CAST(NEWID() AS BINARY(2))), 0) * ROUND(RAND(CAST(NEWID() AS BINARY(2))), 2) * 100 + 1, @EndDate )	
    
    IF ROUND(RAND(CAST(NEWID() AS BINARY(2))), 2) < 0.25 
    BEGIN
    	SET @PatientId = ROUND(RAND(CAST(NEWID() AS BINARY(2))), 4) * 10000	
    	SET @StartDate = DATEADD(day, ROUND(RAND(CAST(NEWID() AS BINARY(2))), 0) * ROUND(RAND(CAST(NEWID() AS BINARY(2))), 2) * 100 + 1, @DateZero )	
    END
    ELSE
    	SET @StartDate = DATEADD(day, ROUND(RAND(CAST(NEWID() AS BINARY(2))), 0) * ROUND(RAND(CAST(NEWID() AS BINARY(2))), 2) * 100 + 1, @EndDate )	
    
    END
    
    commented on Apr 4 2012 9:31AM  .  Report Abuse This post is not formatted correctly
    Adam Tokarski
    58 · 3% · 1021
  • The purpose of a load test script is to provide the same data for everyone. Your script generates different data every time which makes it difficult to compare performance statistics. You should remove all parameters to the RAND() function and precede the first call to the RAND() function with:

    set @PatientId=RAND(1)

    This way everyone will generate the same data.

    With this modification, my statistics are as follows:

    (6585 row(s) affected)
    Table 'Worktable'. Scan count 1, logical reads 197652, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TC79'. Scan count 4, logical reads 196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
     SQL Server Execution Times:
       CPU time = 9672 ms,  elapsed time = 9706 ms.
    

    Another useful measure for determining if we are all getting the same answer is to generate a checksum. In this case, doing a SUM(PatientId) of the result produces 33156119.

    commented on Apr 4 2012 10:28AM  .  Report Abuse This post is not formatted correctly
    dishdy
    17 · 10% · 3263
  • Well, I just tweaked my solution a bit and I now get the following:

    (6585 row(s) affected)
    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.
    Table 'TC79'. Scan count 4, logical reads 196, physical reads 1, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
     SQL Server Execution Times:
       CPU time = 62 ms,  elapsed time = 359 ms.
    
    commented on Apr 4 2012 10:32AM  .  Report Abuse This post is not formatted correctly
    dishdy
    17 · 10% · 3263
  • OK, this is your script with the changes:

    SET NOCOUNT ON
    DECLARE @MaxCnt INT,
            @iCnt INT,
            @DateZero DATETIME,
            @StartDate DATETIME,
            @EndDate DATETIME,
            @PatientId INT
    SET @MaxCnt = 10000
    SET @iCnt = 0
    SET @DateZero = '20010101'
    SET @StartDate = @DateZero
    set @PatientId = RAND(1)
    SET @PatientId = ROUND(RAND(), 4) * 10000 
    
    WHILE @iCnt < @MaxCnt 
    BEGIN
    SET @iCnt = @iCnt + 1
    
    SET @EndDate = DATEADD(day, ROUND(RAND(), 2) * 100, @StartDate)
    
    INSERT INTO TC79(PatientID,AdmissionDate,DischargeDate,Cost)
    SELECT @PatientId,@StartDate,@EndDate,ROUND(RAND(), 4) * 100 
    
    SET @StartDate = DATEADD(day, ROUND(RAND(), 0) * ROUND(RAND(), 2) * 100 + 1, @EndDate ) 
    
    IF ROUND(RAND(), 2) < 0.25 
    BEGIN
        SET @PatientId = ROUND(RAND(), 4) * 10000	
        SET @StartDate = DATEADD(day, ROUND(RAND(), 0) * ROUND(RAND(), 2) * 100 + 1, @DateZero )	
    END
    ELSE
        SET @StartDate = DATEADD(day, ROUND(RAND(), 0) * ROUND(RAND(), 2) * 100 + 1, @EndDate )	
    
    END
    
    commented on Apr 4 2012 12:00PM  .  Report Abuse This post is not formatted correctly
    dishdy
    17 · 10% · 3263
  • The other thing you forget is to clear the table before you start. In any case, I'm assuming you are including the sample data. I have set @MaxCnt to 100000 and these are my stats:

    (78795 row(s) affected)
    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.
    Table 'TC79'. Scan count 4, logical reads 1924, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
     SQL Server Execution Times:
       CPU time = 969 ms,  elapsed time = 4172 ms.
    

    Oh yes, the checksum now is 393831490.

    commented on Apr 4 2012 12:08PM  .  Report Abuse This post is not formatted correctly
    dishdy
    17 · 10% · 3263
  • Thanks for scripts, but rule 2.

    commented on Apr 4 2012 2:57PM  .  Report Abuse This post is not formatted correctly
    castling
    96 · 2% · 562
  • Hey, that script was only to test of performance on big load data.

    Although - as I mentioned - I wanted to redesign it to use RAND(const) in place of RAND(NEWID), thanks to replace it. In question of clear data, I didn't find to necessary to add to script TRUNCATE, but yes, of course, it's handy to have it at one place.

    Good to know, you're looking at CHECKSUM - it's very smart.

    Your stats look pretty good - as only I regain access to my machine, I will match with mine, but - as far I remember - that was comparable.

    commented on Apr 4 2012 2:58PM  .  Report Abuse This post is not formatted correctly
    Adam Tokarski
    58 · 3% · 1021
  • castling what about rule 2?

    commented on Apr 4 2012 3:00PM  .  Report Abuse This post is not formatted correctly
    Adam Tokarski
    58 · 3% · 1021
Previous 1 | 2 | 3 | 4 Next

Your Comment


Sign Up or Login to post a comment.

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.