Can we assume that dates always will be in ascending order, in scope of same Patient?
You cannot assume any order in the input data.
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
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.
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.
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
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.
Thanks for scripts, but rule 2.
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.
castling what about rule 2?
Managed Windows Shared Hosting by OrcsWeb