你是否有遇過相同SP在SSMS執行就是比在.NET Application快很多呢?
例如:SSMS測試約1~3秒，但在.NET Application卻要8~12秒
這篇文章我來分享這問題的我遇過最多數的主因，因為造成這問題的可能因素很多
/*
RiCo技術學院
https://bit.ly/ricoschool
Q&A-SSMS執行SP就是比.NET快
*/
select o.object_id, s.plan_handle, h.query_plan
from sys.objects o
inner join sys.dm_exec_procedure_stats s on o.object_id = s.object_id
cross apply sys.dm_exec_query_plan(s.plan_handle) h
where o.object_id = object_id('uspSalesInvoicesGet')
--4347
select * from sys.dm_exec_plan_attributes (0x05000600B1C48C7EF08C06320E00000001000000000000000000000000000000000000000000000000000000)
--251
select * from sys.dm_exec_plan_attributes (0x05000600B1C48C7E709406320E00000001000000000000000000000000000000000000000000000000000000)
-- EXEC uspSalesInvoicesGet '2013-01-18'
CREATE or alter PROCEDURE uspSalesInvoicesGet (@inval date)
AS
BEGIN
set nocount on;
SELECT [InvoiceID]
,[CustomerID]
,[BillToCustomerID]
,[OrderID]
,[InvoiceDate]
FROM [Sales].[Invoices]
where [InvoiceDate]>=@inval
END
GO
