Join takes too long to execute

This join takes 6 seconds to execute, and that's too long. There are several
hundred thousand records in all tables with and index on the ID field of eac
h
table. The plan is to have this in a SP with clients calling it repeatedly s
o
it needs to be fast.
I'm wondering if i can permanently related these tables outside of the SP?
Then the join wouldn't be necessary and i could just do a SELECT
..where...etc?
select distinct m.* from main m
left join sym2 s on s.id = m.id
left join cat2 c on c.id = m.id
left join servid2 i on i.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
or i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
Thanks,
Don
SQL 2000Hi
You may want to look at the query plan for this, it may help if you use
dynamic SQL or a union.
e.g. (untested!)
select m.*
from main m
join sym2 s on s.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
UNION
select m.*
from main m
join cat2 c on c.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
select m.*
from main m
join servid2 i on i.id = m.id
where i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
For production code you should not use SELECT *
John
"DonSQL2222" wrote:

> This join takes 6 seconds to execute, and that's too long. There are sever
al
> hundred thousand records in all tables with and index on the ID field of e
ach
> table. The plan is to have this in a SP with clients calling it repeatedly
so
> it needs to be fast.
> I'm wondering if i can permanently related these tables outside of the SP?
> Then the join wouldn't be necessary and i could just do a SELECT
> ..where...etc?
> select distinct m.* from main m
> left join sym2 s on s.id = m.id
> left join cat2 c on c.id = m.id
> left join servid2 i on i.id = m.id
> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
> or i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
> Thanks,
> Don
> SQL 2000|||Don,
Does this query really execute? I always thought you could only call
UDF's (in this case iter_charlist_to_table) if you mention the UDF
owner.
How many rows does the table valued UDF return in these three cases? If
it returns more than say 1000 rows, then you should try to remove it.
You only select rows from table main. If main is a regular table with a
primary key, then you can move the joins to EXISTS clauses, remove the
DISTINCT keyword, and change the outer joins to inner joins.
For example:
SELECT *
FROM main m
WHERE EXISTS (
SELECT 1
FROM sym2 s
WHERE s.id=m.id
AND s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT)
)
) OR EXISTS (
SELECT 1
FROM cat2 c
WHERE c.id=m.id
AND c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT)
)
) OR EXISTS (
SELECT 1
FROM servid2 i
WHERE i.id = m.id
AND i.servid in (select str from iter_charlist_to_table(@.msid,
DEFAULT))
)
If the table valued UDF is in fact the main problem, then you could
consider rewriting the query to this:
SELECT DISTINCT *
FROM main m
INNER JOIN (
SELECT id FROM sym2 s
WHERE s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT)
)
UNION ALL
SELECT id FROM cat2 c
WHERE c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT)
)
UNION ALL
SELECT id FROM servid2 i
WHERE i.servid in (select str from iter_charlist_to_table(@.msid,
DEFAULT))
) AS T1 ON T1.id = m.id
Hope this helps,
Gert-Jan
DonSQL2222 wrote:
> This join takes 6 seconds to execute, and that's too long. There are sever
al
> hundred thousand records in all tables with and index on the ID field of e
ach
> table. The plan is to have this in a SP with clients calling it repeatedly
so
> it needs to be fast.
> I'm wondering if i can permanently related these tables outside of the SP?
> Then the join wouldn't be necessary and i could just do a SELECT
> ..where...etc?
> select distinct m.* from main m
> left join sym2 s on s.id = m.id
> left join cat2 c on c.id = m.id
> left join servid2 i on i.id = m.id
> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFA
ULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT
) )
> or i.servid in (select str from iter_charlist_to_table(@.msid, DEFA
ULT))
> Thanks,
> Don
> SQL 2000