June 5, 2008 at 9:39 am
I've had great help on this forum in the past, Thanks to everyone who has contributed in the past to my solutions.
I have a query that is taking minutes to run and I'm wondering what and if i can do anything to speed it up.
Unfortunatly i have no ability to create or modify indexes and have no input on how the tables are being created.
Here is the query
Select
wdg.Widgetid,
Tr.Time,
Tr.Action
From Transaction Tr --22 Million Records, Clustered Index on Time,
--Non Clustered Grouped Index on WidgetId and Time
Join Widgets wdg
On Tr.Widgetid = wdg.Widgetid
And Tr.Time = (Select
TOP 1 Max(Receivedts)
From Transaction
Where Widgetid = wdg.Widgetid
)
Where Tr.WidgetType in (1,2)
And Tr.Action Not In(1,2,3,4,5)
Group By
wdg.Widgetid,
Tr.Time,
Tr.Action
Any help on this would be much appreciated. Thanks
June 5, 2008 at 10:01 am
Do you know all the actions? Can you change that to an "IN" instead of a "NOT IN"
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 5, 2008 at 10:03 am
You are using GROUP BY but have no aggregate funcitons in your select statement.
If you are doing this to remove duplicates, then try using
ROW_NUMBER() instead.
June 5, 2008 at 10:16 am
The DISTINCT keyword is the same as a group by with no aggregates. I don't know if it's faster or not.
June 5, 2008 at 10:40 am
Thanks for the initial suggestions everyone.
I tried removing the group by, and using an In instead of a Not in, isn't an option. I don't know all the actions, just with actions to not include.
The performance is still in the minutes.
I'm still open to any other suggestions..
Thanks
June 5, 2008 at 10:48 am
Have you tried getting rid of the correlated sub-query? That's bound to be hurting you.
Select
wdg.Widgetid,
Tr.Time,
Tr.Action
From Transaction Tr --22 Million Records, Clustered Index on Time,
--Non Clustered Grouped Index on WidgetId and Time
Join Widgets wdg
On Tr.Widgetid = wdg.Widgetid
join (select widgetID, Max(Receivedts) as latest
From Transaction
group by widgetID) currentTran
on wdg.widgetID=currentTran.widgetID
and tr.time=currentTan.latest
Where Tr.WidgetType in (1,2)
And Tr.Action Not In(1,2,3,4,5)
Group By
wdg.Widgetid,
Tr.Time,
Tr.Action
Of course - I'm guessing you're table-scanning on 22M rows, so your optimization choices might be limited.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 5, 2008 at 10:49 am
this part of the code could be fixed up
And Tr.Time = (Select
TOP 1 Max(Receivedts)
From Transaction
Where Widgetid = wdg.Widgetid
)
You shouldn't need to select TOP 1 and Max
Post some DDLs and sample data and we could offer some more help.
Why can't you create indexes?
June 5, 2008 at 11:03 am
In addition to the DDL and sample data, show us what the result set should look like based on the sample data.
I'm a little confused by the correlated subquery back into the Transaction table.
June 6, 2008 at 7:30 am
Is it not possible to replace not in (1,2,3,4,5) with >= 6?
Also, the in is the same as the OR operator: =1 OR = 2 OR = 3 etc.
So you can try replacing the 'IN' with a UNION. I have found that a UNION can sometimes be faster than using IN.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply