SAP Knowledge Base Article - Preview

2529100 - Precedence of evaluation and behavior of NOT EXISTS & EXISTS

Symptom

The following scenario: these kind of queries will take more time:

========

create proc p01 @param char(1)
as
if (@param = 'S' and not exists (select postalcode from authors where postalcode = "94704"))
begin
print "Berkeley author"
return
end
print "XXXXX"
return


create proc p02 @param char(1)
as
if (@param = 'S') and not exists (select postalcode from authors where postalcode = "94704")
begin
print "Berkeley author"
return
end
print "XXXXX"
return

========

Than this:

========

create proc p03 @param char(1)
as
if (@param = 'S' and exists (select postalcode from authors where postalcode = "94704"))
begin
print "Berkeley author"
return
end
print "XXXXX"
return

========

All procedures were executed passing "N" and the value "94704" doesn't exist on table.

When using "NOT EXISTS" the subquery is executed despite of variable “@param” is false (“N”) . In the developers's opinion this should not occur since the first part of is false.


They told the first operand should be completely evaluated and all side effects are completed before continuing evaluation of the logical AND expression. Then the second operand is evaluated only if the first operand evaluates to true (nonzero).

This doesn't occur when using "EXISTS".


Read more...

Environment

  • SAP Adaptive Server Enterprise (ASE) - All versions

Product

SAP Adaptive Server Enterprise 12.5 ; SAP Adaptive Server Enterprise 15.7 ; SAP Adaptive Server Enterprise 16.0

Keywords

performance NOT EXISTS precedence evaluation order operator parentheses expression , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.