Timeline for vehicle tracking system in postgresql performance
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 27, 2012 at 14:32 | comment | added | cavila | 10.000 rows should be nothing to Postresql to scan. In trigger you should put all the logic to make data entry and dependencies resolved in function of the new data arrived. If the new row at given table would change a distance value at another table them this should be put in trigger. Take care that something wrong at any trigger function will abort all operations. So do validation at very begin to avoid undoing lots of stuff. Function names also define trigger calling order. | |
| Dec 27, 2012 at 7:29 | vote | accept | varma | ||
| Dec 27, 2012 at 1:24 | comment | added | Glenn Plas | In my case, the tracer calculates the distance (internal odometer) for me and just sends it along,it's much more accurate too since the device's hardware/software programming can filter out bad ones for you so you don't have to do it centralised | |
| Dec 26, 2012 at 13:38 | comment | added | varma | Thanks... Yes i have some calculation but it need select statments for trips,location,geofence tables which contains max of 10000 rows and updates into the same table... can i do that same in db? Can i split logics(trips/distance/geofence etc) into functions and calling the functions in trigger? will it does any performance impact(calling many functions)? | |
| Dec 26, 2012 at 12:29 | comment | added | cavila | 1 - Trigger MUST run fast. To do a distance update you only need calculate the distance from latest point and store the sum into distance field. Very simple if you have the last point at latest_information table. That should all be done inside the trigger. 2 - PHP will be slower since it will need to call database and wait it to process then send another statement wait. So use PHP to unpack data from client into fields and call the database to do the calculations with the field data. | |
| Dec 26, 2012 at 8:26 | comment | added | varma | Thanks for gave me very usefull infomation. My data from device will give around 10 fields.. like lat,lng,speed,fuel date and time etc.. I am planing to put the latest information in one table (this table contains only one latest row per device).. Can u pls answer the following questions.. 1)What will happen if i take the latest infomrmation from latest table and do the operations(w.r.t. Device Data) in trigger and inserting into log table and updating the same into latest infomation table? 2)What will happen if i do the same above process in PHP when data comes from the device | |
| Dec 24, 2012 at 18:38 | history | answered | cavila | CC BY-SA 3.0 |