How can I optimized this query

Question

0.00/5 (No votes)

See more:

I am trying to optimized this query as i is it is heavy resource intensive in the DB

What I have tried:

INSERT      @UserNotes
SELECT      pn.pkfkGroupCode,
            pn.pkfkGroupDivisionCode,
            pn.pkfkDivisionMasterPolicyID,
            '',
            pn.pkfkPolicyNumber,
            pn.pkPolicyNotesID,
            CASE
                  WHEN LEN(LTRIM(RTRIM(Note))) > 20 THEN LEFT(Note,17) + '...'
                  ELSE LTRIM(RTRIM(Note))
            END AS 'NoteHeader',
            LTRIM(RTRIM(ISNULL(pn.Note,''))), 
            CASE
                  WHEN ISNULL(ActionFlag,'') = 'I' THEN 'Incomplete'
                  WHEN ISNULL(ActionFlag,'') = 'T' THEN 'To Follow Up'
                  WHEN ISNULL(ActionFlag,'') = 'C' THEN 'Complete'
                  WHEN ISNULL(ActionFlag,'') = 'U' THEN 'Unassigned'
            END AS 'Status',
            CONVERT(char(10),CaptureDate,111) AS 'CaptureDate',
            ISNULL(CONVERT(char(10),ActionDate,111),'') AS 'ActionDate',
            ISNULL(CONVERT(char(7),ActionDate,111),'') AS 'DueMonth',
            pn.fkUserID AS 'AssignedBy',
            '',
            ISNULL(AssignedTo,0) AS 'AssignedTo',
            ''
FROM  PolicyNotes pn WITH (NOLOCK)
WHERE (
            pn.fkUserID = CAST(@UserID as varchar(255))
            OR
            ISNULL(pn.AssignedTo,0) = @UserID
            OR
            (
            ISNULL(@PolicyID,0) <> 0 AND ISNULL(pn.AssignedTo,'0') <> CAST(@UserID as varchar(255)) AND ISNULL(fkUserID,'0') <> CAST(@UserID as varchar(255)) AND ISNULL(ActionFlag,'') <> '' AND ActionDate IS NOT NULL
            )
            OR
            (
            ISNULL(pn.AssignedTo,0) = 0 AND ISNULL(ActionFlag,'') = 'U' AND ActionDate IS NOT NULL AND fkUserID IN
            (
                  SELECT      CAST(fkUserID as varchar(255))
                  FROM  SystemControl_Live.dbo.aa_UserTaskGroup
                  WHERE fkTaskGroupID IN
                  (
                        SELECT fkTaskGroupID
                        FROM  SystemControl_Live.dbo.aa_UserTaskGroup
                        WHERE fkUserID = @UserID
                  )
            )
            )
            )
AND         ISNULL(UPPER(LTRIM(RTRIM(pn.ActionFlag))),'N') NOT IN ('N','')
AND         LTRIM(RTRIM(ISNULL(Note,''))) <> ''
AND         (@GroupCode IS NULL OR pn.pkfkGroupCode = @GroupCode)
AND         (@GroupDivisionCode IS NULL OR pn.pkfkGroupDivisionCode = @GroupDivisionCode)
AND         (@DivisionMasterPolicyID IS NULL OR pn.pkfkDivisionMasterPolicyID = @DivisionMasterPolicyID)
AND         (@PolicyNumber IS NULL OR pn.pkfkPolicyNumber = @PolicyNumber)
--Donovan -- remove completed items from list
AND			isnull(pn.ActionFlag,'') <> 'C'
ORDER BY
            ISNULL(ActionDate,'') ASC, pkfkPolicyNumber, CaptureDate

Posted 23-Jan-19 2:17am

MarkNinja

Updated 23-Jan-19 3:50am

Add a Solution

Comments

ZurdoDev 23-Jan-19 8:33am

Make sure you have appropriate indexes. Try to remove functions where WHERE clause if possible. SQL usually cannot use the index on a field if it is wrapped in a function. Narrow down if there is 1 or 2 specific things that are slowing it down. Look at dumping some results into a temp table first instead of multi-level selects.

In other words, start trying a lot of stuff.

Maciej Los 23-Jan-19 8:57am

What sql server version?

2 solutions

Solution 1

Sorry, but we can't help you. We do not have an access to your data and even to your screen.
Please, follow this: How To: Optimize SQL Queries (Tips and Techniques)[^]
One of the most helpfull tools is SQL Server Profiler[^]
For further details, please see:
Run SQL Server Profiler - SQL Server | Microsoft Docs[^]
Performance Monitoring and Tuning Tools - SQL Server | Microsoft Docs[^]
Query Hints (Transact-SQL) - SQL Server | Microsoft Docs[^]

BTW: the performance of query depends on many other factors, such as table indexing, sql server performance, etc.

Posted 23-Jan-19 3:05am

Maciej Los

Comments

CHill60 23-Jan-19 9:50am

5'd

Maciej Los 23-Jan-19 9:54am

Thank you, Caroline.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2019-01-23T03:50:00

Further to the comments and solution above...
You have many things like

SQL

CONVERT(char(10),CaptureDate,111) AS 'CaptureDate',

Why? Use dates properly and only convert them to a "readable" format when you are presenting them in the UI.

Same applies to things like

SQL

CAST(fkUserID as varchar(255))

Consider simplifying that awful WHERE clause by utilising temporary table(s) or table variable(s) - see comment from @ZurdoDev

If you are using SQL Server 2017 or higher replace

SQL

LTRIM(RTRIM(Note))

with

SQL

TRIM(Note)

You should try to rethink your sub-query inside a sub-query inside a where . . .

SQL

SELECT      CAST(fkUserID as varchar(255))
FROM  SystemControl_Live.dbo.aa_UserTaskGroup
WHERE fkTaskGroupID IN
(
      SELECT fkTaskGroupID
      FROM  SystemControl_Live.dbo.aa_UserTaskGroup
      WHERE fkUserID = @UserID
)

I think that this should work just as well (worth checking with your data though)

SQL

SELECT CAST(A.fkUserID as varchar(255))
FROM  SystemControl_Live.dbo.aa_UserTaskGroup A
INNER JOIN SystemControl_Live.dbo.aa_UserTaskGroup B ON A.fkTaskGroupID = B.fkTaskGroupID AND B.fkUserID = @userID