Consulta sobre Isnull en consultas

08/05/2007 - 19:00 por jcac | Informe spam
Hola lista,

Tengo varios procedimientos almacenados que en la parte del where tiene por
ejemplo almacen = isnull(@almacen, almacen)

como es que deberia de cambiarlo, ya que no recuerdo muy bien quien dijo que
no era adecuado y al hacerlo de esta manera no se haria uso de los indices.

Saludos

Preguntas similare

Leer las respuestas

#1 Alejandro Mesa
08/05/2007 - 19:20 | Informe spam
Trata:

Suponiendo que [almacen] es tipo INT y que no toma valores negativos.

...
where
almacen between coalesce(@almacen, 0) and coalesce(@almacen, convert(int,
0x7FFFFFFF))


Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

Las virtudes y maldades del SQL dinámico
http://www.hayes.ch/sql/sql_dinamico.html


AMB

"jcac" wrote:

Hola lista,

Tengo varios procedimientos almacenados que en la parte del where tiene por
ejemplo almacen = isnull(@almacen, almacen)

como es que deberia de cambiarlo, ya que no recuerdo muy bien quien dijo que
no era adecuado y al hacerlo de esta manera no se haria uso de los indices.

Saludos



Respuesta Responder a este mensaje
#2 jcac
08/05/2007 - 19:53 | Informe spam
Gracias Alejandro

Te comento que aun no he revizado los links que me haz adjuntado, pero bueno
esta es una consulta que me devuelve solo 11 registros pero se demora 8
segundos lo cual pienso que es mucho, quizas me puedas indicar que estoy
haciendo mal.
Te adjunto el sp asi como su plan de ejecucion.
Por lo general siempre revizo estos planes de ejecucion en el query analizer
y los veo en forma grafica y donde me realiza un consumo muchio mayor es en
el Bookmark Lookup, donde tengo un costo de 22 y algo mas.

Saludos y gracias por tu tiempo

PD. Prove el como me indicaste pero los tiempos se incrementaron.

L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
Null, Null, '190', Null, Null, '20060101', '20061231'
CREATE Procedure
L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
Null, Null, Null, Null, Null, '20060101', '20060131'
@SUCR_Codigo Char(2), @ALMA_Codigo Char(3), @FAMI_Codigo Char(4),
@SUBF_Codigo Char(4), @PROD_CodProd Char(15), @DVAL_FecConfirI Datetime,
@DVAL_FecConfirF Datetime
As
Select Distinct D.PROD_CodProd From DetValesAlm D
Inner Join Vales V On V.SUCR_Codigo = D.SUCR_Codigo And V.ALMA_Codigo =
D.ALMA_Codigo And V.TIPO_TabVale = D.TIPO_TabVale And V.TIPO_CodVale =
D.TIPO_CodVale And V.VALE_NumVale = D.VALE_NumVale
Inner Join Productos P On P.PROD_CodProd = D.PROD_CodProd
Inner Join Familias F On F.FAMI_Codigo = P.FAMI_Codigo
Inner Join SubFamilias SF On SF.FAMI_Codigo = P.FAMI_Codigo And
SF.SUBF_Codigo = P.SUBF_Codigo
Where D.SUCR_Codigo = IsNull(@SUCR_Codigo, D.SUCR_Codigo) -- Between
Coalesce(@SUCR_Codigo, '00') And Coalesce(@SUCR_Codigo, '99')
And D.ALMA_Codigo = IsNull(@ALMA_Codigo, D.ALMA_Codigo) -- Between
Coalesce(@ALMA_Codigo, '000') And Coalesce(@ALMA_Codigo, '999')
And F.FAMI_Codigo = IsNull(@FAMI_Codigo, F.FAMI_Codigo) -- Between
Coalesce(@FAMI_Codigo, '000') And Coalesce(@FAMI_Codigo, '999')
And SF.SUBF_Codigo = IsNull(@SUBF_Codigo, SF.SUBF_Codigo) -- Between
Coalesce(@SUBF_Codigo, '000') And Coalesce(@SUBF_Codigo, '999')
And D.PROD_CodProd = IsNull(@PROD_CodProd, D.PROD_CodProd) -- Between
Coalesce(@PROD_CodProd, '0000000000') And Coalesce(@PROD_CodProd,
'9999999999')
And V.VALE_Estado = 'C'
And D.DVAL_FecConfir Between @DVAL_FecConfirI And @DVAL_FecConfirF
Order by D.PROD_CodProd

(2 row(s) affected)

StmtText

|--Sort(ORDER BY:([D].[PROD_CodProd] ASC))
|--Hash Match(Inner Join,
HASH:([F].[FAMI_Codigo])=([SF].[FAMI_Codigo]),
RESIDUAL:([F].[FAMI_Codigo]=[SF].[FAMI_Codigo]))
|--Clustered Index
Scan(OBJECT:([Layconsa].[dbo].[Familias].[XPKSubLinea] AS [F]),
WHERE:([F].[FAMI_Codigo]=isnull([@FAMI_Codigo], [F].[FAMI_Codigo])))
|--Hash Match(Inner Join, HASH:([SF].[FAMI_Codigo],
[SF].[SUBF_Codigo])=([P].[FAMI_Codigo], [P].[SUBF_Codigo]),
RESIDUAL:([P].[FAMI_Codigo]=[SF].[FAMI_Codigo] AND
[SF].[SUBF_Codigo]=[P].[SUBF_Codigo]))
|--Index
Scan(OBJECT:([Layconsa].[dbo].[SubFamilias].[XPKVersion] AS [SF]),
WHERE:([SF].[SUBF_Codigo]=isnull([@SUBF_Codigo],
Convert([SF].[SUBF_Codigo]))))
|--Merge Join(Inner Join,
MERGE:([D].[PROD_CodProd])=([P].[PROD_CodProd]),
RESIDUAL:([D].[PROD_CodProd]=[P].[PROD_CodProd]))
|--Sort(DISTINCT ORDER BY:([D].[PROD_CodProd]
ASC))
| |--Merge Join(Inner Join,
MERGE:([V].[VALE_NumVale], [V].[TIPO_TabVale], [V].[TIPO_CodVale],
[V].[SUCR_Codigo], [V].[ALMA_Codigo])=([D].[VALE_NumVale],
[D].[TIPO_TabVale], [D].[TIPO_CodVale], [D].[SUCR_Codigo],
[D].[ALMA_Codigo]), RESIDUAL:(((([D].[SUCR_Codigo]=[V].[SUCR_Codigo] AND
[D].[ALMA_Codigo]=[V].[ALMA_Codigo]) AND
[D].[TIPO_TabVale]=[V].[TIPO_TabVale]) AND
[D].[TIPO_CodVale]=[V].[TIPO_CodVale]) AND
[D].[VALE_NumVale]=[V].[VALE_NumVale]))
| |--Clustered Index
Scan(OBJECT:([Layconsa].[dbo].[Vales].[PK__Vales__1EB046D7] AS [V]),
WHERE:([V].[VALE_Estado]='C') ORDERED FORWARD)
| |--Sort(ORDER BY:([D].[VALE_NumVale]
ASC, [D].[TIPO_TabVale] ASC, [D].[TIPO_CodVale] ASC, [D].[SUCR_Codigo] ASC,
[D].[ALMA_Codigo] ASC))
|
|--Filter(WHERE:([D].[PROD_CodProd]=isnull([@PROD_CodProd],
[D].[PROD_CodProd])))
| |--Bookmark
Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Layconsa].[dbo].[DetValesAlm] AS [D]))
| |--Index
Scan(OBJECT:([Layconsa].[dbo].[DetValesAlm].[IX_DetValesAlm_AlmaCodigo_DValFecConfir]
AS [D]), WHERE:((([D].[DVAL_FecConfir]>=[@DVAL_FecConfirI] AND
[D].[DVAL_FecConfir]<=[@DVAL_FecConfirF]) AND
[D].[ALMA_Codigo]=isnull([@ALMA_Codigo], [D].[ALMA_Codigo])) AND
[D].[SUCR_Codigo]=isnull([@SUCR_Codigo], [D].[SUCR_Codigo])))
|--Clustered Index
Scan(OBJECT:([Layconsa].[dbo].[Productos].[XPKProducto] AS [P]), ORDERED
FORWARD)

(14 row(s) affected)


"Alejandro Mesa" escribió en el
mensaje news:
Trata:

Suponiendo que [almacen] es tipo INT y que no toma valores negativos.

...
where
almacen between coalesce(@almacen, 0) and coalesce(@almacen,
convert(int,
0x7FFFFFFF))


Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

Las virtudes y maldades del SQL dinámico
http://www.hayes.ch/sql/sql_dinamico.html


AMB

"jcac" wrote:

Hola lista,

Tengo varios procedimientos almacenados que en la parte del where tiene
por
ejemplo almacen = isnull(@almacen, almacen)

como es que deberia de cambiarlo, ya que no recuerdo muy bien quien dijo
que
no era adecuado y al hacerlo de esta manera no se haria uso de los
indices.

Saludos



Respuesta Responder a este mensaje
#3 Alejandro Mesa
08/05/2007 - 21:55 | Informe spam
jcac,

Si esas columnas son tipo "char", te aconsejo que uses el operardor LIKE y
que los parametros los declares tipo "varchar".

create procedure ...
@SUCR_Codigo varChar(2) = null,
@ALMA_Codigo varChar(3) = null,
@FAMI_Codigo varChar(4) = null,
@SUBF_Codigo varChar(4) = null,
@PROD_CodProd Char(15) = null,
...

...
where
D.SUCR_Codigo like coalesce(@SUCR_Codigo, '%')
And D.ALMA_Codigo like coalesce(@ALMA_Codigo, '%')
And F.FAMI_Codigo like coalesce(@FAMI_Codigo, '%')
And SF.SUBF_Codigo like coalesce(@SUBF_Codigo, '%')
And D.PROD_CodProd like coalesce(@PROD_CodProd, '%')
And V.VALE_Estado like 'C'
And D.DVAL_FecConfir Between @DVAL_FecConfirI And @DVAL_FecConfirF


Espero que tengas indices adecuados por esas columnas. Lo mismo para las
columnas que participan en el JOIN.


AMB

"jcac" wrote:

Gracias Alejandro

Te comento que aun no he revizado los links que me haz adjuntado, pero bueno
esta es una consulta que me devuelve solo 11 registros pero se demora 8
segundos lo cual pienso que es mucho, quizas me puedas indicar que estoy
haciendo mal.
Te adjunto el sp asi como su plan de ejecucion.
Por lo general siempre revizo estos planes de ejecucion en el query analizer
y los veo en forma grafica y donde me realiza un consumo muchio mayor es en
el Bookmark Lookup, donde tengo un costo de 22 y algo mas.

Saludos y gracias por tu tiempo

PD. Prove el como me indicaste pero los tiempos se incrementaron.

L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
Null, Null, '190', Null, Null, '20060101', '20061231'
CREATE Procedure
L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
Null, Null, Null, Null, Null, '20060101', '20060131'
@SUCR_Codigo Char(2), @ALMA_Codigo Char(3), @FAMI_Codigo Char(4),
@SUBF_Codigo Char(4), @PROD_CodProd Char(15), @DVAL_FecConfirI Datetime,
@DVAL_FecConfirF Datetime
As
Select Distinct D.PROD_CodProd From DetValesAlm D
Inner Join Vales V On V.SUCR_Codigo = D.SUCR_Codigo And V.ALMA_Codigo =
D.ALMA_Codigo And V.TIPO_TabVale = D.TIPO_TabVale And V.TIPO_CodVale =
D.TIPO_CodVale And V.VALE_NumVale = D.VALE_NumVale
Inner Join Productos P On P.PROD_CodProd = D.PROD_CodProd
Inner Join Familias F On F.FAMI_Codigo = P.FAMI_Codigo
Inner Join SubFamilias SF On SF.FAMI_Codigo = P.FAMI_Codigo And
SF.SUBF_Codigo = P.SUBF_Codigo
Where D.SUCR_Codigo = IsNull(@SUCR_Codigo, D.SUCR_Codigo) -- Between
Coalesce(@SUCR_Codigo, '00') And Coalesce(@SUCR_Codigo, '99')
And D.ALMA_Codigo = IsNull(@ALMA_Codigo, D.ALMA_Codigo) -- Between
Coalesce(@ALMA_Codigo, '000') And Coalesce(@ALMA_Codigo, '999')
And F.FAMI_Codigo = IsNull(@FAMI_Codigo, F.FAMI_Codigo) -- Between
Coalesce(@FAMI_Codigo, '000') And Coalesce(@FAMI_Codigo, '999')
And SF.SUBF_Codigo = IsNull(@SUBF_Codigo, SF.SUBF_Codigo) -- Between
Coalesce(@SUBF_Codigo, '000') And Coalesce(@SUBF_Codigo, '999')
And D.PROD_CodProd = IsNull(@PROD_CodProd, D.PROD_CodProd) -- Between
Coalesce(@PROD_CodProd, '0000000000') And Coalesce(@PROD_CodProd,
'9999999999')
And V.VALE_Estado = 'C'
And D.DVAL_FecConfir Between @DVAL_FecConfirI And @DVAL_FecConfirF
Order by D.PROD_CodProd

(2 row(s) affected)

StmtText

|--Sort(ORDER BY:([D].[PROD_CodProd] ASC))
|--Hash Match(Inner Join,
HASH:([F].[FAMI_Codigo])=([SF].[FAMI_Codigo]),
RESIDUAL:([F].[FAMI_Codigo]=[SF].[FAMI_Codigo]))
|--Clustered Index
Scan(OBJECT:([Layconsa].[dbo].[Familias].[XPKSubLinea] AS [F]),
WHERE:([F].[FAMI_Codigo]=isnull([@FAMI_Codigo], [F].[FAMI_Codigo])))
|--Hash Match(Inner Join, HASH:([SF].[FAMI_Codigo],
[SF].[SUBF_Codigo])=([P].[FAMI_Codigo], [P].[SUBF_Codigo]),
RESIDUAL:([P].[FAMI_Codigo]=[SF].[FAMI_Codigo] AND
[SF].[SUBF_Codigo]=[P].[SUBF_Codigo]))
|--Index
Scan(OBJECT:([Layconsa].[dbo].[SubFamilias].[XPKVersion] AS [SF]),
WHERE:([SF].[SUBF_Codigo]=isnull([@SUBF_Codigo],
Convert([SF].[SUBF_Codigo]))))
|--Merge Join(Inner Join,
MERGE:([D].[PROD_CodProd])=([P].[PROD_CodProd]),
RESIDUAL:([D].[PROD_CodProd]=[P].[PROD_CodProd]))
|--Sort(DISTINCT ORDER BY:([D].[PROD_CodProd]
ASC))
| |--Merge Join(Inner Join,
MERGE:([V].[VALE_NumVale], [V].[TIPO_TabVale], [V].[TIPO_CodVale],
[V].[SUCR_Codigo], [V].[ALMA_Codigo])=([D].[VALE_NumVale],
[D].[TIPO_TabVale], [D].[TIPO_CodVale], [D].[SUCR_Codigo],
[D].[ALMA_Codigo]), RESIDUAL:(((([D].[SUCR_Codigo]=[V].[SUCR_Codigo] AND
[D].[ALMA_Codigo]=[V].[ALMA_Codigo]) AND
[D].[TIPO_TabVale]=[V].[TIPO_TabVale]) AND
[D].[TIPO_CodVale]=[V].[TIPO_CodVale]) AND
[D].[VALE_NumVale]=[V].[VALE_NumVale]))
| |--Clustered Index
Scan(OBJECT:([Layconsa].[dbo].[Vales].[PK__Vales__1EB046D7] AS [V]),
WHERE:([V].[VALE_Estado]='C') ORDERED FORWARD)
| |--Sort(ORDER BY:([D].[VALE_NumVale]
ASC, [D].[TIPO_TabVale] ASC, [D].[TIPO_CodVale] ASC, [D].[SUCR_Codigo] ASC,
[D].[ALMA_Codigo] ASC))
|
|--Filter(WHERE:([D].[PROD_CodProd]=isnull([@PROD_CodProd],
[D].[PROD_CodProd])))
| |--Bookmark
Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Layconsa].[dbo].[DetValesAlm] AS [D]))
| |--Index
Scan(OBJECT:([Layconsa].[dbo].[DetValesAlm].[IX_DetValesAlm_AlmaCodigo_DValFecConfir]
AS [D]), WHERE:((([D].[DVAL_FecConfir]>=[@DVAL_FecConfirI] AND
[D].[DVAL_FecConfir]<=[@DVAL_FecConfirF]) AND
[D].[ALMA_Codigo]=isnull([@ALMA_Codigo], [D].[ALMA_Codigo])) AND
[D].[SUCR_Codigo]=isnull([@SUCR_Codigo], [D].[SUCR_Codigo])))
|--Clustered Index
Scan(OBJECT:([Layconsa].[dbo].[Productos].[XPKProducto] AS [P]), ORDERED
FORWARD)

(14 row(s) affected)


"Alejandro Mesa" escribió en el
mensaje news:
> Trata:
>
> Suponiendo que [almacen] es tipo INT y que no toma valores negativos.
>
> ...
> where
> almacen between coalesce(@almacen, 0) and coalesce(@almacen,
> convert(int,
> 0x7FFFFFFF))
>
>
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> Las virtudes y maldades del SQL dinámico
> http://www.hayes.ch/sql/sql_dinamico.html
>
>
> AMB
>
> "jcac" wrote:
>
>> Hola lista,
>>
>> Tengo varios procedimientos almacenados que en la parte del where tiene
>> por
>> ejemplo almacen = isnull(@almacen, almacen)
>>
>> como es que deberia de cambiarlo, ya que no recuerdo muy bien quien dijo
>> que
>> no era adecuado y al hacerlo de esta manera no se haria uso de los
>> indices.
>>
>> Saludos
>>
>>
>>



Respuesta Responder a este mensaje
#4 jcac
08/05/2007 - 22:39 | Informe spam
Alejandro

Si justo lei el documento que me pasaste en ingles e indica como me dices,
que utilice el like, y estaba en eso, adicionalmente indica alli que se se
utilice o ejecute el sp con with recompile al final del mismo esto es
obligatorio o es que no entendi esa parte.

con respecto de los indices si las tengo indizados dichos campos y en las
relaciones todas son fks con pks.

Saludos



"Alejandro Mesa" escribió en el
mensaje news:
jcac,

Si esas columnas son tipo "char", te aconsejo que uses el operardor LIKE y
que los parametros los declares tipo "varchar".

create procedure ...
@SUCR_Codigo varChar(2) = null,
@ALMA_Codigo varChar(3) = null,
@FAMI_Codigo varChar(4) = null,
@SUBF_Codigo varChar(4) = null,
@PROD_CodProd Char(15) = null,
...

...
where
D.SUCR_Codigo like coalesce(@SUCR_Codigo, '%')
And D.ALMA_Codigo like coalesce(@ALMA_Codigo, '%')
And F.FAMI_Codigo like coalesce(@FAMI_Codigo, '%')
And SF.SUBF_Codigo like coalesce(@SUBF_Codigo, '%')
And D.PROD_CodProd like coalesce(@PROD_CodProd, '%')
And V.VALE_Estado like 'C'
And D.DVAL_FecConfir Between @DVAL_FecConfirI And @DVAL_FecConfirF


Espero que tengas indices adecuados por esas columnas. Lo mismo para las
columnas que participan en el JOIN.


AMB

"jcac" wrote:

Gracias Alejandro

Te comento que aun no he revizado los links que me haz adjuntado, pero
bueno
esta es una consulta que me devuelve solo 11 registros pero se demora 8
segundos lo cual pienso que es mucho, quizas me puedas indicar que estoy
haciendo mal.
Te adjunto el sp asi como su plan de ejecucion.
Por lo general siempre revizo estos planes de ejecucion en el query
analizer
y los veo en forma grafica y donde me realiza un consumo muchio mayor es
en
el Bookmark Lookup, donde tengo un costo de 22 y algo mas.

Saludos y gracias por tu tiempo

PD. Prove el como me indicaste pero los tiempos se incrementaron.

L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
Null, Null, '190', Null, Null, '20060101', '20061231'
CREATE Procedure
L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
Null, Null, Null, Null, Null, '20060101', '20060131'
@SUCR_Codigo Char(2), @ALMA_Codigo Char(3), @FAMI_Codigo Char(4),
@SUBF_Codigo Char(4), @PROD_CodProd Char(15), @DVAL_FecConfirI Datetime,
@DVAL_FecConfirF Datetime
As
Select Distinct D.PROD_CodProd From DetValesAlm D
Inner Join Vales V On V.SUCR_Codigo = D.SUCR_Codigo And V.ALMA_Codigo >> D.ALMA_Codigo And V.TIPO_TabVale = D.TIPO_TabVale And V.TIPO_CodVale >> D.TIPO_CodVale And V.VALE_NumVale = D.VALE_NumVale
Inner Join Productos P On P.PROD_CodProd = D.PROD_CodProd
Inner Join Familias F On F.FAMI_Codigo = P.FAMI_Codigo
Inner Join SubFamilias SF On SF.FAMI_Codigo = P.FAMI_Codigo And
SF.SUBF_Codigo = P.SUBF_Codigo
Where D.SUCR_Codigo = IsNull(@SUCR_Codigo, D.SUCR_Codigo) -- Between
Coalesce(@SUCR_Codigo, '00') And Coalesce(@SUCR_Codigo, '99')
And D.ALMA_Codigo = IsNull(@ALMA_Codigo, D.ALMA_Codigo) -- Between
Coalesce(@ALMA_Codigo, '000') And Coalesce(@ALMA_Codigo, '999')
And F.FAMI_Codigo = IsNull(@FAMI_Codigo, F.FAMI_Codigo) -- Between
Coalesce(@FAMI_Codigo, '000') And Coalesce(@FAMI_Codigo, '999')
And SF.SUBF_Codigo = IsNull(@SUBF_Codigo, SF.SUBF_Codigo) -- Between
Coalesce(@SUBF_Codigo, '000') And Coalesce(@SUBF_Codigo, '999')
And D.PROD_CodProd = IsNull(@PROD_CodProd, D.PROD_CodProd) -- Between
Coalesce(@PROD_CodProd, '0000000000') And Coalesce(@PROD_CodProd,
'9999999999')
And V.VALE_Estado = 'C'
And D.DVAL_FecConfir Between @DVAL_FecConfirI And @DVAL_FecConfirF
Order by D.PROD_CodProd

(2 row(s) affected)

StmtText

|--Sort(ORDER BY:([D].[PROD_CodProd] ASC))
|--Hash Match(Inner Join,
HASH:([F].[FAMI_Codigo])=([SF].[FAMI_Codigo]),
RESIDUAL:([F].[FAMI_Codigo]=[SF].[FAMI_Codigo]))
|--Clustered Index
Scan(OBJECT:([Layconsa].[dbo].[Familias].[XPKSubLinea] AS [F]),
WHERE:([F].[FAMI_Codigo]=isnull([@FAMI_Codigo], [F].[FAMI_Codigo])))
|--Hash Match(Inner Join, HASH:([SF].[FAMI_Codigo],
[SF].[SUBF_Codigo])=([P].[FAMI_Codigo], [P].[SUBF_Codigo]),
RESIDUAL:([P].[FAMI_Codigo]=[SF].[FAMI_Codigo] AND
[SF].[SUBF_Codigo]=[P].[SUBF_Codigo]))
|--Index
Scan(OBJECT:([Layconsa].[dbo].[SubFamilias].[XPKVersion] AS [SF]),
WHERE:([SF].[SUBF_Codigo]=isnull([@SUBF_Codigo],
Convert([SF].[SUBF_Codigo]))))
|--Merge Join(Inner Join,
MERGE:([D].[PROD_CodProd])=([P].[PROD_CodProd]),
RESIDUAL:([D].[PROD_CodProd]=[P].[PROD_CodProd]))
|--Sort(DISTINCT ORDER BY:([D].[PROD_CodProd]
ASC))
| |--Merge Join(Inner Join,
MERGE:([V].[VALE_NumVale], [V].[TIPO_TabVale], [V].[TIPO_CodVale],
[V].[SUCR_Codigo], [V].[ALMA_Codigo])=([D].[VALE_NumVale],
[D].[TIPO_TabVale], [D].[TIPO_CodVale], [D].[SUCR_Codigo],
[D].[ALMA_Codigo]), RESIDUAL:(((([D].[SUCR_Codigo]=[V].[SUCR_Codigo] AND
[D].[ALMA_Codigo]=[V].[ALMA_Codigo]) AND
[D].[TIPO_TabVale]=[V].[TIPO_TabVale]) AND
[D].[TIPO_CodVale]=[V].[TIPO_CodVale]) AND
[D].[VALE_NumVale]=[V].[VALE_NumVale]))
| |--Clustered Index
Scan(OBJECT:([Layconsa].[dbo].[Vales].[PK__Vales__1EB046D7] AS [V]),
WHERE:([V].[VALE_Estado]='C') ORDERED FORWARD)
| |--Sort(ORDER BY:([D].[VALE_NumVale]
ASC, [D].[TIPO_TabVale] ASC, [D].[TIPO_CodVale] ASC, [D].[SUCR_Codigo]
ASC,
[D].[ALMA_Codigo] ASC))
|
|--Filter(WHERE:([D].[PROD_CodProd]=isnull([@PROD_CodProd],
[D].[PROD_CodProd])))
| |--Bookmark
Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Layconsa].[dbo].[DetValesAlm] AS
[D]))
| |--Index
Scan(OBJECT:([Layconsa].[dbo].[DetValesAlm].[IX_DetValesAlm_AlmaCodigo_DValFecConfir]
AS [D]), WHERE:((([D].[DVAL_FecConfir]>=[@DVAL_FecConfirI] AND
[D].[DVAL_FecConfir]<=[@DVAL_FecConfirF]) AND
[D].[ALMA_Codigo]=isnull([@ALMA_Codigo], [D].[ALMA_Codigo])) AND
[D].[SUCR_Codigo]=isnull([@SUCR_Codigo], [D].[SUCR_Codigo])))
|--Clustered Index
Scan(OBJECT:([Layconsa].[dbo].[Productos].[XPKProducto] AS [P]), ORDERED
FORWARD)

(14 row(s) affected)


"Alejandro Mesa" escribió en el
mensaje news:
> Trata:
>
> Suponiendo que [almacen] es tipo INT y que no toma valores negativos.
>
> ...
> where
> almacen between coalesce(@almacen, 0) and coalesce(@almacen,
> convert(int,
> 0x7FFFFFFF))
>
>
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> Las virtudes y maldades del SQL dinámico
> http://www.hayes.ch/sql/sql_dinamico.html
>
>
> AMB
>
> "jcac" wrote:
>
>> Hola lista,
>>
>> Tengo varios procedimientos almacenados que en la parte del where
>> tiene
>> por
>> ejemplo almacen = isnull(@almacen, almacen)
>>
>> como es que deberia de cambiarlo, ya que no recuerdo muy bien quien
>> dijo
>> que
>> no era adecuado y al hacerlo de esta manera no se haria uso de los
>> indices.
>>
>> Saludos
>>
>>
>>



Respuesta Responder a este mensaje
#5 Alejandro Mesa
09/05/2007 - 15:11 | Informe spam
jcac,

Si justo lei el documento que me pasaste en ingles e indica como me dices,
que utilice el like, y estaba en eso, adicionalmente indica alli que se se
utilice o ejecute el sp con with recompile al final del mismo esto es
obligatorio o es que no entendi esa parte.



Cuando se usan muchos parametros y algunos de ellos son opcionales, la
primera vez que se ejecuye el sp los planes de ejecucion de cada sentencia
"select" se crearan de acuerdo a los valores de los parametros y es muy
probable que el plan de ejecucion para cuando se le asigna valores a los
parametros opcionales difiere del plan de ejecucion de cuando se usa el valor
por defecto, que en muchas ocasiones se usa NULL. Por eso es que se
recomineda quese ejecute el sp o se cree con la opcion "with recompile", para
que cada vez que se ejecute, SQL Server no busque un plan en el cache, sino
que creee uno nuevo. Cuando se usa esta opcion, SQL Server no pone el plan
generado en el cache, ya que la proxima vez que se ejecute el sp, este sera
compilado nuevamente.


AMB

"jcac" wrote:

Alejandro

Si justo lei el documento que me pasaste en ingles e indica como me dices,
que utilice el like, y estaba en eso, adicionalmente indica alli que se se
utilice o ejecute el sp con with recompile al final del mismo esto es
obligatorio o es que no entendi esa parte.

con respecto de los indices si las tengo indizados dichos campos y en las
relaciones todas son fks con pks.

Saludos



"Alejandro Mesa" escribió en el
mensaje news:
> jcac,
>
> Si esas columnas son tipo "char", te aconsejo que uses el operardor LIKE y
> que los parametros los declares tipo "varchar".
>
> create procedure ...
> @SUCR_Codigo varChar(2) = null,
> @ALMA_Codigo varChar(3) = null,
> @FAMI_Codigo varChar(4) = null,
> @SUBF_Codigo varChar(4) = null,
> @PROD_CodProd Char(15) = null,
> ...
>
> ...
> where
> D.SUCR_Codigo like coalesce(@SUCR_Codigo, '%')
> And D.ALMA_Codigo like coalesce(@ALMA_Codigo, '%')
> And F.FAMI_Codigo like coalesce(@FAMI_Codigo, '%')
> And SF.SUBF_Codigo like coalesce(@SUBF_Codigo, '%')
> And D.PROD_CodProd like coalesce(@PROD_CodProd, '%')
> And V.VALE_Estado like 'C'
> And D.DVAL_FecConfir Between @DVAL_FecConfirI And @DVAL_FecConfirF
>
>
> Espero que tengas indices adecuados por esas columnas. Lo mismo para las
> columnas que participan en el JOIN.
>
>
> AMB
>
> "jcac" wrote:
>
>> Gracias Alejandro
>>
>> Te comento que aun no he revizado los links que me haz adjuntado, pero
>> bueno
>> esta es una consulta que me devuelve solo 11 registros pero se demora 8
>> segundos lo cual pienso que es mucho, quizas me puedas indicar que estoy
>> haciendo mal.
>> Te adjunto el sp asi como su plan de ejecucion.
>> Por lo general siempre revizo estos planes de ejecucion en el query
>> analizer
>> y los veo en forma grafica y donde me realiza un consumo muchio mayor es
>> en
>> el Bookmark Lookup, donde tengo un costo de 22 y algo mas.
>>
>> Saludos y gracias por tu tiempo
>>
>> PD. Prove el como me indicaste pero los tiempos se incrementaron.
>>
>> L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
>> Null, Null, '190', Null, Null, '20060101', '20061231'
>> CREATE Procedure
>> L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
>> L_DVALSS_ReprocesoProductosEnGeneralPorSucursalPorAlmacenPorFamiliaPorSubfamiliaPorProductoDeUnaFechaAOtra
>> Null, Null, Null, Null, Null, '20060101', '20060131'
>> @SUCR_Codigo Char(2), @ALMA_Codigo Char(3), @FAMI_Codigo Char(4),
>> @SUBF_Codigo Char(4), @PROD_CodProd Char(15), @DVAL_FecConfirI Datetime,
>> @DVAL_FecConfirF Datetime
>> As
>> Select Distinct D.PROD_CodProd From DetValesAlm D
>> Inner Join Vales V On V.SUCR_Codigo = D.SUCR_Codigo And V.ALMA_Codigo > >> D.ALMA_Codigo And V.TIPO_TabVale = D.TIPO_TabVale And V.TIPO_CodVale > >> D.TIPO_CodVale And V.VALE_NumVale = D.VALE_NumVale
>> Inner Join Productos P On P.PROD_CodProd = D.PROD_CodProd
>> Inner Join Familias F On F.FAMI_Codigo = P.FAMI_Codigo
>> Inner Join SubFamilias SF On SF.FAMI_Codigo = P.FAMI_Codigo And
>> SF.SUBF_Codigo = P.SUBF_Codigo
>> Where D.SUCR_Codigo = IsNull(@SUCR_Codigo, D.SUCR_Codigo) -- Between
>> Coalesce(@SUCR_Codigo, '00') And Coalesce(@SUCR_Codigo, '99')
>> And D.ALMA_Codigo = IsNull(@ALMA_Codigo, D.ALMA_Codigo) -- Between
>> Coalesce(@ALMA_Codigo, '000') And Coalesce(@ALMA_Codigo, '999')
>> And F.FAMI_Codigo = IsNull(@FAMI_Codigo, F.FAMI_Codigo) -- Between
>> Coalesce(@FAMI_Codigo, '000') And Coalesce(@FAMI_Codigo, '999')
>> And SF.SUBF_Codigo = IsNull(@SUBF_Codigo, SF.SUBF_Codigo) -- Between
>> Coalesce(@SUBF_Codigo, '000') And Coalesce(@SUBF_Codigo, '999')
>> And D.PROD_CodProd = IsNull(@PROD_CodProd, D.PROD_CodProd) -- Between
>> Coalesce(@PROD_CodProd, '0000000000') And Coalesce(@PROD_CodProd,
>> '9999999999')
>> And V.VALE_Estado = 'C'
>> And D.DVAL_FecConfir Between @DVAL_FecConfirI And @DVAL_FecConfirF
>> Order by D.PROD_CodProd
>>
>> (2 row(s) affected)
>>
>> StmtText
>>
>> |--Sort(ORDER BY:([D].[PROD_CodProd] ASC))
>> |--Hash Match(Inner Join,
>> HASH:([F].[FAMI_Codigo])=([SF].[FAMI_Codigo]),
>> RESIDUAL:([F].[FAMI_Codigo]=[SF].[FAMI_Codigo]))
>> |--Clustered Index
>> Scan(OBJECT:([Layconsa].[dbo].[Familias].[XPKSubLinea] AS [F]),
>> WHERE:([F].[FAMI_Codigo]=isnull([@FAMI_Codigo], [F].[FAMI_Codigo])))
>> |--Hash Match(Inner Join, HASH:([SF].[FAMI_Codigo],
>> [SF].[SUBF_Codigo])=([P].[FAMI_Codigo], [P].[SUBF_Codigo]),
>> RESIDUAL:([P].[FAMI_Codigo]=[SF].[FAMI_Codigo] AND
>> [SF].[SUBF_Codigo]=[P].[SUBF_Codigo]))
>> |--Index
>> Scan(OBJECT:([Layconsa].[dbo].[SubFamilias].[XPKVersion] AS [SF]),
>> WHERE:([SF].[SUBF_Codigo]=isnull([@SUBF_Codigo],
>> Convert([SF].[SUBF_Codigo]))))
>> |--Merge Join(Inner Join,
>> MERGE:([D].[PROD_CodProd])=([P].[PROD_CodProd]),
>> RESIDUAL:([D].[PROD_CodProd]=[P].[PROD_CodProd]))
>> |--Sort(DISTINCT ORDER BY:([D].[PROD_CodProd]
>> ASC))
>> | |--Merge Join(Inner Join,
>> MERGE:([V].[VALE_NumVale], [V].[TIPO_TabVale], [V].[TIPO_CodVale],
>> [V].[SUCR_Codigo], [V].[ALMA_Codigo])=([D].[VALE_NumVale],
>> [D].[TIPO_TabVale], [D].[TIPO_CodVale], [D].[SUCR_Codigo],
>> [D].[ALMA_Codigo]), RESIDUAL:(((([D].[SUCR_Codigo]=[V].[SUCR_Codigo] AND
>> [D].[ALMA_Codigo]=[V].[ALMA_Codigo]) AND
>> [D].[TIPO_TabVale]=[V].[TIPO_TabVale]) AND
>> [D].[TIPO_CodVale]=[V].[TIPO_CodVale]) AND
>> [D].[VALE_NumVale]=[V].[VALE_NumVale]))
>> | |--Clustered Index
>> Scan(OBJECT:([Layconsa].[dbo].[Vales].[PK__Vales__1EB046D7] AS [V]),
>> WHERE:([V].[VALE_Estado]='C') ORDERED FORWARD)
>> | |--Sort(ORDER BY:([D].[VALE_NumVale]
>> ASC, [D].[TIPO_TabVale] ASC, [D].[TIPO_CodVale] ASC, [D].[SUCR_Codigo]
>> ASC,
>> [D].[ALMA_Codigo] ASC))
>> |
>> |--Filter(WHERE:([D].[PROD_CodProd]=isnull([@PROD_CodProd],
>> [D].[PROD_CodProd])))
>> | |--Bookmark
>> Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Layconsa].[dbo].[DetValesAlm] AS
>> [D]))
>> | |--Index
>> Scan(OBJECT:([Layconsa].[dbo].[DetValesAlm].[IX_DetValesAlm_AlmaCodigo_DValFecConfir]
>> AS [D]), WHERE:((([D].[DVAL_FecConfir]>=[@DVAL_FecConfirI] AND
>> [D].[DVAL_FecConfir]<=[@DVAL_FecConfirF]) AND
>> [D].[ALMA_Codigo]=isnull([@ALMA_Codigo], [D].[ALMA_Codigo])) AND
>> [D].[SUCR_Codigo]=isnull([@SUCR_Codigo], [D].[SUCR_Codigo])))
>> |--Clustered Index
>> Scan(OBJECT:([Layconsa].[dbo].[Productos].[XPKProducto] AS [P]), ORDERED
>> FORWARD)
>>
>> (14 row(s) affected)
>>
>>
>> "Alejandro Mesa" escribió en el
>> mensaje news:
>> > Trata:
>> >
>> > Suponiendo que [almacen] es tipo INT y que no toma valores negativos.
>> >
>> > ...
>> > where
>> > almacen between coalesce(@almacen, 0) and coalesce(@almacen,
>> > convert(int,
>> > 0x7FFFFFFF))
>> >
>> >
>> > Dynamic Search Conditions in T-SQL
>> > http://www.sommarskog.se/dyn-search.html
>> >
>> > Las virtudes y maldades del SQL dinámico
>> > http://www.hayes.ch/sql/sql_dinamico.html
>> >
>> >
>> > AMB
>> >
>> > "jcac" wrote:
>> >
>> >> Hola lista,
>> >>
>> >> Tengo varios procedimientos almacenados que en la parte del where
>> >> tiene
>> >> por
>> >> ejemplo almacen = isnull(@almacen, almacen)
>> >>
>> >> como es que deberia de cambiarlo, ya que no recuerdo muy bien quien
>> >> dijo
>> >> que
>> >> no era adecuado y al hacerlo de esta manera no se haria uso de los
>> >> indices.
>> >>
>> >> Saludos
>> >>
>> >>
>> >>
>>
>>
>>



Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaSiguiente Respuesta Tengo una respuesta
Search Busqueda sugerida