September 27, 2012 at 2:42 pm
I am trying to get an idea of how much records I will be processing from a transactional table during a rollup process. The records are to be rolled up by week for Store, Product & Customer. I am grouping by these columns the query is a simple count (*). My question is how long is too long to be waiting for a count. My query looks something like this:
--Define procedure variables
DECLARE@CalendarWeekKey SMALLINT
-- Holds a list of
CREATE TABLE #CalendarKeys
(
CalendarDayKey SMALLINT PRIMARY KEY
,CalendarWeekKey SMALLINT
)
-- Load #CalendarKeys with
INSERTINTO #CalendarKeys
(
CalendarDayKey
,CalendarWeekKey
)
SELECT DISTINCT
CalendarDayKey
,CalendarWeekKey
FROMdbo.DimCalendarDay dcd
WHERELastProcessed = 1
SELECTcount(*) as Total
FROMdbo.FactTransactions ft
JOIN #CalendarKeys ck ON ft.CalendarDayKey = ck.CalendarDayKey
GROUP BY ft.ProductKey
,ft.StoreKey
,ft.CustomerKey
I have been running about 30 minutes -- thoughts
September 27, 2012 at 2:46 pm
Mark F-428640 (9/27/2012)
I am trying to get an idea of how much records I will be processing from a transactional table during a rollup process.I have been running about 30 minutes -- thoughts
Your indexes aren't aligning to what you're trying to do. The DISTINCT is another grouping structure, FactTables may not have the index you need, so my guess is you're clustered index scanning *everything*, particularly since you didn't also add an index to your #Temp.
What's the schemas on these underlying tables (with indexes)? That'll help us figure out what else you might want here.
Edit: I should also probably ask how much data are you looking at here? It might just be cost of doing business if it's large enough but 30 minutes seems extreme.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 27, 2012 at 3:26 pm
Mark F-428640 (9/27/2012)
INSERTINTO #CalendarKeys(
CalendarDayKey
,CalendarWeekKey
)
SELECT DISTINCT
CalendarDayKey
,CalendarWeekKey
FROMdbo.DimCalendarDay dcd
WHERELastProcessed = 1
Does your calendar table have duplicate rows?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply