SELECT EXISTS
Ingo Stiller
We need a high performance solution for a SELECT EXISTS database
query :-)
A)
In the past, we do it like this way:
clear/e "ENTITY"
FLD_1.ENTITY=value1
FLD_2.ENTITY>value2
...
RETRIEVE/e "ENTITY"
IF($dbocc(ENTITY)>0)
;Found
ELSE
; Not found
ENDIF
If you look at the generated SQL, this will result in a
SELECT FLD_A,FLD_B,FLD_C,... FROM ENTITY
WHERE FLD_1=value1 AND FLD_2>value2 ...
As the database don't know if we need more rows form the table, it
will optimize this i a was to loop through all rows.
B)
Use of SELECTDB
SELECTDB COUNT( FLD_1) FROM "ENTITY"
U_WHERE ( FLD_1.ENTITY=value1 & FLD_2.ENTITY>values2 ... )
INTO v_COUNTER
IF(v_COUNTER>0)
;Found
ELSE
; Not found
ENDIF
If you look at the generated SQL, this will result in a
SELECT COUNT(FLD_1) FROM ENTITY
WHERE FLD_1=value1 AND FLD_2>value2 ...
In this case, all rows (from table and/or index) will be read to
count them.
C)
What we need is this
SELECT
CASE WHEN EXISTS
(
SELECT 1 FROM ENTITY WHERE FLD_1=value AND FLD_2=value ...
)
THEN 1
ELSE 0
END
In this case, the database drive will optimize the access to read
only the first row that match the criteria. And that is much faster
then all other solution!
[Believe me, I did check the explanation plan:-))) ]
BUT: There is a disadvantage :(
You can't use U_WHERE or U_CONDITION as SQL/PRINT don't know this
features.
So if you need a generic solution, this is not real solution.
Generic:
Different databases
Complex queries
Decouple the 'normal' programing from the techincal part
Example
EXISTS SELECT 1 FROM ENTITY U_WHERE ( PERIOD_BEGIN.ENTITY>$date )
How to translate this to MSS, ORA or SOL
How to translate this to a german,english,... database
Any ideas?
TIA
Ingo
Ingo Stiller
Eric Godsey
ex:
String var_sql
Var_sql= "selectdb count(*) from user_tables where table_name =
'mytable';"
Sql/print var_sql
Then check message frame to see if it returned a number.
Hi Freaks
B)
Use of SELECTDB
Example
Any ideas?
TIA
Ingo
_______________________________________________
Uniface User Group Discussion Forum
For more information:
http://lists.umanitoba.ca/mailman/listinfo/uniface-l
To unsubscribe/set options:
http://lists.umanitoba.ca/mailman/options/uniface-l
Ulrich Merkel
just a very old one:
what about using LOOKUP (you will need a form for each select, but ...).
and another very old one:
you may abuse the WHERE to add some clauses to the SQL statement:
SELECT NAME FROM EMP ORDER BY NAME[5,10] works fine.
Unfortunately uniface (5.2.f) doesn't allow a
read order by "NAME[5,10]"
So we tried some dirty tricks using the where-clause:
read where "text" causes uniface to generate a sql statement like
SELECT ... FROM ... WHERE (text)
The following strange looking uniface statement builds a hitlist sorted by a
substring:
read where "0=0) order by NAME[5,10] --"
This forces uniface to construct
SELECT ... FROM ... WHERE (0=0) order by NAME[5,10] --)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
The '--' starts a comment in Informix SQL (as it does in Oracle SQL, I think)
and is needed to eliminate the final parenthesis. I hope this will be helpful to
someone else.
AFAIK, there are some commands to limit the hitlist (to 1 record only) which may be applied here.
Alternative: Transfor the entities ti views and put the limitation to the WHERE of the View.
Success, Uli
Hope we will meet again in the next weeks.
> -----Ursprüngliche Nachricht-----
> Von: Uniface User Group Discussion Forum <unif...@uug.org>
> Gesendet: 28.03.07 20:23:52
> An: uniface...@lists.umanitoba.ca
> Betreff: [Uniface-L] SELECT EXISTS
-- Ulrich Merkel --
ulrich...@web.de
+49(69)317881
Ingo Stiller
Eric Godsey schrieb:
> Can you do a sql that queries the user_tables for table_name?
>
> ex:
> String var_sql
> Var_sql= "selectdb count(*) from user_tables where table_name =
> 'mytable';"
> Sql/print var_sql
>
> Then check message frame to see if it returned a number.
Hi Eric
ThanX for your idea, but ... :-))
But, I don't want to count all rows.
What we need is the answer to an existence question:
"If or if not at least one row will match our U_WHERE criteria"
And if possible, independent from the database type :-(
Ingo
Ingo Stiller
Eric Godsey schrieb:
> Can you do a sql that queries the user_tables for table_name?
>
> ex:
> String var_sql
> Var_sql= "selectdb count(*) from user_tables where table_name =
> 'mytable';"
> Sql/print var_sql
>
> Then check message frame to see if it returned a number.
Paul Koldijk
<READ TRIGGER>
read where exists(select 'x' from entity)
</READ TRIGGER>
Francois Moritz
If it's a performance problem why don't you try to implement a solution in
oracle itself ?
do a oracle storedproc and add where (ROWNUM=1)
or use SQL from uniface and add this condition.
regards
François Moritz
Hewitt Associates SA
Avenue Edouard-Dubois 20, CH-2000 Neuchâtel
Phone: +41 32 732 34 29
Fax: +41 32 732 31 00
Hewitt Associates is a leading outsourcing and consulting firm globally
and in Switzerland
delivering a complete range of human capital management services.
For more detailed information: www.hewitt.ch and www.hewitt.com
"Ingo Stiller" <i2st...@gmx.de>
Sent by: uniface-...@uug.org
28.03.2007 21:36
Please respond to
"Uniface User Group Discussion Forum" <unif...@uug.org>
To
uniface...@lists.umanitoba.ca
cc
Subject
[Uniface-L] Re: SELECT EXISTS
Eric Godsey schrieb:
Hi Eric
Ingo
_______________________________________________
Uniface User Group Discussion Forum
For more information: http://lists.umanitoba.ca/mailman/listinfo/uniface-l
To unsubscribe/set options:
http://lists.umanitoba.ca/mailman/options/uniface-l
The information contained in this e-mail and any accompanying documents may contain information that is confidential or otherwise protected from disclosure. If you are not the intended recipient of this message, or if this message has been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this message, including any attachments. Any dissemination, distribution or other use of the contents of this message by anyone other than the intended recipient
is strictly prohibited.
Iain Sharp
wrote:
How about.
read "<ENTITY_1>" options "maxhits=1" u_where etc etc.
This should retrieve only the first matching record....
Iain
Iain Sharp
Simon MacLoughlin
Is there a combined index on (fld_1, fld_2) ? And is fld_1 numeric ?
If so using SELECTDB MIN(FLD_1) rather than SELECTDB COUNT(FLD_1) should
be pretty fast on all DBMSs as MIN(FLD_1) will only need to find the
first row in the index matching the criteria...
As I say, this would only be fast if there is a combined index on
(fld_1, fld_2) but then this would also be the case with an EXISTS
clause. Also Uniface places restrictions on the datatypes you can use a
MIN clause with.
Cheers
Simon
-----Original Message-----
From: uniface-...@uug.org [mailto:uniface-...@uug.org] On
Behalf Of Ingo Stiller
Sent: 28 March 2007 18:34
To: uniface...@lists.umanitoba.ca
Subject: [Uniface-L] SELECT EXISTS
Hi Freaks
B)
Use of SELECTDB
Example
Any ideas?
TIA
Ingo
_______________________________________________
Ingo Stiller
wrote:
> Hello
> If it's a performance problem why don't you try to implement a solution in
> oracle itself ?
> do a oracle storedproc and add where (ROWNUM=1)
> or use SQL from uniface and add this condition.
> regards
>
Hi François
Nice, but ... :-))
We do have SOL, ORA und MSS on the playground
So I have to implement this for all DBMS
SOL: WHERE ROWNUM<2
ORA: WHERE ROWNUM=1
MSS: SELECT TOP 1
Ok, this will do the job, but then there is the problem, that I don't
know which U_WHERE my colleagues are using. So I have to create a VIEW
for each tables with this condition. And each of this views is a new
table in the meta-dictonary :(
Ingo
Ingo Stiller
> If it's a performance problem why don't you try to implement a solution in
> oracle itself ?
> do a oracle storedproc and add where (ROWNUM=1)
> or use SQL from uniface and add this condition.
> regards
>
Hi François
Ingo Stiller
> On 28 Mar 2007 12:36:21 -0700, "Ingo Stiller" <i2stil...@gmx.de>
Hi Iain
BUT :-))
The DBMS thinks, that you possible want more. And so It does not
optimize the access.
Ingo
Ingo
Ingo Stiller
Hi Iain
Knut Dybendahl
Just a suggestion,
do a clear/e
feed in the retrieve profile using QBF
then do a "lookup" (database independant)
$status contains the number of records found according
to the QBF (which really is a "read u_where"...)
HTH - Knut
> _______________________________________________
> Uniface User Group Discussion Forum
> For more information:
> http://lists.umanitoba.ca/mailman/listinfo/uniface-l
> To unsubscribe/set options:
> http://lists.umanitoba.ca/mailman/options/uniface-l
>
____________________________________________________________________________________
Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.
http://videogames.yahoo.com/platform?platform=120121
Ingo Stiller
> Hi Ingo,
>
> Just a suggestion,
>
> do a clear/e
> feed in the retrieve profile using QBF
> then do a "lookup" (database independant)
> $status contains the number of records found according
> to the QBF (which really is a "read u_where"...)
>
Hi Knut
First, you are not the icebear, or? :-)
Second, as you say, it counts the rows :-(
Ingo
Ingo Stiller
>
> Just a suggestion,
>
> do a clear/e
> feed in the retrieve profile using QBF
> then do a "lookup" (database independant)
> $status contains the number of records found according
> to the QBF (which really is a "read u_where"...)
>
Hi Knut
Ingo Stiller
<simon.maclough...@hisvector.com> wrote:
Hi Simon
> Is there a combined index on (fld_1, fld_2) ? And is fld_1 numeric ?
It depends :-)
> If so using SELECTDB MIN(FLD_1) rather than SELECTDB COUNT(FLD_1) should
> be pretty fast on all DBMSs as MIN(FLD_1) will only need to find the
> first row in the index matching the criteria...
But have to read further till the end, there could be a smaller value
at the very end.
>
> As I say, this would only be fast if there is a combined index on
> (fld_1, fld_2) but then this would also be the case with an EXISTS
> clause. Also Uniface places restrictions on the datatypes you can use a
> MIN clause with.
In one of our cases, we got the following situation:
Primary key is
PK_1 (String)
PK_2 (technical number)
Index
FLD_3 (string)
PK_1 (string, same field as above)
Attribute field
FLD_4 (numeric)
The question is, is there a least one row with the following condtion:
WHERE PK_1='00000001' AND FLD_3='000002' AND FLD_4>0
The table is big, very big.
Behind every PK_1 there are many million rows, identified by PK_2
If one use a simple SELECT, MS-SQL often uses the cluster order to
look for the rows.
Not before I put a SELECT EXISTS, MS-SQL uses the index and a bookmark
lookup to the table.
As this is a generic problem, I need a generic solution.
What I did yesterday eve in the pub is to write a little program,
which gets the table name and a (modified) U_WHERE clause. Then I
translate the U_WHERE to a WHERE clause and surround it by a few
SELECTs. Now I could use SQL/print to get the result.
This ist the resulting SQL-stament
SELECT CASE WHEN EXISTS (SELECT 1 FROM DY1100 WHERE GES_NR='00004711'
AND ANT_NR='000002' AND BETRAG_OFF>0) THEN 1 ELSE 0 END AS X
And it is fast :-))
Ingo
Ingo Stiller
<simon.maclough...@hisvector.com> wrote:
Hi Simon
> Is there a combined index on (fld_1, fld_2) ? And is fld_1 numeric ?
It depends :-)
> If so using SELECTDB MIN(FLD_1) rather than SELECTDB COUNT(FLD_1) should
> be pretty fast on all DBMSs as MIN(FLD_1) will only need to find the
> first row in the index matching the criteria...
But have to read further till the end, there could be a smaller value
at the very end.
>
> As I say, this would only be fast if there is a combined index on
> (fld_1, fld_2) but then this would also be the case with an EXISTS
> clause. Also Uniface places restrictions on the datatypes you can use a
> MIN clause with.
Porter Mike
stuff from select statements without referring to a specific table so:
select 1 from dual where exists (select 'X' from DY1100 WHERE
GES_NR='00004711' AND ANT_NR='000002' AND BETRAG_OFF>0)
Will return 1 row (value 1) so you can do a sql rather than sql/print
and only searches DY1100 until it finds the first row matching the
profile your success check is on $result = 1 (success) or null
(failure).
Unfortunately I have no clue about the syntax for the other DBs but Im
sure that sort of thing will be feasible.
Mike
-----Original Message-----
From: Ingo Stiller [mailto:i2st...@gmx.de]
Sent: 29 March 2007 14:32
To: uniface...@lists.umanitoba.ca
Subject: [Uniface-L] Re: SELECT EXISTS
Hi Simon
It depends :-)
Attribute field
FLD_4 (numeric)
Ingo
Ingo Stiller
Hi Mike
> In oracle there is a dummy table called dual which you can use to get
> stuff from select statements without referring to a specific table so:
>
> select 1 from dual where exists (select 'X' from DY1100 WHERE
> GES_NR='00004711' AND ANT_NR='000002' AND BETRAG_OFF>0)
>
> Will return 1 row (value 1) so you can do a sql rather than sql/print
> and only searches DY1100 until it finds the first row matching the
> profile your success check is on $result = 1 (success) or null
> (failure).
You are right, I can use SQL instead of SQL/print.
But this solves not the problem mit the datatypes :-(
In this example above, there are only 'simple' types, but what about
date fields?
select 1 from dual where exists (select 'X' from DY1100 WHERE
GES_NR='00004711' AND ANT_NR='000002' AND VALUTA_DATE>'01-02-2007')
Is this date format true for all databases and languages?
> Unfortunately I have no clue about the syntax for the other DBs but Im
> sure that sort of thing will be feasible.
>
Just create a table DUAL with one dummy column
Then insert into this table only one row.
So one can use the same trick then orcale.
BTW: You are not the owner of our new irish pub in Mainz? :-)
It called "The Porter house"
Ingo
Ingo Stiller
Hi Mike
> In oracle there is a dummy table called dual which you can use to get
> stuff from select statements without referring to a specific table so:
>
> select 1 from dual where exists (select 'X' from DY1100 WHERE
> GES_NR='00004711' AND ANT_NR='000002' AND BETRAG_OFF>0)
>
> Will return 1 row (value 1) so you can do a sql rather than sql/print
> and only searches DY1100 until it finds the first row matching the
> profile your success check is on $result = 1 (success) or null
> (failure).
You are right, I can use SQL instead of SQL/print.
But this solves not the problem mit the datatypes :-(
In this example above, there are only 'simple' types, but what about
date fields?
select 1 from dual where exists (select 'X' from DY1100 WHERE
GES_NR='00004711' AND ANT_NR='000002' AND VALUTA_DATE>'01-02-2007')
Is this date format true for all databases and languages?
> Unfortunately I have no clue about the syntax for the other DBs but Im
> sure that sort of thing will be feasible.
>
Just create a table DUAL with one dummy column
Porter Mike
table only one row.
>So one can use the same trick then orcale.
That should work
>
>BTW: You are not the owner of our new irish pub in Mainz? :-)
> It called "The Porter house"
I wish - although I wouldn't make it Irish themed (I hate guinness)
Porter Mike
a string to a date so interms of oracle SQL change your
VALUTA_DATE>'01-02-2007'
to
VALUTA_DATE>TO_DATE('01-02-2007', 'DD-MM-YYYY')
-----Original Message-----
From: Ingo Stiller [mailto:i2st...@gmx.de]
Sent: 29 March 2007 15:20
To: uniface...@lists.umanitoba.ca
Subject: [Uniface-L] Re: SELECT EXISTS
Porter Mike
Ingo Stiller
You know, that kilkenny is also from Irland :-))
I hate guiness too, but kilkenny: I do drink a few pints al least ones
a week
Ingo Stiller
You know, that kilkenny is also from Irland :-))