Help with this query. How can I optimize it

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with this query. How can I optimize it

Author

Topic

Staryon
Starting Member

10 Posts

Posted - 2003-01-10 : 16:22:17

Hi!

Here it goes my first post for the forum! Unfortunately is a problem that I have with a query :(

I have the following table:

FK_ID DATA
1457 Spain
1458 England
1457 France
1457 Spain
1460 Portugal
1457 Germany
1458 Spain
1460 France

I need to retrieve that FK_ID which have the DATA='Spain' AND DATA='France'. For this example, we'll get the FK_ID = 1457

The ugly solution is this one:
select distinct FK_ID from MyTable where FK_ID in
( select FK_ID from MyTable where (Data='Spain') AND FK_ID in
( select FK_ID from MyTable where (Data='France')
)
)

Do you guys how can I build a more optimized query that makes the same?

Thanks a lot

Edited by - Staryon on 01/10/2003 16:24:35

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-10 : 16:42:20

This might be a little faster:

SELECT A.FK_ID
FROM myTable A INNER JOIN myTable B
ON A.FK_ID=B.FK_ID
WHERE A.Data='Spain' AND B.Data='France'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-01-10 : 17:02:14

Not only is Robvolk's query probably faster, but it is also easier to read.

Staryon
Starting Member

10 Posts

Posted - 2003-01-10 : 17:48:59

Thanks for your answer, robvolk

My only concern is that I may have more than two conditions, therefore I could have many INNERJOINs ( or many SELECT ... with my solution ) for the same table, but I suppose that maybe there is not a better way to do this.

Thanks a lot, I'll use it with my code.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-10 : 18:08:15

This might help with multiple conditions:

SELECT FK_ID
FROM myTable
WHERE Data IN('France','Spain')
GROUP BY FK_ID
HAVING Count(DISTINCT Data)=2

So if you wanted to add England:

SELECT FK_ID
FROM myTable
WHERE Data IN('France','Spain','England')
GROUP BY FK_ID
HAVING Count(DISTINCT Data)=3

It's a little more flexible, but the only way to truly make it automatic is to use two tables; the original and another containing only the values being searched for:

CREATE TABLE #Countries (Data varchar(20) NOT NULL PRIMARY KEY)
INSERT INTO #Countries VALUES ('Spain')
INSERT INTO #Countries VALUES ('France')
INSERT INTO #Countries VALUES ('England')

SELECT A.FK_ID
FROM myTable A INNER JOIN #Countries C
ON A.Data=C.Data
GROUP BY A.FK_ID
HAVING Count(DISTINCT A.Data)=(SELECT Count(DISTINCT Data) FROM #Countries)

You'd have to weigh the effort in creating and populating the temp table with the values, against the (minimal) rewriting needed to accommodate more or fewer search items. I'm also inclined to think that the GROUP BY...HAVING technique may not perform as well as the self-join, unless you'll be searching for 5 or more items most of the time. It depends on how the table is indexed and how the data is distributed.

Edited by - robvolk on 01/10/2003 18:11:06

Staryon
Starting Member

10 Posts

Posted - 2003-01-10 : 18:35:37

Thanks a lot for that wonderful explanation, robvolk
I will consider to use one of those examples.

The only problem with your second option, is that I don't know a priori what countries (it was an example) I am going to use, so I cannot populate that table. I'll keep in mind that solution for the future.

Take care

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources