----- Original Message -----
> From: "Manivannan S." <manivannan_s@spanservices.com>
>
> Hi all,
>
> [lots of data]
> [slow reports]
> [wooo NoSQL magic]

Not that I want to discourage you, but my standard first question is "why do you think NoSQL (let alone any specific product) is the right solution?" :-)

Don't get me wrong, it might be; but from what little I now know about your environment, it sounds like applying some data warehousing techniques might suffice - and being the cynical dinosaur that I am, I have a healthy reluctance about welding new technology onto a stable environment.

To speed up reporting (and note that these techniques are often applied even when implementing NoSQL solutions, too) it is usually a good first step to set up a process of data summarization.

Basically, you pre-calculate averages, medians, groupings, whatever you need for your reports; and your job also saves the last record IDs it's processed; then on the next run, you only read the new records and update your summary tables to incorporate the new data.

Suppose I have a table like this:

ID | Val
--------
1 1
2 7
3 5
4 13

I want to report the average on a daily basis, and calculating that over those rows is unbearably slow because I'm running the process on a wristwatch from 1860 :-)

So I get a summary table, calculate (1+7+5+13)/4 = 6.5 and that then gets a record saying this:

Avg | elementCount | lastSeen
-----------------------------
6.5 4 4

Now, over the course of the day, the elements 4, 17 and 2 get added with sequential row numbers. Instead of calculating (1+7+5+13+4+17+2)/7, which would be slow; I can substitute the already summarized data by Avg*elementCount. Thus, I calculate (6.5*4 + 4+17+2)/7 = 7, which is a lot faster, and my summary table now looks like this:

Avg | elementCount | lastSeen
-----------------------------
7 7 7

This is of course a stupid example, but it saves you a lot of time if you already have the summary of several thousand elements and only need to update it for a handful. Similar tricks are possible for a lot of typical reporting stuff - you don't need to re-calculate data for past months over and over again, for instance - and that's what makes your reports run fast.


Just my 2 cents :-)
/johan

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql