SQL Server查询性能 [英] SQL Server Query Performance

问题描述

我在一个表上有一个简单的查询

该表包含10M +行

查询在文件加载过程中运行并运行数千次

该表在相关字段中编入索引

执行大约需要20毫秒。它并不多，但是当你运行它500,000次时它会加起来

除了在客户端缓存整个表并在内存中管理它之外，还有什么方法可以减少它吗？ />

I have a simple query on a single table
The table contains 10M+ rows
The query in ran during a file loading process and is run hunderd of thousands of times
The table is indexed on the relevant fields
It takes around 20msec's to execute. It ain't much, but when you run it 500,000 times it adds up
Is there any way to reduce it, aside from caching the entire table on the client side and managing it in memory?

SELECT * FROM MyTable WITH (NOLOCK) WHERE  EntryID !=75375653 AND IsRemoved=0 AND CompanyID=4986771 AND Reference=9790490 AND PaymentNumber=6 AND Token='2342342342343' AND (VoucherID=892124 OR ExternalTransactionID='10978186298')

执行计划只显示索引搜索单个索引（79％）+嵌套循环内连接（21％）

Execution plan just shows Index seek on the single index(79%) + nested loops inner join(21%)

推荐答案

在客户端缓存它绝对无济于事。数据库引擎应该更有效地处理数据。我建议使用调优顾问（如果您使用的是sql server或oracle）。其他数据库引擎有相似的工具。数据库引擎具有有关查询的统计信息（以及更多信息），并且可以提供有关缺少真正有助于提高性能的索引的建议它比执行计划更有用。



祝你好运！

Caching it on the client absolutely won't help. The database engine should be more efficient at handling the data. I would suggest using the tuning advisor (if your using sql server or oracle). Other database engines have simmilar tools. The database engine has statistics about the queries (and more) and can advise about missing indexes that would really help performance. It is more usefull than just the execution plan.

Good luck!

这篇关于SQL Server查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！