252 lines
9.5 KiB
Transact-SQL
252 lines
9.5 KiB
Transact-SQL
/******************************************************************************/
|
|
/* */
|
|
/* CUSTOM SPS for test scripts */
|
|
/* */
|
|
/******************************************************************************/
|
|
|
|
/* sp to test timing of sp exec */
|
|
|
|
IF EXISTS (select * from sysobjects where name = 'sp_TimeProcedure')
|
|
BEGIN
|
|
drop procedure sp_TimeProcedure
|
|
END
|
|
go
|
|
|
|
CREATE PROCEDURE sp_TimeProcedure
|
|
@Statement nvarchar(4000)
|
|
AS
|
|
BEGIN
|
|
declare @StartTime datetime, @EndTime datetime
|
|
declare @ExecMs int, @ExecSecs int
|
|
|
|
select @StartTime = getdate()
|
|
exec (@Statement)
|
|
select @EndTime = getdate()
|
|
select @ExecMs = datediff(ms, @StartTime, @EndTime)
|
|
select @ExecSecs = Datediff(ss, @StartTime, @EndTime)
|
|
|
|
select @Statement + ":" + convert(nvarchar(20),@ExecSecs) + " seconds (" + convert(nvarchar(20),@ExecMs) + " ms)"
|
|
return 0
|
|
END
|
|
go
|
|
|
|
create table #Parents (ClassId numeric)
|
|
go
|
|
create table #Children (ClassId int, SuperClassId int)
|
|
go
|
|
|
|
/******************************************************************************/
|
|
/* */
|
|
/* TEST SCRIPTS - stress and performance testing */
|
|
/* */
|
|
/* Create n classes */
|
|
/* Create n instances */
|
|
/* Update n classes */
|
|
/* Update n instances */
|
|
/* Delete n classes */
|
|
/* Delete n instances */
|
|
/* Queries */
|
|
/* */
|
|
/******************************************************************************/
|
|
|
|
/* CREATE A VARIETY OF NEW CLASSES */
|
|
|
|
/******************************************************************************/
|
|
/* */
|
|
/* Class Creation */
|
|
/* */
|
|
/******************************************************************************/
|
|
|
|
set nocount on
|
|
|
|
declare @ClassName nvarchar(450), @ObjectPath nvarchar(450), @ScopePath nvarchar(450), @ParentClass nvarchar(450)
|
|
declare @ClassFlags int, @InsertFlags int
|
|
declare @RetValue numeric
|
|
|
|
declare @ObjectId numeric, @ObjectKey nvarchar(450)
|
|
declare @StartTime datetime, @EndTime datetime
|
|
declare @ExecMs int, @ExecSecs int
|
|
|
|
declare @Prop1 int
|
|
|
|
print 'Creating Class1...'
|
|
select @StartTime = getdate()
|
|
exec sp_InsertClassAndData @ObjectId output, "Class1", "Class1?root", "root:Class1", "root", "", 0, 0,
|
|
"Key1", 8, "", 0, 4, 0, 0, "", @Prop1 output
|
|
select @ExecMs = datediff(ms, @StartTime, getdate())
|
|
|
|
/* Basic classes */
|
|
|
|
select @ClassName = "Class1"
|
|
select @ObjectPath = 'root:Class1'
|
|
select @ObjectKey = 'Class1?root'
|
|
select @ScopePath = 'root'
|
|
select @ParentClass = 'Class1?root'
|
|
|
|
select @ClassFlags = 0
|
|
select @InsertFlags = 0 /* 1=update, 2=insert */
|
|
|
|
select @RetValue = 2
|
|
while (@RetValue < 100)
|
|
BEGIN
|
|
select @ClassName = substring(@ClassName,1,5) + ltrim(convert(varchar(10),@RetValue))
|
|
select @ObjectPath = substring(@ObjectPath,1,29) + ltrim(convert(varchar(10),@RetValue))
|
|
select @ObjectKey = @ClassName + "?root"
|
|
select 'Creating ' + @ClassName + '...'
|
|
select @ObjectId = null
|
|
|
|
select @StartTime = getdate()
|
|
exec sp_InsertClassAndData @ObjectId output, @ClassName, @ObjectKey, @ObjectPath, @ScopePath, @ParentClass, 0, 0,
|
|
"Prop1", 19, "0", 0, 0, 0, 0, "", @Prop1 output
|
|
select @EndTime = getdate()
|
|
select @ExecMs = @ExecMs + datediff(ms,@StartTime,@EndTime)
|
|
select @RetValue = @RetValue + 1
|
|
END
|
|
select @EndTime = getdate()
|
|
|
|
select @ExecMs / 100 MsToExecute
|
|
go
|
|
|
|
/******************************************************************************/
|
|
/* */
|
|
/* Batch Instance Creation */
|
|
/* */
|
|
/******************************************************************************/
|
|
|
|
print 'Testing batch insert procedures...'
|
|
|
|
/* Test of new procedures */
|
|
|
|
set nocount on
|
|
declare @ObjKey nvarchar(450), @ObjPath nvarchar(1024)
|
|
declare @PropID1 int, @PropID2 int
|
|
declare @PropValue1 nvarchar(1024), @PropValue2 nvarchar(1024), @PropValue3 nvarchar(1024)
|
|
declare @ClassID numeric
|
|
declare @ScopeID numeric
|
|
declare @Begin int, @End int
|
|
declare @StartTime datetime, @EndTime datetime
|
|
declare @Time int, @ObjID numeric
|
|
declare @QfrID int
|
|
|
|
select @Time = 0
|
|
|
|
select @Begin = 1
|
|
select @End = @Begin + 1000
|
|
select @ClassID = ClassId from ClassMap where ClassName = 'Class2'
|
|
select @PropID1 = PropertyId from PropertyMap where PropertyName = 'Prop1'
|
|
select @PropID2 = PropertyId from PropertyMap where PropertyName = 'Prop2'
|
|
select @QfrID = PropertyId from PropertyMap where PropertyName = 'InstQfr'
|
|
select @ScopeID = ObjectId from ObjectMap where ObjectKey = 'root'
|
|
|
|
while (@Begin < @End)
|
|
BEGIN
|
|
select @PropValue1 = convert(varchar(20), @Begin)
|
|
select @ObjPath = 'root:Class2.Prop1=' + ltrim(@PropValue1)
|
|
select @ObjKey = ltrim(@PropValue1)+"?Class2?root"
|
|
select @PropValue2 = "1"
|
|
select @PropValue3 = "342"
|
|
|
|
select @StartTime = getdate()
|
|
/* This takes 4 ms/instance */
|
|
|
|
begin transaction
|
|
exec @ObjID = sp_BatchInsertProperty @ObjID, @ObjKey, @ObjPath, @ClassID, @ScopeID, 0, 0, @PropID1, @PropValue1, 0, @PropID2, @PropValue2, 0/*, @PropID2, @PropValue3, 1*/
|
|
exec sp_BatchInsert @ObjID, @ClassId, @ScopeID, @QfrID, "Hello", 0, 0, 0/*, @QfrID, "Goodbye", 0, 0, 1*/
|
|
commit transaction
|
|
|
|
/* The old procs take 25 ms/instance */
|
|
/*
|
|
exec @ObjID = sp_InsertInstance @ClassID, 'root',
|
|
@ObjKey, 0, 0, 0, 0
|
|
exec sp_InsertInstanceNumericData @ClassID, @ObjID, @PropID1, @PropValue1
|
|
exec sp_InsertInstanceNumericData @ClassID, @ObjID, @PropID2, @PropValue2
|
|
*/
|
|
select @EndTime = Getdate()
|
|
select @Time = @Time + datediff(ms, @StartTime, @EndTime)
|
|
|
|
select @Begin = @Begin + 1
|
|
END
|
|
|
|
select @Time/1000 TotalMsPerInstance
|
|
|
|
/******************************************************************************/
|
|
/* */
|
|
/* Instance Deletion */
|
|
/* */
|
|
/******************************************************************************/
|
|
|
|
/* To test instance deletion speed without wiping out the entire
|
|
database, only delete the first instance from each class */
|
|
|
|
declare @ClassID numeric, @ObjId numeric, @Time int
|
|
declare @StartTime datetime, @EndTime datetime
|
|
declare @Count int
|
|
select @Count = 0
|
|
|
|
select @Time = 0
|
|
select @ClassID = ClassId from ClassMap where ClassName = 'Class1'
|
|
while (@ClassID != NULL)
|
|
BEGIN
|
|
select @Count = @Count + 1
|
|
create table #Deleted (ObjId numeric)
|
|
|
|
select @ObjId = min(ObjectId) from ObjectMap a where ClassId = @ClassID
|
|
and NOT EXISTS (select ClassId from ClassMap where a.ObjectId = ClassId)
|
|
select @StartTime = getdate()
|
|
|
|
exec sp_DeleteInstance @ObjId
|
|
|
|
select @EndTime = getdate()
|
|
select @Time = @Time + datediff(ms, @StartTime, @EndTime)
|
|
select @ClassID = min(ClassId) from ClassMap where ClassId > @ClassID
|
|
IF EXISTS (select * from tempdb..sysobjects where name like '#Deleted%')
|
|
drop table #Deleted
|
|
END
|
|
|
|
select @Time / @Count TotalMsPerInstance
|
|
go
|
|
|
|
/******************************************************************************/
|
|
/* */
|
|
/* Class Deletion */
|
|
/* */
|
|
/******************************************************************************/
|
|
|
|
/* There is absolutely no way to make this a fast operation.
|
|
You have to enumerate and delete all instances for each class!!! */
|
|
|
|
declare @ClassID numeric, @Time int
|
|
declare @StartTime datetime, @EndTime datetime
|
|
declare @Count int
|
|
select @Count = 0
|
|
|
|
select @Time = 0
|
|
select @ClassID = ClassId from ClassMap where ClassName = 'Class1'
|
|
while (@ClassID != NULL)
|
|
BEGIN
|
|
select @Count = @Count + 1
|
|
|
|
create table #Deleted (ObjId numeric)
|
|
select @StartTime = getdate()
|
|
exec sp_DeleteClass @ClassID
|
|
select @EndTime = getdate()
|
|
select @Time = @Time + datediff(ms, @StartTime, @EndTime)
|
|
select @ClassID = min(ClassId) from ClassMap where ClassId > @ClassID
|
|
IF EXISTS (select * from tempdb..sysobjects where name like '#Deleted%')
|
|
drop table #Deleted
|
|
END
|
|
|
|
select @Time / @Count TotalMsPerInstance
|
|
|
|
go
|
|
|
|
drop table #Parents
|
|
drop table #Children
|
|
go
|
|
|
|
print '*** DONE ***'
|
|
go
|
|
|
|
set nocount off
|
|
go
|