MIDRANGE dot COM Mailing List Archive



Home » RPG400-L » February 2011

RE: sql Vs RLA to test existance



fixed

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



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








Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact