Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Any suggestions on how to optimize a query written in Dynamic SQL?

    I added the subquery and now this thing consistently takes more than five minutes to return 7100+ rows. Any suggestions? Thanks again, you guys are the best.

    ddave
    ----------------------------
    SET @StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
    FROM dbo.ViewEligibilityPHC VEL
    LEFT OUTER JOIN (SELECT *
    FROM dbo.MEMB_LISHISTS l
    WHERE l.LISThruDate is null
    AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
    WHERE VEL.OPTHRUDT is null
    AND VEL.OPT LIKE ''' + @HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @Prvdr + ''')
    AND VEL.HCC LIKE ''' + @HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @CaseMngr + ''')
    AND VEL.OPFROMDT <= CAST(''' + @SDate + ''' AS datetime)
    AND ISNULL(VEL.OPTHRUDT, CAST(''' + @TDate + ''' AS datetime)) >= CAST(''' + @EDate + ''' AS datetime)) '

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Be nice and post the results of:

    PRINT @StrQry1

    ...so we can focus on the SQL and not the dynamic concatenation...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    Sorry it took so long. I had to modify the procedure to print that string and run it again:-). These are the fields. It is confidential medical data so I can't put the actual data here. The procedure is below. Also if there are any obvious errors any tips would be greatly appreciated.

    Counter
    SubsidyLevel
    MEMBID
    PATID
    SUBSSN
    AIDSDATE
    AIDCODE
    OPFROMDT
    OPTHRUDT
    OPT
    CURRENTCOUNTYID
    LASTNM
    FIRSTNM
    BIRTH
    HCP1NUM
    SEX
    HCC
    HCCName
    CaseMgrID
    CaseMgrName
    PCPFROMDT
    PCP
    PCPName
    STREET
    STREET2
    CITY
    STATE
    ZIP
    PHONE
    CURRHIST
    INTLZIP
    TransferOut
    CoPay
    CoPayEffDate
    PartsABD

    -- The entire code is:


    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO



    alter PROCEDURE DBO.STANDARD_MEMBERSHIP_QUERY_test_20071015

    @StartDate DateTime, @EndDate DateTime,
    @HPlan varchar(20), @HCC varchar(3),
    @Prvdr varchar(20), @CaseMngr varchar(5),
    @Report smallint

    AS

    SET NOCOUNT ON

    BEGIN

    DECLARE @SDate varchar(10), @EDate varchar(10),
    @TDate varchar(10)

    SET @SDate = Convert(varchar(10),@StartDate, 101)
    SET @EDate = Convert(varchar(10), @EndDate, 101)
    SET @TDate = '06/06/2079'

    DECLARE @SDateP varchar(10), @EDateP varchar(10)
    DECLARE @MySQL varchar(1600), @StrQry1 varchar(800), @StrQry2 varchar(800)

    SET @SDateP = Convert(varchar(10), DateAdd(Month, -1, @StartDate), 101)
    SET @EDateP = Convert(varchar(10), DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, Cast(@SDateP AS datetime))+1, 0)), 101)


    SET @StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
    FROM dbo.ViewEligibilityPHC VEL
    LEFT OUTER JOIN (SELECT *
    FROM dbo.MEMB_LISHISTS l
    WHERE l.LISThruDate is null
    AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
    WHERE VEL.OPTHRUDT is null
    AND VEL.OPT LIKE ''' + @HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @Prvdr + ''')
    AND VEL.HCC LIKE ''' + @HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @CaseMngr + ''')
    AND VEL.OPFROMDT <= CAST(''' + @SDate + ''' AS datetime)
    AND ISNULL(VEL.OPTHRUDT, CAST(''' + @TDate + ''' AS datetime)) >= CAST(''' + @EDate + ''' AS datetime)) '


    SET @MySQL = @StrQry1

    END

    EXEC (@MySQL)


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

  4. #4
    Join Date
    Feb 2004
    Posts
    193
    also and in the meantime I am studying how to index the view in question. I have rarely worked with views and I have never created an index before.

    ddave

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One more time....
    Quote Originally Posted by blindman
    Be nice and post the results of:

    PRINT @StrQry1

    ...so we can focus on the SQL and not the dynamic concatenation...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    to do what blindman is asking, change your EXEC(@MySQL) to PRINT @MySQL and post the result.

  7. #7
    Join Date
    Nov 2005
    Posts
    122
    You should try to avoid sub queries.

    Code:
    SET @StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.* 
                       FROM dbo.ViewEligibilityPHC VEL 
                       LEFT JOIN dbo.MEMB_LISHISTS Q1 
    		                      on l.LISThruDate is null 
                                            AND l.Deleted = ''0'' and VEL.MEMBID = Q1.MemberID
                       WHERE VEL.OPTHRUDT is null
                         AND VEL.OPT LIKE ''' +  @HPlan  + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @Prvdr + ''') 
                         AND VEL.HCC LIKE ''' + @HCC + ''' AND (VEL.CaseMgrID IS NULL OR  VEL.CaseMgrID LIKE ''' + @CaseMngr + ''')
                         AND VEL.OPFROMDT <= CAST(''' + @SDate + ''' AS datetime) 
                         AND ISNULL(VEL.OPTHRUDT, CAST(''' + @TDate + ''' AS datetime)) >= CAST(''' + @EDate + ''' AS datetime)) '
    I also suspect that massive index/table scans causes your problem.

    Please post the execution plan for the query.
    How to:
    print @StrQry1
    copy the SQL to a new query windows.
    Execute
    Code:
    set showplan_text on
    go
    your query here...
    Copy and paste the text from the result window to this thread.
    Last edited by kaffenils; 10-16-07 at 06:27.

  8. #8
    Join Date
    Feb 2004
    Posts
    193
    Actually we resolved it by altering the view. We needed to add one field and we just added it to the view instead of going through all this. Thanks anyway.

    ddave

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    the word dynamic in the term dynamic sql almost makes it sound like a good thing. it deserves a name more akin to it's nature. maybe kludged sql? maybe poor perfroming insecure code?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Perhaps "Injectable SQL"? Or maybe "Objectional SQL"?

    It has its uses, but buy can it be abused.

  11. #11
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Why are you using dynamic sql ?, I usually only use dynamic sql if table names need to be assigned dynamically based on certain conditions. Where do you append the "%" for the likes, as part of the input variable ?

  12. #12
    Join Date
    Feb 2004
    Posts
    193

    Cool

    That's when I use it also to dynamically name tables for example. I got it that way from my supervisor. Sigh, I guess I will just have to blame him.

    ddave

  13. #13
    Join Date
    Nov 2005
    Posts
    122
    And when you have to use dynamic SQL (or injection-sql) do NOT use do it by concatenating the parameters into the sql string. Use sp_executesql with parameters.

Posting Permissions

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