SQL Server 游标索引
我有一个应用程序有时运行缓慢，我认为这与数据库游标有关。
没有对应用程序源的任何访问权限，因此我无法控制数据库调用，但数据库是打开的，因此我可以在需要时添加索引。
问题是我不知道如何用索引加速游标。
游标查询很简单，看起来像
SELECT * FROM table WHERE field1=1 AND field2=2 ORDER BY field3, field4
（表包含大约 1M 行。有时也有一个左连接）
如果我直接在 SSMS 中运行查询，则需要不到一秒的时间，但是当它从应用程序中运行时游标可能需要 30 秒才能获取第一行（使用 sql-trace 验证）。
WHERE 和 ORDER BY 子句中的字段都单独建立索引。
我想 field1、field2、field3、field4 上的组合索引会使速度更快。有没有一种方法可以加快速度，而无需为每个字段组合和顺序创建索引？
（重复一遍：我对应用程序如何访问数据库没有影响。性能只能通过索引来调整）
I have an application that sometimes runs slow and I think it is related to database cursors.
Don't have any access to the application source so I can't control the database calls but the database is open so I can add index where needed.
Problem is that I don't know really know how to speed up cursors with index.
The cursor queries are simple and look like
SELECT * FROM table WHERE field1=1 AND field2=2 ORDER BY field3, field4
(Table contains about a 1M rows. Sometimes there is a single left join as well)
If I run the query directly in SSMS it takes less than a second but when it is run from the application in a cursor it can take 30 seconds to fetch the first row (verified with sql-trace).
The fields in WHERE and ORDER BY clauses are all indexed separately.
I guess a combined index on field1,field2,field3,field4 would make it faster. Is there a way to speed it up without creating an index for each combination and order of fields?
(Just to repeat: I have no influence on how the application accesses the database. Performance can only be tuned via index)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我经常做的一件事（如果可能的话）是运行 DB Tuning Advisor。
不要误会我的意思 - 我并不遵循他的所有规则和建议，但这是查看正在发生的事情、发生的频率等的简单方法。
几个小时的（典型！！！）工作量有助于获得一些基本的“感觉”正在发生的事情。
之后您可以决定是否实施某些建议。即使你在设计上尽了最大努力 - 这样的检查看起来到底发生了什么（并不总是可预测的），也许你忘记了一些统计数据或不同的索引可以帮助......
One thing I always do (if possible) I run the DB Tuning Advisor.
Don't get me wrong - I don't follow all his rules and suggestions, but it is an easy way to see what's going on, how often what occures and so on.
Some hours of (typical!!!) workload are good to get some basic "feeling" what's going on.
And after it you can decide to implement some of the suggestions or not. And even if you did your best in design - such a check looks what's really going on (not always predictable) and maybe you forget some statistics or a different index could help...
我将更改查询以使用实际列名而不是 SELECT *，然后在 field1=1 和 field2=2 上创建覆盖索引。如果可能的话，我会在 field3 和 field4 上放置聚集索引。
如果您使用的是 SQL 2005+，请尝试查看 CTE 而不是游标，或者重构您的查询以使用临时表。
I'd change the query to use actual column names and not SELECT *, and then create a covering index on field1=1 and field2=2. If possible, I'd put a clustered index on field3 and field4.
If you're on SQL 2005+, try looking at CTE instead of cursor, or refactor your query to use temp tables.