SQL Server Performance Forum – Threads Archive

Data "near" the query – takes too long

Hi all, I have an Alarm-Database (SQL2000) looking like this: EventStampUTC / Tagname / AlarmState
2006-06-25 08:23 / Alarm1 / On
2006-06-25 08:34 / Alarm2 / On
2006-06-25 08:47 / Alarm2 / Off
2006-06-25 09:31 / Alarm1 / Off
2006-06-25 10:11 / Alarm2 / On
2006-06-25 10:28 / Alarm2 / Off
2006-06-25 10:40 / Alarm2 / On
2006-06-25 11:56 / Alarm2 / Off
2006-06-25 14:20 / Alarm1 / On
(filesize about 2GB!) The user queries by EventStampUTC and Tagname
If the query is from 10:00 to 11:00 I donÂ´t know the AlarmState of "Alarm1"
So I wrote this query: SELECT EventStampUTC, Tagname, Alarmstate FROM v_Alm2Trend WHERE
EventStampUTC >= ‘06.28.2006 10:00:00’ AND EventStampUTC <= ‘06.28.2006 11:00:00’
AND Tagname IN (‘Alarm1′,’Alarm2’)
UNION ALL
SELECT MAX(EventStampUTC) as EventStampUTC, Tagname, Alarmstate FROM v_Alm2Trend WHERE
EventStampUTC < ‘06.28.2006 10:00:00’
AND Tagname IN (‘Alarm1′,’Alarm2’)
GROUP BY Tagname,AlarmState
ORDER BY Tagname,EventStampUTC The query works, but it takes 1 minute to complete
Can someone optimize it? Thank you
Manfred

what does the execution plan show, operations and estimated row count

You have not posted about which indexes on what columns you have on this table

also run the two SELECT’s separately without the UNION ALL,
sometimes this can cause problems but i am thinking that the second one is what is causing your problem

By the way, we do have a German speaking forum here. Just in case…[<img src=’/community/emoticons/emotion-1.gif’ alt=’

Sorry, forgot to post the URL. You can find it here:http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=47 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis By the way, we do have a German speaking forum here. Just in case…[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Frank pls stop privatising the thread[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

[<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] You can always opt for your own Indian language forum here. I’m sure Brad is all ears. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

quote:Originally posted by ranjitjain You have not posted about which indexes on what columns you have on this table

It is not "my" database-design. I can only create views. Can I create an index inside a view also? (newbie…)

quote:Originally posted by joechang what does the execution plan show, operations and estimated row count

IÂ´m querying a view, so the execution plan is very complicated.
Now I get 18 rows back
Estimated Rowcount 519
Subtree Costs 478 I tried to query the 2 SELECT Statements seperately.
I know that the performance-problem is the "MAX(EventStampUTC)"
Is there another way? I tried with "TOP 10" but it brings me data from 2 months ago->DB is not sorted by EventStampUTC…

without being able to build suitable indexes to your needs
try the lower query with both lower and upper bounds on the date,
EventStampUTC BETWEEN A AND B, with a range no more than a few days

Yes, you can create an index on a view. This is smartly called "Indexed View" and greatly explained in the Books Online. I don’t know if you have the German or English version of BOL, as in the German version search for "Indizierte Sicht(en)". Be sure you read closely through the explanations as indexed views come at a certain cost and should carefully be evaluated if they suit your needs. Also, it depends on your version (Standard or Enterprise) whether the optimiser automatically considers indexed views or not.
As Joe requested the execution plan, you can get it in text format when you use SET SHOWPLAN_TEXT_ON GO and then execute your query in Query Analyzer. That result can you post here. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Thank you all for your answers! I can not create indexes on the view because the view doesnÂ´t fit the requirements for indexes (UNION, …) IÂ´ll go on now studying the "Performance Audit" from this website…
http://www.sql-server-performance.com/sql_server_performance_audit.asp Manfred

]]>