RE: sql Vs RLA to test existance

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de McGovern, Sean
Envoyé : mardi 15 février 2011 17:18
À : RPG programming on the IBM i / System i
Objet : RE: sql Vs RLA to test existance

I don't disagree that RLA would be better rather than SQL EXISTS.

But whether a table scan is done depends on whether the
engine THINKS this would be the faster approach (is there an
index on myfield, how my rows on the table etc.). Are you
sure it is a table scan on mytable or a table scan on SYSDUMMY1 ?



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
Sent: 15 February 2011 15:50
To: RPG programming on the IBM i / System i
Subject: RE: sql Vs RLA to test existance


Thanks, I didn't remember the sysdummy and I couldn't think
how to use exists with only one table.

Sorry to disappoint, but if you run your requests with visual
explain you will see that the whole table gets scanned.
That's why finally I'm sticking with RLA.

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de McGovern, Sean
Envoyé : mardi 15 février 2011 16:16 À : RPG programming on

the IBM i

/ System i Objet : RE: sql Vs RLA to test existance

Bad idea for performance. Why count all the rows just to discover
whether 1 row exists ?

Better would be...

Select 1 into :X from ???? Where myfield = 'myfield' Fetch

First Row

Only

Or, using exists...

Select 1 into :X From SYSIBM/SYSDUMMY1 Where Exists (Select 1 from
mytable Where myfield = 'myfield')