(ds**********@gmail.com) writes:
I have one simple query joining two tables with left outer join on 3
fields and using MIN on two fields. These two tables have lot of data
about 3 mil in total. I am trying to migrate db from MS Access to SQL
2005. I get about 689000 rows in SQL Server, vs 863000 rows in MS
Access.
SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1.
[STATE]), T1.COUNT
FROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] AND
T1.[STATE] = T2.[STATE]
WHERE T1.[REASON] <'SOMETHING' AND T2.[REASON] <'SOMETHING'
GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNT
HAVING T1.[MON] <'-' AND T1.[ANUM] <'-'
I have about 30 queries to migrate and I am sort of stuck. Does any
one have any idea ?
Unfortunately, it's impossible to tell what the reason might be from
that scant amount of information. But I could offer some ideas on how
you should proceed to narrow it down.
0) Move the conditions in the HAVING clause to the WHERE clause. At
least in this particular query, there is no reason for having those
conditions in the HAVING clause.
1) Remove the GROUP BY (and MIN), and compare the number of rows.
If the number of rows now are the same(A), there are some issues
with the grouping as such. If the number of rows still are different
(and with a bigger total difference), there is an issue in
the selection (B).
2) (A) Remove columns from the GROUP BY list, and see if the difference
is due to a certain column.
3) (B) Narrow it down further by removing one of more of the conditions
from the WHERE clause (including those you moved in from HAVING).
4) (B) If that does not help play with the JOIN part.
At some point it's probably a good idea to narrow down also the dataset,
to see if there some certain data that is causing the difference. For
instance, different handling of lowercase/uppercase, accents etc.
And, oh, you said above that you were joining with outer join, but
above you have an inner join... If you use different join types in
Access and SQL Server, that could be an explanation.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx