which object should i use (efficiency)
I have an importer app which imports message data from cars
I'm trying to save the fuel data, however, depending on the hill, or sloshing of fuel in tank the readings are widely different, so i need to use an AVERAGING facility
I wish to take the current reading, and average it against the last 5,10,20 or 25 readings and save the average
I could do this by doing a SQL select every time i get new data to get the last N records, and work out the average, but it will hammer the DB when scaling
Therefore I need to put this data in memory and do the look up quickly there
I need to store the following data in an Object (the latest 25 records)
IMEI, GpsDateTime, Fuel Reading
When I get new message data, I will use the last 5,10,20 or 25 records from this proposed object to work out the average
The bit i'm unsure about is which object to use to store this data, as it needs to be able to do a few things like return the latest N records
I did look at a. Dictionary, but i gather the data is stored in a random order
I was also considering a Dictonary to store IMEI and then another type of list that stores FuelValue and Date, however these may not be unique
Either way, i need to store the IMEI for the lookup, and from there i would request the fuel value and order by the date it reported
i.e. I would quickly need to request the LATEST 5 records from this object (ordered by GpsDatetime), and use the FUEL READING from this to work out the average
The cars often report data in the wrong order, so I cannot assume the order receive the data is the order it was sent, so i have to use the GpsDateTime that is sent on each of the import messages to order the results set
performance is the name of the game here, I could be processing 100's of records a second
How would you do this, and what objects would you use?
I'm trying to save the fuel data, however, depending on the hill, or sloshing of fuel in tank the readings are widely different, so i need to use an AVERAGING facility
I wish to take the current reading, and average it against the last 5,10,20 or 25 readings and save the average
I could do this by doing a SQL select every time i get new data to get the last N records, and work out the average, but it will hammer the DB when scaling
Therefore I need to put this data in memory and do the look up quickly there
I need to store the following data in an Object (the latest 25 records)
IMEI, GpsDateTime, Fuel Reading
When I get new message data, I will use the last 5,10,20 or 25 records from this proposed object to work out the average
The bit i'm unsure about is which object to use to store this data, as it needs to be able to do a few things like return the latest N records
I did look at a. Dictionary, but i gather the data is stored in a random order
I was also considering a Dictonary to store IMEI and then another type of list that stores FuelValue and Date, however these may not be unique
Either way, i need to store the IMEI for the lookup, and from there i would request the fuel value and order by the date it reported
i.e. I would quickly need to request the LATEST 5 records from this object (ordered by GpsDatetime), and use the FUEL READING from this to work out the average
The cars often report data in the wrong order, so I cannot assume the order receive the data is the order it was sent, so i have to use the GpsDateTime that is sent on each of the import messages to order the results set
performance is the name of the game here, I could be processing 100's of records a second
How would you do this, and what objects would you use?
ASKER
It's 25 records per car
There are 36, 000 cars
There are 36, 000 cars
SQL Server would be a good option for this. Even if the data increases SQL SERVER can handle. [36000*25]
You can write a select in a manner that it will quickly fetch the data. You can also create indexes which will improve the performance if reqd.
If you can provide more details like data, then we can probably write the query and you can run and check it out.
You can write a select in a manner that it will quickly fetch the data. You can also create indexes which will improve the performance if reqd.
If you can provide more details like data, then we can probably write the query and you can run and check it out.
ASKER
Actually, this is just one operation, there are countless more things happening with SQL and its getting hammered, i'm trying to optimise all the queries, but i'm also trying to limit the calls to sql where its unnecessary
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So pick whatever method that fits your temper and related code.
/gustav