Query in a loop slows down over time

[realtytrends]

I have tried to figure it out for a whole day but have no clue. Really hope can get some help here. The following is the code that I am having trouble with. ZoomedPixelCoordinateSet is the representation of a stored procedure. The query version of this just dog slow so I made a stored procedure. I am pulling 50 records each time.

public List GetZoomedTileCoordinates(
short zoom, int skipNum, int takeNum)
{
List list = new List();
try
{
using (RTMySQLInternetEntities ircontext = new RTMySQLInternetEntities())
{
ircontext.CommandTimeout = 300;
var zoomedList = (from z in ircontext.ZoomedPixelCoordinateSet
where z.Zoom == zoom
orderby z.GTileY
select new { z.Zoom, z.GTileX, z.GTileY }).Distinct().OrderBy(o => o.GTileY).ThenBy(o => o.GTileX).Skip(skipNum).Take(takeNum);
foreach (var i in zoomedList)
{
ZoomedTileCoordinates ztc = new ZoomedTileCoordinates();
ztc.Zoom = i.Zoom;
ztc.TileX = i.GTileX;
ztc.TileY = i.GTileY;
list.Add(ztc);
}
}
}
catch (Exception ex)
{
string str = ex.Message;
}

return list;
}
It starts fast. But after a while the eval to zoomedList become very slow. It took about minute and half to come back. Any idea?

Thanks so much!

QZ

[AndreyR]

The reason is the MySQL server LIMIT behaviour. Take a look at this thread on MySQL forums, for example.