January 24, 2022 at 5:42 pm
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
January 24, 2022 at 5:55 pm
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.
January 24, 2022 at 6:37 pm
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
January 24, 2022 at 7:15 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 24, 2022 at 7:26 pm
dbo.Member_MRN_lkupAllMBRNOs is a table valued function.
Can you provide the code?
The execution plan of the query would also be helpful.
January 24, 2022 at 8:03 pm
This was removed by the editor as SPAM
January 26, 2022 at 2:17 pm
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2022 at 6:07 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 26, 2022 at 6:22 pm
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.
January 27, 2022 at 6:37 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply