SQL Merge
One typical task of an SQL developer is to merge two sets of data. For example, you may have a data entry screen where users may modify existing data, delete rows, and insert rows into the same set. Then you wish to merge these modifications with the existing set of data in your relational database.
There are a lot of techniques for submitting this set of data to a database engine for processing. Regardless of the method chosen, you still must merge the set submitted from the user into that stored in the database. Some developers simply delete the existing data and insert the new set. If the set is small enough I suppose this works.
But there are definitely drawbacks to this approach, especially if you are auditing the changes.
In order to merge you need to do three things.
- You need to update existing records from the new set where the data has been modified
- You need to delete previously persisted records no longer in the new set
- You need to insert new records added to the new set
This is the order I prefer to do the merge because of performance. If I did the insert first, then I would be reviewing each of the records just inserted to make sure they weren’t modified (unless I had some sort of identifier in the new set determining what action should be taken). So, doing the Updates first reduces some of the load…this obviously doesn’t matter much when you are working with small sets of data.
SQL Server 2008 shipped with the Merge command. It handles writing all the necessary code to merge the two sets together for you. I haven’t used the Merge command yet, even though it has been out for years. Recently some of the guys I work with decided to try using Merge and found that the performance was not equivalent with writing your own merge process. I don’t have any benchmarks to know how much they differ.
The differences in time were at the Millisecond level. That may not even be an issue for most systems. One factor may be the data types contained in the sets. If you have XML data types or large data types it may perform differently. Those columns are likely to be ignored for comparison on a hand written queries.
Have any thoughts? Share them with us by sending an email to btaylor@sswug.org.
Cheers,
Ben
Reader Responses: Back to the Cloud
Jonathan:
Thank you for raising a contentious discussion point, however I have to disagree. My comments below yours.
- The platform may be deemed not profitable and deprecated…you have to re-tool
This depends on the application design and whether the software belongs to you. In n tier cases it can be as simple as re-installing the app and database on a new host and re-configuring the data connection. perhaps also configure a new website. Modern development tools like Silverlight are built for quick deployment. Upgrading to new platforms is also not as big an issue as in the past.
- The company may go out of business, or sell to a competitor who rolls the offering up into a more expensive bundle
Again this depends on design and software ownership. If you are using third party software as a service and the company folds or sells or changes the offering, you would still be left with support and business continuation issues even if the software was hosted on your own network. If you are using your own software and it is relocate-able, it is just a case of re-hosting.
- There can be unplanned outages due to software or hardware errors
Yes even the biggest and most sophisticated companies have outages. Personally these would be far better controlled in a specialised data centre where all clients are paying for realtime usage. Recovery is also likely to be far quicker in order to stem the revenue loss. Large data centres also have far more redundancy built in than any single company can afford.
Sorry Ben I think your comments belong to an IT era where IT professionals (particularly tech professionals), had a strong need to own and control all their IT components. Having said that I am strongly opposed to outsourcing technical skills (but that is an entirely different discussion). Ideally in my book, skills should be kept in-house, platforms should be cloud based. All the arising issues of security stability and reliability of platform owners can be dealt with as the model improves. There is however one issue that you have not mentioned and that is legal. There is a real issue in some cases of data being held in other countries. The legal implications
Editor:
I am very much positive on the Cloud. I think the question should be, "why can’t I go to the Cloud?", In many cases it is the best option for many different reasons. I’m sure it will become more of a norm as time goes on. Going to the Cloud does not replace my responsibility to manage my contingency requirements.
Ung:
When do you think there will be a realistic cloud that’s viable?
Editor:
I’m not qualified to make a recommendation. Personally, I believe the Cloud is mature enough now. It is no less stable than an in-house managed system, and most likely more stable.
My recommendation is that you must plan for disasters. They happen, no matter where your software is hosted.
Cloud vendors continue to improve their products at such a rapid pace that I will have to write about something else.
Thomas:
I think it depends on the contract with your cloud vendor and their errors and omissions coverage… damage clauses make enterprise less responsible financially for outages and disasters by compensating the business for the outage; but I get your point… I hope you see mine.
$$SWYNK$$
Featured White Paper(s)
Office 365 SharePoint Online – Architectural Considerations
Written by AvePoint
The goal of this white paper is to clarify strategies f… (read more)
Featured Script
Pad Value
Function that will pad a value with a passed parameter…. (read more)