replacing of semi-joins

  • Thread starter Thread starter Leonard Poon
  • Start date Start date
L

Leonard Poon

I want to ask if there is any alternatives by using only the "WHERE" clause
that produce exactly the same result as semi-join of SQL. I found the
overhead is too much of using the semi-join.
The execution time for a query that only uses the "inner joins" is much much
faster.

Is there any expression that can replace the semi-joins?

Leonard
 
Assuming you have a query that looks something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
EXISTS
(SELECT
[Your Other Table].*
FROM
[Your Other Table]
WHERE
[Your Other Table].[Your Field] = [Your Table].[Your Field])

you might try one of the following:

1. Equivalent query using "In" operator

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Your Field]
In
(SELECT
[Your Other Table].[Your Field]
FROM
[Your Other Table])

2. Equivalent query using inner join with distinct subquery

SELECT
[Your Table].*
FROM
[Your Table]
INNER JOIN
(SELECT DISTINCT
[Your Other Table].[Your Field]
FROM
[Your Other Table]) AS [Subquery]
ON
[Your Table].[Your Field] = [Subquery].[Your Field]

In might help in all three cases to index "Your Field" in both tables.
 
Thanks,

This is another example of my query that is to be optimized.

SELECT *
FROM [Table A] INNER JOIN [Table B] ON [Table A].ID=[Table B].ID
INNER JOIN ...
INNER JOIN [Table n] ON [Table n].ID=[Table m].ID
LEFT OUTER JOIN [Table O] ON [Table n].ID=[Table O].ID
WHERE ...

As you can see, there's a left-join in the query. If I skip off the
left-join table as a test for its execution time, it only takes 3-4 seconds.
However, as I left-joined the [Table 0], it requires 1 minute to have the
result. I've already used the indexes for tables, but it seems not helping
much. I've also tried breaking it into subqueries and nested query or
whatever. I know the time taken for running a query is highly depends on the
number of joined rows and disk i/o , etc factors.

But, How can I make it faster? I'm dying for a solution.

Leonard
 
What's the cardinality of the relationship between Table N and Table O? If
it's 1:1, and you only need one field from Table O, then you might try a
scalar subquery, as in something like:

SELECT
*,
(SELECT
[Table O].[Your Field]
FROM
[Table O]
WHERE
[Table O].ID = [Table N].ID) AS [Your Field]
FROM
[Table A]
INNER JOIN
[Table B]
ON
[Table A].ID=[Table B].ID
INNER JOIN
....
INNER JOIN
[Table N]
ON
[Table N].ID=[Table M].ID
WHERE
....

What conditions (if any) does your WHERE clause place on fields from Table
O?
 
Thanks for your suggestion.

Here is the relationship:

[Table N] 1---(0..n) [Table O]

In fact, all the other tables in the query are 1 to Many. Your suggested
query may not be applicable to me.

As for the WHERE clause, there is nothing for [Table O].

Leonard
 
I'm not sure what else to suggest.

You might try encouraging the outer join to be done first by using and
inline view something like this

SELECT
*
FROM
[Table A]
INNER JOIN
[Table B]
ON
[Table A].ID=[Table B].ID
INNER JOIN
....
INNER JOIN
(SELECT
*
FROM
[Table N]
LEFT JOIN
[Table O]
ON
[Table N].ID=[Table O].ID) AS [Inline View]
ON
[Table M].ID = [Inline View].[Table N].ID
WHERE
....

but I don't know if that will help.

If you're using MSDE/SQL Server (as it appears), you might try generating
some execution plans to figure out how the query is being executed, and
rewrite or hint it to improve things. You might get other suggestions by
posting your question to one of the SQL Server forums.

If you really are using Jet, there are ways to get at query execution plans
too. A search on

Jet ShowPlan

should turn up some information on how to do this.
 
Back
Top