David FOXWELL | 15 Feb 15:44 2011

sql Vs RLA to test existance

Hi,

I need to find if a transaction of a particular type and amount exists for a client.

I cannot remember how to apply the SQL Exists( ) to look for the transaction.

Select '1' from ???? where exists ( select * from mytable where myfield = 'myfield')

Then again, I seem to remember that EXists will scan all the rows and not just stop at the first one that
satisfies the search criteria as I could do with RLA.

Can someone put me straight please?

Thanks.

--

-- 
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@...
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@...
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Marc Couture | 15 Feb 15:55 2011
Picon

RE: sql Vs RLA to test existance


What about

Select count(*) into :X from ???? myfield = 'myfield')  ?

 “If you think education is expensive, try ignorance"
(Derek Bok, pres. of Harvard Univ. 1971-1990)

> From: David.FOXWELL@...
> To: rpg400-l@...
> Date: Tue, 15 Feb 2011 15:44:30 +0100
> Subject: sql Vs RLA to test existance
> 
> Hi,
> 
> I need to find if a transaction of a particular type and amount exists for a client.
> 
> I cannot remember how to apply the SQL Exists( ) to look for the transaction.
> 
> Select '1' from ???? where exists ( select * from mytable where myfield = 'myfield')
> 
> 
> Then again, I seem to remember that EXists will scan all the rows and not just stop at the first one that
satisfies the search criteria as I could do with RLA.
> 
> Can someone put me straight please?
> 
> Thanks.
> 
> -- 
(Continue reading)

McGovern, Sean | 15 Feb 16:15 2011

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')

-----Original Message-----
From: rpg400-l-bounces@...
[mailto:rpg400-l-bounces@...] On Behalf Of Marc Couture
Sent: 15 February 2011 14:55
To: RPG programming on the IBM i / System i
Subject: RE: sql Vs RLA to test existance

What about

Select count(*) into :X from ???? myfield = 'myfield')  ?

 "If you think education is expensive, try ignorance"
(Derek Bok, pres. of Harvard Univ. 1971-1990)

> From: David.FOXWELL@...
> To: rpg400-l@...
> Date: Tue, 15 Feb 2011 15:44:30 +0100
(Continue reading)

David FOXWELL | 15 Feb 16:50 2011

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@... 
> [mailto:rpg400-l-bounces@...] 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')
> 
> 
> 
> -----Original Message-----
> From: rpg400-l-bounces@...
(Continue reading)

McGovern, Sean | 15 Feb 17:17 2011

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@...
[mailto:rpg400-l-bounces@...] On Behalf Of David FOXWELL
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@... 
> [mailto:rpg400-l-bounces@...] 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...
> 
(Continue reading)

David FOXWELL | 15 Feb 18:03 2011

RE: sql Vs RLA to test existance


Maybe I'm misinterpreting the results.
I tried 
Select * from tablewith6millionlines  fetch first row only and it was lightning fast.

Visual explain shows 2 stages : 
1 complete table scan 6 million lines selected 001 ms.
2 Extraction of n lines. 3.134E-5 ms

> -----Message d'origine-----
> De : rpg400-l-bounces@... 
> [mailto:rpg400-l-bounces@...] 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@... 
> Sent: 15 February 2011 15:50
> To: RPG programming on the IBM i / System i
> Subject: RE: sql Vs RLA to test existance
(Continue reading)

McGovern, Sean | 15 Feb 22:02 2011

RE: sql Vs RLA to test existance

When a table scan is performed, if the first row matches the selection criteria and you only require one row,
then it is going to be fast. No other rows will be read. If a table scan is performed and it is the last row of a
table with 6 million rows that matches the selection criteria, it is going to be slower.

________________________________

From: rpg400-l-bounces@... on behalf of David FOXWELL
Sent: Tue 15/02/2011 17:03
To: RPG programming on the IBM i / System i
Subject: RE: sql Vs RLA to test existance

Maybe I'm misinterpreting the results.
I tried
Select * from tablewith6millionlines  fetch first row only and it was lightning fast.

Visual explain shows 2 stages :
1 complete table scan 6 million lines selected 001 ms.
2 Extraction of n lines. 3.134E-5 ms

> -----Message d'origine-----
> De : rpg400-l-bounces@...
> [mailto:rpg400-l-bounces@...] 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
(Continue reading)

McGovern, Sean | 15 Feb 17:28 2011

RE: sql Vs RLA to test existance

I should rephrase that first comment. 

I don't disagree that RLA would be better (for performance) rather than SQL EXISTS.

-----Original Message-----
From: McGovern, Sean 
Sent: 15 February 2011 16:18
To: RPG programming on the IBM i / System i
Subject: 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@...
[mailto:rpg400-l-bounces@...] On Behalf Of David FOXWELL
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@... 
(Continue reading)

Luis Rodriguez | 15 Feb 15:57 2011
Picon

Re: sql Vs RLA to test existance

David,

IIRC, EXISTS stops as soon as it finds the first record. The syntax is:

SELECT *  FROM TABLE1  WHERE EXISTS(SELECT 1 FROM TABLE2 WHERE TABLE1.FIELD
= TABLE2.FIELD) ;

Regards,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--

On Tue, Feb 15, 2011 at 10:14 AM, David FOXWELL <David.FOXWELL@...>wrote:

> Hi,
>
> I need to find if a transaction of a particular type and amount exists for
> a client.
>
> I cannot remember how to apply the SQL Exists( ) to look for the
> transaction.
>
> Select '1' from ???? where exists ( select * from mytable where myfield =
> 'myfield')
>
>
> Then again, I seem to remember that EXists will scan all the rows and not
> just stop at the first one that satisfies the search criteria as I could do
> with RLA.
>
(Continue reading)

Luis Rodriguez | 15 Feb 16:14 2011
Picon

Re: sql Vs RLA to test existance

In the SQL vs RLA contest, if you are using the SQL example below just for
checking records, RLA should win in performance, as the process of
Validating the query, optimizing it (creating an Access Plan) building the
ODP, etc is very expensive in time terms, compared to a simple CHAIN, where
practically the only overhead is when you OPEN the file.

Regards,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--

On Tue, Feb 15, 2011 at 10:27 AM, Luis Rodriguez <luisro58@...> wrote:

> David,
>
> IIRC, EXISTS stops as soon as it finds the first record. The syntax is:
>
> SELECT *  FROM TABLE1  WHERE EXISTS(SELECT 1 FROM TABLE2 WHERE TABLE1.FIELD
> = TABLE2.FIELD) ;
>
> Regards,
> Luis Rodriguez
> IBM Certified Systems Expert — eServer i5 iSeries
> --
>
>
>
>
> On Tue, Feb 15, 2011 at 10:14 AM, David FOXWELL <David.FOXWELL@...>wrote:
>
(Continue reading)

Tommy.Holden | 15 Feb 16:19 2011

Re: sql Vs RLA to test existance

Using SETLL will outperform the CHAIN as well...

Thanks,
Tommy Holden

From:   Luis Rodriguez <luisro58@...>
To:     "RPG programming on the IBM i / System i" <rpg400-l@...>
Date:   02/15/2011 09:17 AM
Subject:        Re: sql Vs RLA to test existance
Sent by:        rpg400-l-bounces@...

In the SQL vs RLA contest, if you are using the SQL example below just for
checking records, RLA should win in performance, as the process of
Validating the query, optimizing it (creating an Access Plan) building the
ODP, etc is very expensive in time terms, compared to a simple CHAIN, 
where
practically the only overhead is when you OPEN the file.

Regards,
Luis Rodriguez
IBM Certified Systems Expert ? eServer i5 iSeries
--

On Tue, Feb 15, 2011 at 10:27 AM, Luis Rodriguez <luisro58@...> 
wrote:

> David,
>
> IIRC, EXISTS stops as soon as it finds the first record. The syntax is:
>
(Continue reading)

Schutte, Michael D | 15 Feb 16:37 2011

RE: sql Vs RLA to test existance

I second this. Using SETLL will set %EQUAL(TABLE) to either *ON if found or *OFF if not found.

-----Original Message-----
From: rpg400-l-bounces@...
[mailto:rpg400-l-bounces@...] On Behalf Of Tommy.Holden@...
Sent: Tuesday, February 15, 2011 10:19 AM
To: RPG programming on the IBM i / System i
Subject: Re: sql Vs RLA to test existance

Using SETLL will outperform the CHAIN as well...

Thanks,
Tommy Holden

From:   Luis Rodriguez <luisro58@...>
To:     "RPG programming on the IBM i / System i" <rpg400-l@...>
Date:   02/15/2011 09:17 AM
Subject:        Re: sql Vs RLA to test existance
Sent by:        rpg400-l-bounces@...

In the SQL vs RLA contest, if you are using the SQL example below just for
checking records, RLA should win in performance, as the process of
Validating the query, optimizing it (creating an Access Plan) building the
ODP, etc is very expensive in time terms, compared to a simple CHAIN,
where
practically the only overhead is when you OPEN the file.

Regards,
Luis Rodriguez
IBM Certified Systems Expert ? eServer i5 iSeries
(Continue reading)

Luis Rodriguez | 15 Feb 16:44 2011
Picon

Re: sql Vs RLA to test existance

Right.

Bad habit of mine, as in our shop, 99% of times we need to check existence
AND retrieve a value, hence the CHAIN. But yes, if you only need to check
for the record existence, I believe SETLL should be faster.

Thanks for the head-up.

Regards,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--

On Tue, Feb 15, 2011 at 10:49 AM, <Tommy.Holden@...>wrote:

> Using SETLL will outperform the CHAIN as well...
>
> Thanks,
> Tommy Holden
>
>
> From:   Luis Rodriguez <luisro58@...>
> To:     "RPG programming on the IBM i / System i" <rpg400-l@...>
> Date:   02/15/2011 09:17 AM
> Subject:        Re: sql Vs RLA to test existance
> Sent by:        rpg400-l-bounces@...
>
>
>
> In the SQL vs RLA contest, if you are using the SQL example below just for
(Continue reading)

David FOXWELL | 15 Feb 16:53 2011

RE: sql Vs RLA to test existance


> -----Message d'origine-----
> [mailto:rpg400-l-bounces@...] De la part de 
> Tommy.Holden@...

> Using SETLL will outperform the CHAIN as well...
> 
> Thanks,
> Tommy Holden
> 
I like the sound of that, as I would not even read anything. But I'm looking for a transaction rate where the
rate is more than 150%.
--

-- 
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@...
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@...
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Luis Rodriguez | 15 Feb 16:30 2011
Picon

Re: sql Vs RLA to test existance

Of course, you can limit the SELECT  further by using FETCH:

SELECT *  FROM TABLE1  WHERE EXISTS(SELECT 1 FROM TABLE2 WHERE TABLE1.FIELD
= TABLE2.FIELD)
  FETCH FIRST ROW ONLY
;

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--

On Tue, Feb 15, 2011 at 10:44 AM, Luis Rodriguez <luisro58@...> wrote:

> In the SQL vs RLA contest, if you are using the SQL example below just for
> checking records, RLA should win in performance, as the process of
> Validating the query, optimizing it (creating an Access Plan) building the
> ODP, etc is very expensive in time terms, compared to a simple CHAIN, where
> practically the only overhead is when you OPEN the file.
>
> Regards,
> Luis Rodriguez
> IBM Certified Systems Expert — eServer i5 iSeries
> --
>
>
>
> On Tue, Feb 15, 2011 at 10:27 AM, Luis Rodriguez <luisro58@...>wrote:
>
(Continue reading)

McGovern, Sean | 15 Feb 17:00 2011

RE: sql Vs RLA to test existance

There is no need to have EXISTS and the FETCH FIRST ROW ONLY together,
they will both give the same result (and performance will be similar).
To me, it just confuses the requirement.

-----Original Message-----
From: rpg400-l-bounces@...
[mailto:rpg400-l-bounces@...] On Behalf Of Luis Rodriguez
Sent: 15 February 2011 15:31
To: RPG programming on the IBM i / System i
Subject: Re: sql Vs RLA to test existance

Of course, you can limit the SELECT  further by using FETCH:

SELECT *  FROM TABLE1  WHERE EXISTS(SELECT 1 FROM TABLE2 WHERE
TABLE1.FIELD
= TABLE2.FIELD)
  FETCH FIRST ROW ONLY
;

Regards,

Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries
--

On Tue, Feb 15, 2011 at 10:44 AM, Luis Rodriguez <luisro58@...>
wrote:

> In the SQL vs RLA contest, if you are using the SQL example below just
for
(Continue reading)

Luis Rodriguez | 15 Feb 17:25 2011
Picon

Re: sql Vs RLA to test existance

Sean,

I read the original requirement as checking the existence of Table1 records
in Table2. So, if Table1 has more than one record that satisfies the
existence check in Table2, fetch would limit the number of Table1 records
read.

Now, I realize that the requirement seems to be a lot simpler than what I
initially thought, ie., just one table, so my suggestion would not apply...

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--

On Tue, Feb 15, 2011 at 11:30 AM, McGovern, Sean <Sean.McGovern@...
> wrote:

> There is no need to have EXISTS and the FETCH FIRST ROW ONLY together,
> they will both give the same result (and performance will be similar).
> To me, it just confuses the requirement.
>
>
>
> -----Original Message-----
> From: rpg400-l-bounces@...
> [mailto:rpg400-l-bounces@...] On Behalf Of Luis Rodriguez
> Sent: 15 February 2011 15:31
> To: RPG programming on the IBM i / System i
(Continue reading)

DeLong, Eric | 15 Feb 17:46 2011

RE: sql Vs RLA to test existence

I wonder if the real question here is "can I take an application
designed from RLA perspective, and turn this into a SQL application?"  I
think trying to convert from RLA to SQL without breaking out of the RLA
mindset results in frustration and dissatisfaction.  

For SQL to be really effective, you need to "rethink" how you accomplish
your goals.  Just converting chain, setll, read, or write into SQL
alternatives misses the point of set-oriented operation of SQL.

Jmo,
-Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@...
[mailto:rpg400-l-bounces@...] On Behalf Of Luis Rodriguez
Sent: Tuesday, February 15, 2011 10:25 AM
To: RPG programming on the IBM i / System i
Subject: Re: sql Vs RLA to test existance

Sean,

I read the original requirement as checking the existence of Table1
records
in Table2. So, if Table1 has more than one record that satisfies the
existence check in Table2, fetch would limit the number of Table1
records
read.

Now, I realize that the requirement seems to be a lot simpler than what
I
(Continue reading)

David FOXWELL | 15 Feb 18:12 2011

RE: sql Vs RLA to test existence


> -----Message d'origine-----
> De : rpg400-l-bounces@... 
> [mailto:rpg400-l-bounces@...] De la part de DeLong, Eric
> Envoyé : mardi 15 février 2011 17:46
> À : RPG programming on the IBM i / System i
> Objet : RE: sql Vs RLA to test existence
> 
> I wonder if the real question here is "can I take an 
> application designed from RLA perspective, and turn this into 
> a SQL application?"  I think trying to convert from RLA to 
> SQL without breaking out of the RLA mindset results in 
> frustration and dissatisfaction.  
> 
> For SQL to be really effective, you need to "rethink" how you 
> accomplish your goals.  Just converting chain, setll, read, 
> or write into SQL alternatives misses the point of 
> set-oriented operation of SQL.

I thought at first I was needing a set operation as I needed to compare the maximum rate for all transactions
against a reference rate.
That meant reading all transactions. But in fact I only needed to know if one of those transactions reached
the reference rate. 
--

-- 
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@...
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@...
Before posting, please take a moment to review the archives
(Continue reading)


Gmane