Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Performance Improvement

    Hi All,
    Have an existing stored procedure as below .My question is it sets the forceplan to on within it .Does this affect the performance when run .How do i go about checking whether it does affect performance at run time.
    Thanks in advance

    create procedure edoc.diary_trades @UserName varchar(30), @Tracking varchar(8), @TradeId varchar(10), @SystemNo varchar(10), @SummitId varchar(10) as
    begin

    delete from tempdb.dbo.book_list where UserName=@UserName

    insert into tempdb.dbo.book_list (UserName,Book)
    select distinct @UserName, BookName
    from tsUSERPREFS UP,
    tsRESOURCE R,
    tsBOOK B,
    tsUSER U
    where R.ResourceName='BooksManager'
    and R.ResourceId=UP.ResourceId
    and U.UserName= @UserName
    and ( UP.UserId = U.UserId or UP.UserId in ( select GroupId from tsUSERGROUP where UserId = U.UserId ))
    and B.BookId=convert(numeric(15),case when UP.Property like '%[0-9]%' then UP.Property end)

    commit

    set forceplan on

    select
    case
    when exists (select 1 from tsdocDiary where TsarId = t.TradeId and upper(tsdocDiary.Memo) like '%DTCC%') then 'Y'
    else 'N'
    end as DTCC,
    t.TradeId,
    case
    when Counterpart ='COMMODITIES' then t.Calendars
    else t.Counterpart
    end as Counterpart,
    t.FundGroupId,
    t.Book,
    convert(char(18),t.TradeDate,103) as TradeDate,
    t.Amount,
    rtrim(ltrim(o.OpsStatus)) as OpsStatus,
    t.AssetSubtype,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId1
    end as ExtTicketId1,
    rtrim(t.Status) as Status,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId2
    end as ExtTicketId2,
    t.Asset,
    t.SourceId,
    t.Calendars,
    t.StructureName,
    t.AccountType
    from tempdb.dbo.book_list l,
    tpstTrades t,
    tblOWLWorkflow o,
    tsOpsStatus os,
    tsCOUNTERPART c
    where l.UserName=@UserName
    and l.Book=t.Book
    and t.StructureName in (
    select convert(varchar(40),s.Name) from tpstStructures s
    where s.Operations='Y' and s.SystemName='TSAR'
    )
    and t.Counterpart *=c.Id
    and t.Delivery<>'NONE'
    and t.Counterpart<>'MICEX'
    and t.AssetSubtype!='BRADY'
    and t.AssetType<>'CSH'
    and t.StructureName not in ('STOCK','BOND')
    and t.TradeId = o.TradeId
    and os.OpsStatus=convert(varchar(5), o.OpsStatus)

    and case
    when @TradeId != 'unused' then convert(int, @TradeId)
    else t.TradeId
    end = t.TradeId

    and case
    when @SystemNo != 'unused' then @SystemNo
    else t.SourceId
    end = t.SourceId

    and case
    when @SummitId != 'unused' then @SummitId
    else t.ExtTicketId2
    end = t.ExtTicketId2

    order by TradeId desc

    select
    case
    when exists (select 1 from tsdocDiary where TsarId = t.TradeId and upper(tsdocDiary.Memo) like '%DTCC%') then 'Y'
    else 'N'
    end as DTCC,
    t.TradeId,
    case
    when Counterpart ='COMMODITIES' then t.Calendars
    else t.Counterpart
    end as Counterpart,
    t.FundGroupId,
    t.Book,
    convert(char(18),t.TradeDate,103) as TradeDate,
    t.Amount,
    rtrim(ltrim(o.OpsStatus)) as OpsStatus,
    t.AssetSubtype,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId1
    end as ExtTicketId1,
    rtrim(t.Status) as Status,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId2
    end as ExtTicketId2,
    t.Asset,
    t.SourceId,
    t.Calendars,
    t.StructureName,
    t.AccountType
    from tempdb.dbo.book_list l,
    tpstTrades t,
    tblOWLWorkflow o,
    tsOpsStatus os,
    tsCOUNTERPART c
    where l.UserName=@UserName
    and l.Book=t.Book
    and t.StructureName in (
    select convert(varchar(40),s.Name) from tpstStructures s
    where s.Operations='Y' and s.SystemName='TSAR'
    )
    and t.Counterpart *=c.Id
    and t.Delivery<>'NONE'
    and t.Counterpart<>'MICEX'
    and t.AssetSubtype!='BRADY'
    and t.AssetType<>'CSH'
    and t.StructureName not in ('STOCK','BOND')
    and t.TradeId = o.TradeId
    and o.OpsLevel = 0
    and os.OpsStatus=convert(varchar(5), o.OpsStatus)
    and os.Tracking in ('TKT','TAC')

    order by TradeId desc

    select
    case
    when exists (select 1 from tsdocDiary where TsarId = t.TradeId and upper(tsdocDiary.Memo) like '%DTCC%') then 'Y'
    else 'N'
    end as DTCC,
    t.TradeId,
    case
    when Counterpart ='COMMODITIES' then t.Calendars
    else t.Counterpart
    end as Counterpart,
    t.FundGroupId,
    t.Book,
    convert(char(18),t.TradeDate,103) as TradeDate,
    t.Amount,
    rtrim(ltrim(o.OpsStatus)) as OpsStatus,
    t.AssetSubtype,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId1
    end as ExtTicketId1,
    rtrim(t.Status) as Status,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId2
    end as ExtTicketId2,
    t.Asset,
    t.SourceId,
    t.Calendars,
    t.StructureName,
    t.AccountType
    from tempdb.dbo.book_list l,
    tpstTrades t,
    tblOWLWorkflow o,
    tsOpsStatus os,
    tsCOUNTERPART c,
    tsBOOK b
    where l.UserName=@UserName
    and l.Book=t.Book
    and t.StructureName in (
    select convert(varchar(40),s.Name) from tpstStructures s
    where s.Operations='Y' and s.SystemName='TSAR'
    )
    and t.Counterpart=c.Id -- !!! it is supposed to be inner join !!!
    and t.Delivery<>'NONE'
    and t.Counterpart<>'MICEX'
    and t.AssetSubtype!='BRADY'
    and t.AssetType<>'CSH'
    and t.StructureName not in ('STOCK','BOND')
    and isnull(c.Ext2, 'N') = 'Y'
    and t.TradeId = o.TradeId
    and o.OpsLevel IN (0, 2)
    and isnull(o.DocOnAmend, 'Y') <>'N'
    and os.OpsStatus=convert(varchar(5), o.OpsStatus)
    and os.Tracking in ('CON','TAC')
    and b.BookName = t.Book
    and not ( exists (select null from tsLookup
    where Type = 'ED'
    and Description = t.StructureName
    and rtrim(t.Status) != 'MAT')
    or isnull(b.BookGroup,'N/A')='MEXICO')
    order by TradeId desc

    select
    case
    when exists (select 1 from tsdocDiary where TsarId = t.TradeId and upper(tsdocDiary.Memo) like '%DTCC%') then 'Y'
    else 'N'
    end as DTCC,
    t.TradeId,
    case
    when Counterpart ='COMMODITIES' then t.Calendars
    else t.Counterpart
    end as Counterpart,
    t.FundGroupId,
    t.Book,
    convert(varchar(18),t.TradeDate,103) as TradeDate,
    t.Amount,
    rtrim(ltrim(o.OpsStatus)) as OpsStatus,
    t.AssetSubtype,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId1
    end as ExtTicketId1,
    rtrim(t.Status) as Status,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId2
    end as ExtTicketId2,
    t.Asset,
    t.SourceId,
    t.Calendars,
    t.StructureName,
    t.AccountType
    from tempdb.dbo.book_list l,
    tpstTrades t,
    tblOWLWorkflow o,
    tsOpsStatus os,
    tsCOUNTERPART c
    where l.UserName=@UserName
    and l.Book=t.Book
    and t.StructureName in (
    select convert(varchar(40),s.Name) from tpstStructures s
    where s.Operations='Y' and s.SystemName='TSAR'
    )
    and t.Counterpart *=c.Id
    and t.Delivery<>'NONE'
    and t.Counterpart<>'MICEX'
    and t.AssetSubtype!='BRADY'
    and t.AssetType<>'CSH'
    and t.TradeId = o.TradeId
    and os.OpsStatus=convert(varchar(5), o.OpsStatus)
    and os.Tracking='PND'
    order by TradeId desc

    select
    case
    when exists (select 1 from tsdocDiary where TsarId = t.TradeId and upper(tsdocDiary.Memo) like '%DTCC%') then 'Y'
    else 'N'
    end as DTCC,
    t.TradeId,
    case
    when Counterpart ='COMMODITIES' then t.Calendars
    else t.Counterpart
    end as Counterpart,
    t.FundGroupId,
    t.Book,
    convert(varchar(18),t.TradeDate,103) as TradeDate,
    t.Amount,
    rtrim(ltrim(o.OpsStatus)) as OpsStatus,
    t.AssetSubtype,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId1
    end as ExtTicketId1,
    rtrim(t.Status) as Status,
    case
    when Counterpart ='COMMODITIES' then t.SourceId
    else t.ExtTicketId2
    end as ExtTicketId2,
    t.Asset,
    t.SourceId,
    t.Calendars,
    t.StructureName,
    t.AccountType
    from tempdb.dbo.book_list l,
    tpstTrades t,
    tblOWLWorkflow o,
    tsOpsStatus os,
    tsCOUNTERPART c
    where l.UserName=@UserName
    and l.Book=t.Book
    and t.StructureName in (
    select convert(varchar(40),s.Name) from tpstStructures s
    where s.Operations='Y' and s.SystemName='TSAR'
    )
    and t.Counterpart *=c.Id
    and t.Delivery<>'NONE'
    and t.Counterpart<>'MICEX'
    and t.AssetSubtype!='BRADY'
    and t.AssetType<>'CSH'
    and t.TradeId = o.TradeId
    and os.OpsStatus=convert(varchar(5), o.OpsStatus)
    and os.Tracking='UNG'
    order by TradeId desc

    end

  2. #2
    Join Date
    Jun 2002
    Posts
    10
    Hello,

    Using 'forceplan' forces the query optimizer to use the join order specified in the query, rather than deciding on what it thinks is best. To test performance variations, create an identical stored proc without using 'set forceplan', then use 'set statisitcs i/o on' and 'set statistics time on' to measure the execution time and resource utilization for identical runs of the two procedure variations.

    Hope that is useful to you,

    --Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •