Re: Tandem SQL question

On Nov 5, 7:51 am, roshint...@xxxxxxxxxxx wrote:
Hi,

What is required here is the equivalent to the Enscribe "START"
position.

The only equivalent to a START is an embedded OPEN cursor command. A
SELECT is an "all-at-once" command.

Each qualifying COUNT(*) could find 100 or more rows. We are only
interested in whether there were zero or non-zero rows found.

So you're interested in its existence.

What we want is for SQL to say, "I've found the first qualifying row"
and stop looking any further.

But, where are you starting?

At the moment, SQL scans every row (be it in an index or table)
counting rows even though we know when it finds one that we are now
happy and don't want to waste time going any further.

In Enscribe, I would have said, get first record from FILE-A, then
START on FILE-B whith the other condition. This would have replied
found or EOF. So, I would have found two or one records (in fact only
set the position pointer).

Are you saying that tableB.field3 is an indexed column? If so, is
your table large enough to justify this index? The optimizer may find
it just as fast to merely swipe the entire table into cache.

If it's not a key, then a good optimizer will take a pessimistic
approach and assume that the value will be found after the middle or
near the end of any key value. SQL has much more efficient data
access methods than positioning on a key and cursoring forward. In
this case, a non-keyed table scan will usually get the value faster
than keyed reads.

In SQL, it appears that I have to find 101 or 1 rows every time I just
want to see if a condition is true or not.

I was sure that you could limit NS COBOL SQL to how many rows it found
at a time (a bit like in SQLCI) but i can't remember how to do it.

Do you have statistics updated? That can mean the difference between
a two-hour elapsed time and a tenth of a second.

As a general rule, use the types of syntax that Doug and John
suggest. COUNT(*), in a subquery, generally produces less efficient
plans (even though it's logically equivalent).

Without some sample DDL, it's difficult to determine what you're
after. With some quick and dirty substitution, I came up with:

select emp.emp_id, emp.emp_name
from employee emp
where emp.dept = "ABC"
and exists (select *
from =projects proj where proj.dept = emp.dept);

In this case, a department/projects table would be in order. That
would provide a key that would allow the optimizer to do a SEMIJOIN
(just check for existence).

Joe Bishop

.

Relevant Pages

  • Re: UPDATE syntax question
    ... Specifically in an EXISTS clause, the optimizer will just test for the ... existence of any rows, and not worry about what columns were specified. ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance measurement and optimization levels
    ... > char ch; ... A good optimizer will optimize the above out of existence. ... assignment. ...
    (comp.lang.cpp)