intermittent performance issue of one query

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 2008 Forums

SQL Server Administration (2008)

intermittent performance issue of one query

Author

Topic

bigbelly
Starting Member

39 Posts

Posted - 2010-11-02 : 21:56:07

Hi Gurus

Recently I started to have a time-out issue with one query which used to run very well on server1:

select * from table1 
where col1 >= dateadd(dd, -1, getdate())
and col1 < getdate() 
and col2 in 
(
select col2 from table1 
where col1 >= dateadd(dd, -1, getdate())
and col1 < getdate() 
group by col2 having count(*) > 1
)

I found only if the subquery returns empty record, the whole query will get time-out INTERMITTENTLY(some days no problem. some days time-out). (Note: if I just run subquery, it could return records, empty or not, instantly.)

To double confirm it's an intermittent issue, today we exported table1's data to server2 and run same query on both servers.
On server1 the query get time-out because subquery returns no record for today while query runs on server2 has no problem at all.

anyone has similar experience before? please shed some lights for me. thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-02 : 23:46:50

love the screen name big...

Is col1 and col2 indexed? when did you last rebuild indexes? tried updating stats with full scan?

bigbelly
Starting Member

39 Posts

Posted - 2010-11-03 : 01:58:08

thanks russell.

col1 is indexed while col2 is not.

However I don't think indexing is the main reason. Because only when subquery returns empty records, then intermittently (normally in different days instead of within one day) query will get time-out. When subquery could return non-empty records, the whole query speed is quite fast. Plus it's always quite fast to run subquery part.

Moreover, if I change the code to:

select table1.* from table1, 
(
select col2 from table1 
where col1 >= dateadd(dd, -1, getdate())
and col1 < getdate() 
group by col2 having count(*) > 1
) ABC
where table1.col1 >= dateadd(dd, -1, getdate())
and table1.col1 < getdate() 
and table1.col2 = ABC.col2

then no problem at all. It got to have something to do with IN syntax.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources