February 26, 2003 at 9:09 am
Is there a way to force the optimiser to filter the 'where' clause before performing any joins?
Regards,
Andy Jones
.
February 26, 2003 at 5:31 pm
Have you tried putting the conditional statement in the where clause into the join statement? This can greatly increase the efficency of the query in many cases.
For example, let's suppose you have the following query:
Select *
From Table1 As T1 with(nolock)
Inner Join Table2 As T2 with(nolock) On T1.IDField = T2.IDField
Where T1.OtherField = 'Some Value'
If the where clause eliminates a great deal of rows from the results, you will see a noticeable difference if you write the query like this:
Select *
From Table1 As T1 with(nolock)
Inner Join Table2 As T2 with(nolock) On T1.IDField = T2.IDField And T1.OtherField = 'Some Value'
February 27, 2003 at 5:17 am
However, the query engine makes some odd decisions sometimes.
Might try a subquery for Table1 thus forcing the query to handle the WHERE condition for the subquery first before the join.
But the option Rawhide points out may run faster.
Is there a specific scenario you can give us?
February 27, 2003 at 5:34 am
Sure, here is a simplified version.
I am loading data from a flat file into a holding table (T1), each record is identifiable by the code in the first field e.g.
T1
C1(varchar(3))C2 (varchar(3))
A1
A2
Bxyz
I want to join this table to another table (T2) in a query .
T2
F1 (int)
1
2
Before running the following query I have validated that for records in T1 that have C1 = 'A' all values in C2 are integers.
select
T1.C1
from
T1
join T2 on T1.C2 = T2.F1
where
T1.C1 = 'A'
This gives an error saying 'Cannot convert 'xyz' to an integer'.
My problem is this error happens on one server and not another. I presume this is to do with volume of data and in one case the optimser is performing
the join before the where filter and on the other server is performing the where filter first.
I could change the join to [join T2 on T1.C2 = convert(varchar(3),T2.F1)] but this causes an index scan instead of a seek, so I wanted a way to force the query
to always perform the where filter first.
Regards,
Andy Jones
.
February 27, 2003 at 5:57 am
So really your issue is datatyping problems. I believ your other server may be implicitly converting the int to varchar but the server in question isn't so it gives the error.
So really you have two options to content with.
Either this which validats and sets value to INT type in Table 1 when is a numeric value
select
T1.C1
from
T1
join T2
on cast((CASE ISNUMERIC(T1.C2) WHEN 1 THEN T1.C2 ELSE NULL END) as int) = T2.F1
where
T1.C1 = 'A'
or this whch cast data from Tabl2 to varchar
select
T1.C1
from
T1
join T2
on T1.C2 = CAST(T2.F1 as varchar(3))
where
T1.C1 = 'A'
both explicitly cats the data to common types.
You should always be explicit to ensure changes in future versions of SQL do no effect your code.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply