Low Performance Using View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Low Performance Using View

Author

Topic

vicentepietrobon
Starting Member

4 Posts

Posted - 2013-01-08 : 07:46:40

Hello, I'm new here, but i think i can get any help here with some new friends.

I'm using a view and it take almost 4 min to run, when i take the select inside those view and run in a new query window it's execute in like 9 seconds and i don't understand why on the view it took 4 min.

There is my query:

SELECT ANO, MES, SETOR.ZY_CENTROC AS CCUSTO, TOTAL.Z0I_CODMC,
SUM(SETOR.QUTDEV*TOTAL.VLRUNT) AS VALOR
FROM (

SELECT LEFT(Z0I_DTPGTO,4) AS ANO, SUBSTRING(Z0I_DTPGTO,5,2) AS MES, Z0I_DATADE,Z0I_DATAAT, Z0I_SITIO, Z0I_VALOR, Z0I_CODMC, Z0I_EXTRA,
SUM(CASE WHEN Z03_TIPO = 'E' THEN Z01_QTDE ELSE 0 END) AS QUTRET,
SUM(CASE WHEN Z03_TIPO = 'S' THEN Z01_QTDE ELSE 0 END) AS QUTDEV,
CASE WHEN SUM(CASE WHEN Z03_TIPO = 'S' THEN Z01_QTDE ELSE 0 END) <> 0 THEN (Z0I_VALOR+Z0I_EXTRA)/(SUM(CASE WHEN Z03_TIPO = 'S' THEN Z01_QTDE ELSE 0 END)) ELSE 0 END AS VLRUNT,
Z0I_DTPGTO
FROM Z0I010 Z0I
LEFT JOIN Z02010 AS Z02 ON Z02_DATA BETWEEN Z0I_DATADE AND Z0I_DATAAT AND Z02.D_E_L_E_T_ = ''
AND Z02.Z02_FILIAL = Z0I.Z0I_FILIAL
LEFT JOIN SZY010 AS SZY ON SZY.D_E_L_E_T_ = '' AND Z02_SETOR = ZY_CODIGO AND ZY_CODSITI = Z0I_SITIO AND Z02.Z02_FILIAL = SZY.ZY_FILIAL
LEFT JOIN SZX010 AS SZX ON SZX.D_E_L_E_T_ = '' AND ZY_CODSITI = ZX_CODIGO AND SZX.ZX_FILIAL = SZY.ZY_FILIAL
LEFT JOIN Z03010 AS Z03 ON Z03.D_E_L_E_T_ = '' AND Z02_TIPO = Z03_CODIGO AND Z03_SEINTE = 'V' AND Z03_USADO IN ('1','3') AND Z02.Z02_FILIAL = Z03.Z03_FILIAL
LEFT JOIN Z01010 AS Z01 ON Z01_DOC = Z02_DOC AND Z01.D_E_L_E_T_ = '' AND Z01_CODMC = Z0I_CODMC AND Z02.Z02_FILIAL = Z01.Z01_FILIAL
WHERE Z0I.D_E_L_E_T_ = '' AND Z0I_DTPGTO >= '20120101'
AND ZY_CODSITI IS NOT NULL AND ZX_CODIGO IS NOT NULL
GROUP BY LEFT(Z0I_DTPGTO,4), SUBSTRING(Z0I_DTPGTO,5,2), Z0I_DATADE,Z0I_DATAAT, Z0I_SITIO, Z0I_CODMC,Z0I_VALOR,Z0I_EXTRA, Z0I_DTPGTO
) AS TOTAL

LEFT JOIN (

SELECT ZY_CODSITI, Z02_SETOR, Z02_DATA, Z01_CODMC, ZY_CENTROC,
SUM(CASE WHEN Z03_TIPO = 'E' THEN Z01_QTDE ELSE 0 END) AS QUTRET,
SUM(CASE WHEN Z03_TIPO = 'S' THEN Z01_QTDE ELSE 0 END) AS QUTDEV
FROM Z02010 AS Z02
LEFT JOIN SZY010 AS SZY
ON SZY.D_E_L_E_T_ = '' AND Z02_SETOR = ZY_CODIGO
LEFT JOIN Z03010 AS Z03 ON Z03.D_E_L_E_T_ = '' AND Z02_TIPO = Z03_CODIGO AND Z03_SEINTE = 'V' AND Z03_USADO IN ('1','3') AND Z02.Z02_FILIAL = Z03.Z03_FILIAL
LEFT JOIN Z01010 AS Z01 ON Z01.D_E_L_E_T_ = '' AND Z01_DOC = Z02_DOC AND Z01_CODMC IN ('00','01','02','03','04','05','06','07','09','10','11','12','13','14','15','21','70','73','75','77')
WHERE Z02.D_E_L_E_T_ = ''
AND Z01_CODMC IS NOT NULL AND Z03_CODIGO IS NOT NULL
GROUP BY ZY_CODSITI,Z02_SETOR,Z02_DATA,Z01_CODMC, ZY_CENTROC

) AS SETOR

ON SETOR.Z02_DATA BETWEEN TOTAL.Z0I_DATADE AND TOTAL.Z0I_DATAAT AND SETOR.Z01_CODMC = TOTAL.Z0I_CODMC AND SETOR.ZY_CODSITI = TOTAL.Z0I_SITIO
WHERE Z02_SETOR IS NOT NULL

GROUP BY ANO, MES, SETOR.ZY_CENTROC, TOTAL.Z0I_CODMC, TOTAL.Z0I_SITIO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-08 : 07:54:09

Have you looked at the query plan?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vicentepietrobon
Starting Member

4 Posts

Posted - 2013-01-08 : 07:56:19

The execution plan?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-08 : 08:04:55

Try ctrl-L or query - include actual execution plan.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vicentepietrobon
Starting Member

4 Posts

Posted - 2013-01-08 : 08:09:33

allright, i did that, but i don't understand too much what it means, if you want i can save the execution plan and sent to you.
I'm not really a DBA, I'm new from that :/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-08 : 08:19:35

You should see some big thick lines showing where it is using most resources.
Also look out for tables scans of large tables.

Compare it with the plan of the query run outside the view and you should see where the problem is

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vicentepietrobon
Starting Member

4 Posts

Posted - 2013-01-08 : 08:24:48

Ok, i will do that and try understand what's happening.
Thanks by the way;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources