Existence of a value - performance efficient in SQL

Question

Well I was asked a question yesterday and it is as follows:

"Which is the most efficienmt way to find out the existence of a certain value in a database table?"

Retreival of data is not necessary but just to find out if there is a value X existing in the database table.

For example:

 ROW_ID      ARTIKEL      SUPPLIERID    ORGID      PIECES      COSTPRICE      DISCOUNT    VALUE_DRILL_DOWN
1           TV            SONY          922         6            110           2.5         14
2           Radio         SONY          922         10           80            1            4
3           Computer      SONY          922         6            65            1.5          0
4           Laptop        SONY          922         14           95            1.5          0
5           Mobile        SONY          922         18           95            1.5          0
6           Playstation   SONY          922         4            95            1.5          0

Now if I have to find the existence of a record with "Radio" in the above table:

SELECT * from EXAMPLE_TABLE where ARTIKEL='Radio';
OR
Select "ARTIKEL" from EXAMPLE_TABLE where ARTIKEL='Radio';
OR
Select COUNT(*) from EXAMPLE_TABLE where ARTIKEL='Radio';

I would say there are three possibilities according to me to find the existence of a value "RADIO" in the table. The performance efficient query would be the second query. Is there any better way I could do that or any function to achieve this? Any suggestions?

Thanks in advance

@CJBS Anything for the matter. Just wanted to know most performant efficient way to do it. — Sangamesh Hs, Feb 12 '14 at 08:20
@AnthonyAccioly Oh I thought these simple SQL queries would be the same for all DBMS. I am currently using HANA database. — Sangamesh Hs, Feb 12 '14 at 08:24

score 3 · Accepted Answer · answered Feb 12 '14 at 08:08

3

Look at an explain plan to know with certainty. I suspect you'll see identical performance.

Try this idea for an improvement:

select 'found it' from EXAMPLE_TABLE where ARTIKEL='Radio'
limit 1;

answered Feb 12 '14 at 08:08

mdahlman

9,204
4
44
72

I find an identical performance with and without ´LIMIT 1´. Do you think these are the only ways to figure out a value in a database table? – Sangamesh Hs Feb 12 '14 at 08:12
@SangameshHs I'm assuming that you're testing this with more than the 6 example records above...? – CJBS Feb 12 '14 at 08:50
@CJBS absolutely. That was just an example table to make my question more clear for one to understand :) – Sangamesh Hs Feb 12 '14 at 08:52

score 2 · Answer 2 · edited May 23 '17 at 11:49

2

For MySQL go with:

SELECT EXISTS(SELECT 1 FROM EXAMPLE_TABLE WHERE ARTIKEL = 'Radio' LIMIT 1);

Also make sure that you have proper indexes:

CREATE INDEX ET_ARTIKEL_IDX ON EXAMPLE_TABLE(ARTIKEL);

Resource: Best way to test if a row exists in a MySQL table

While I'm not confident to talk about SQL Server, this blog post also suggests that EXISTS is the way to go.

Update: OP @CJBS updated the question removing the MySQL and SQL Server tags, but I will keep the answer here for further reference.

edited May 23 '17 at 11:49

Community

1
1

answered Feb 12 '14 at 08:14

Anthony Accioly

21,918
9
70
118

Actually I removed the tags, and added hana DBMS, as OP indicated that's what he was using. See OP comment above: " Oh I thought these simple SQL queries would be the same for all DBMS. I am currently using HANA database". You still got my upvote! – CJBS Feb 12 '14 at 08:35
@Anthony thanks for your effort. I really appreciate. – Sangamesh Hs Feb 12 '14 at 08:53
Fixed the answer comment hehehe. Sangamesh, no problem :). – Anthony Accioly Feb 12 '14 at 13:07

Existence of a value - performance efficient in SQL

2 Answers2