February 15, 2017 at 11:50 am
Hello,
We are building a new OLTP system, where we can load data through files. In the sense that if you think that you will be taking action on more than 100 items, it would be hard to go to each entry and do it, so they want to solve this action by loading a file,
Now when you are trying to merge data from staging to OLTP, would compare what has changed and try to update, So in this scenerio would it be beneficial to have a HashKey generated in OLTP tables and store it
What is the advantage and disadvantage of it.
CUrrently our OLAP system has that feature, trying to understand if hashkeys are typically used in a OLTP system or not.
Thanks
February 15, 2017 at 1:54 pm
HashKeys can be "compute-expensive", so constantly computing these values could be a severe performance problem for a tightly managed or capacity limited OLTP environment. On the other hand, if you've got CPU cycles sitting seriously unused, that might be another story. However, if you already have a table with say, 1.2 BILLION records and you need to add a HashKey, you've got a serious problem unless you've got a sizable down-time window available.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 9:24 pm
ajjarapumadhavi - Wednesday, February 15, 2017 11:50 AMHello,We are building a new OLTP system, where we can load data through files. In the sense that if you think that you will be taking action on more than 100 items, it would be hard to go to each entry and do it, so they want to solve this action by loading a file,
Now when you are trying to merge data from staging to OLTP, would compare what has changed and try to update, So in this scenerio would it be beneficial to have a HashKey generated in OLTP tables and store it
What is the advantage and disadvantage of it.CUrrently our OLAP system has that feature, trying to understand if hashkeys are typically used in a OLTP system or not.
Thanks
So it sounds like you don't have a Primary Key associated with the incoming and existing data that you can use? If not, computing some form of hash of all values can be used to determine data changes very quickly. If CPU on the SQL Server is a bottleneck you can preprocess the files on a different machine to add the computed hash to each row in the file.
Do note that all hash functions are NOT created equal!!!! Look up Thomas Kejser's article(s) on this topic before you implement!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply