my Store procedure spend TOO MANY TIME, why?

Hi
I have a sql that return about 35 rows
If I execute that sql in the analizer it spend 4 seconds (with
variables)
If I execute the same sql in a store procedure it spend 35 seconds
(with parameters)
The size of the variables or parameters are the same as the columns of the
tables so...
Why it can be?
This is the store procedure
CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
@.Campania int,
@.FechaDesde nchar(10),
@.FechaHasta nchar(10),
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2)
+ '-' + Left(@.FechaDesde, 2)
SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2)
+ '-' + Left(@.FechaHasta, 2)
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
-- DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <=
CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >=
CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
@.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
ORDER BY TGENERAL.PVP
END
RETURN @.@.ROWCOUNT
GORal,
First of all, you must change the name of your stored procedure.
Procedures named sp_* are treated differently by the query
processor. This name is used to indicate that there is a system
stored procedure in the master database available from all databases,
so the processor must first search the objects in master, find no
system procedure, then look for your procedure in your database.
If this doesn't solve the problem, consider adding WITH RECOMPILE
to the definition of the stored procedure. What you may be seeing is
the result of a cached plan for the stored procedure that is optimal
for one set of parameters and very bad for another. When you run
this query outside a procedure, the plan will not be cached, but
will be recompiled for the particular variable values.
If that still doesn't work, try declaring local variables for your
parameters and reassigning them inside the procedure:
...
declare @.Campania2 int,
@.FechaDesde2 nchar(10),
@.FechaHasta2 nchar(10),
@.Pais2 nchar(5),
@.Ciudad2 nchar(5),
@.Duracion2 int,
@.Precio2 int
select
@.Campania2 = @.Campania,
@.FechaDesde2 = @.FechaHasta,
..
This sometimes improves the choice of plan with or
without adding WITH RECOMPILE. In your case, it
might also be worthwhile to use local datetime variables for
the result of CONVERT(DATETIME, @.FechaHasta, 102),
CONVERT(DATETIME, @.FechaDesde, 102), and
CONVERT(DATETIME, GETDATE(), 102)
Queries this complicated with many parameters can be difficult
to optimize, particularly if the optimal query plan depends on
the particular parameter values.
Finally, I didn't look closely, but I don't quite understand the
purpose of
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
instead of just putting .Multimedias.IDtipoMultimedia = 0 into
the where clause before grouping. I'm also not sure why the
extra . before Multimedias.
Steve Kass
Drew University
Ral Martn wrote:

>Hi
> I have a sql that return about 35 rows
> If I execute that sql in the analizer it spend 4 seconds (with
>variables)
> If I execute the same sql in a store procedure it spend 35 seconds
>(with parameters)
>The size of the variables or parameters are the same as the columns of the
>tables so...
> Why it can be?
>This is the store procedure
>CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
> @.Campania int,
> @.FechaDesde nchar(10),
> @.FechaHasta nchar(10),
> @.Pais nchar(5),
> @.Ciudad nchar(5),
> @.Duracion int,
> @.Precio int
>AS
>
>DECLARE @.PrecioMin int, @.PrecioMax int
>SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2
)
>+ '-' + Left(@.FechaDesde, 2)
>SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2
)
>+ '-' + Left(@.FechaHasta, 2)
>DECLARE @.TablaAcrear varchar(2000)
>DECLARE @.STRINGINSERT varchar(4000)
>DECLARE @.STRINGadevolver varchar(1000)
>DECLARE @.NombreTablaTemporal nvarchar(250)
>BEGIN
>
> SET @.PrecioMin = 0
> SET @.PrecioMax = 999999
> IF @.Precio = 1
> BEGIN
> SET @.PrecioMin = 0
> SET @.PrecioMax = 300
> END
> ELSE IF @.Precio = 2
> BEGIN
> SET @.PrecioMin = 301
> SET @.PrecioMax = 600
> END
> ELSE IF @.Precio = 3
> BEGIN
> SET @.PrecioMin = 601
> SET @.PrecioMax = 900
> END
> ELSE IF @.Precio = 4
> BEGIN
> SET @.PrecioMin = 901
> SET @.PrecioMax = 1200
> END
> ELSE IF @.Precio = 5
> BEGIN
> SET @.PrecioMin = 1201
> SET @.PrecioMax = 999999
> END
>
> DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
>decimal(9,0),
> @.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
>@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
> DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
>@.IDduracion int
> DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
>
> set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
>int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
>decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
> exec (@.TablaAcrear)
>
> -- CIUDADES
>
> -- DECLARE Cursor1 CURSOR FOR
>
> SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
>PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
>TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
>TGENERAL.IDduracion as IDduracion,
> TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
>Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
>Multimedias.alto as alto
> FROM (
> SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
>TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
> MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
>AS Ciudad
> FROM (
> SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
>MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
> MIX.IDduracion, MIX.nombreIDPVSRC AS
>nombreIDPVSRC, MIX.nombre AS Ciudad
> FROM (
> SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
>PVSRC.IDvuelo,
> PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
>Ciudades.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
>INNER JOIN
> Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
> Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
> CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
> WHERE
> (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
>(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AN
D
>(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
>(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
>(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
>@.Campania)
> AND (Ciudades.IDCIUDAD = @.Ciudad)
> GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
> PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
> )
> MIX
> INNER JOIN
> (
> SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
>PreciosXFechasPVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <=
>CONVERT(DATETIME, @.FechaHasta, 102)) AND
> (PreciosXFechasPVSRC.fechaBaja >=
>CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
>@.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> )
> TABLAPVSRC
> ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
>TABLAPVSRC.nombre
> GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
>MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
> ORDER BY MIX.PVP
> )
> MIXG
> INNER JOIN
> (
> SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
>PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
> PVSRC.IDpaquete
> FROM PreciosXFechasPVSRC INNER JOIN
> PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
>102)) AND
> (PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
>102)) AND
> (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
>PVSRC.IDpaquete
> )
> TABLAPVP
> ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
> )
> TGENERAL
> INNER JOIN PVSRC
> ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
> INNER JOIN
> .Multimedias ON .PVSRC.IDmultimedia =
>.Multimedias.IDmultimedia
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
>TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
>Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> ORDER BY TGENERAL.PVP
>
> END
>
>RETURN @.@.ROWCOUNT
>GO
>
>|||Thanks a lot Steve but with those changes it is worse than before.
I modified de "sp_" , added WITH RECOMPILE, added local parameters and I
modified the query
at the end with
WHERE (.Multimedias.IDtipoMultimedia = 0)
in stead of
HAVING (.Multimedias.IDtipoMultimedia = 0)
but now it spend 80 seconds so if you have more ideas...
now the store procedure is:
CREATE PROCEDURE BuscardorOfertasmejorCIUDADES
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
DROP TABLE BORRAR4
SET @.NombreTablaTemporal = '_tempBusc' + LTRIM(RTRIM(@.Pais)) +
LTRIM(RTRIM(@.Ciudad)) + LTRIM(RTRIM(STR(@.Campania))) +
LTRIM(RTRIM(@.FechaDesde)) + LTRIM(RTRIM(@.FechaHasta)) +
LTRIM(RTRIM(STR(@.Precio)))
SET @.NombreTablaTemporal = LTRIM(RTRIM( replace(@.NombreTablaTemporal, '-',
'_') ))
SET @.NombreTablaTemporal = 'BORRAR4'
if NOT exists (select * from sysobjects where name = @.NombreTablaTemporal )
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.STRINGINSERT = 'INSERT INTO ' + @.NombreTablaTemporal + ' (
IDPVSRC, nombreIDPVSRC , CIUDAD, IDPVP, PVP, URLMULTIMEDIA , Ancho, Alto) '
SET @.STRINGINSERT = @.STRINGINSERT + 'VALUES (' +
RTRIM(LTRIM(STR(@.IDPVSRC))) + ', '
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNombre)) + '" ,'
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNomCiudad)) + '" , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(STR(@.IDPVP)))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.PVP))) + ' , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(@.IDPVSRCUrl))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAncho)))
+ ' , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAlto)))
SET @.STRINGINSERT = @.STRINGINSERT + ' ) '
-- @.STRINGINSERT
EXEC (@.STRINGINSERT)
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
END
CLOSE Cursor1
DEALLOCATE Cursor1
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
RETURN @.@.ROWCOUNT
END
ELSE
BEGIN
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
END
GO
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:ejJalkYlFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Ral,
> First of all, you must change the name of your stored procedure.
> Procedures named sp_* are treated differently by the query
> processor. This name is used to indicate that there is a system
> stored procedure in the master database available from all databases,
> so the processor must first search the objects in master, find no
> system procedure, then look for your procedure in your database.
> If this doesn't solve the problem, consider adding WITH RECOMPILE
> to the definition of the stored procedure. What you may be seeing is
> the result of a cached plan for the stored procedure that is optimal
> for one set of parameters and very bad for another. When you run
> this query outside a procedure, the plan will not be cached, but
> will be recompiled for the particular variable values.
> If that still doesn't work, try declaring local variables for your
> parameters and reassigning them inside the procedure:
> ...
> declare @.Campania2 int,
> @.FechaDesde2 nchar(10),
> @.FechaHasta2 nchar(10),
> @.Pais2 nchar(5),
> @.Ciudad2 nchar(5),
> @.Duracion2 int,
> @.Precio2 int
> select
> @.Campania2 = @.Campania,
> @.FechaDesde2 = @.FechaHasta,
> ...
> This sometimes improves the choice of plan with or
> without adding WITH RECOMPILE. In your case, it
> might also be worthwhile to use local datetime variables for
> the result of CONVERT(DATETIME, @.FechaHasta, 102),
> CONVERT(DATETIME, @.FechaDesde, 102), and
> CONVERT(DATETIME, GETDATE(), 102)
> Queries this complicated with many parameters can be difficult
> to optimize, particularly if the optimal query plan depends on
> the particular parameter values.
> Finally, I didn't look closely, but I don't quite understand the
> purpose of
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
> TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
> Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> instead of just putting .Multimedias.IDtipoMultimedia = 0 into
> the where clause before grouping. I'm also not sure why the
> extra . before Multimedias.
>
> Steve Kass
> Drew University
>
> Ral Martn wrote:
>
seconds
the
2)
2)
(IDPVSRC
smallint)'
,
MIXG.Ciudad
IDPVSRC,
AS
PreciosXFechasPVSRC.IDPVSRC
AND
MIX.IDvuelo,
IDPVP,
@.FechaHasta,
TABLAPVP.PVP|||Sorry, the correct store procedure is like that
(without cursors)
CREATE PROCEDURE BuscardorOfertasmejorCIUDADESsincursor
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
RETURN @.@.ROWCOUNT
END
GO