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.
我有一个包含800万条记录的表,包含很多字段,包括lat / long值,并且它有一个Lat / Long字段的索引。
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).
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.
在我的开发机器中,它使用索引,并在大约50ms内返回(我第一次查询特定点)。在我的生产服务器中,它也使用索引,但第一个查询返回大约需要2-4秒,后续查询大约需要10毫秒。
In my dev machine, SQL Server is taking 500Mb of memory, in my server, about 130Mb.
在我的开发机器中,SQL Server在我的服务器中占用了大约130Mb的500Mb内存。
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.
这是两台机器上W2k3上的SQL Express 2005(免费版)。我能想到的一个区别是我的机器是32位,服务器是64,但我不认为这会如何影响内存中索引的加载。
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.
此外,服务器在内存中运行不足。它有2Gb物理内存,大约500Mb的提交费用,所以有很多空闲。
Any ideas will be greatly appreciated! Thanks!
任何想法将不胜感激!谢谢!
1 个解决方案
#1
2
When I encounter this situation there are usually two things I try, in the following order:
当我遇到这种情况时,通常会尝试两件事,按以下顺序排列:
1 - update statistics on that table:
1 - 更新该表的统计信息:
UPDATE STATISTICS Data
更新统计数据
2 - Rebuild the index: (right-click on the index in SQL Server Management Studio and select Rebuild)
2 - 重建索引:(右键单击SQL Server Management Studio中的索引并选择Rebuild)
#1
2
When I encounter this situation there are usually two things I try, in the following order:
当我遇到这种情况时,通常会尝试两件事,按以下顺序排列:
1 - update statistics on that table:
1 - 更新该表的统计信息:
UPDATE STATISTICS Data
更新统计数据
2 - Rebuild the index: (right-click on the index in SQL Server Management Studio and select Rebuild)
2 - 重建索引:(右键单击SQL Server Management Studio中的索引并选择Rebuild)