Jump to content
filipex_27

Performance Query

Recommended Posts

filipex_27

Boas...tenhos uma query do tipo

SELECT top 1000 ft1.dateandtime AS data_inicio,
(SELECT TOP 1 dateandtime
	 FROM [dbo].[floattable]
	 WHERE val = 0
AND tagindex = ft1.tagindex
AND dateandtime > ft1.dateandtime) AS data_fim,
tagname, ft1.tagindex
FROM [dbo].[floattable] ft1
LEFT JOIN [dbo].[tagtable]
ON ft1.tagindex = tagtable.tagindex
WHERE ft1.status = ''
AND ft1.val = 1
AND ft1.tagindex <> 1
AND (tagname LIKE ('%PEG__%')
OR tagname LIKE ('%PED__%'))

o meu problema está no tempo de execução(perde muito tempo para calcular a data_fim)...têm alguma ideia que me permita melhorar a performance da mesma??

A base de dados é sql server 2000 :(

o val = 1 significa a abertura, o val=0 fecho!

Share this post


Link to post
Share on other sites
M6

Assim a olho nú, diria para passare esse SQL da cláusula SELECT para um join ou para uma Common Table Expression (CTE).

Mas a melhor abordagem é veres o execution plan e veres onde a query está a demorar tempo e proquê.


10 REM Generation 48K!
20 INPUT "URL:", A$
30 IF A$(1 TO 4) = "HTTP" THEN PRINT "400 Bad Request": GOTO 50
40 PRINT "404 Not Found"
50 PRINT "./M6 @ Portugal a Programar."

 

Share this post


Link to post
Share on other sites
AntonioMateus

O top é caro, e estas a fazer um TOP 1 x TOP 1000, se fizeres uma derivada com GROUP BY e fizeres um LEFT JOIN com ela consegues a mesma coisa e de certeza que ficará bem mais rápido!

Edited by AntonioMateus

Share this post


Link to post
Share on other sites
xBoShY
SELECT top 1000 ft1.dateandtime AS data_inicio, min(ft2.dateandtime) as data_fim, tagtable.tagname, ft1.tagindex
FROM [dbo].[floattable] as ft1
LEFT JOIN [dbo].[floattable] as ft2
ON ft2.tagindex = ft1.tagindex AND ft2.dateandtime > ft1.dateandtime AND val = 0
LEFT JOIN [dbo].[tagtable] as tagtable
ON ft1.tagindex = tagtable.tagindex
WHERE ft1.STATUS = ''
AND ft1.val = 1
AND ft1.tagindex <> 1
AND (tagtable.tagname LIKE ('%PEG__%')
OR tagtable.tagname LIKE ('%PED__%'))
GROUP BY ft1.dateandtime, tagtable.tagname, ft1.tagindex

Edited by xBoShY

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.