Monday, May 26, 2008

Performance: Hours to Seconds

I was sent the following query. It was taking about two hours to run. After the 'ORDER BY's were added, it went down to seconds to run. One reason I'm surprised is each separate query wasn't pulling that many records: the first about 900; the second less than 100.
 Select Count(*), V1.User_id
From
( Select a.store_id, a.user_id
From a,b
Where a.store_id=b.store_id
And miscellaneous conditions
Order By a.store_id -- added by me
) V1,
( Select a.store_id, a.user_id
From a,b
Where a.store_id=b.store_id
And miscellaneous conditions
Order By a.store_id -- added by me
) V2
Where V1.store_id=V2.store_id
Group by V1.user_id
Order by V1.user_id;

No comments:

Post a Comment