As i am trying to de normalize GPS positions received with various journey details , which i received during a day, i need best solution so i can have better performance Kindly guide.
I have two tables, One has Position requests received during entire day
Position table has following columns
JourneyDetail has following columns
so to denormalize each position with journey detail,i have created one fact table
and few dimensions created like (depo,block,driver,journeyDetail).
Now Daily I am receivng 2,50,000 records in postions and 2500 journey details, so i am updating postions with journey detail on the bases of start time and endtime of journey. my code creates 2500 update query and sends to sql for updation of those position which fall in between this date ranges.
I am submiting these in bunch of 100 update query in sql command to sql server so round trip time can be save. but for all 2500 journey i have to submit 25 different bunch of 100 queries, so it takes approximately 2 hour to update this positions.
Q. So is there any way to design Star or snow flake schema for this scenario so i can view positions falling between this start and end of journey time ?
Q. Is any other way to do speedy update ? because it is possible i can receive more that 25 lac records in a day. so my transformation process will die..!!!
Let me Know Your precious Reply.
Thanks & Regards in advance
Based on your information, it seems more like your questions are around SQL Server side relationship design and query design (effective relational data warehouse design). Can you please post your question to SQL Server Forum (http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/threads) instead for proper guidance on your questions?
Hope this helps.
Meer Al - MSFT
- Proposed as answer by Meer Al - MSFT Tuesday, May 15, 2012 2:32 PM