Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following table Transactions. The table looks like this:

CREATE TABLE [dbo].[Transactions](
[TransactionID] [bigint] IDENTITY(1,10) NOT NULL,
[PlayerID] [bigint] NOT NULL,
[BalanceTransactionTypeID] [int] NOT NULL,
[BalanceTransactionSubTypeID] [int] NULL,
[PointDelta] [money] NULL,
[TransactionDate] [datetime] NOT NULL,

The table contains the following constraints and indexes.

CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED 
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Transactions_PlayerID] ON [dbo].    
[Transactions]
(
[PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,     
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON   [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Transactions_TransactionDate] ON [dbo].    
[Transactions]
(
[TransactionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
 DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
 [PRIMARY]

I am writting a query where the goal is to return the top 50 point earners, and write the results using SSIS into a seperate table that will be looked at daily. The job is scheduled to run once a day. The main issue is there are over 3 billion rows and growing by about 300K per day. I know the ultimate solution is to partition it etc since its never used in game and its just historical. We have plans to roll it up as well. For right now however I need to make this query run in a reasonable time. I have tried using a CTE to get the player ids and the sum of the Delta then joining the result of that to another table by id to get the player details, I have tried using a straight up query with the join included all of which takes over 25 minutes to run the query. What can i do to tune this and make the query runable in a normal amount of time? If there is nothing let me know but I am really hoping there is some way to optomize this for the time being. As always thanks in advance for all help.

share|improve this question
    
The constraints and indexes are from another table, not from Transactions. –  ypercube Jan 31 '13 at 22:55
    
fixed and updated. –  deadLock Jan 31 '13 at 23:03
1  
I would store pre-calculated totals of your PointDelta in an indexed view, so that you do not have to query you large table. –  AlexKuznetsov Jan 31 '13 at 23:18
1  
Also I like your nick name. My system does not use SSIS at all. –  AlexKuznetsov Jan 31 '13 at 23:19
9  
Hi, thanks for the question and I hope you've found the answers helpful. I'd like to ask that you consider changing your nick name: some find it funny and relate to the sentiment, but I know for a fact that it has put at least one of our top experts off attempting to answer the question. I also personally find it offensive and unhelpful. –  Jack Douglas Feb 1 '13 at 7:58

2 Answers 2

We have plans to roll it up as well.

Cancel your other plans and do this now. Stop trying to optimise the evaluation of 3 billion rows and instead evaluate rollup + 300k rows. From your outline of the situation, anything else is a waste of time and someone's money.

A parody definition of insanity is doing the same thing over and over and expecting different results. Reading 3 billion rows that aren't changing fits that definition. Stop.

share|improve this answer
    
I agree 100% I am doing this in parallel however I am interested in knowing if there is any way to perform this optimization. I should also mention the report does not need to eval 3 billion rows as I am only looking for a specific BalanceTransactionTypeID and TransactionDate in the query. pseudo the query looks like this: SELECT TOP(50) PlayerID, RANK() OVER(ORDER BY SUM(PointDelta) DESC) AS PointsEarned FROM dbo.Transactions WHERE BalanceTransactionTypeID = 5 AND TransactionDate >= @TranDate but i do appreciate you confirming what i am doing for a "real solution" is correct! –  deadLock Jan 31 '13 at 23:28
    
I was more looking for something like would adding a non-clustered index to BalanceTransactionTypeID help etc... –  deadLock Jan 31 '13 at 23:29
2  
@SSISPissesMeOff You should post the query, given that it filters on some types, I would expect an index involving that column would help. I expect an NCI on the transaction type and date would indeed help a lot based on the query in your comment. –  Cade Roux Jan 31 '13 at 23:42
1  
@SSISPissesMeOff And INCLUDE the PlayerID and PointDelta columns - that index would be covering - also if it's just one (or a few) types for basically just this query - make a filtered index: mssqltips.com/sqlservertip/1785/… –  Cade Roux Jan 31 '13 at 23:47
    
@Cade Roux Please post the answer about NCI and the filtered index so I can accept it! Thank you. –  deadLock Feb 1 '13 at 1:25

You should post the query, given that it filters on some types, I would expect an index involving that column would help. I expect an NCI on the transaction type and date would indeed help a lot based on the query in your comment.

And INCLUDE the PlayerID and PointDelta columns - that index would be covering - also if it's just one (or a few) types for basically just this query - make a filtered index: http://www.mssqltips.com/sqlservertip/1785/sql-server-filtered-indexes-what-they-are-how-to-use-and-performance-advantages/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.