Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » SQL Server »

Optimizar query

 Estas en el tema de Optimizar query en el foro de SQL Server en Foros del Web. Buenas foro. Tengo un a query en sql server pero tarda mucho , aunque solo envie un cliente y los calculos sean muy rapidos tarda ...
  #1 (permalink)  
Antiguo 22/03/2017, 15:53
Avatar de jandrogdz  
Fecha de Ingreso: julio-2012
Ubicación: public $Guadalajara
Mensajes: 397
Antigüedad: 12 años, 4 meses
Puntos: 12
Pregunta Optimizar query
Buenas foro.

Tengo un a query en sql server pero tarda mucho , aunque solo envie un cliente y los calculos sean muy rapidos tarda mucho, alguien me podria apoyar con sus consejos a optimizar la query.

De antemano muchas gracias.

Código SQL: 
Ver original
  1. DECLARE @pFechaDel DATE = '2017-03-22'
  2. DECLARE @pFechaAl DATE = '2017-03-22'
  3. DECLARE @IdGuids nvarchar(MAX) =  '7078a725-ca59-4b71-bc56-21b8f456d63b'
  4.  
  5. SELECT isnull(SUM(CH.Total),0) AS Facturas, 
  6.  
  7. isnull(COUNT(CH.Total),0) AS CountFacturas, 
  8. (SELECT isnull(SUM(P1.Monto),0) FROM Finanzas.PagosrecibidosCheque P1 INNER JOIN 
  9. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE  CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND  
  10. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS Cheques, 
  11. (SELECT isnull(COUNT(P1.Monto),0) FROM Finanzas.PagosrecibidosCheque P1 INNER JOIN 
  12. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE  CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND  
  13. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS CountCheques, 
  14. (SELECT isnull(SUM(P1.Monto),0) FROM Finanzas.PagosRecibidosTransf P1 INNER JOIN 
  15. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND 
  16. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS Transferencias, 
  17. (SELECT isnull(COUNT(P1.Monto),0) FROM Finanzas.PagosRecibidosTransf P1 INNER JOIN 
  18. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND 
  19. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS CountTransferencias, 
  20. (SELECT isnull(SUM(P1.Monto),0) FROM Finanzas.PagosRecibidosEfect P1 INNER JOIN 
  21. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND 
  22. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS Efectivos, 
  23. (SELECT isnull(COUNT(P1.Monto),0) FROM Finanzas.PagosRecibidosEfect P1 INNER JOIN 
  24. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND 
  25. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS CountEfectivos, 
  26. (SELECT isnull(SUM(P1.Monto),0) FROM Finanzas.PagosRecibidosTarjeta P1 INNER JOIN 
  27. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND 
  28. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS Tarjetas, 
  29. (SELECT isnull(COUNT(P1.Monto),0) FROM Finanzas.PagosRecibidosTarjeta P1 INNER JOIN 
  30. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND 
  31. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS CountTarjetas, 
  32. (SELECT isnull(SUM(CH1.Total),0) FROM Ventas.vCalculosHeader CH1 WHERE CH1.TipoDocto='V4' AND CH1.Cancelada=0 AND CONVERT(DATE, CH1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl 
  33. AND CH1.ClaveCliente IN (SELECT C.Clave FROM Clientes C WHERE C.IdGuid IN (@IdGuids))) AS NotasCredito, 
  34. (SELECT isnull(COUNT(CH1.Total),0) FROM Ventas.vCalculosHeader CH1 WHERE CH1.TipoDocto='V4' AND CH1.Cancelada=0 AND CONVERT(DATE, CH1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl 
  35. AND CH1.ClaveCliente IN (SELECT C.Clave FROM Clientes C WHERE C.IdGuid IN (@IdGuids))) AS CountNotasCredito, 
  36. (SELECT isnull(SUM(CH1.Total),0) FROM Ventas.vCalculosHeader CH1 WHERE CH1.TipoDocto='V6' AND CH1.Cancelada=0 AND CONVERT(DATE, CH1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl 
  37. AND CH1.ClaveCliente IN (SELECT C.Clave FROM Clientes C WHERE C.IdGuid IN (@IdGuids))) AS DevolucionMcia, 
  38. (SELECT isnull(COUNT(CH1.Total),0) FROM Ventas.vCalculosHeader CH1 WHERE CH1.TipoDocto='V6' AND CH1.Cancelada=0 AND CONVERT(DATE, CH1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl 
  39. AND CH1.ClaveCliente IN (SELECT C.Clave FROM Clientes C WHERE C.IdGuid IN (@IdGuids))) AS CountDevolucionMcia, 
  40. (SELECT IsNull(SUM(b.Cargo)-SUM(b.Abono),0) 
  41. FROM Ventas.vDetalleClientes b 
  42. WHERE CONVERT(DATE, b.Fecha , 103) < @pFechaDel AND b.IdGuid IN (SELECT IdGuid FROM Ventas.vCalculosHeader WHERE Cancelada=0)
  43. AND b.ClaveCliente IN (SELECT C.Clave FROM Clientes C WHERE C.IdGuid IN (@IdGuids)) 
  44. ) AS SaldoIniFijo, 
  45. (SELECT IsNull(SUM(b.Cargo)-SUM(b.Abono),0) 
  46. FROM Ventas.vDetalleClientes b 
  47. WHERE CONVERT(DATE, b.Fecha , 103) <= @pFechaAl AND b.IdGuid IN (SELECT IdGuid FROM Ventas.vCalculosHeader WHERE Cancelada=0)
  48. AND b.ClaveCliente IN (SELECT C.Clave FROM Clientes C WHERE C.IdGuid IN (@IdGuids)) 
  49.  ) AS SaldoFinFijo, 
  50. datediff(d,@pFechaDel,@pFechaAl)+1 AS DifFechas
  51.  
  52.  
  53. FROM Ventas.vCalculosHeader CH WITH (INDEX = idx_ClaveCliente)
  54. WHERE CH.TipoDocto='V3' AND CONVERT(DATE, CH.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND Cancelada =0
  55. AND CH.ClaveCliente IN (SELECT C.Clave FROM Clientes C WHERE C.IdGuid IN (@IdGuids))
__________________
Lo imposible solo cuesta un poco mas
  #2 (permalink)  
Antiguo 22/03/2017, 16:13
Avatar de Libras
Colaborador
  
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Optimizar query
De sql server o de mysql? y ese query esta muy mal realizado, estas haciendo un query por cada columna y eso no esta nada bien, podrias empezar a optimizar el query quitando los queries duplicados y ponerlos en un solo query
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #3 (permalink)  
Antiguo 22/03/2017, 16:26
Avatar de jandrogdz  
Fecha de Ingreso: julio-2012
Ubicación: public $Guadalajara
Mensajes: 397
Antigüedad: 12 años, 4 meses
Puntos: 12
Respuesta: Optimizar query
Muchas Gracias por contestar.

Es en sql server y no se si podrias orientarme como hacerlo, soy nuevo en esto de sql.

Te lo agradeceria mucho Libras.
__________________
Lo imposible solo cuesta un poco mas
  #4 (permalink)  
Antiguo 22/03/2017, 17:00
Avatar de Libras
Colaborador
  
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Optimizar query
Sip te podria orientar, para empezar eso que estas haciendo esta muy mal, no tienes porque hacer 2 queries para sacar un sum y un count, y mucho menos poner un subquery en cada columna

por ejemplo:

Esto:
Código SQL: 
Ver original
  1. (SELECT isnull(SUM(P1.Monto),0) FROM Finanzas.PagosrecibidosCheque P1 INNER JOIN 
  2. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE  CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND  
  3. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS Cheques, 
  4. (SELECT isnull(COUNT(P1.Monto),0) FROM Finanzas.PagosrecibidosCheque P1 INNER JOIN 
  5. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE  CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND  
  6. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)AS CountCheques,

Puede quedar en esto:

Código SQL: 
Ver original
  1. SELECT isnull(SUM(P1.Monto),0) cheques, isnull(COUNT(p1.monto),0) countcheques  FROM Finanzas.PagosrecibidosCheque P1 INNER JOIN 
  2. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE  CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND  
  3. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0

Y asi susecivamente para los demas queries :)

Ahora @idguids, va a recibir mas de un valor? porque estas usando el IN?
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #5 (permalink)  
Antiguo 22/03/2017, 17:04
Avatar de jandrogdz  
Fecha de Ingreso: julio-2012
Ubicación: public $Guadalajara
Mensajes: 397
Antigüedad: 12 años, 4 meses
Puntos: 12
Respuesta: Optimizar query
Gracias por la yuda mi amigo, si @guids puede recibir mas de un valor por eso es que utilizo el IN o tu que me recomiendas hacer ahi...
__________________
Lo imposible solo cuesta un poco mas
  #6 (permalink)  
Antiguo 22/03/2017, 17:50
Avatar de jandrogdz  
Fecha de Ingreso: julio-2012
Ubicación: public $Guadalajara
Mensajes: 397
Antigüedad: 12 años, 4 meses
Puntos: 12
Respuesta: Optimizar query
Disculpa he modificado como me haz comentado pero me manda un error:

Código SQL: 
Ver original
  1. DECLARE @pFechaDel DATE = '2017-03-22'
  2. DECLARE @pFechaAl DATE = '2017-03-22'
  3. DECLARE @IdGuids nvarchar(MAX) =  '7078a725-ca59-4b71-bc56-21b8f456d63b'
  4.  
  5. -- Facturas...
  6. SELECT ISNULL(SUM(CH.Total),0) AS Facturas,
  7. ISNULL(COUNT(CH.Total),0) AS CountFacturas,
  8. (SELECT isnull(SUM(P1.Monto),0) cheques, isnull(COUNT(p1.monto),0) countcheques  FROM Finanzas.PagosrecibidosCheque P1 INNER JOIN 
  9. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE  CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND  
  10. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0)
  11.     FROM Ventas.vCalculosHeader CH  
  12. WHERE CH.TipoDocto ='V3' AND CONVERT(DATE, CH.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND Cancelada = 0
  13. AND CH.ClaveCliente IN (SELECT C.Clave FROM Clientes C WHERE C.IdGuid IN (@IdGuids))

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
__________________
Lo imposible solo cuesta un poco mas
  #7 (permalink)  
Antiguo 23/03/2017, 06:50
Avatar de gnzsoloyo
Moderador criollo
  
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 17 años
Puntos: 2658
Respuesta: Optimizar query
Cita:
Es en sql server y no se si podrias orientarme como hacerlo, soy nuevo en esto de sql.
SQL Server NO es MySQL.

Movido al foro de SQL Server.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #8 (permalink)  
Antiguo 23/03/2017, 08:04
Avatar de Libras
Colaborador
  
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Optimizar query
Cita:
Iniciado por jandrogdz Ver Mensaje
Disculpa he modificado como me haz comentado pero me manda un error:


Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Lo que pasa es que el subquery esta regresando mas de un valor, cuando haces un subquery con mas de un valor no se usa como lo estas haciendo se hace de esta manera:

Código SQL: 
Ver original
  1. DECLARE @pFechaDel DATE = '2017-03-22'
  2. DECLARE @pFechaAl DATE = '2017-03-22'
  3. DECLARE @IdGuids nvarchar(MAX) =  '7078a725-ca59-4b71-bc56-21b8f456d63b'
  4.  
  5. -- Facturas...
  6. SELECT ISNULL(SUM(CH.Total),0) AS Facturas,
  7. ISNULL(COUNT(CH.Total),0) AS CountFacturas
  8.     FROM Ventas.vCalculosHeader CH  
  9.  
  10. ---aqui hay que ver con que campo podrias unir este query con el de ventas.....
  11. LEFT JOIN (SELECT isnull(SUM(P1.Monto),0) cheques, isnull(COUNT(p1.monto),0) countcheques  FROM Finanzas.PagosrecibidosCheque P1 INNER JOIN 
  12. Finanzas.PagosRecibidosHeader PR ON PR.IdGuid=P1.IdGuidHeader WHERE  CONVERT(DATE, P1.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND  
  13. PR.IdGuidCliente IN (@IdGuids) AND IsNull(PR.PagoCancelado,0)=0) AS completa ON(completa.id=ch.id)
  14.  
  15. WHERE CH.TipoDocto ='V3' AND CONVERT(DATE, CH.Fecha , 103) BETWEEN @pFechaDel AND @pFechaAl AND Cancelada = 0
  16. AND CH.ClaveCliente IN (SELECT C.Clave FROM Clientes C WHERE C.IdGuid IN (@IdGuids))
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #9 (permalink)  
Antiguo 27/03/2017, 12:52
Avatar de jandrogdz  
Fecha de Ingreso: julio-2012
Ubicación: public $Guadalajara
Mensajes: 397
Antigüedad: 12 años, 4 meses
Puntos: 12
Respuesta: Optimizar query
Muchas gracias por la ayuda ya he logrado optimizar esa query pero ahora en el momento de hacer calculos se tarda mucho en hacer una consulta.

Código SQL: 
Ver original
  1. DECLARE @pFechaDel DATE = '2017-03-01'
  2.  
  3.  
  4.      -- SALDOS
  5.     SELECT IsNull(SUM(b.Cargo)-SUM(b.Abono),0) AS SaldoIniFijo
  6.     FROM Ventas.vDetalleClientes b 
  7.     WHERE CONVERT(DATE, b.Fecha , 103) < @pFechaDel AND b.IdGuid IN (SELECT IdGuid FROM Ventas.vCalculosHeader WHERE Cancelada=0)
  8.     AND b.ClaveCliente IN (SELECT C.Clave FROM Clientes C WITH(INDEX = pk_Clientes) WHERE C.IdGuid IN ('CBA7C309-5D05-494F-8D2B-592E871697D3'))

Esta pequeña consulta puede tardar hasta 25 segundos con un solo cliente, como puedo hacer para que no tarde tanto
__________________
Lo imposible solo cuesta un poco mas
  #10 (permalink)  
Antiguo 27/03/2017, 12:55
Avatar de Libras
Colaborador
  
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Optimizar query
Quitale el IN y usa un EXISTS

porque forzas a usar el indice?

Código SQL: 
Ver original
  1. SELECT C.Clave FROM Clientes C WITH(INDEX = pk_Clientes) WHERE C.IdGuid IN ('CBA7C309-5D05-494F-8D2B-592E871697D3')
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #11 (permalink)  
Antiguo 27/03/2017, 13:28
Avatar de jandrogdz  
Fecha de Ingreso: julio-2012
Ubicación: public $Guadalajara
Mensajes: 397
Antigüedad: 12 años, 4 meses
Puntos: 12
Respuesta: Optimizar query
Que tal Libras.

Perdon le deje lo del index pero era sin esa parte, y he cambiado lo que me comentas de IN por EXISTS asi es como quedo

Código SQL: 
Ver original
  1. DECLARE @pFechaDel DATE = '2017-03-01'
  2.      -- SALDOS
  3.     SELECT IsNull(SUM(b.Cargo)-SUM(b.Abono),0) AS SaldoIniFijo
  4.     FROM Ventas.vDetalleClientes b 
  5.     WHERE CONVERT(DATE, b.Fecha , 103) < @pFechaDel 
  6.     AND EXISTS (SELECT IdGuid FROM Ventas.vCalculosHeader ch WHERE ch.IdGuid = b.IdGuid AND ch.Cancelada=0)
  7.     AND EXISTS (SELECT C.Clave FROM Clientes C WHERE C.Clave = b.ClaveCliente AND C.IdGuid IN ('CBA7C309-5D05-494F-8D2B-592E871697D3'))

Asi es como deberia de quedar?
__________________
Lo imposible solo cuesta un poco mas
  #12 (permalink)  
Antiguo 27/03/2017, 13:37
Avatar de Libras
Colaborador
  
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Optimizar query
Se ve mejor de esa manera :) mejoro el rendimiento del query??
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #13 (permalink)  
Antiguo 27/03/2017, 13:47
Avatar de jandrogdz  
Fecha de Ingreso: julio-2012
Ubicación: public $Guadalajara
Mensajes: 397
Antigüedad: 12 años, 4 meses
Puntos: 12
Respuesta: Optimizar query
Si en algunas veces si es mas rapida en otras tarda un poco mas, esto a que se debe?
__________________
Lo imposible solo cuesta un poco mas
  #14 (permalink)  
Antiguo 27/03/2017, 15:09
Avatar de Libras
Colaborador
  
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Optimizar query
Puede depender de los rangos de los parametros, el plan de ejecucion, si una tabla tiene muchos updates etc etc, revisaste el plan de ejecucion de tu query?
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #15 (permalink)  
Antiguo 27/03/2017, 15:19
Avatar de jandrogdz  
Fecha de Ingreso: julio-2012
Ubicación: public $Guadalajara
Mensajes: 397
Antigüedad: 12 años, 4 meses
Puntos: 12
Respuesta: Optimizar query
En realidad no habia escuchado sobre el plan de una query es que trata o como saber eso
__________________
Lo imposible solo cuesta un poco mas
  #16 (permalink)  
Antiguo 27/03/2017, 15:25
Avatar de Libras
Colaborador
  
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Optimizar query
Ejecuta tu query con ctrl+L desde el sql management, y ahi te van a aparecer una imagen
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #17 (permalink)  
Antiguo 29/03/2017, 10:30
Avatar de iislas
Colaborador
  
Fecha de Ingreso: julio-2007
Ubicación: Mexico, D.F.
Mensajes: 6.482
Antigüedad: 17 años, 4 meses
Puntos: 180
Respuesta: Optimizar query
Aqui un TUTORIAL BASICO para interpretar los planes de ejecucion:

http://devtroce.com/2011/08/04/tutor...de-sql-server/
__________________
MCTS Isaias Islas

Etiquetas: mysql, sql
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta


« Tema Anterior | Próximo Tema »


La zona horaria es GMT -6. Ahora son las 11:16.