Solución:
Tuve el mismo problema que el póster original, pero la respuesta citada no me resolvió el problema. La consulta aún se ejecutó muy lenta desde un procedimiento almacenado.
Encontré otra respuesta aquí “Parameter Sniffing”, Gracias Omnibuzz. Se reduce al uso de “Variables locales” en sus consultas de procedimiento almacenado, pero lea el original para comprenderlo mejor, es un gran artículo. p.ej
Manera lenta:
CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
SELECT *
FROM orders
WHERE customerid = @CustID
END
Manera rápida:
CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
DECLARE @LocCustID varchar(20)
SET @LocCustID = @CustID
SELECT *
FROM orders
WHERE customerid = @LocCustID
END
Espero que esto ayude a alguien más, hacer esto redujo mi tiempo de ejecución de más de 5 minutos a aproximadamente 6-7 segundos.
Encontré el problema, aquí está el script de las versiones lenta y rápida del procedimiento almacenado:
dbo.ViewOpener__RenamedForCruachan__Slow.PRC
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Slow
@SessionGUID uniqueidentifier
AS
SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
dbo.ViewOpener__RenamedForCruachan__Fast.PRC
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Fast
@SessionGUID uniqueidentifier
AS
SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Si no notó la diferencia, no lo culpo. La diferencia no está en el procedimiento almacenado en absoluto. La diferencia que convierte una consulta rápida de 0.5 costos en una que hace un spool ansioso de 6 millones de filas:
Lento: SET ANSI_NULLS OFF
Rápido: SET ANSI_NULLS ON
Esta respuesta también podría tener sentido, ya que la vista tiene una cláusula de unión que dice:
(table.column IS NOT NULL)
Entonces hay algunos NULLs involucrados.
La explicación se prueba aún más volviendo a Query Analizer y ejecutando
SET ANSI_NULLS OFF
.
DECLARE @SessionGUID uniqueidentifier
SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'
.
SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
Y la consulta es lenta.
Entonces el problema no es porque la consulta se ejecuta desde un procedimiento almacenado. El problema es que la opción predeterminada de conexión de Enterprise Manager es ANSI_NULLS off, en vez de ANSI_NULLS on, que es el valor predeterminado de QA.
Microsoft reconoce este hecho en KB296769 (ERROR: No se puede usar SQL Enterprise Manager para crear procedimientos almacenados que contienen objetos de servidor vinculados). La solución alternativa es incluir el ANSI_NULLS opción en el cuadro de diálogo del procedimiento almacenado:
Set ANSI_NULLS ON
Go
Create Proc spXXXX as
....
Haga esto para su base de datos. Tengo el mismo problema: funciona bien en una base de datos, pero cuando copio esta base de datos en otra usando la importación SSIS (no la restauración habitual), este problema ocurre con la mayoría de mis procedimientos almacenados. Entonces, después de buscar en Google un poco más, encontré el blog de Pinal Dave (que por cierto, encontré la mayoría de sus publicaciones y me ayudó mucho, así que gracias Pinal Dave).
Ejecuto la siguiente consulta en mi base de datos y corrigió mi problema:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
Espero que esto ayude. Simplemente pasando la ayuda de otros que me ayudaron.