set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <4/29/2007>
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[SP_PT_029]
@rowNumber [bigint] = 1000
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @int64Val bigint
DECLARE @int32Val int
DECLARE @strVal nchar(20)
DECLARE @dtVal DATETIME
DECLARE @blobVal binary(16)
DECLARE @i int
DECLARE @duration INT
DECLARE @begin DATETIME
DECLARE @end DATETIME
DECLARE @execTime DATETIME
SELECT @i=0
DECLARE @beginAll DATETIME
select @beginAll = GETDATE()
-- Insert a row to table Result_PT_029
BEGIN
SELECT @execTime = GETDATE()
BEGIN TRANSACTION
INSERT INTO [TestSTCClient].[dbo].[RESULT_PT_029]
([ExecTime], [RowNumber])
VALUES (@execTime, @rowNumber);
COMMIT TRANSACTION
END
-- Drop table PT_029
PRINT 'Dropping table ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
DROP TABLE [dbo].[PT_029]
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Delete] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Create table PT_029
CREATE TABLE [dbo].[PT_029](
[Column_Int64] [bigint] NOT NULL,
[Column_Int32] [int] NULL,
[Column_String] [nchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Column_DateTime] [datetime] NULL,
[Column_Blob] [binary](16) NULL
)
-- Set values and append rows
PRINT 'Importing data ...'
BEGIN
select @begin = GETDATE()
DECLARE @transRows bigint
SET @transRows = 50000 -- 50000 rows in a time.
DECLARE @remainingRows bigint
DECLARE @rowNo bigint
SET @rowNo = 0
SET @remainingRows = @rowNumber
WHILE(@remainingRows > 0)
BEGIN
DECLARE @rowsImporting bigint
IF(@remainingRows >= @transRows)
SET @rowsImporting = @transRows
ELSE
SET @rowsImporting = @remainingRows
BEGIN TRANSACTION
SET @i=0
WHILE(@i < @rowsImporting)
BEGIN
SELECT @int64Val = @rowNo
SELECT @int32Val = CAST(RAND(@rowNo+1) * 1000000 AS int)
exec SP_GenerateString 1, 1, 1, 20, @strVal OUT --Generate a random string
--SELECT @strVal = 'abcdefghijabcdefghij'
SELECT @dtVal = GETDATE()
DECLARE @randomString varchar(16)
exec SP_GenerateString 0, 1, 1, 16, @randomString OUT
SELECT @blobVal = CAST(@randomString AS binary(16))
INSERT INTO [TestSTCClient].[dbo].[PT_029]
([Column_Int64]
,[Column_Int32]
,[Column_String]
,[Column_DateTime]
,[Column_Blob])
VALUES (@int64Val, @int32Val, @strVal, @dtVal, @blobVal);
SELECT @i=@i+1
SELECT @rowNo = @rowNo + 1
END
SET @remainingRows = @remainingRows - @transRows
DECLARE @status varchar(200)
IF (@remainingRows > 0)
SET @status = 'Imported ' + CAST((@rowNumber - @remainingRows) as varchar(20)) + ' rows.'
ELSE
SET @status = 'Imported ' + CAST(@rowNumber as varchar(20)) + ' rows.'
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Status] = @status
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
PRINT 'Building index ...'
--Build index for each column.
BEGIN TRANSACTION
Alter TABLE PT_029 ADD PRIMARY KEY(Column_Int64)
CREATE INDEX IX_PT_029_INT64 ON dbo.PT_029(Column_Int64)
CREATE INDEX IX_PT_029_INT32 ON dbo.PT_029(Column_Int32)
CREATE INDEX IX_PT_029_String ON dbo.PT_029(Column_String)
CREATE INDEX IX_PT_029_DateTime ON dbo.PT_029(Column_DateTime)
CREATE INDEX IX_PT_029_Blob ON dbo.PT_029(Column_Blob)
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [AppendRows] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Count on Column_Int64
PRINT 'Agg_Count on Column_Int64 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(column_int64) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Count_Int64] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Count on Column_Int32
PRINT 'Agg_Count on Column_Int32 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(column_int32) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Count_Int32] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Count on Column_String
PRINT 'Agg_Count on Column_String ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(column_string) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Count_String] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Count on Column_DateTime
PRINT 'Agg_Count on Column_DateTime ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(column_datetime) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Count_DateTime] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Count on Column_Blob
PRINT 'Agg_Count on Column_Blob ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(column_blob) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Count_Blob] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_DistinctCount on Column_Int64
PRINT 'Agg_DistinctCount on Column_Int64 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(distinct column_int64) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_DistinctCount_Int64] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_DistinctCount on Column_Int32
PRINT 'Agg_DistinctCount on Column_Int32 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(distinct column_int32) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_DistinctCount_Int32] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_DistinctCount on Column_String
PRINT 'Agg_DistinctCount on Column_String ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(distinct column_string) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_DistinctCount_String] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_DistinctCount on Column_DateTime
PRINT 'Agg_DistinctCount on Column_DateTime ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(distinct column_datetime) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_DistinctCount_DateTime] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_DistinctCount on Column_Blob
PRINT 'Agg_DistinctCount on Column_Blob ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT COUNT(distinct column_blob) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_DistinctCount_Blob] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Max on Column_Int64
PRINT 'Agg_Max on Column_Int64 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MAX(column_int64) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Max_Int64] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Max on Column_Int32
PRINT 'Agg_Max on Column_Int32 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MAX(column_int32) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Max_Int32] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Max on Column_String
PRINT 'Agg_Max on Column_String ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MAX(column_string) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Max_String] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Max on Column_DateTime
PRINT 'Agg_Max on Column_DateTime ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MAX(column_datetime) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Max_DateTime] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Max on Column_Blob
PRINT 'Agg_Max on Column_Blob ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MAX(column_blob) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Max_Blob] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Min on Column_Int64
PRINT 'Agg_Min on Column_Int64 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MIN(column_int64) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Min_Int64] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Min on Column_Int32
PRINT 'Agg_Min on Column_Int32 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MIN(column_int32) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Min_Int32] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Min on Column_String
PRINT 'Agg_Min on Column_String ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MIN(column_string) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Min_String] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Min on Column_DateTime
PRINT 'Agg_Min on Column_String ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MIN(column_datetime) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Min_DateTime] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Min on Column_Blob
PRINT 'Agg_Min on Column_Blob ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT MIN(column_blob) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Min_Blob] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Sum on Column_Int64
PRINT 'Agg_Sum on Column_Int64 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT SUM(column_int64) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Sum_Int64] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
-- Agg_Sum on Column_Int32
PRINT 'Agg_Sum on Column_Int32 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT SUM(CAST(column_int32 AS BIGINT)) FROM PT_029
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Agg_Sum_Int32] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select 1 row, all columns
PRINT 'Select 1 row, all columns ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT * FROM [PT_029] WHERE [Column_Int64] > 0 and [Column_Int64] < 2
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_1_row] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select 10 row, all columns
PRINT 'Select 10 row, all columns ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT * FROM [PT_029] WHERE [Column_Int64] > 0 and [Column_Int64] < 11
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_10_row] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select 100 row, all columns
PRINT 'Select 100 row, all columns ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT * FROM [PT_029] WHERE [Column_Int64] > 0 and [Column_Int64] < 101
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_100_row] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select 1000 row, all columns
PRINT 'Select 1000 row, all columns ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT * FROM [PT_029] WHERE [Column_Int64] > 0 and [Column_Int64] < 1001
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_1000_row] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select 10000 row, all columns
PRINT 'Select 10000 row, all columns ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT * FROM [PT_029] WHERE [Column_Int64] > 0 and [Column_Int64] < 10001
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_10000_row] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select 10000 row, only column string
PRINT 'Select 10000 row, only column string ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT [Column_String] FROM [PT_029] WHERE [Column_Int64] > 0 and [Column_Int64] < 10001
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_10000_row_string] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select 100000 row, only column string
PRINT 'Select 100000 row, only column string ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT [Column_String] FROM [PT_029] WHERE [Column_Int64] > 0 and [Column_Int64] < 100001
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_100000_row_string] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select 1000000 row, only column string
PRINT 'Select 1000000 row, only column string ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT [Column_String] FROM [PT_029] WHERE [Column_Int64] > 0 and [Column_Int64] < 1000001
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_1000000_row_string] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select top 1
PRINT 'Select top 1 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT top 1 * FROM [PT_029]
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_top_1] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select top 10
PRINT 'Select top 10 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT top 10 * FROM [PT_029]
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_top_10] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select top 100
PRINT 'Select top 100 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT top 100 * FROM [PT_029]
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_top_100] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select top 1000
PRINT 'Select top 1000 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT top 1000 * FROM [PT_029]
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_top_1000] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select top 10000
PRINT 'Select top 10000 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT top 10000 * FROM [PT_029]
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_top_10000] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
--Select top 100000
PRINT 'Select top 100000 ...'
BEGIN
select @begin = GETDATE()
BEGIN TRANSACTION
SELECT top 100000 * FROM [PT_029]
COMMIT TRANSACTION
select @end = GETDATE()
SELECT @duration = DATEDIFF(millisecond, @begin, @end)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Select_top_100000] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
DECLARE @endAll DATETIME
select @endAll = GETDATE()
BEGIN
SELECT @duration = DATEDIFF(hour, @beginAll, @endAll)
BEGIN TRANSACTION
UPDATE [TestSTCClient].[dbo].[Result_PT_029]
SET [Duration] = @duration
WHERE [ExecTime] = @execTime;
COMMIT TRANSACTION
END
END
No comments:
Post a Comment