Foros del web, para los que viven de bits

Por favor me podrian ayudar con algo, cuando consulto mas de 4000 registros el tiempo de visualizacion de resultados es DEMASIADO largo
por los 30 seg o más. El numero de columnas es solo de 10.
Alguien me podría sugerir maneras de optimizar estas consultas??

Trabajo con SQLServer2005

Que tal Luis.

Cuanto se tarda sólo la consulta?.

Porque si lo que se tarda es la consulta, seguramente le hace falta un indice.

Y si se tarda más en presentar los datos( después de la consulta), nos podrías dar más información de como estas mostrando estos datos.

Felices fiestas!!

Si publicas el query y el plan de ejecución, podríamos darnos una mejor idea de que pasa.

cual es el select?
cual es la estructura de la tabla?

en donde estas desplegando los datos en el Management Studio o en alguna aplicacion? web o winform?

esta bien les adjunto la cantidad de codigo que tengo en mi SP: (espero que no se caiga este servidor ji,ji,ji)



CREATE procedure [dbo].[cxc_P_RptMayorClientes]
@PI_IdEmpresa int,
@PI_IdOficina int,
@PI_IdCliente int,
@PI_FechaInicial varchar(10),
@PI_FechaFinal varchar(10),
@PI_TipoPago int,
@PI_SaldoInicial char(1)

as
declare
@VL_NotasCredito money,
@VL_Saldo money,
@VL_NumDocumentoPago varchar(200),
@VL_FormaPago varchar(200),
@VL_Observacion varchar(300),
@VL_IngresoCaja char(1),
@VL_DepConfirmado char(1),
@VL_DepVerificado char(1),
@VL_Canjeado char(1),
@VL_Venta varchar(3),
@VL_Emision varchar(3)



create table #TablaCuentaxCobrar
( Empresa varchar(100),
OficinaCobro varchar(100),
Cliente varchar(100),
UsrCliente varchar(50),
FechaDocumento varchar(10),
TipoDocumento varchar(10),
Venta varchar(10),
Emision varchar(10),
Numero varchar(50),
MontoDocumento money,
NumDocumentoPago varchar(200),
FormaPago varchar(200),
Observacion varchar(300),
IngresoCaja char(1),
DepConfirmado char(1),
DepVerificado char(1),
Canjeado char(1),
SaldoInicial char(1))

create table #TablaCuentaxCobrar1
( Empresa varchar(100),
OficinaCobro varchar(100),
Cliente varchar(100),
UsrCliente varchar(50),
FechaDocumento varchar(10),
TipoDocumento varchar(150),
Venta varchar(150),
Emision varchar(150),
Numero varchar(150),
MontoDocumento money,
NumDocumentoPago varchar(200),
FormaPago varchar(200),
Observacion varchar(300),
IngresoCaja char(1),
DepConfirmado char(1),
DepVerificado char(1),
Canjeado char(1),
SaldoInicial char(1))

IF @PI_SaldoInicial = 'T'
exec cxc_P_RptMayorClientesTotalizado @PI_IdEmpresa,
@PI_IdOficina ,
@PI_IdCliente ,
@PI_FechaInicial ,
@PI_FechaFinal ,
@PI_TipoPago ,
@PI_SaldoInicial
ELSE
BEGIN


--DETALLE DE MOVIMIENTOS EN GENERAL DE DOCUMENTO
if @PI_SaldoInicial = 'N'
BEGIN
INSERT INTO #TablaCuentaxCobrar
( Empresa, OficinaCobro, Cliente, UsrCliente, FechaDocumento,
TipoDocumento, Venta, Emision, Numero, MontoDocumento,
NumDocumentoPago, FormaPago, Observacion, IngresoCaja, DepConfirmado,
DepVerificado, Canjeado, SaldoInicial)
SELECT
--a.IdEmpresa,
sige_participante.dbo.Par_F_getNombreParticipante( a.IdEmpresa) as Empresa,
--a.IdOficina,
sige_participante.dbo.Par_F_getNombreParticipante( a.IdOficina) as OficinaCobro,
sige_participante.dbo.Par_F_getNombreParticipante( a.IdCliente) as Cliente,
sige_participante.dbo.Par_F_getIdUsuario(a.IdClien te) as UsrCliente,
convert(varchar(10),a.FechaIngreso,101) as FechaDocumento,
sige_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,a.IdT ipoDocumento) as TipoDocumento,
'Venta' = case
when a.IdTipoDocumento<>5 then sige_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,a.IdT ipoDocumento)+ '-' + rtrim(convert(varchar(20),a.IdDocumento))
when a.DocOrigen is null and a.IdTipoDocumento=5
then substring(isnull(sige_facturacion.dbo.fac_F_get_Nu mFactura (a.IdEmpresa,a.IdOficina,a.IdDocumento),sige_catal ogo.dbo.Ctl_F_conCatalogoDescAlt(8,a.IdTipoDocumen to)+ '-' + rtrim(convert(varchar(20),a.IdDocumento))),1,3)
else '' end,
'Emision' = case
when a.IdTipoDocumento<>5 then sige_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,a.IdT ipoDocumento)+ '-' + rtrim(convert(varchar(20),a.IdDocumento))
when a.DocOrigen is null and a.IdTipoDocumento=5
then substring(isnull(sige_facturacion.dbo.fac_F_get_Nu mFactura (a.IdEmpresa,a.IdOficina,a.IdDocumento),sige_catal ogo.dbo.Ctl_F_conCatalogoDescAlt(8,a.IdTipoDocumen to)+ '-' + rtrim(convert(varchar(20),a.IdDocumento))),5,3)
else '' end,
'Numero' = case
when a.IdTipoDocumento<>5 then sige_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,a.IdT ipoDocumento)+ '-' + rtrim(convert(varchar(20),a.IdDocumento))
when a.DocOrigen is null and a.IdTipoDocumento=5
then substring(isnull(sige_facturacion.dbo.fac_F_get_Nu mFactura (a.IdEmpresa,a.IdOficina,a.IdDocumento),sige_catal ogo.dbo.Ctl_F_conCatalogoDescAlt(8,a.IdTipoDocumen to)+ '-' + rtrim(convert(varchar(20),a.IdDocumento))),9,10)
else a.DocOrigen end,
a.MontoDocumento as MontoDocumento,
'' as NumDocumentoPago,
'' as FormaPago,
'' as Observacion,
--a.IdCliente
'' as IngresoCaja,
'' as DepConfirmado,
'' as DepVerificado,
'' as Canjeado,
a.SaldoInicial
from cxc_CuentaxCobrar a,
cxc_DetalleCuentaxCobrar b
where a.IdEmpresa = @PI_IdEmpresa
and (a.IdOficina = @PI_IdOficina or @PI_IdOficina = @PI_IdOficina)
and (a.IdCliente = @PI_IdCliente or @PI_IdCliente=0)
and a.Estado <> 'E'
and a.SaldoInicial = @PI_SaldoInicial
and a.IdEmpresa = b.IdEmpresa
and a.IdOficina = b.IdOficina
and a.IdTipoDocumento = b.IdTipoDocumento
and a.IdDocumento = b.IdDocumento
and a.FechaIngreso between (case when @PI_FechaInicial = '' then '01-01-1900' when @PI_FechaInicial <> '' then @PI_FechaInicial end) and @PI_FechaFinal
--DETALLE DE MOVIMIENTOS--ANTICIPOS

ELSE
BEGIN


--PAGOS CON FORMA DE PAGO SOLO CON RETENCION
select
--a.IdEmpresa,
sige_participante.dbo.Par_F_getNombreParticipante( a.IdEmpresa) as Empresa,
--a.IdOficinaCobro as IdOficina,
sige_participante.dbo.Par_F_getNombreParticipante( a.IdOficinaCobro) as OficinaCobro,
sige_participante.dbo.Par_F_getNombreParticipante( a.IdCliente) as Cliente,
sige_participante.dbo.Par_F_getIdUsuario(a.IdClien te) as UsrCliente,
convert(varchar(10),a.FechaCobro,101) as FechaDocumento,
sige_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,c.IdT ipoDocumento) as TipoDocumento,
'Venta' = case
when c.IdTipoDocumento<>5 then sige_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,c.IdT ipoDocumento)+ '-' + rtrim(convert(varchar(20),c.IdDocumento))
when d.DocOrigen is null and c.IdTipoDocumento=5
then substring(isnull(sige_facturacion.dbo.fac_F_get_Nu mFactura (c.IdEmpresa,c.IdOficina,c.IdDocumento),sige_catal ogo.dbo.Ctl_F_conCatalogoDescAlt(8,c.IdTipoDocumen to)+ '-' + rtrim(convert(varchar(20),c.IdDocumento))),1,3)
else '' end,
'Emision' = case
when c.IdTipoDocumento<>5 then sige_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,c.IdT ipoDocumento)+ '-' + rtrim(convert(varchar(20),a.IdDocumento))
when d.DocOrigen is null and c.IdTipoDocumento=5
then substring(isnull(sige_facturacion.dbo.fac_F_get_Nu mFactura (c.IdEmpresa,c.IdOficina,c.IdDocumento),sige_catal ogo.dbo.Ctl_F_conCatalogoDescAlt(8,c.IdTipoDocumen to)+ '-' + rtrim(convert(varchar(20),c.IdDocumento))),5,3)
else '' end,
'Numero' = case
when c.IdTipoDocumento<>5 then sige_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,c.IdT ipoDocumento)+ '-' + rtrim(convert(varchar(20),a.IdDocumento))
when d.DocOrigen is null and c.IdTipoDocumento=5
then substring(isnull(sige_facturacion.dbo.fac_F_get_Nu mFactura(c.IdEmpresa,c.IdOficina,c.IdDocumento),si ge_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,c.IdTip oDocumento)+ '-' + rtrim(convert(varchar(20),c.IdDocumento))),9,10)
else d.DocOrigen end,
(b.MontoRImpRenta) *-1 as MontoDocumento,
sige_catalogo.dbo.Ctl_F_conCatalogoDescAlt(8,13)+ '-' + cast(a.IdReciboCobro as varchar) as NumDocumentoPago,
'RETENCION' as FormaPago,
a.Observacion,
'' as IngresoCaja,
'' as DepConfirmado,
'' as DepVerificado,
'' as Canjeado,
'P' as SaldoInicial
INTO #TempRetencion
from cxc_Cobro a,
cxc_DetalleCobro b,
cxc_DetalleCuentaxCobrar c,
cxc_CuentaxCobrar d
where a.IdEmpresa = @PI_IdEmpresa
and (a.IdOficinaCobro = @PI_IdOficina or @PI_IdOficina = @PI_IdOficina)
and (a.IdCliente = @PI_IdCliente or @PI_IdCliente=0)
and a.MontoTotal = 0
and a.Estado <> 'E'
---
and a.IdEmpresa = b.IdEmpresa
and a.IdOficinaCobro = b.IdOficinaCobro
and a.IdReciboCobro = b.IdReciboCobro
---
and b.IdEmpresa = c.IdEmpresa
and b.IdOficina = c.IdOficina
and b.IdTipoDocumento = c.IdTipoDocumento
and b.IdDocumento = c.IdDocumento
---
and c.IdEmpresa = d.IdEmpresa
and c.IdOficina = d.IdOficina
and c.IdTipoDocumento = d.IdTipoDocumento
and c.IdDocumento = d.IdDocumento
and a.FechaCobro between (case when @PI_FechaInicial = '' then '01-01-1900' when @PI_FechaInicial <> '' then @PI_FechaInicial end) and @PI_FechaFinal
and d.SaldoInicial = @PI_SaldoInicial


select *from #TablaAnticipos
UNION
select *from #TablaAnticipos1
UNION
select *from #TablaCuentaxCobrar
UNION
select *from #TablaCuentaxCobrar1
UNION
select *from #TempFormaPago
UNION
select *from #TempRetencion
UNION
select *from #TempReembolsos
UNION
select *from #TempCreditosAbiertos

drop table #TablaCuentaxCobrar
drop table #TablaCuentaxCobrar1
drop table #TablaAnticipos
drop table #TablaAnticipos1
END


gracias por la ayuda. y eso que recorté mi codigo; es de 282 lineas.

Perdona que no haya respondido de inmediato, apenas me he dado el tiempo de revisar tu código.
Aunque al principio te iba a sugerir que trataras de eliminar las tablas temporales, creo que no son un gran problema en esta consulta comparada con la enorme cantidad de llamadas a funciones en las consultas. Aunque podrías intentar usar common table expresions en lugar de las tablas temporales.
Creo que tu problema de performace, son todas las llamadas a funciones.
SQL Server es muy eficiente procesando conjuntos de registros. Para obtener los resultados requeridos el motor tiene a su disposición varios operadores, por ejemplo: Hash Match, Merge Join, Nested Loops, etc. Incluso cuando no obtienes los resultados mediante joins sino subqueries, el optimizador de consultas puede optar por los mismos operadores que usaría en joins.
Sin embargo, al emplear funciones en lugar operadores relacionales dejas de procesar conjuntos de registros, para procesar fila por fila. Probablemente no tan lento como un cursor, pero varias ordenes de magnitud más lento que con operadores relacionales. Según mi opinión, allí está tu mayor pérdida en performance.

Que tal Luis, veo que le tratas de sacar provecho al foro.

Por cierto, sería también de mencionar también que las funciones pertenecen a otras bases de datos.

Beakdan, disculpa la ignorancia, cual es el mencionado Optimizador de Consultas?

Yo trabajé con luisespoch hace algún tiempo, por lo que conozco la estructura de su base y la manera de programar y según los maestros que diseñaron los "estándares" de como trabajar en los procedimientos para procesar datos optaron por trabajar con tablas temporales, para barrernos la tabla temporal en lugar de utilizar cursores. Y de allí se generalizó el uso de tablas temporales.

El uso de las funciones es otro estándar. Si quiero consultar un dato de otra base, no puedo hacer un select a la tabla de la otra base sino que tengo que hacer una función que haga el select en la otra base y se debe utilizar la función.

No tengo ningun interés directo en el tema, solo es por curiosidad y para saber que medidas tomar en estos casos. Se pueden solucionar estos problemas con el menor impacto a los procedimientos hechos?

Developer9

"El uso de las funciones es otro estándar. Si quiero consultar un dato de otra base, no puedo hacer un select a la tabla de la otra base sino que tengo que hacer una función que haga el select en la otra base y se debe utilizar la función."

Esto que comentas, no es del todo cierto, para hacer un SELECT de una tabla que esta en OTRA BASE, es tan simple como utiliziar UNC.

SELECT * FROM tuserver.tubase.dbo.tutabla WHERE..............

Claro eso ya lo se. Y sí que lo utilicé en secreto

Me refería a que ese era el estándar con el que se trabaja en el proyecto en el que está luisespoch (en el cual yo trabajé un año)

Si le haces un select a una tabla de otra base y te descubren....

je je, yo en algunos casos me saltaba esa norma por rapidez

El optimizador de consultas (busca query optimizer) es el componente del motor de SQL que determina la mejor manera de ejecutar tu consulta tratando de obtener el resultado más rápido con un mínimo impacto en los recursos del sistema. Esa podría ser una definición.
Dicho componente determina si una consulta dada usará o no un índice, y qué tipo de operación realizará (Table Scan, Index Seek, Index Scan, Merge Join, Loop Join, Hash Match, etc.), si usará o no paralelismo, etc. No es directamente accesible por el usuario, sin embargo, con los "optimizer hints" puedes modificar la manera en que se construye el plan de ejecución para una consulta determinada.

A veces he tenido que trabajar con estándares locales de los cuales no debo desviarme. Sin embargo, muchos de estos estándares han sido implementados por programadores, no por dba's.

Si estoy programando en C++, C#, VB, o lo que quieras, en general es una muy buena idea reusar código con funciones. Una función que calcula el RFC suena como algo útil. En SQL también lo puede ser. Sin embargo una función que busca en una tabla un dato a partir de un ID, puede no ser tan buena idea. El motivo es que si la vas a emplear en un where, esta será invocada una vez quizá. Pero si la usas en el juego de resultados, esta será invocada tantas veces como filas haya en tu resultado. Y el tiempo acumulado de las sucesivas invocaciones puede ser significativo, mucho más si la consulta en la función no tiene un índice adecuado.

En cuanto a las tablas temporales, te puedo decir que en general son usadas en demasía. Algunas veces, pueden ser muy útiles. He trabajado durante los últimos dos años con datawarehouses y datamarts, haciendo a veces joins entre tablas con millones de registros, y en general una tabla temporal es mí última alternativa. El costo de una tabla temporal es por el tiempo que demora su creación física. Y porque en general al crear una tabla temporal nadie les crea indices (lo cual también implica un costo).

Quizá con unos cuantos miles de de registros no tengan problemas, pero si han trabajado con tablas verdaderamente masivas, sabrán que con esta clase de consultas... sin comentarios.

PD: Si buscas RBAR (row by agonizing row) en el contexto de SQL, encontrarás muchos ejemplo de consultas que aparentemente están bien construidas, y que sin embargo operan fila a fila.

oye Dev... y a que obedecía ese estandar? se me hace medio bobo no?

Saludos!

Hola, además de lo que comentan arriba (que me parece lo mejor) también puedes agregar unos índices en las tablas temporales, después de los "inserts" y antes de los "selects". El tiempo de creación de cada índice podría ser menor al tiempo en que tardan las consultas sin índices respecto de las mismas con los índices. Ahí tienes que ver en cuales campos pones los índices (los que estan en los join y los where de preferencia)

saludos