SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Low Performance Using View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vicentepietrobon
Starting Member

Brazil
4 Posts

Posted - 01/08/2013 :  07:46:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  07:54:09  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

vicentepietrobon
Starting Member

Brazil
4 Posts

Posted - 01/08/2013 :  07:56:19  Show Profile  Reply with Quote
The execution plan?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  08:04:55  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

vicentepietrobon
Starting Member

Brazil
4 Posts

Posted - 01/08/2013 :  08:09:33  Show Profile  Reply with Quote
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 :/
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  08:19:35  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

vicentepietrobon
Starting Member

Brazil
4 Posts

Posted - 01/08/2013 :  08:24:48  Show Profile  Reply with Quote
Ok, i will do that and try understand what's happening.
Thanks by the way;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000