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!
SSIS Package Config File Encryption
How to clean sys.conversation_endpoints
Searching SQL Server
1 - update statistics on that table:.
ACT Professional for Windows-Memory leak?
UPDATE STATISTICS Data .
iterating XML data in sql server 2005
2 - Rebuild the index: (right-click on the index in SQL Server Management Studio and select Rebuild).
use column alias in other calculation in sql server 2005
How can I display two rows worth of data on one line side-by-side in Report Designer?