You need to update one or more columns in a table with millions of records with data from another table, what’s a good way of going this with low risk and in an optimized way? One of my favorite ways of dealing with millions of records in a table is processing inserts, deletes, or updates in batches.
Updating data in batches of 10,000 records at a time and using a transaction is a simple and efficient way of performing updates on millions of records. The key is to batch out your data to allow you to process a smaller number of records at a time.
Example:
use DatabaseName
go
DECLARE @batchId INT
DECLARE @batchSize INT
DECLARE @results INT
SET @results = 1
SET @batchSize = 10000
SET @batchId = 0
-- when 0 rows returned, exit the loop
WHILE (@results > 0)
BEGIN
BEGIN TRAN;
UPDATE Table1 SET columnA = Table2.columnA
FROM Table2
INNER JOIN Table1 ON Table2.Id = Table1.Id
WHERE Table1.columnA is null
AND (Table2.Id > @batchId
AND Table2.Id <= @batchId + @batchSize)
SET @results = @@ROWCOUNT
-- next batch
SET @batchId = @batchId + @batchSize
COMMIT TRAN;
END
-- the sql below is just to measure the performance of this update, it is not needed to update your data.
DECLARE @startTime DATETIME
SELECT @startTime = GETDATE()
SET STATISTICS PROFILE ON
SELECT COUNT(*)
FROM Table1 first OPTION (RECOMPILE)
SET STATISTICS PROFILE OFF
SELECT 'It took ' + CONVERT(VARCHAR(8), DATEDIFF(ms, @startTime, GETDATE())) + ' ms'
GO
In the query above we declare the following variables to do the batching:
@results – this variable will hold the number of records updated; when zero, the query will stop. This is to avoid an infinite loop.
@batchId – this is set to zero initially, and it is used to compare the table id against it, and after each update, it is set to the id plus the batch size. This allows splitting the millions of records into batches.
@batchSize – the number of records to update at a time.
It is important to know that the above algorithm will only work when your table’s primary keys are of type int. If your table’s primary keys are guids, the above will not work.
The section at the end of the query is only used to review the performance of your updates, it is not necessary to update the data and it is a nice way to see how the query performs overall.
I hope the information in this post is helpful, and it helps you learn a simple way to update millions of records in batches, making it easier and lower risk than attempting to do it all at once.
Software development is hard, and it isn’t always the programming language or the framework you use, it’s the people who work on it.
People are an essential part of a team; everything can be easily changed and fixed, but you need to make sure people work well together to achieve effective communication and a great culture. In my software development career of over 20 years, the critical difference between successful software projects and failed ones has been how engineering teams are created and how they are allowed to function over time.
excellent tool for writing software, and when you install the right extensions, it can be very powerful. However, if these extensions and VS Code isn’t set up correctly, many of its features or extensions might not work the way you expect them to work.
Below is a list of settings that I use as my base when setting up VS Code for any type of web development. You can also get them from the gist located here:
The way to get to the setting in VS Code is by typing Ctrl-Shift P and then type “settings”. A settings.json file will open and this is where you can remove all existing settings and paste the settings below.
The VS Code editor is such a pleasure to work with; it is my code editor of choice when working with light C# projects or any other languages such as JavaScript or Ruby on Rails.
Below are my must-have extensions for anyone doing web development. There are many more magnificent extensions, but I feel like the following are not only useful but required to augment your web development experience with VS Code.
GitLens increases the capabilities of Git built into Visual Studio Code. It helps you visualize code authorship at a glance through Git blame and code lens annotations, among other things.
Prettier is an opinionated code formatter. It enforces a consistent style by parsing your code and re-printing it with its own rules that take the maximum line length into account, wrapping code when necessary.
Default Formatter:
To ensure that this extension is used over other extensions you may have installed, be sure to set it as the default formatter in your VS Code settings. This setting can be set for all languages or by a specific language.
A Visual Studio Code extension that provides CSS class name completion for the HTML class attribute based on the definitions found in your workspace or external files referenced through the link element.
That’s all, folks! I invite you to try other extensions, but make sure not to clutter your VS Code experience by installing extensions you might not need or use.
Also, remember to adjust your VS Code settings to make sure you get the most productivity and the best experience when using VS Code and these extensions.
If you know of any other VS Code extensions for web development, please feel free to share them in the comments below. Cheers.
So you have a static website and need to host it somewhere, there are many places to host your site but since you also want your site to have a security certificate, and you want all of this at a reasonable price, your options are limited.
Last year, I tried Azure Storage to host a static website, but at the time, the options to get and apply a security certificate to a site hosted on Azure storage was complicated, it was very confusing.
This time I decided to give AWS a try, and it turns out, hosting a static website using AWS’s S3 storage service works really well for static websites.
AWS also has a somewhat simple way to set up your new static website with a free security certificate. Below I will show you the steps to accomplish this.
These are the services you’ll need to configure to host your static website and an SSL certificate with Amazon’s AWS:
S3
CloudFront
Certificate Manager
Route53
The instructions below assume that you’ve already signed up for a AWS account.
S3 – Create and change the properties of your static website bucket
After you login to your AWS Console, search for S3 and create a new bucket for your static website. When creating the new bucket, you only need to give it a unique name, and then uncheck the Block all public access options under permissions. Don’t change any of the other default values or options.
Properties tab
After you’ve created a new bucket to hold your website files, click on the bucket name and then on the properties tab. From here, select the Static Website Hosting, it should look like the screenshot below.
In this window, you want to put the name of your main page (i.e. index.html), an optional error page, and any redirection rules, also optional.
Before you move on to the next step, copy the Endpoint value from the Static website hosting window and save it, you’ll need it for the CloudFront Distribution section below. In the example above, the endpoint is the URL: http://solopractica.s3-website-us-west-2.amazonaws.com
Permissions tab
Make sure the Block all public access option is unchecked. Now go to the Bucket Policy option and click on Policy Generator.
Select the options as shown above, make sure the Amazon Resource Name (ARN) follows this format: arn:aws:s3:::YourBucketName/* and replace YourBucketName with the actual name of your newly created bucket.
Finally, click on Add Statement and then Generate Policy, a new window will open with your new bucket policy in JSON format. Copy the JSON document, and paste it into the Bucket Policy space back in the bucket properties page.
If you get the following error message after saving the new policy: Policy has invalid resource, make sure the bucket name value is correct, and save again.
You’ll see a warning informing you that your bucket has public access, that is fine, the bucket needs public access to host your static website.
CloudFront Distribution – Create and configure
A CloudFront distribution is required if you want to host a static site and distribute media files using HTTP or HTTPS.
To create a CloudFront distribution, go to the AWS console, and type CloudFront in the search box. When the CloudFront Service page opens, click on Create Distribution, and then select Get Started for the Web option.
CloudFront distribution properties
Do not be overwhelmed by the many options in this window, you only need to change a few of these properties. Below are basic instructions on how to fill out the Create Distribution form.
Origin Settings
Origin Domain Name: Select your bucket endpoint from this list.
Origin Path: Leave it blank.
Origin ID: It gets filled automatically when you select the origin domain name above.
Lambda Function Associations: Leave default/blank value.
Distribution Settings
Price Class: Use Only U.S., Canada and Europe (the cost of it will change based on what you select here. Click on the information icon next to this setting and make the right choice for you).
AWS WAF Web ACL: None.
Alternate Domain Names (CNAMEs): Type your domain name and any subdomains you have for the bucket hosting your static website. For example, for my site solopractica.com, I entered the following values here: solopractica.com http://www.solopractica.com
SSL Certificate: Custom SSL Certificate. This is where you’ll also be clicking on Request or Import a Certificate with ACM (see section below).
Choose the DNS validation option, it’s the fastest and easiest. If you don’t have access to your domain DNS settings, then you can try Email validation instead. Click Review, and then Confirm and request.
Validation
Once you go through the steps to validate your domain(s), you’ll see a window with your Request in progress and the instructions to add a CNAME record to the DNS configuration for your domain. Click Continue.
Certificates
This page will show the status of your certificate request, you can refresh the status of your request to get the status updated. The time to get your domain verified depends in part, on your domain registrar.
After your certificate has been approved, go back to your Distribution and click on it to edit it. From the edit page, make sure you have the Custom SSL Certificate option selected and then select your brand new SSL certificate from the list.
Route 53 – Create and configure
This is the last step, it will allow AWS to route your domain name and certificate to the appropriate resource.
Go to the AWS console and type Route 53 in the search box, click on the Route 53 link, and then on Hosted zones.
Hosted zones
Create a new hosted zone, enter your domain name, make sure the Public Hosted Zone is selected and click Create.
Record Sets
Two record sets are created by default when creating a new zone, a NS (Name Server), and SOA (Start of authority).
While selecting the newly created Hosted Zone, click on Create Record Set.
Use the following settings and values when creating the new record set:
Name: Leave the name box empty.
Type: A – IPv4 address.
Alias: Yes.
Alias Target: Select it from the list, you should see a value available if all of the steps above were completed successfully.
Routing Policy: Simple.
Evaluate Target Health: No.
After this, you can add another record set of type A for any additional domain names you might be using, for example, http://www.yourdomain.com.
That’s it, by now you should be able to open your browser and go to your domain, it should be available with the https protocol.
