存储过程性能测试

begin
declare @popedomname nvarchar(2000)
declare @id int, @fatherid int;
set @popedomname = '';

select @fatherid = FatherId, @popedomname = PopedomName + ',',@id =[ID]  from PUB_Popedom where ClassName like '%EmployeeGrop%';

while @fatherid <>0
begin
select @fatherid = FatherId,@popedomname = @popedomname + PopedomName + ',' from PUB_Popedom where ID = @fatherid;
end
select left(@popedomname,len(@popedomname)-1);
end 

select  Id,LineName,Img,STAAvg,ClickNum,PrimaryPrice  from V_TravelLineStarList where  LogicDel<>1 and AuditTag=0 and StartCity like '1,7%' and OverCity like '1,7%' and getdate()>isnull(TimeStart,'1900-1-1') and getdate()<isnull(TimeOver,'2088') and id<20000

declare @i int
select * from V_TravelLineStarList where id<5000

exec dbo.ProcCustomTaxisPage  'V_TravelLineStarList','id','LogicDel<>1 and AuditTag=0 and StartCity like ''1,7%'' and OverCity like ''1,7%'' and getdate()>isnull(TimeStart,''1900-1-1'') and getdate()<isnull(TimeOver,''2088'') and id<20000',20,2,'id',1,'LineName,Img,STAAvg,ClickNum,PrimaryPrice',100000
declare @i int
set @i=0
select getdate()
while (@i<100)
begin
exec dbo.ProcCustomTaxisPage  '(select  Id,LineName,Img,STAAvg,ClickNum,PrimaryPrice  from V_TravelLineStarList where  LogicDel<>1 and AuditTag=0 and StartCity like ''1,7%'' and OverCity like ''1,7%'' and getdate()>isnull(TimeStart,''1900-1-1'') and getdate()<isnull(TimeOver,''2088'') and id<20000) a','id','',10,2,'id',1,'LineName,Img,STAAvg,ClickNum,PrimaryPrice',100000
--exec dbo.ProcCustomTaxisPage  'V_TravelLineStarList','id','LogicDel<>1 and AuditTag=0 and StartCity like ''1,7%'' and OverCity like ''1,7%'' and getdate()>isnull(TimeStart,''1900-1-1'') and getdate()<isnull(TimeOver,''2088'') and id<20000',20,2,'id',1,'LineName,Img,STAAvg,ClickNum,PrimaryPrice',100000
set @i=@i+1
end
select getdate()

 

declare @i int,@j int
set @i=0 ;set @j=0
select getdate()
while (@i<100000)
begin
INSERT [WMS_InOrOutType] ([TypeName],[ZjsId],[ZjsName],[CompanyId],[InOrOutTag],[State],[ModifyTime],[ModifyUserName]) VALUES ( 'jgjkgjhgjhg' + cast(@j as nvarchar(20)),1,'A    (ZJS)总公司',-1,1,0,'2008-2-24 0:00:00','赵国宏')

set @i=@i+1; set @j = @j + 1;
end
select getdate()

 

 

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER   PROCEDURE ProcCustomTaxisPage
   (
      @Table_Name               varchar(5000),       /* 查询的表名 */
      @Sign_Record              varchar(50),        /* 标志字段 */
      @Filter_Condition         varchar(1000),       /* 过滤条件 */
      @Page_Size                int,                /* 每页记录数 */
      @Page_Index               int,                /* 页号 */
                    @TaxisField               varchar(1000),
      @Taxis_Sign               int,                /* 排序标志 0:正序 1:倒序 */
                    @Find_RecordList          varchar(1000),       /* 查询的字段,字段间用,分割 空为查询全部*/
      @Record_Count             int                 /* 总记录数 */
   )
   AS
   BEGIN
   DECLARE  @Start_Number          int
   DECLARE  @End_Number            int
   DECLARE  @TopN_Number           int
   DECLARE  @sSQL                  varchar(8000)
                 if(@Find_RecordList='')
                 BEGIN
                      SELECT @Find_RecordList='*'
                 END
   SELECT @Start_Number =(@Page_Index-1) * @Page_Size
   IF @Start_Number<=0
   SElECT @Start_Number=0
   SELECT @End_Number=@Start_Number+@Page_Size
   IF @End_Number>@Record_Count
   SELECT @End_Number=@Record_Count
   SELECT @TopN_Number=@End_Number-@Start_Number
   IF @TopN_Number<=0
   SELECT @TopN_Number=0
   print @TopN_Number
   print @Start_Number
   print @End_Number
   print @Record_Count
                 IF @TaxisField=''
                 begin
                    select  @TaxisField=@Sign_Record
                 end
   IF @Taxis_Sign=0
     BEGIN
     IF @Filter_Condition=''
     BEGIN
      SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
          WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
          WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
         ORDER BY '+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField
     END
    ELSE
    BEGIN
    SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField
     END
   END
  ELSE
   BEGIN
   IF @Filter_Condition=''
    BEGIN
     SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
           WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
           WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
           ORDER BY '+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC'
       END
   ELSE
   BEGIN
    SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
       WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC'
   END
   END
   EXEC (@sSQL)
   IF @@ERROR<>0
   RETURN -3               /* 查询记录出错 */
   RETURN 0
   END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

declare @i int,@k int,@name nvarchar(100) ;
set @i = 0;set @k = @i + 1; 
while @i < 801
begin
set @name = '旅游网线路测试' + cast(@i  as nvarchar(100));
INSERT [TL_TravelLines] ([Num],[PriceInclude],[LineName],[GroupUpper],[Img],[LineInfo],[LineRouting],[Mode],[Point],[PreferentialPrice],[PrimaryPrice],[Days],[StartCyc],[TimeOver],[TimeStart],[TypeByGeography],[ClickNum],[UpdateTime],[Commend],[StartCommend],[EndCommend],[Pop],[PopStart],[PopEnd],[ShowOrder],[AuditTime],[AuditTag],[Auditor],[OfferUser],[ShowFront],[LogicDel],[forother1]) VALUES ( '3c6e0d9a-944b-4cc2-9525-1053bdd1b2f9','1',@name,1,'http://172.16.39.11:2008/TravelLine/images/No_Special_Photo.gif','1','1','1',0,1.0000,1.0000,1,'1','2008-9-25 0:00:00','2007-9-25 0:00:00','2',@k,'2007-9-25 11:35:21',@k,'2007-9-29 0:00:00','2008-9-24 0:00:00',@k,'2007-9-25 0:00:00','2008-9-24 0:00:00',0,'2007-9-25 11:35:21',0,'385','385',0,0,'1');
set @i = @i + 1; set @k = @k + 1;
end

go

declare @i int,@name nvarchar(100), @j int;
set @j = (select (case when (max([id])+1) is null then 1 else (max([id])+1) end) from TL_TravelLinesOrder);
set @i = 0; 
while @i < 10
begin
set @name = '旅游网线路测试' + cast(@i  as nvarchar(100));
INSERT  TL_TravelLinesOrder ([id],[Num],[PriceInclude],[LineName],[GroupUpper],[Img],[LineInfo],[LineRouting],[Mode],[Point],[PreferentialPrice],[PrimaryPrice],[Days],[StartCyc],[TimeOver],[TimeStart],[TypeByGeography],[ClickNum],[UpdateTime],[Commend],[StartCommend],[EndCommend],[Pop],[PopStart],[PopEnd],[ShowOrder],[AuditTime],[AuditTag],[Auditor],[OfferUser],[ShowFront],[LogicDel],[forother1]) VALUES (@j, '3c6e0d9a-944b-4cc2-9525-1053bdd1b2f9','1',@name,1,'http://172.16.39.11:2008/TravelLine/images/No_Special_Photo.gif','1','1','1',0,1.0000,1.0000,1,'1','2008-9-25 0:00:00','2007-9-25 0:00:00','2',35,'2007-9-25 11:35:21',7,'2007-9-29 0:00:00','2008-9-24 0:00:00',7,'2007-9-25 0:00:00','2008-9-24 0:00:00',0,'2007-9-25 11:35:21',0,'385','385',0,0,'1');
set @i = @i + 1;
set @j = @j + 1;
end