Query lento con indices

Takirary,

No creo que obtengas muchas sugerencias, cuando no sabemos la estructura de
las tablas involucradas, los indices que tienen, el codigo del sp,
informacion sobre el plan de ejecucion, etc.

Si de veras quieres ayuda, entonces primero tendras que ayudarnos a nosotros
a entender mejor tu ambiente.


AMB

"Takirary" wrote:

Tengo un problema que me genera muchas dudas, tengo varios Store
Procedure que se ejecutan en un MsSQL con SP4 (el servidor tambien
tiene todos los SP) y cuando se procesan por primera vez tarda 24 seg
o 30 seg si lo vuelvo a ejecutar inmediatamente tarda 3 o 4 segundos,
pasados 7 o 10 min el tiempo de procesamiento vuelve a ser de 24 seg
o 30 seg.

Hasta ahora he realizado diferentes pruebas, en las cuales incluyo
detalle

1. Actualice todos los SP de la Maquina y de SQL Server

2. Verifique los indices de cada tabla (estan normalizadas), y tiene
los indices necesarios

3. Reralice pruebas con el Monitor de Rendimiento, el servidor no
presenta problemas de Procesamiento, Memoria o Disco (si vi bloqueos y
opte por otra prueba mas adelante), los usuarios que acceden no pasan
mas de 30 conexiones a diferentes tablas de la misma base de datos,
raramente sobrepasan las 10 conexiones concurrentes, es mas he probado
con consultas que solo puede ejecutar un cargo superior y no un
operador

4. Probe El plan de ejecucion estimado (tambien con SET SHOWPLAN_TEXT)
y en una de las tablas que no tenia un indice al agregarlo me degrada
el Query algunos segundos mas por lo que opte por sacar ese indice

5. Probe diferentes alternativas para acelerar el procesamiento aunque
me devuelva datos sucios a traves de SET TRANSACTION ISOLATION LEVEL
y tampo co lo mejora considerablemente siguiendo con el mismo tiempo
inicial

6 Como a traves del Monitor de Rendimiento vi algunos bloqueos
instale otro servidor con todos los SP y con un backup de la base de
datos sin meterlo en el dominio (sin ningun usuario accediendo y sin
ninguna otra aplicacion que pueda afectar, cabe destacar que tanto el
equipo original como el de prueba no son controladores de dominio por
si alguno lo penso...) para asegurarme que no hubiese otra aplicacion
pero me sigue dando los mismos tiempos iniciales

Un datos mas, No opero con Cursores que podrian estar planchando la
consulta

Obviamente que lo unico que me resta hacer es tratar de dividir el
Query que estoy tirando pero por las dudas lo consulto ALGUIEN LE
PASO ESTO...?, LO SOLUCIONO DE ALGUNA FORMA...?

Es decir porque la consulta inicial tarda mas si tiene todos los
indices necesarios y despues tarda lo que una base optimizada y normal

Desde ya muchas gracias

Si lo se Alejandro lo que pasa es que tenia la vaga esperanza que
alguien me respondiera con un ... probate poner un ... o proba setear
un... pero no es el caso por ello para quienes esten dispuestos a
ayudar ahi va tutta la info, (mil disculpas por no responder antes
pero estuve con mucho candombe... :C



Contactos_bco - Estructura
con_id int no 4 10 0 no (n/a) (n/a) NULL
prs_id int no 4 10 0 yes (n/a) (n/a) NULL
con_doc_nro varchar no 11 yes no no Modern_Spanish_CI_AS
con_tel_nro varchar no 50 yes no no Modern_Spanish_CI_AS
con_tel_prefijo varchar no 50 yes no no
Modern_Spanish_CI_AS
con_tel_discado varchar no 50 yes no no
Modern_Spanish_CI_AS
con_obs varchar no 4000 yes no no Modern_Spanish_CI_AS
con_nombre varchar no 50 yes no no Modern_Spanish_CI_AS
con_apellido varchar no 25 yes no no Modern_Spanish_CI_AS
cam_id int no 4 10 0 no (n/a) (n/a) NULL
con_fecha datetime no 8 no (n/a) (n/a) NULL
usu_nick varchar no 50 no no no Modern_Spanish_CI_AS
usu_id_owner int no 4 10 0 yes (n/a) (n/a) NULL
prs_origen varchar no 50 yes no no Modern_Spanish_CI_AS

Contactos_bco - Indices
IX_con_tel_nro nonclustered located on PRIMARY con_tel_nro
IX_con_tel_prefijo nonclustered located on PRIMARY con_tel_prefijo
IX_contactos__doc_nro nonclustered located on PRIMARY con_doc_nro
IX_contactos_bco__apellido nonclustered located on PRIMARY
con_apellido
IX_contactos_bco__nombre nonclustered located on PRIMARY con_nombre
IX_contactos_bco__prs_id nonclustered located on PRIMARY prs_id
IX_contactos_fecha nonclustered located on PRIMARY con_fecha
IX_prs_origen nonclustered located on PRIMARY prs_origen
IX_usu_id_owner nonclustered located on PRIMARY usu_id_owner
pk_contactos_bco clustered, unique, primary key located on PRIMARY
con_id

Contactos_bco - Connstraint

FOREIGN KEY FK_contactos_bco_usuarios No Action No Action Enabled
Is_For_Replication usu_id_owner
REFERENCES CRM.dbo.usuarios (usu_id)
PRIMARY KEY (clustered) pk_contactos_bco (n/a) (n/a) (n/a) (n/a)
con_id



contacto_estados_proceso - Estructura
-
cep_id int no 4 10 0 no (n/a) (n/a) NULL
coe_id int no 4 10 0 no (n/a) (n/a) NULL
cep_fecha datetime no 8 no (n/a) (n/a) NULL
cep_estado bit no 1 no (n/a) (n/a) NULL
vrz_id int no 4 10 0 yes (n/a) (n/a) NULL
con_id int no 4 10 0 no (n/a) (n/a) NULL
niv_id int no 4 10 0 yes (n/a) (n/a) NULL
opn_id int no 4 10 0 yes (n/a) (n/a) NULL
mde_id int no 4 10 0 yes (n/a) (n/a) NULL
cep_obs varchar no 250 yes no no Modern_Spanish_CI_AS
usu_nick varchar no 50 no no no Modern_Spanish_CI_AS

contacto_estados_proceso - Indices
-
IX_contacto_estados_proceso__con_id nonclustered located on PRIMARY
con_id
IX_contacto_estados_proceso__fecha nonclustered located on PRIMARY
cep_fecha
pk_contacto_estados_proceso clustered, unique, primary key located on
PRIMARY cep_id

contacto_estados_proceso - Constraint
-
DEFAULT on column cep_estado df_contacto_estados_proceso_cep_estado (n/
a) (n/a) (n/a) (n/a) (1)
DEFAULT on column cep_fecha df_contacto_estados_proceso_cep_fecha (n/
a) (n/a) (n/a) (n/a) (getdate())
FOREIGN KEY fk_contacto_estados_proceso_contacto_estados No Action No
Action Enabled Is_For_Replication coe_id
REFERENCES CRM.dbo.contacto_estados (coe_id)
FOREIGN KEY fk_contacto_estados_proceso_contactos_bco No Action No
Action Enabled Is_For_Replication con_id
REFERENCES CRM.dbo.contactos_bco (con_id)
FOREIGN KEY fk_contacto_estados_proceso_motivos_desistio No Action No
Action Enabled Is_For_Replication mde_id
REFERENCES CRM.dbo.motivos_desistio (mde_id)
FOREIGN KEY fk_contacto_estados_proceso_nivel_interes No Action No
Action Enabled Is_For_Replication niv_id
REFERENCES CRM.dbo.nivel_interes (niv_id)
FOREIGN KEY FK_contacto_estados_proceso_opciones_nivel No Action No
Action Enabled Is_For_Replication opn_id
REFERENCES CRM.dbo.opciones_nivel (opn_id)
FOREIGN KEY fk_contacto_estados_proceso_resultado_veraz No Action No
Action Enabled Is_For_Replication vrz_id
REFERENCES CRM.dbo.resultado_veraz (vrz_id)
PRIMARY KEY (clustered) pk_contacto_estados_proceso (n/a) (n/a) (n/a)
(n/a) cep_id



contacto_estados - Estructura
coe_id int no 4 10 0 no (n/a) (n/a) NULL
coe_des char no 25 no no no Modern_Spanish_CI_AS
coe_orden tinyint no 1 3 0 yes (n/a) (n/a) NULL
coe_estado bit no 1 no (n/a) (n/a) NULL
usu_nick varchar no 50 no no no Modern_Spanish_CI_AS

contacto_estados - Indices
pk_estado_contacto clustered, unique, primary key located on PRIMARY
coe_id

contacto_estados - Connstraint

DEFAULT on column coe_estado DF_contacto_estados_coe_estado (n/a) (n/
a) (n/a) (n/a) (0)
PRIMARY KEY (clustered) pk_estado_contacto (n/a) (n/a) (n/a) (n/a)
coe_id



nivel_interes - Estructura
niv_id int no 4 10 0 no (n/a) (n/a) NULL
niv_des char no 25 yes no yes Modern_Spanish_CI_AS
campanias varchar no 50 yes no no Modern_Spanish_CI_AS
niv_orden tinyint no 1 3 0 yes (n/a) (n/a) NULL
niv_estado bit no 1 no (n/a) (n/a) NULL
niv_sigue bit no 1 yes (n/a) (n/a) NULL
usu_nick varchar no 50 no no no Modern_Spanish_CI_AS

nivel_interes - Indices
pk_nivel_interes clustered, unique, primary key located on PRIMARY
niv_id

nivel_interes - Connstraint

DEFAULT on column niv_estado DF_nivel_interes_niv_estado (n/a) (n/a)
(n/a) (n/a) (0)
PRIMARY KEY (clustered) pk_nivel_interes (n/a) (n/a) (n/a) (n/a)
niv_id



resultado_veraz - Estructura
-
vrz_id int no 4 10 0 no (n/a) (n/a) NULL
vrz_des char no 25 yes no yes Modern_Spanish_CI_AS
vrz_orden tinyint no 1 3 0 yes (n/a) (n/a) NULL
vrz_seguir_proc bit no 1 no (n/a) (n/a) NULL
usu_nick varchar no 50 no no no Modern_Spanish_CI_AS

resultado_veraz - Indices
-
pk_resultado_veraz clustered, unique, primary key located on PRIMARY
vrz_id

resultado_veraz - Connstraint
PRIMARY KEY (clustered) pk_resultado_veraz (n/a) (n/a) (n/a) (n/a)
vrz_id



llamada_estados - Estructura
-
lle_id int no 4 10 0 no (n/a) (n/a) NULL
lle_des char no 25 yes no yes Modern_Spanish_CI_AS
campanias varchar no 50 yes no no Modern_Spanish_CI_AS
lle_orden tinyint no 1 3 0 yes (n/a) (n/a) NULL
lle_estado bit no 1 no (n/a) (n/a) NULL
lle_sigue bit no 1 yes (n/a) (n/a) NULL
usu_nick varchar no 50 no no no Modern_Spanish_CI_AS

llamada_estados - Indices
-
pk_resultado_llamada clustered, unique, primary key located on PRIMARY
lle_id

llamada_estados - Connstraint
DEFAULT on column lle_estado DF_llamada_estados_lle_estado (n/a) (n/a)
(n/a) (n/a) (0)
PRIMARY KEY (clustered) pk_resultado_llamada (n/a) (n/a) (n/a) (n/a)
lle_id



usuarios - Estructura

usu_id int no 4 10 0 no (n/a) (n/a) NULL
usu_nick varchar no 50 yes no no Modern_Spanish_CI_AS
usu_nom varchar no 50 yes no no Modern_Spanish_CI_AS
usu_estado bit no 1 no (n/a) (n/a) NULL
usu_fecha_desde smalldatetime no 4 no (n/a) (n/a) NULL
usu_fecha_hasta smalldatetime no 4 yes (n/a) (n/a) NULL
usu_pass varchar no 50 yes no no Modern_Spanish_CI_AS
pas_id int no 4 10 0 yes (n/a) (n/a) NULL
usu_padre int no 4 10 0 yes (n/a) (n/a) NULL
reporting varchar no 10 yes no no Modern_Spanish_CI_AS
fk_employee int no 4 10 0 yes (n/a) (n/a) NULL
issupervisor bit no 1 yes (n/a) (n/a) NULL
fk_secure_rol int no 4 10 0 yes (n/a) (n/a) NULL
usu_last_login smalldatetime no 4 yes (n/a) (n/a) NULL
usu_mail varchar no 50 yes no no Modern_Spanish_CI_AS

usuarios - Indices

IX_usu_nick nonclustered, unique located on PRIMARY usu_nick
IX_usuarios__padre nonclustered located on PRIMARY usu_padre
pk_usuarios clustered, unique, primary key located on PRIMARY usu_id

usuarios - Connstraint
-
DEFAULT on column usu_estado df_usuarios_usu_estado (n/a) (n/a) (n/a)
(n/a) (1)
DEFAULT on column usu_fecha_desde df_usuarios_usu_fecha_desde (n/a) (n/
a) (n/a) (n/a) (getdate())
PRIMARY KEY (clustered) pk_usuarios (n/a) (n/a) (n/a) (n/a) usu_id




Estructura Tabla Llamadas
-
lla_id int no 4 10 0 no (n/a) (n/a) NULL
con_id int no 4 10 0 no (n/a) (n/a) NULL
lla_inicio datetime no 8 no (n/a) (n/a) NULL
lla_fin datetime no 8 yes (n/a) (n/a) NULL
lle_id int no 4 10 0 yes (n/a) (n/a) NULL
ill_id int no 4 10 0 yes (n/a) (n/a) NULL
usu_nick varchar no 50 no no no Modern_Spanish_CI_AS


Indices Tabla Llamadas
-
FOREIGN KEY fk_llamadas_contactos_bco No Action No Action Enabled
Is_For_Replication con_id
REFERENCES CRM.dbo.contactos_bco (con_id)
FOREIGN KEY FK_llamadas_llamadas_estados No Action No Action Enabled
Is_For_Replication lle_id
REFERENCES CRM.dbo.llamada_estados (lle_id)
FOREIGN KEY fk_llamadas_receptor_llamada No Action No Action Enabled
Is_For_Replication ill_id
REFERENCES CRM.dbo.receptor_llamada (rll_id)
FOREIGN KEY fk_llamadas_resultado_llamada No Action No Action Enabled
Is_For_Replication lle_id
REFERENCES CRM.dbo.llamada_estados (lle_id)
PRIMARY KEY (clustered) pk_llamadas (n/a) (n/a) (n/a) (n/a) lla_id





exec CRM_Test_Perfomance 101, 1 , null, null, null, null,
'FERNAND' --set showplan_text off
CREATE proc [dbo].[CRM_Test_Perfomance] @usu_id
int, @cam_id int = 1, @usu_id_owner int = null, @coe_id int null, @con_tel_nro varchar(30) = null, @con_nombre varchar(50) null, @con_apellido varchar(50) = null, @con_doc_nro var

IF (@usu_id_owner = 0 or @usu_id_owner is null)
SET @usu_id_owner = @usu_id

IF @coe_id = 0
SET @coe_id = null
select @con_tel_nro = replace(replace(replace(isnull(@con_tel_nro,
''), '.', '%'), ' ', '%'), '-', '%') + '%'


IF @cam_id = 1
BEGIN select c.con_id, c.con_nombre as [Nombre],
c.con_apellido as [Apellido], c.con_tel_nro as [Telefono],
c.con_doc_nro as [Documento], case when not cep.niv_id is null then
niv.niv_des else lle.lle_des end as Resultado,


|--Compute Scalar(DEFINE:([Expr1021]=If ([cep].
[niv_id]<>NULL) then [niv].[niv_des] else [lle].[lle_des]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:
([c].[usu_id_owner]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([lla].[lle_id]))
| |--Sort(ORDER BY:([c].[con_apellido] ASC))
| | |--Hash Match(Left Outer Join, HASH:
([c].[con_id])=([lla].[con_id]))
| | |--Nested Loops(Left Outer Join,
OUTER REFERENCES:([cep].[niv_id]))
| | | |--Nested Loops(Left Outer
Join, OUTER REFERENCES:([cep].[coe_id]))
| | | | |--Nested Loops(Left
Anti Semi Join, OUTER REFERENCES:([c].[con_id]))
| | | | | |--Nested
Loops(Left Semi Join, WHERE:([c].[usu_id_owner]=[usu_id]))
| | | | | | |--Nested
Loops(Inner Join, OUTER REFERENCES:([contacto_estados_proceso].
[con_id], [Expr1009]))
| | | | | | | |--
Stream Aggregate(GROUP BY:([c].[con_id]) DEFINE:
([Expr1009]=MAX([contacto_estados_proceso].[cep_id]), [c].
[usu_id_owner]=ANY([c].[usu_id_owner]), [c].[con_nombre]=ANY([c].
[con_nombre]), [c].[
| | | | | | | | |--
Nested Loops(Inner Join, OUTER REFERENCES:([c].[con_id]))
| | | | | | |
| |--Nested Loops(Left Semi Join, WHERE:([c].
[usu_id_owner]=[usu_id]))
| | | | | | |
| | |--Filter(WHERE:(([c].
[con_doc_nro]=isnull([@con_doc_nro], Convert([c].[con_doc_nro])) AND
Convert([c].[con_fecha])>=Convert(dateadd(month, -1, getdate()))) AND
[c].[cam_id]=[@cam
| | | | | | |
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:
([CRM].[dbo].[contactos_bco] AS [c]) WITH PREFETCH)
| | | | | | |
| | | |--Hash Match(Inner Join, HASH:([c].
[con_id])=([c].[con_id]))
| | | | | | |
| | | |--Hash Match(Inner Join, HASH:([c].
[con_id])=([c].[con_id]))
| | | | | | |
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([Expr1056], [Expr1057],
LikeRangeInfo('%'+isnull([@con_apellido], '')+'%', NULL)))
| | | | | | |
| | | | | |--Compute Scalar(DEFINE:
([Expr1056]=Convert(LikeRangeStart('%'+isnull([@con_apellido], '')
+'%', NULL)), [Expr1057]=Convert(LikeRangeEnd('%'+isnull([@con_
| | | | | | |
| | | | | | |--Constant Scan
| | | | | | |
| | | | | |--Index Seek(OBJECT:([CRM].
[dbo].[contactos_bco].[IX_contactos_bco__apellido] AS [c]), SEEK:([c].
[con_apellido] > [Expr1056] AND [c].[con_apellido] < [Ex
| | | | | | |
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([Expr1059], [Expr1060],
LikeRangeInfo('%'+isnull([@con_tel_nro], '')+'%', NULL)))
| | | | | | |
| | | | |--Compute Scalar(DEFINE:
([Expr1059]=Convert(LikeRangeStart('%'+isnull([@con_tel_nro], '')+'%',
NULL)), [Expr1060]=Convert(LikeRangeEnd('%'+isnull([@con_t
| | | | | | |
| | | | | |--Constant Scan
| | | | | | |
| | | | |--Index Seek(OBJECT:([CRM].
[dbo].[contactos_bco].[IX_con_tel_nro] AS [c]), SEEK:([c].
[con_tel_nro] > [Expr1059] AND [c].[con_tel_nro] < [Expr1060]), WHE
| | | | | | |
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([Expr1062], [Expr1063],
LikeRangeInfo('%'+isnull([@con_nombre], '')+'%', NULL)))
| | | | | | |
| | | |--Compute Scalar(DEFINE:
([Expr1062]=Convert(LikeRangeStart('%'+isnull([@con_nombre], '')+'%',
NULL)), [Expr1063]=Convert(LikeRangeEnd('%'+isnull([@con_nombre]
| | | | | | |
| | | | |--Constant Scan
| | | | | | |
| | | |--Index Seek(OBJECT:([CRM].[dbo].
[contactos_bco].[IX_contactos_bco__nombre] AS [c]), SEEK:([c].
[con_nombre] > [Expr1062] AND [c].[con_nombre] < [Expr1063]),
| | | | | | |
| | |--Table Scan(OBJECT:(dbo.usuarios_arbol))
| | | | | | |
| |--Index Seek(OBJECT:([CRM].[dbo].[contacto_estados_proceso].
[IX_contacto_estados_proceso__con_id]), SEEK:
([contacto_estados_proceso].[con_id]=[c].[con_id]) ORDERED FORWARD)
| | | | | | | |--
Clustered Index Seek(OBJECT:([CRM].[dbo].[contacto_estados_proceso].
[pk_contacto_estados_proceso] AS [cep]), SEEK:([cep].
[cep_id]=[Expr1009]), WHERE:([contacto_estados_proceso].[con_id]=[cep
| | | | | | |--Table
Scan(OBJECT:(dbo.usuarios_arbol))
| | | | | |--Index
Seek(OBJECT:([CRM].[dbo].[legajos_bco].[IX_legajos_bco__con_id]), SEEK:
([legajos_bco].[con_id]=[c].[con_id]), WHERE:([legajos_bco].
[con_id]=NULL OR [legajos_bco].[con_id]=[c].[con_id]) ORDERED F
| | | | |--Clustered Index
Seek(OBJECT:([CRM].[dbo].[contacto_estados].[pk_estado_contacto] AS
[ce]), SEEK:([ce].[coe_id]=[cep].[coe_id]) ORDERED FORWARD)
| | | |--Clustered Index
Seek(OBJECT:([CRM].[dbo].[nivel_interes].[pk_nivel_interes] AS [niv]),
SEEK:([niv].[niv_id]=[cep].[niv_id]) ORDERED FORWARD)
| | |--Hash Match(Inner Join, HASH:
([Expr1013], [llamadas].[con_id])=([lla].[lla_id], [lla].[con_id]),
RESIDUAL:([Expr1013]=[lla].[lla_id] AND [lla].[con_id]=[llamadas].
[con_id]))
| | |--Hash Match(Aggregate,
HASH:([llamadas].[con_id]) DEFINE:([Expr1013]=MAX([llamadas].
[lla_id])))
| | | |--Clustered Index
Scan(OBJECT:([CRM].[dbo].[llamadas].[pk_llamadas]))
| | |--Clustered Index
Scan(OBJECT:([CRM].[dbo].[llamadas].[pk_llamadas] AS [lla]))
| |--Clustered Index Seek(OBJECT:([CRM].[dbo].
[llamada_estados].[pk_resultado_llamada] AS [lle]), SEEK:([lle].
[lle_id]=[lla].[lle_id]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([CRM].[dbo].
[usuarios].[pk_usuarios] AS [u]), SEEK:([u].[usu_id]=[c].
[usu_id_owner]) ORDERED FORWARD)



set @HoraFina= getdate()
set @HoraTota= @HoraFina - @HoraInic