0

Since I don't know to calculate efficiency I'll ask here and I hope someone could tell me what is better and explain it a bit.

The scenario:
Currently I have a table that insert rows of production of each worker.
Something like: (Worker1) produced (product10) with (some amount) for a Date.
And that goes for each station he worked in though the day.

The Question:
I need to generate a report of the sum of amounts that worker produced for each date. I know how to generate the report either way but the question is how is it more efficient?

Having to run a query for each person that sums up the production for each date? or having a table that I'll insert the total amount, workerID and date?

Again if you could explain it a bit further it would be nice, if not than at least an educated answer would help me a lot with this problem.

Example:
This is what I have right now in my production table: 

ID     EmpID     ProductID     Amount     Dateofproduction
----------------------------------------------------------
1      1         1             100        14/01/2013  
2      1         2             20         14/01/2012

This is what I want in the end:

EmpID    Amount    DateofProduction
-----------------------------------
1        120       14/01/2013

Should I start another table for this? or should I just sum what I have in the production table and take what I need?
Bear in mind that the production table will get larger and larger each day (of course).

Sagi Rokach
  • 147
  • 1
  • 2
  • 13
  • Can you please show your table schema/sample data and expected results based on that? :) Then someone can help you out here as it shows your current structure clearly. – bonCodigo Jan 14 '13 at 13:57

2 Answers2

1

Unless I am missing something, it sounds like you just want this:

select empid,
  sum(amount) TotalAmount,
  Dateofproduction
from yourtable
group by empid, Dateofproduction

See SQL Fiddle with Demo

Result:

| EMPID | TOTALAMOUNT | DATEOFPRODUCTION |
------------------------------------------
|     1 |         120 |       2013-01-14 |

Note: I am guessing that the second row of data you provided is supposed to be 2013 not 2012.

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • yeah I meant 2013 and I know how to write the sql query to pull the information out. The question is if it'll be more efficient to have a table that will do just that and hold the info for me just to select the dates I want? – Sagi Rokach Jan 14 '13 at 14:59
  • @SagiRokach how much data are you talking about? millions of records? – Taryn Jan 14 '13 at 15:01
  • About 1k records a month, and I gather that I don't need another table. I'll archive the table each year or so and it'll be fine. Thanks. – Sagi Rokach Jan 14 '13 at 21:53
  • @SagiRokach As long as you have indexes on the proper fields, then running this query should not cause any issues. 1k a month is very small to run a query against. – Taryn Jan 14 '13 at 21:54
1

i) Direct : 

select EmpId, sum(Amount) as Amount, DateOfProduction 
from ProductionTable
group by EmpId, DateOfProduction.

ii) Now, the size of the table will keep growing. And you need only day-wise reports.

Is this table being used by anyone else? Can some of the data be archived? If some of the data can be archived, I would suggest, after each day and reporting, backup all the data from this table to a secondary archive table. So, every day you will have to query only today's worth of records.

Secondly, you can consider adding an index to DateOfProduction. You will then be able to restrict your queries in date range. For example, select EmpId, sum(Amount) as Amount, DateOfProduction from ProductionTable group by EmpId, DateOfProduction where DateOfProduction = Date(now()). (or something similar)

Because it is just a single table and no complicated queries, MySql will be easily able to take care of millions of records. Try EXPLAIN on the queries to check the number of records being touched and indexes being used.

TJ-
  • 14,085
  • 12
  • 59
  • 90
  • so basically it won't matter for the sql server? I plan to archive every quarter or something, not everyday since there is a monthly report I need to do too. if you say the server will work with it it's fine by me the question is how will it be more efficient? – Sagi Rokach Jan 14 '13 at 15:03
  • How many records do you project every month? 100 Million? [More](http://stackoverflow.com/questions/1926079/how-many-rows-in-a-database-are-too-many)? I would suggest you to create such dummy records and measure the performance. You can read through [this](http://dba.stackexchange.com/questions/20335/can-mysql-reasonably-perform-queries-on-billions-of-rows) answer for [more insights](http://dba.stackexchange.com/questions/20335/can-mysql-reasonably-perform-queries-on-billions-of-rows) . Also, do you prepare and show reports realtime? – TJ- Jan 14 '13 at 16:06
  • No not that many... maybe 1k per month so not that many, ok so I don't need that table I gather. Thanks. – Sagi Rokach Jan 14 '13 at 21:52