Writing Dynamic Stored Procedure

  • Jeffrey ,

    Can you post a full XML query plan of one of your queries that Seek ?.

    If you could try to make the query as simple as possible though, just one column / variable.

    We may be able to gleam a vital piece of information from that.



    Clear Sky SQL
    My Blog[/url]

  • Dave Ballantyne (6/3/2009)


    Jeffrey ,

    Can you post a full XML query plan of one of your queries that Seek ?.

    If you could try to make the query as simple as possible though, just one column / variable.

    We may be able to gleam a vital piece of information from that.

    I'd also like to see the plans, but with at least 2 columns/variables in the where and, if possible, a couple of different plans based on different parameters passed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • TheSQLGuru (6/2/2009)


    alter PROCEDURE Test4 ( @ProdID int = null, @Qty int = null)

    AS

    select TransactionID

    from [Production].[TransactionHistory] -- with (index = [IX_TransactionHistory_ProductID])

    where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits of INT here!

    AND Quantity BETWEEN coalesce(@Qty, 0) AND coalesce(@Qty, 99999999)

    GO

    Exec Test4 @ProdID = 790

    4.16 cost with forced index, 11 IO

    0.711 cost without forced index (CI scan), 792 IO

    due to the mathematics of the optimizer (i.e. the MUCH higher cost associated with the known-to-be-not-sequential-io index seek/bookmark lookup the query plan cost of seeking/lookup 2 rows is MUCH higher than scaning the entire table despite significantly fewer total IOs.

    Gail, I wonder if your larger table would still be more efficient doing the scan than with the forced seek?

    It is. The cost of the forced index is way higher and the IOs are slightly higher.

    CI Scan

    Table 'TransactionHistory'. Scan count 3, logical reads 7367, physical reads 0

    Cost 7.19

    Forced index

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0

    Table 'TransactionHistory'. Scan count 3, logical reads 8109, physical reads 0 -- 2641 key lookups.

    Cost 35.02

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • good artical..

    but it's good useful for the dynamic sql when using 'sp_executesql'

  • Dave Ballantyne (6/3/2009)


    Jeffrey ,

    Can you post a full XML query plan of one of your queries that Seek ?.

    If you could try to make the query as simple as possible though, just one column / variable.

    We may be able to gleam a vital piece of information from that.

    I'll put together a few execution plans and post the results. The query is fairly simple already and does not return a lot of columns. The join is a bit complex with multiple columns and a date range - but the execution plans are still fairly simple.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • sunil,

    You can use this style too....

    set @Gender = isNull('M', '_')

    set @Age = isNull(22, 0)

    SELECT * FROM test_EmployeeDetails

    Where Gender LIKE @Gender AND Age > @Age

    Thanks!

    Azeem

  • this does not help if you have nullable fields

  • Interesting.....

    I can now get multiple index seeks 😀

    drop proc test2

    go

    create proc test2 @ProductId integer,

    @ReferenceOrderID integer,

    @Quantity integer

    as

    select *

    from [Production].[TransactionHistory]

    where (@productid is not null and @productid = ProductID)

    or (@ReferenceOrderID is not null and @ReferenceOrderID = ReferenceOrderID)

    --or (@quantity is not null and @quantity = Quantity) Uncomment This to force a scan

    go

    exec test2 @productid = 790,@ReferenceOrderID = NULL,@Quantity=NULL

    go

    exec test2 @productid = NULL,@ReferenceOrderID =61197 ,@Quantity=NULL

    It seems to seek if ALL the referenced columns are indexed, but uncommenting the Quantity lookup a scan takes place.

    Edit : Ive updated my blog http://sqlandthelike.blogspot.com/ with this info



    Clear Sky SQL
    My Blog[/url]

  • isNull funcation for nullable value ... try then reply

Viewing 9 posts - 76 through 84 (of 84 total)

You must be logged in to reply to this topic. Login to reply