• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

NOT and ISNULL Question

I have developed a query, part of which is below:

WHERE NOT (Table1.field1 IS NULL AND Table1.field1 in (60, 61, 62, 63) AND isnull(ltrim(rtrim(Table2.field2)), "") = "" ) AND NOT (Table2.field2 LIKE "110%")

Looking at the execution plan for this query, there is a high % against this part of the query and why it takes a while to execute.

Is there another way to write this to make it execute faster?

Thanks
0
halifaxman
Asked:
halifaxman
  • 3
1 Solution
 
David ToddSenior DBACommented:
Hi,

First point
Table1.field1 can not be both null and in (60, 61 ... etc at the same time.

Regards
  David
0
 
LowfatspreadCommented:
Hi halifaxman,
> WHERE NOT (Table1.field1 IS NULL AND Table1.field1 in (60, 61, 62, 63) AND
> isnull(ltrim(rtrim(Table2.field2)), "") = "" ) AND NOT (Table2.field2 LIKE "110%")


alway try and avoid using NOT (... expressions)

but
(Table1.field1 IS NULL
  AND Table1.field1 in (60, 61, 62, 63)
  AND isnull(ltrim(rtrim(Table2.field2)), "") = "" )

is always False so NOT ... becomes true?

is the above correct should the in test be against a column other than field1?

if the field1 test is correct what are you trying to achieve with it?


i think currently
the where clause should be

where table2.field2 < '110'
   or table2.field2 > '11099999999999'


Cheers!
0
 
LowfatspreadCommented:
halifaxman,


In general try an avoid use of the NOT condition
unless its to say IS NOT NULL

any other use end ups limitinf the effectiveness of your indexes..

0
 
LowfatspreadCommented:
sorry..
NOT EXISTS is ok as well as IS NOT NULL
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now