June 7, 2008 at 4:53 am
Hi all,
i m continuously collecting data from comm port & putting in DB with rate of 1 value per minute.. such 5-6 years value r to b kept in db.....On user request, d values are averaged ( specific interval...lets say 30 value in half hour)...these averaged value are then graphed on Mschart...Problem is that when i want to show 2-4 year earlier data in 2-6 day duration with averaging interval of 30-60 minutes.....it takes long time.....
eg...
s = "select avg(savg) as Average from Table_all_sdetails where" + "stime>=' " + CStr(DateAdd("n", ivt * j, Me.dtStart.Value)) + " ' and sTime<' " + CStr(DateAdd("n", (ivt * j) + ivt, Me.dtStart.Value)) + " ' " "
upto my observation averaging is taking most of time...any solution is greatly appretiated....
June 7, 2008 at 6:04 am
It is affected by the number of records.
Do you have a covering index on (stime,savg) to avoid bookmark lookups?
June 7, 2008 at 6:34 am
definitely....no. of record is important factor.....but it cannnot be reduced.....And one thing, for reducing burden to show all values, i m showing averaged values but it do not solve problem to expected extent..
June 7, 2008 at 7:38 am
A couple of things. One, is the interval time for the averaging fixed (ie 6:00 - 6:29, 6:30 - 6:59) or is it dynamic (one time it may be 6:00 - 6:29, the next 6:15 - 6:44).
Could you post the DDL for the tables (Key columns (primary, alternate, etc), relevant data columns) and some sample data, the code you currently use, and what the results should look like based on your sample data. I would also suspect a few out here would also be interested in the execute plan of the query as well.
Be sure to post the sample data as insert statements to make it easier for people to assist you.
Depending on your requirements, it looks like what needs to be done is to pre-aggregate the data so that the calculations don't have to be done every time you query. This is why I asked the question about the interval that you are averaging over. If it is dynamic, it may be difficult to impossible to pre-aggregate the data as we won't know what interval to use.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply