?
Solved

Single Column Counter VS Multi Column calculation

Posted on 2011-09-07
5
Medium Priority
?
352 Views
Last Modified: 2012-05-12
I have a table called users, prizes and point_transactions each with records into the millions.
In order to determine the number of available points per user, without using a view, should I:
     A) Track a new column in the users table called 'available_points' and UPDATE the column by incrementing or decrementing this field when they add or spend points and derive the available points with the query "SELECT available_points FROM users WHERE user_id = $id"

     or

     B) Use the existing columns and derive the available points with the SELECT/calculation:
"SELECT (SELECT SUM(points_purchased) FROM point_transactions WHERE user_id = $user_id) - (SELECT SUM(prize_amunt) FROM prizes WHERE user_id = $user_id) AS points_available"?

Please explain why your choice would be more efficient or yield better performance over the alternative.
0
Comment
Question by:aristanoble
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36499253
I think I might segment the tables into a "basis" and a "transactions" table.  Of course you would keep an archive of all the transactions so you could be auditable, but every day (or perhaps week) you would lock the tables, make the summations, and reestablish the contents of the basis table.

This will work faster than a large SELECT SUM query because it will access fewer rows.  It will be more technologically defensible because even a single query error could corrupt the available points values.

That's my $0.02.  HTH, ~Ray
0
 
LVL 42

Accepted Solution

by:
dqmq earned 1000 total points
ID: 36499277
Advantages of A:
More efficient during retrieval of the total points
Less complex to retrieve points
Retrieval of total points does not degrade as number of transactions increases.

Advantages of B:
More efficient when accumulating or using points
Higher degree of integrity (only one way to determine points)

I cannot advise which is better; it all depends on where you are experiencing the most pain.  Or, rather, which type of performance is most critical.  If properly indexed, option A will be mostly sensitive to the number of transactions for the user of interest, not to the millions of transactions.

A couple of asides:

1.  You expressly want to avoid a view.  To me, that's kind of silly, as both options can be conveniently implemented with a view.  

2.  I would do option A with a trigger or a materialized view

3.  I would consider different sql for option b:

SELECT U.user_id, P.points as available_points
  from user_table u
  inner join
  (select user_id, sum(points_purchased) as points  FROM point_transactions
   group by user_id
   union all
   Select user_id, sum(prize_amount) * -1 FROM prizes
   group by user_id) as P on p.user_id = u.user_id
where u.user_id = $user_id







0
 
LVL 4

Assisted Solution

by:nfaria
nfaria earned 1000 total points
ID: 36503727
As you have millions of records in each table maybe I would go for a C


I would create in the users table not one but two columns user_points_purchased and user_prizes_amount.

Then ceate stored procedures for purchasePoints() and addPrize() that in a transaction (assuming you have InnoDB) writes in both tables.

I would get the value you want with simply SELECT user_points_purchased - user_prizes_amount As points_available FROM users;

Whith this approach you garantee integrity and don´t ever have the need to do large SUMs to calculate the value you want.
0
 
LVL 1

Author Comment

by:aristanoble
ID: 36505007
hmmm @nfaria so it would be like Credits, Debits and (Credits - Debits = ) Balance.
0
 
LVL 4

Expert Comment

by:nfaria
ID: 36509087
yes, you should do some testing to see how much time you gain in reading data and how much time you loose on writing.

But I think you would have a much faster reading for a tiny bit slower insert.

0

Featured Post

Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question