How to force Query Optimizer to do what I want ?

I'm really going nuts. I have the following two tables:
Table1: 20 Mio Records, 2 Fields
Table2: 600'000 Records, 25 Fields
and the following query:
SELECT Fields FROM Table1 t1, Table2 t2
WHERE t1.id = t2.id AND t1.Searchword = 'productdesc' AND t2.status = 12 AND
t2.category <> 3
ORDER BY field1, field2, field3
The Query optimizer decides to filter (and sort!!) table 2 first and then
join it to table 1. this takes up to 10 minutes. If it would go the other wa
y
round and first query table 1 using the Searchword criteria and then joint
the result set to table2, the query would take maybe 1-2 seconds. I updated
statistics, all indices are there. What the hell is going on ?
BTW: If I remove the ORDER BY, it takes just 1 seconds two. Very strange...
HOW can I force the query optimizer to do it right ? I tried FORCEPLAN ON,
that didn't help. I assume since table 1 has so many rows and just 2 fields,
it decides it has very poor selectivity (which is actually wrong) and uses
the smaller table 2 instead. I tried JOIN ON, doesn't help.
Any help is greatly appreciated !
mbrtal. Enterprise Windows Application Development.Please be more specific about your problem. Give the _exact_ query (for this
kind of performance trouble shooting it is essential to know which columns
you select and order by. "Fields" and "field1, field2, field3" just doesn't
convey that kind of information) and give the DDL for you tables _and_
indexes. (See www.aspfaq.com/5006)
Jacco Schalkwijk
SQL Server MVP
"mbrtal" <mbrtal@.discussions.microsoft.com> wrote in message
news:20ACC629-4052-45E9-8C00-59A45CC1C226@.microsoft.com...
> I'm really going nuts. I have the following two tables:
> Table1: 20 Mio Records, 2 Fields
> Table2: 600'000 Records, 25 Fields
> and the following query:
> SELECT Fields FROM Table1 t1, Table2 t2
> WHERE t1.id = t2.id AND t1.Searchword = 'productdesc' AND t2.status = 12
> AND
> t2.category <> 3
> ORDER BY field1, field2, field3
> The Query optimizer decides to filter (and sort!!) table 2 first and then
> join it to table 1. this takes up to 10 minutes. If it would go the other
> way
> round and first query table 1 using the Searchword criteria and then joint
> the result set to table2, the query would take maybe 1-2 seconds. I
> updated
> statistics, all indices are there. What the hell is going on ?
> BTW: If I remove the ORDER BY, it takes just 1 seconds two. Very
> strange...
> HOW can I force the query optimizer to do it right ? I tried FORCEPLAN ON,
> that didn't help. I assume since table 1 has so many rows and just 2
> fields,
> it decides it has very poor selectivity (which is actually wrong) and uses
> the smaller table 2 instead. I tried JOIN ON, doesn't help.
> Any help is greatly appreciated !
> --
> mbrtal. Enterprise Windows Application Development.
>|||mbrtal,
You say about Table1 that "it [sql-server] decides it has very poor
selectivity (which is actually wrong)". How do you know that? If the
table has high selectivity and you have updated the statistics WITH
FULL_SCAN, then SQL-Server will judge the selectivity correctly. Why do
you think the different access path will result in a 30,000 percent
performance gain if you haven't seen SQL-Server execute the query this
way?
If you want to test the performance and see the query plan when Table1
is accessed first, then make sure Table1 is mentioned first (which is
already the case) and add the query hint OPTION (FORCE ORDER) to the
query.
But I have to agree with Jacco. Please post the actual query and
(simplified) DDL. Some information we are missing now:
- What indexes are present, and are they clustered?
- What tables do field1, field2 and field3 originate from?
- What data type definition do the columns Table1(SearchWord),
Table2(Status) and Table2(Category) have?
- Does Table1(id) have the same data type and size as Table2(id)?
- etc. etc.
If SQL-Server does not choose the 'obvious' plan, there there is
probably a very good reason for it. The limited information you posted
will not reveal the necessary details...
Gert-Jan
mbrtal wrote:
> I'm really going nuts. I have the following two tables:
> Table1: 20 Mio Records, 2 Fields
> Table2: 600'000 Records, 25 Fields
> and the following query:
> SELECT Fields FROM Table1 t1, Table2 t2
> WHERE t1.id = t2.id AND t1.Searchword = 'productdesc' AND t2.status = 12 A
ND
> t2.category <> 3
> ORDER BY field1, field2, field3
> The Query optimizer decides to filter (and sort!!) table 2 first and then
> join it to table 1. this takes up to 10 minutes. If it would go the other
way
> round and first query table 1 using the Searchword criteria and then joint
> the result set to table2, the query would take maybe 1-2 seconds. I update
d
> statistics, all indices are there. What the hell is going on ?
> BTW: If I remove the ORDER BY, it takes just 1 seconds two. Very strange..
.
> HOW can I force the query optimizer to do it right ? I tried FORCEPLAN ON,
> that didn't help. I assume since table 1 has so many rows and just 2 field
s,
> it decides it has very poor selectivity (which is actually wrong) and uses
> the smaller table 2 instead. I tried JOIN ON, doesn't help.
> Any help is greatly appreciated !
> --
> mbrtal. Enterprise Windows Application Development.