self join performance issue? Or large# of return rows?

[realtytrends]

Hi,
First all, let me just say thanks for whoever are willing to help. I am getting close to finish a big project but this is a road block right now. I am sure there are some people out there can point me to the right direction and a solution to my problem.

I am porting mssql to mysql and the applications is done through entity framework. And it worked fine under mssql but under mysql it timed out. The following is the linq:


(from mp in ircontext.ZoomedPixelCoordinateSet
join c in
(
from zp in ircontext.ZoomedPixelCoordinateSet
where zp.GTileX == gTile.X && zp.GTileY == gTile.Y && zp.Zoom == PointInPolygonZoom
select new { zp.StateCode, zp.NeighborhoodID, zp.Zoom }
).Distinct()
on new { StateCode = mp.StateCode, NeighborhoodID = mp.NeighborhoodID, Zoom = mp.Zoom }
equals
new { StateCode = c.StateCode, NeighborhoodID = c.NeighborhoodID, Zoom = c.Zoom }
orderby mp.StateCode, mp.NeighborhoodID, mp.SortOrder, mp.OriginalSortOrder
select new {
StateCode = mp.StateCode,
NeighborhoodID = mp.NeighborhoodID,
TileX = mp.GTileX,
TileY = mp.GTileY,
X = mp.GX,
Y = mp.GY,
SortOrder = mp.SortOrder
}
).ToList();

thanks
Qin

[AndreyR]

I recommend you to profile the SQL query sent to the server.
To get this query you can use either DBMonitor or the ObjectQuery.ToTraceString() method.
Try to execute this query on the server, this will show if the problem is associated with it.