Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Faster Query AND ( criteria AND criteria)

Status
Not open for further replies.
tqeonline

tqeonline

MIS
Oct 5, 2009
304
US
Hello All!

Is there a better way to do criteria?

This is what I have and it takes a good minute or two.

Code: 
SELECT [TS_USER_11] 'Product Owner'
    ,[TS_USER_10] as 'Product Area'
    ,[TS_TEST_ID] as 'Test ID'
    ,[TS_NAME] as 'Test Name'
    ,[TS_STATUS] as 'Status'
    ,[TS_RESPONSIBLE] as 'Creator'
    ,[TS_CREATION_DATE] 'Date Created'
    ,[TS_TYPE] 'Type'
FROM TEST WITH (NOLOCK), ALL_LISTS WITH (NOLOCK)
WHERE TEST.TS_SUBJECT = ALL_LISTS.AL_ITEM_ID
    AND (TEST.TS_TYPE = 'MANUAL' OR TEST.TS_TYPE = 'QUICKTEST_TEST')
    AND ALL_LISTS.AL_ABSOLUTE_PATH Not Like 'AAAAAJAAD%'--Archived
    AND ALL_LISTS.AL_ABSOLUTE_PATH Not Like 'AAAAAJABL%'--QA Services
    AND ALL_LISTS.AL_ABSOLUTE_PATH Not Like 'AAAAAJACB%'--Recycle Bin
    AND (TS_USER_10 = 'ASG' AND TS_USER_11  <> 'Leslie Burnett')
    OR (TS_USER_10 = 'ATBS' AND TS_USER_11  <> 'Beth Hubner')
    OR (TS_USER_10 = 'AUI' AND TS_USER_11  <> 'Jacky Rasaphoumy')
    OR (TS_USER_10 = 'AUI-EU' AND TS_USER_11  <> 'Deepa Kalangi')
    OR (TS_USER_10 = 'BB' AND TS_USER_11  <> 'Santaji Nanaware')
    OR (TS_USER_10 = 'BOFA' AND TS_USER_11  <> 'Azza Shehata')
    OR (TS_USER_10 = 'BPM' AND TS_USER_11  <> 'Joseph Brooks')
    OR (TS_USER_10 = 'CRM-BILL' AND TS_USER_11  <> 'Mandeep Uppal')
    OR (TS_USER_10 = 'CRM-CL' AND TS_USER_11  <> 'Beth Hubner')
    OR (TS_USER_10 = 'CRM-COP' AND TS_USER_11  <> 'Nick Thorne')
    OR (TS_USER_10 = 'CRM-CSAT' AND TS_USER_11  <> 'Nick Thorne')
    OR (TS_USER_10 = 'CRM-EU' AND TS_USER_11  <> 'Beth Hubner')
    OR (TS_USER_10 = 'CRM-HS' AND TS_USER_11  <> 'Nick Thorne')
    OR (TS_USER_10 = 'CRM-MTS' AND TS_USER_11  <> 'Mandeep Uppal')
    OR (TS_USER_10 = 'CRM-SCR' AND TS_USER_11  <> 'Jacky Rasaphoumy')
    OR (TS_USER_10 = 'CSET' AND TS_USER_11  <> 'Shelly McCormack')
    OR (TS_USER_10 = 'CS-NET' AND TS_USER_11  <> 'Ryan Oglesby')
    OR (TS_USER_10 = 'CS-TIBCO' AND TS_USER_11  <> 'Ryan Oglesby')
    OR (TS_USER_10 = 'DAX' AND TS_USER_11  <> 'Azza Shehata')
    OR (TS_USER_10 = 'DAX-EU' AND TS_USER_11  <> 'Deepa Kalangi')
    OR (TS_USER_10 = 'EC' AND TS_USER_11  <> 'Shelly McCormack')
    OR (TS_USER_10 = 'EP' AND TS_USER_11  <> 'Fatema Pedhiwala')
    OR (TS_USER_10 = 'ETL' AND TS_USER_11  <> 'Nick Thorne')
    OR (TS_USER_10 = 'IG' AND TS_USER_11  <> 'Nick Thorne')
    OR (TS_USER_10 = 'INF' AND TS_USER_11  <> 'Brad Wirths')
    OR (TS_USER_10 = 'INTST' AND TS_USER_11  <> 'Swapnil Patil')
    OR (TS_USER_10 = 'IR' AND TS_USER_11  <> 'Mandeep Uppal')
    OR (TS_USER_10 = 'IS' AND TS_USER_11  <> 'Nick Thorne')
    OR (TS_USER_10 = 'IVR' AND TS_USER_11  <> 'Kimberly Cooper')
    OR (TS_USER_10 = 'LI' AND TS_USER_11  <> 'Fatema Pedhiwala')
    OR (TS_USER_10 = 'LOG' AND TS_USER_11  <> 'Ryan Oglesby')
    OR (TS_USER_10 = 'LR' AND TS_USER_11  <> 'Mandeep Uppal')
    OR (TS_USER_10 = 'MEA' AND TS_USER_11  <> 'Mandeep Uppal')
    OR (TS_USER_10 = 'MRAA' AND TS_USER_11  <> 'Mandeep Uppal')
    OR (TS_USER_10 = 'PS' AND TS_USER_11  <> 'Nick Thorne')
    OR (TS_USER_10 = 'PT' AND TS_USER_11  <> 'Shelly McCormack')
    OR (TS_USER_10 = 'QAS' AND TS_USER_11  <> 'Brandon Ainsworth')
    OR (TS_USER_10 = 'QAS' AND TS_USER_11  <> 'Shelly McCormack')
    OR (TS_USER_10 = 'RA' AND TS_USER_11  <> 'Azza Shehata')
    OR (TS_USER_10 = 'SD' AND TS_USER_11  <> 'Brandon Ainsworth')
    OR (TS_USER_10 = 'STAR' AND TS_USER_11  <> 'Azza Shehata')
    OR (TS_USER_10 = 'VTI' AND TS_USER_11  <> 'Swapnil Patil')
    OR (TS_USER_10 = 'W7' AND TS_USER_11  <> 'Jason Little')
    OR (TS_USER_10 = 'WATS-BS' AND TS_USER_11  <> 'Leslie Burnett')
    OR (TS_USER_10 = 'WISE' AND TS_USER_11  <> 'Leslie Burnett')
    OR (TS_USER_10 = 'WITS-ATT' AND TS_USER_11  <> 'Leslie Burnett')
    OR (TS_USER_10 = 'WITS-CENT' AND TS_USER_11  <> 'Leslie Burnett')
    OR (TS_USER_10 = 'WITS-SPR' AND TS_USER_11  <> 'Leslie Burnett')
    OR (TS_USER_10 = 'WUI' AND TS_USER_11  <> 'Shelly McCormack')
    OR (TS_USER_10 = 'WUI-ATT' AND TS_USER_11  <> 'Shelly McCormack')
    OR (TS_USER_10 = 'WUI-EU' AND TS_USER_11  <> 'Deepa Kalangi')
ORDER BY [TS_USER_11]

It runs in maybe 2 seconds if I took out the Self-join. I tried to make it a WHERE NOT EXISTS but it was just as slow.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
 
Sort by date Sort by votes
Yeah... breaking that up into a Users table and ProdDept table would help your cause greatly.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------
 
Upvote 0 Downvote
What I did was just encompass the whole OR structure in extra () and it is now like a sub second query

Code: 
SELECT [TS_USER_11] as 'Product Owner',count(*) as 'Count'
        FROM TEST WITH (NOLOCK), ALL_LISTS WITH (NOLOCK)
        WHERE TEST.TS_SUBJECT = ALL_LISTS.AL_ITEM_ID
            AND (TEST.TS_TYPE = 'MANUAL' OR TEST.TS_TYPE = 'QUICKTEST_TEST')
            AND ALL_LISTS.AL_ABSOLUTE_PATH Not Like 'AAAAAJAAD%'--Archived
            AND ALL_LISTS.AL_ABSOLUTE_PATH Not Like 'AAAAAJABL%'--QA Services
            AND ALL_LISTS.AL_ABSOLUTE_PATH Not Like 'AAAAAJACB%'--Recycle Bin
            AND ((TS_USER_10 = 'ASG' AND TS_USER_11  <> 'Leslie Burnett')
            OR (TS_USER_10 = 'ATBS' AND TS_USER_11  <> 'Beth Hubner')
            OR (TS_USER_10 = 'AUI' AND TS_USER_11  <> 'Jacky Rasaphoumy')
            OR (TS_USER_10 = 'AUI-EU' AND TS_USER_11  <> 'Deepa Kalangi')
            OR (TS_USER_10 = 'BB' AND TS_USER_11  <> 'Santaji Nanaware')
            OR (TS_USER_10 = 'BOFA' AND TS_USER_11  <> 'Azza Shehata')
            OR (TS_USER_10 = 'BPM' AND TS_USER_11  <> 'Joseph Brooks')
            OR (TS_USER_10 = 'CRM-BILL' AND TS_USER_11  <> 'Mandeep Uppal')
            OR (TS_USER_10 = 'CRM-CL' AND TS_USER_11  <> 'Beth Hubner')
            OR (TS_USER_10 = 'CRM-COP' AND TS_USER_11  <> 'Nick Thorne')
            OR (TS_USER_10 = 'CRM-CSAT' AND TS_USER_11  <> 'Nick Thorne')
            OR (TS_USER_10 = 'CRM-EU' AND TS_USER_11  <> 'Beth Hubner')
            OR (TS_USER_10 = 'CRM-HS' AND TS_USER_11  <> 'Nick Thorne')
            OR (TS_USER_10 = 'CRM-MTS' AND TS_USER_11  <> 'Mandeep Uppal')
            OR (TS_USER_10 = 'CRM-SCR' AND TS_USER_11  <> 'Jacky Rasaphoumy')
            OR (TS_USER_10 = 'CSET' AND TS_USER_11  <> 'Shelly McCormack')
            OR (TS_USER_10 = 'CS-NET' AND TS_USER_11  <> 'Ryan Oglesby')
            OR (TS_USER_10 = 'CS-TIBCO' AND TS_USER_11  <> 'Ryan Oglesby')
            OR (TS_USER_10 = 'DAX' AND TS_USER_11  <> 'Azza Shehata')
            OR (TS_USER_10 = 'DAX-EU' AND TS_USER_11  <> 'Deepa Kalangi')
            OR (TS_USER_10 = 'EC' AND TS_USER_11  <> 'Shelly McCormack')
            OR (TS_USER_10 = 'EP' AND TS_USER_11  <> 'Fatema Pedhiwala')
            OR (TS_USER_10 = 'ETL' AND TS_USER_11  <> 'Nick Thorne')
            OR (TS_USER_10 = 'IG' AND TS_USER_11  <> 'Nick Thorne')
            OR (TS_USER_10 = 'INF' AND TS_USER_11  <> 'Brad Wirths')
            OR (TS_USER_10 = 'INTST' AND TS_USER_11  <> 'Swapnil Patil')
            OR (TS_USER_10 = 'IR' AND TS_USER_11  <> 'Mandeep Uppal')
            OR (TS_USER_10 = 'IS' AND TS_USER_11  <> 'Nick Thorne')
            OR (TS_USER_10 = 'IVR' AND TS_USER_11  <> 'Kimberly Cooper')
            OR (TS_USER_10 = 'LI' AND TS_USER_11  <> 'Fatema Pedhiwala')
            OR (TS_USER_10 = 'LOG' AND TS_USER_11  <> 'Ryan Oglesby')
            OR (TS_USER_10 = 'LR' AND TS_USER_11  <> 'Mandeep Uppal')
            OR (TS_USER_10 = 'MEA' AND TS_USER_11  <> 'Mandeep Uppal')
            OR (TS_USER_10 = 'MRAA' AND TS_USER_11  <> 'Mandeep Uppal')
            OR (TS_USER_10 = 'PS' AND TS_USER_11  <> 'Nick Thorne')
            OR (TS_USER_10 = 'PT' AND TS_USER_11  <> 'Shelly McCormack')
            OR (TS_USER_10 = 'QAS' AND TS_USER_11  <> 'Brandon Ainsworth')
            OR (TS_USER_10 = 'QAS' AND TS_USER_11  <> 'Shelly McCormack')
            OR (TS_USER_10 = 'RA' AND TS_USER_11  <> 'Azza Shehata')
            OR (TS_USER_10 = 'SD' AND TS_USER_11  <> 'Brandon Ainsworth')
            OR (TS_USER_10 = 'STAR' AND TS_USER_11  <> 'Azza Shehata')
            OR (TS_USER_10 = 'VTI' AND TS_USER_11  <> 'Swapnil Patil')
            OR (TS_USER_10 = 'W7' AND TS_USER_11  <> 'Jason Little')
            OR (TS_USER_10 = 'WATS-BS' AND TS_USER_11  <> 'Leslie Burnett')
            OR (TS_USER_10 = 'WISE' AND TS_USER_11  <> 'Leslie Burnett')
            OR (TS_USER_10 = 'WITS-ATT' AND TS_USER_11  <> 'Leslie Burnett')
            OR (TS_USER_10 = 'WITS-CENT' AND TS_USER_11  <> 'Leslie Burnett')
            OR (TS_USER_10 = 'WITS-SPR' AND TS_USER_11  <> 'Leslie Burnett')
            OR (TS_USER_10 = 'WUI' AND TS_USER_11  <> 'Shelly McCormack')
            OR (TS_USER_10 = 'WUI-ATT' AND TS_USER_11  <> 'Shelly McCormack')
            OR (TS_USER_10 = 'WUI-EU' AND TS_USER_11  <> 'Deepa Kalangi'))
        GROUP BY [TS_USER_11]
        ORDER BY [TS_USER_11]

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
 
Upvote 0 Downvote
I don't see much of the difference and I doubt of the performance improvement. You need to have a table with TS_User_10 and TS_User_11 fields and do

select ...
from myTable T JOIN LookupTable LT on

T.TS_User_10 = LT.TS_User_10 and T.TS_User_11 <> LT.TS_User_11

then I expect a better performance and also your code will be easier to manage and add extra conditions if needed.

PluralSight Learning Library
 
Upvote 0 Downvote
There is a HUGE different. I put () around the last conditional set and it took it from a 4 minute query down to a sub-second query. I'm not kidding.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
 
Upvote 0 Downvote
Are you sure you have the same result set?

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------
 
Upvote 0 Downvote
Yes i triple checked. I am working with PRoduction Data so i had to verify.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
Gaps and islands
Replies
8
Views
230
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
How to enhance query to run faster ?
Replies
0
Views
100
ahmedsa2018
ahmedsa2018
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
288
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
282
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
147
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top