asp.net - SQL SP 帮助查询单击提交按钮后在 ASP.NET 中显示记录所需的性能。250 万条记录耗时 8 分钟

Question

ALTER  PROCEDURE [dbo].[getmytrasactions]
    @pageindex int,
    @pagesize int,
    @whereclause varchar(1024),
    @orderby varchar(1024),
    @direction varchar(1024),
    @starttimestampdate datetime,
    @endtimestampdate datetime,
    @applicationid int,
    @ignoretimestamps bit = 0 
AS

    SET NOCOUNT ON

    declare @rowstart int,   @rowend int
    declare @orderclause varchar(100)
    declare @starttimestampdateid int
    declare @endtimestampdateid int

    if isnull(@pagesize,0) < 1 
        set @pagesize = 50
    if isnull(@pageindex,0) < 1 
        set @pageindex = 1
    set @rowstart = ((@pageindex -1) * @pagesize) + 1
    set @rowend = @rowstart + @pagesize -1 

    create table #temp (rank int identity, id bigint, timestampdateid int, applicationid int)

    if @orderby = 'rquid' or @orderby = 'policyguid'
        set @orderclause = 'ORDER BY Convert(varchar(36), ' + @orderby  + ') ' + @direction

    else
        set @orderclause = 'ORDER BY ' + @orderby  + ' ' +  @direction

    print @orderclause

    if @starttimestampdate is null 
        set @starttimestampdate = getdate()
    if @endtimestampdate is null 
        set @endtimestampdate = @starttimestampdate
    if @applicationid is null 
        set @applicationid = 0

    set @starttimestampdateid = (datepart(yyyy, @starttimestampdate) * 10000) + (datepart(mm, @starttimestampdate) * 100) + (datepart(dd, @starttimestampdate))

    set @endtimestampdateid = (datepart(yyyy, @endtimestampdate) * 10000) + (datepart(mm, @endtimestampdate) * 100) + (datepart(dd, @endtimestampdate))

    if isnull(@whereclause, '') = '' 
        set @whereclause = 'where 1=1'

    set @whereclause = @whereclause + ' and applicationid = ' + cast(@applicationid as varchar(2))

     -- Add the timestamp to the whereclause to make the search more efficient. in some cases
    -- we will want to ignore the timestamps. For example, when passing in a policyguid.

    if @ignoretimestamps = 0 
        set @whereclause = @whereclause + ' and timestampdateid between ' + cast(@starttimestampdateid as char(8)) + ' and ' + cast(@endtimestampdateid as char(8))

    exec ('insert #temp (id,timestampdateid,applicationid)
            SELECT id,timestampdateid,applicationid
            FROM dbo.getmytimes with (nolock)' + ' ' + @whereclause + ' ' + @orderclause)

    select @@rowcount

    SELECT  tt.Id
            , [Timestamp]
            , tt.ApplicationId
            , RqUID
            , ContextGUID
            , tt.PolicyGUID
            , TransactionName
            , TransactionDesc
            , TransactionTime/1000. as TransactionTime
            , ComputerName
            , TransactionType
            , ActivityType
            , UserTypeId
            , case  when StatusCode = 0 then 'green'
                    when StatusCode = 100 then 'red'
                    when StatusCode > 0 and StatusCode < 100 then 'yellow'
              else null end as StatusCode
            , ErrorNumber
            , Details
            , BuildLabel
            , [Level]
    FROM getmytimes tt with (nolock)
      join #temp t on t.id = tt.Id and t.timestampdateid = tt.TimeStampDateID and t.applicationid = tt.ApplicationID
    WHERE    t.rank between + @rowstart and @rowend  
    AND      ((@ignoretimestamps=0 AND tt.timestampdateid between @starttimestampdateid and @endtimestampdateid) OR @ignoretimestamps=1)
    AND     tt.ApplicationID = @ApplicationID
    ORDER BY t.Rank

标签： asp.netsql-serverperformance