ADO.NET 查询优化
我遇到了一件奇怪的事情。 我在 C# ADO.NET 应用程序上运行探查器时得到的第一个查询。 它所做的并不像传递参数的方式那么有趣 - 这个查询需要 250 多秒才能完成，但是当我通过更改参数传递方式（参见查询 2）来修改它（查询 1）时，那么完成需要<0.001秒。
这里到底发生了什么？ 我们可以做些什么来强制 ADO.NET 生成更优化的查询吗？
-----------------------------------------------------------------------
-- Query 1
-----------------------------------------------------------------------
exec sp_executesql N'
SELECT
*
FROM
BSM.Instruments
WHERE
DataBlockId=@0 AND
InstrumentId IN (
SELECT
DISTINCT InstrumentId
FROM
BSM.InstrumentPositions
WHERE
DataBlockId=@0 AND
PortfolioId IN (
SELECT
DISTINCT PortfolioId
FROM
BSM.PortfolioLeaves(@1,@2)
)
)'
,N'@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier'
,@0='B1C69A98-50D3-11DE-A173-00155DE1F500'
,@1='ABADF5F7-50D3-11DE-A173-00155DE1F500'
,@2='ABADFF41-50D3-11DE-A173-00155DE1F500'
-----------------------------------------------------------------------
-- Query 2
-----------------------------------------------------------------------
declare @0 uniqueidentifier = 'B1C69A98-50D3-11DE-A173-00155DE1F500'
declare @1 uniqueidentifier = 'ABADF5F7-50D3-11DE-A173-00155DE1F500'
declare @2 uniqueidentifier = 'ABADFF41-50D3-11DE-A173-00155DE1F500'
exec sp_executesql N'
SELECT
*
FROM
BSM.Instruments
WHERE
DataBlockId=@0 AND
InstrumentId IN (
SELECT
DISTINCT InstrumentId
FROM
BSM.InstrumentPositions
WHERE
DataBlockId=@0 AND
PortfolioId IN (
SELECT
DISTINCT PortfolioId
FROM
BSM.PortfolioLeaves(@1,@2)
)
)
'
,N'@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier'
,@0
,@1
,@2
I have a bit of a strange one happening. The first query I got from running a profiler on a C# ADO.NET application. What it is doing is not as interesting as the way the parameters are being passed - This query is taking 250+ seconds to complete, but when I modify it (Query 1) by changing the way the parameters are passed (see Query 2), then it takes <0.001 seconds to complete.
What is at play here? Is there anything we can do to force ADO.NET to generate a more optimal query?
-----------------------------------------------------------------------
-- Query 1
-----------------------------------------------------------------------
exec sp_executesql N'
SELECT
*
FROM
BSM.Instruments
WHERE
DataBlockId=@0 AND
InstrumentId IN (
SELECT
DISTINCT InstrumentId
FROM
BSM.InstrumentPositions
WHERE
DataBlockId=@0 AND
PortfolioId IN (
SELECT
DISTINCT PortfolioId
FROM
BSM.PortfolioLeaves(@1,@2)
)
)'
,N'@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier'
,@0='B1C69A98-50D3-11DE-A173-00155DE1F500'
,@1='ABADF5F7-50D3-11DE-A173-00155DE1F500'
,@2='ABADFF41-50D3-11DE-A173-00155DE1F500'
-----------------------------------------------------------------------
-- Query 2
-----------------------------------------------------------------------
declare @0 uniqueidentifier = 'B1C69A98-50D3-11DE-A173-00155DE1F500'
declare @1 uniqueidentifier = 'ABADF5F7-50D3-11DE-A173-00155DE1F500'
declare @2 uniqueidentifier = 'ABADFF41-50D3-11DE-A173-00155DE1F500'
exec sp_executesql N'
SELECT
*
FROM
BSM.Instruments
WHERE
DataBlockId=@0 AND
InstrumentId IN (
SELECT
DISTINCT InstrumentId
FROM
BSM.InstrumentPositions
WHERE
DataBlockId=@0 AND
PortfolioId IN (
SELECT
DISTINCT PortfolioId
FROM
BSM.PortfolioLeaves(@1,@2)
)
)
'
,N'@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier'
,@0
,@1
,@2
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您还应该删除 SELECT * 并将其替换为 SELECT 和列定义。 另外，在查询数据时，使用 GUID 会比使用 int 慢。
You should also get rid of the SELECT * and replace it with SELECT and the column definitions. Also using a GUID will be slower than using an int when querying data.
保证最佳性能的最佳方法是将查询放入存储过程中。 然后使用查询分析器分析查询计划并验证 SQL Server 优化器是否使用有意义的索引（而不是表扫描）来检索数据。
希望这有帮助，
比尔
The best way to guarantee optimal performance is to put your query into a stored procedure. Then use query analyzer to analyze the query plan and verify that the SQL Server optimizer is using meaningful indexes (as opposed to table scans) to retrieve the data.
Hope this helps,
Bill