This is a multi-part message in MIME format.
------_=_NextPart_001_01C88090.787C2278
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi,
=20
I have two tables; table1 has 3500 records (small table) and table2
(bigtable) has 25 million records.
=20
I need to Inner Join the two tables based on two columns in each table.
=20
My query reads (I have changed the literal names here for easier
reading):
=20
SELECT sm.recordid, sm.areaid, bg.distance
FROM smalltable sm=20
INNER JOIN bigtable bg ON (bg.originid=3Dsm.originid AND
bg.detinationid=3Dsm.detinationid)
WHERE sm.somecondition>0
=20
I have tried indexing the originid and destinationid columns in both
tables, and indexed the somecondition column but my query is still
running extraordinarily slow. I looked at the query plan, and it seems
that my query plan only references the index on the somecondition
column. I need to make this run much faster, any tips?
=20
Regards,
B
------_=_NextPart_001_01C88090.787C2278
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@[EMAIL PROTECTED]
Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3DEN-US link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Hi,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I have two tables; table1 has 3500 records (small =
table) and
table2 (bigtable) has 25 million records.<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I need to Inner Join the two tables based on two =
columns in
each table.<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>My query reads (I have changed the literal names here =
for
easier reading):<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>SELECT sm.recordid, sm.areaid, =
bg.distance<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>FROM smalltable sm <o:p></o:p></span></font></p>
<p class=3DMsoNormal style=3D'text-indent:.5in'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>INNER JOIN bigtable bg ON =
(bg.originid=3Dsm.originid
AND bg.detinationid=3Dsm.detinationid)<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>WHERE =
sm.somecondition>0<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I have tried indexing the originid and destinationid =
columns
in both tables, and indexed the somecondition column but my query is =
still
running extraordinarily slow. I looked at the query plan, and it =
seems that my
query plan only references the index on the somecondition column. =
I need to
make this run much faster, any tips?<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Regards,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>B<o:p></o:p></span></font></p>
</div>
</body>
</html>
------_=_NextPart_001_01C88090.787C2278--
|
