Slow Query

  • Hi

    Any ideas on how to improve performance?

    @strMBRNO VARCHAR(255)

    AS

    SET NOCOUNT ON

    SELECT DISTINCT GRPID, PLNCD, XPLNE, EXTNO, EffDate, TermDate
    FROM History
    WHERE MBRNO IN (SELECT MBRNO FROM dbo.Member_MRN_lkupAllMBRNOs(@strMBRNO))
    ORDER BY EffDate ASC
  • Without seeing an execution plan or ddl, I the below are just guesses.

    Depending on the number of rows returned by your nested select, you may get better performance by changing your WHERE statement.  How specifically you change it depends on how many rows you get back with the nested select.  MAY get better performance by changing it to a JOIN if possible rather than in your WHERE clause, but this depends on what that function is doing.

    If the data doesn't need to be ordered, removing the order by and ordering it at the application side may give you a performance boost.

    If the data is not duplicated in any rows, removing the DISTINCT will give a performance boost.

    Depending on various factors, having it run parallel or run single threaded may help performance.

    The above is all guesswork though as we can't see your data, your execution plan, indexes, or anything... just the one query...  It could be that your function expects an INT and you are passing a VARCHAR, so having the input types match and not having the implicit conversion MAY help performance too.

    Now, a non SQL way to make it faster - faster disk, faster network, more CPU, more memory <-- any of these MAY make it faster.

    Making History an in-memory table MAY make it faster too.  Populating a temp table or table variable with the function data MAY give better performance too.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Awesome. Really appreciate.

     

    Great stuff! I hadn't thought of a few of these.

    • This reply was modified 4 months ago by  krypto69.
  • I would also supply us with the code for that function you are calling as well as a actual explain plan for one of the executions - issue may well be on the function

  • SELECT DISTINCT GRPID, PLNCD, XPLNE, EXTNO, EffDate, TermDate

    FROM History

    WHERE MBRNO IN (SELECT MBRNO FROM dbo.Member_MRN_lkupAllMBRNOs(@strMBRNO))

    ORDER BY EffDate ASC

    How large (MB/GB) is the "History" table?

    How is it clustered?

    If it's a (very) large table, does it have a covering index on ( MBRNO, EffDate ) INCLUDE ( EXTNO, GRPID, PLNCD, TermDate, XPLNE )?

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • dbo.Member_MRN_lkupAllMBRNOs is a table valued function.

    Can you provide the code?

    The execution plan of the query would also be helpful.

  • This was removed by the editor as SPAM

  • If Member_MRN_lkupAllMBRNOs is a multi-statement table-valued user-defined function, that alone is going to cause pretty massive performance heads. Either switch it to being an in-line function, or eliminate the use of a function entirely and go with a query, sub-query, view, CTE, or whatever will work to replace the function.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    If Member_MRN_lkupAllMBRNOs is a multi-statement table-valued user-defined function, that alone is going to cause pretty massive performance heads. Either switch it to being an in-line function, or eliminate the use of a function entirely and go with a query, sub-query, view, CTE, or whatever will work to replace the function.

    Generally, yes, for MSTVF.  But in this case, it's only run one time, for a static value which is only 255 bytes long.  So I don't think in this case that will be a big performance hit.  Unlike the situation where the function has to be run repeatedly based on a changing column table.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    SELECT DISTINCT GRPID, PLNCD, XPLNE, EXTNO, EffDate, TermDate FROM History WHERE MBRNO IN (SELECT MBRNO FROM dbo.Member_MRN_lkupAllMBRNOs(@strMBRNO)) ORDER BY EffDate ASC

    How large (MB/GB) is the "History" table?

    How is it clustered?

    If it's a (very) large table, does it have a covering index on ( MBRNO, EffDate ) INCLUDE ( EXTNO, GRPID, PLNCD, TermDate, XPLNE )?

    I think an index on (MBRNO, EffDate, GRPID, PLNCD, XPLNE, EXTNO, TermDate) would be more effective at selecting distinct items as there would be no need to select distinct (and sort) items from the unsorted included columns.

  • Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    SELECT DISTINCT GRPID, PLNCD, XPLNE, EXTNO, EffDate, TermDate FROM History WHERE MBRNO IN (SELECT MBRNO FROM dbo.Member_MRN_lkupAllMBRNOs(@strMBRNO)) ORDER BY EffDate ASC

    How large (MB/GB) is the "History" table?

    How is it clustered?

    If it's a (very) large table, does it have a covering index on ( MBRNO, EffDate ) INCLUDE ( EXTNO, GRPID, PLNCD, TermDate, XPLNE )?

    I think an index on (MBRNO, EffDate, GRPID, PLNCD, XPLNE, EXTNO, TermDate) would be more effective at selecting distinct items as there would be no need to select distinct (and sort) items from the unsorted included columns.

    I was just copying the original query given by OP, as a reference.  I wasn't trying to suggest it was good (or bad).

    I think avoiding the sort for DISTINCT would only be true if MBRNO and EffDate when combined were unique.  I would not think that would be true in a History table.

     

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 11 posts - 1 through 10 (of 10 total)

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