Better way to test if exist
Dirk wanted a fast way to check whether a value exists in a column with few distinct values but thousands of duplicate rows; his SELECT DISTINCT ... WHERE col='XXX' was slow because it scanned all matching rows. Suggestions included GROUP BY ... HAVING COUNT(*)>1 (which gave the same cost in SET EXPLAIN) and using a cursor that fetches just one row and checks SQLCODE 0 vs 100. The accepted fix was Rudy Fernandes' trick: SELECT 1 FROM systables WHERE tabid=1 AND EXISTS (SELECT 1 FROM test_table WHERE test_column='XXX'), which returns 1 if present and no rows otherwise; Dirk reported it worked well.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
what is the best way to test if an duplicate value exist ?
Example: A indexed table column char(3) with 4 different entries. But
this 4 entries are duplicate average 1000 times.
Now I tried to test like this:
select distinct test_column from test_table where <column_test> = 'XXX';
That works fine, but it's very slow when the entry exists. Isn't there a
better way ?
TIA
Dirk
Try this:
Select test_column, count(*) from test_table
group by 1
having count(*) > 1
/Arthur
In article <39605C63.DC47038F@stueken.de>,
Dirk Niemeier <dirk.niemeier@stueken.de> wrote:
> Hi,
> what is the best way to test if an duplicate value exist ?
> Example: A indexed table column char(3) with 4 different entries. But
> this 4 entries are duplicate average 1000 times.
>
> Now I tried to test like this:
>
> select distinct test_column from test_table where <column_test>= 'XXX';
>
> That works fine, but it's very slow when the entry exists. Isn't
there a
> better way ?
>
> TIA
> Dirk
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Hi Arthur,
I think there aren't any changes between the distinct and the group by
version. The estimated time from explain (set explain on) is 12 in both
times.
TIA
Dirk
arthur_apw@my-deja.com schrieb:
> Try this:
>
> Select test_column, count(*) from test_table
> group by 1
> having count(*) > 1>
> /Arthur
>
> In article <39605C63.DC47038F@stueken.de>,
> Dirk Niemeier <dirk.niemeier@stueken.de> wrote:
> > Hi,
> > what is the best way to test if an duplicate value exist ?
> > Example: A indexed table column char(3) with 4 different entries. But
> > this 4 entries are duplicate average 1000 times.
> >
> > Now I tried to test like this:
> >
> > select distinct test_column from test_table where <column_test>> = 'XXX';
> >
> > That works fine, but it's very slow when the entry exists. Isn't
> there a
> > better way ?
> >
> > TIA
> > Dirk
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Hi,
If you are doing this by an by an application you could try using a cursor.
You only have to fetch one time. If at least one row matching exists you
get SQLCODE = 0 else you get 100.
We are using this in our application to test the existence of data.
Greetings
Frank
Dirk Niemeier schrieb:
> Hi,
> what is the best way to test if an duplicate value exist ?
> Example: A indexed table column char(3) with 4 different entries. But
> this 4 entries are duplicate average 1000 times.
>
> Now I tried to test like this:
>
> select distinct test_column from test_table where <column_test> = 'XXX';>
> That works fine, but it's very slow when the entry exists. Isn't there a
> better way ?
>
> TIA
> Dirk
Hi Frank,
do you think that the usage of an cursor may be faster ? I think the usage of
declare, open and fetch takes the same time.
In that case the open statement will take the time. Won't it ? I don't test it,
but I don't see any differences.
It coul't be that there are differences if the DB ist running in different
isolation levels. May be that DIRTY READ is an nice
choice. And a select without DISTINCT, GROUP BY or ORDER BY.
TIA
Dirk
Frank Langelage schrieb:
> Hi,
>
> If you are doing this by an by an application you could try using a cursor.
> You only have to fetch one time. If at least one row matching exists you
> get SQLCODE = 0 else you get 100.
> We are using this in our application to test the existence of data.
>
> Greetings
> Frank
>
> Dirk Niemeier schrieb:
>
> > Hi,
> > what is the best way to test if an duplicate value exist ?
> > Example: A indexed table column char(3) with 4 different entries. But
> > this 4 entries are duplicate average 1000 times.
> >
> > Now I tried to test like this:
> >
> > select distinct test_column from test_table where <column_test> = 'XXX';> >
> > That works fine, but it's very slow when the entry exists. Isn't there a
> > better way ?
> >
> > TIA
> > Dirk
You could try the following :
select 1 from systables
where tabid = 1
and exists (
select 1 from test_table
where test_column = 'XXX');
Returns 1 if value exists, 0 rows for non-exist.
Rudy
Dirk Niemeier wrote:
> Hi,
> what is the best way to test if an duplicate value exist ?
> Example: A indexed table column char(3) with 4 different entries. But
> this 4 entries are duplicate average 1000 times.
>
> Now I tried to test like this:
>
> select distinct test_column from test_table where <column_test> = 'XXX';>
> That works fine, but it's very slow when the entry exists. Isn't there a
> better way ?
>
> TIA
> Dirk
Hi Rudy,
thank you, that's a nice solution. It works very fine.
Dirk
Rudy Fernandes schrieb:
> You could try the following :
>
> select 1 from systables
> where tabid = 1
> and exists (
> select 1 from test_table
> where test_column = 'XXX');>
> Returns 1 if value exists, 0 rows for non-exist.
>
> Rudy
>
> Dirk Niemeier wrote:
>
> > Hi,
> > what is the best way to test if an duplicate value exist ?
> > Example: A indexed table column char(3) with 4 different entries. But
> > this 4 entries are duplicate average 1000 times.
> >
> > Now I tried to test like this:
> >
> > select distinct test_column from test_table where <column_test> = 'XXX';> >
> > That works fine, but it's very slow when the entry exists. Isn't there a
> > better way ?
> >
> > TIA
> > Dirk