I have a table that has 8 million records, with many fields, including lat/long values, and it has an index over the Lat/Long fields.
I'm making a query to find the records that fall within a square around a point (to later refine into a circle), which is kind of:
SELECT Lat,Long FROM Data WHERE (Lat BETWEEN 1 AND 2) AND (Long BETWEEN 1 AND 2).
In my dev machine, this uses the index, and returns in about 50ms (the first time I do a query for a specific point).
In my production server, it also uses the index, but it takes about 2-4 seconds to return for the first query, 10ms for the following ones.
In my dev machine, SQL Server is taking 500Mb of memory, in my server, about 130Mb.
To me, the obvious conclusion is that in my machine the index is loaded into memory, and in the production sever it's not...
Is my assumption correct? What can I do to fix it?
This is SQL Express 2005 (the free version) on W2k3 in both machines. The one difference I can think of is that my machine is 32bit, and the server is 64, but I don't think how this could affect the loading of the index in memory.
Also, the server is not running short in memory. It has 2Gb physical mem, and a commit charge of around 500Mb, so there's plenty to spare.
Any ideas will be greatly appreciated! Thanks!