This post is a visual guide about one of the most powerful features of Microsoft SQL Server Management Studio (SSMS) 2008: Import/Export database data.

These tasks are useful for example when you need to debug an application with real world/production data to find possible bugs in your code. You can import the production database data to your local development database and find what’s causing problems. These tasks can be used to create a protection layer because you won’t be working “directly” with production data.

As you see, if I right click a database in SSMS I get access to this rich menu full of interesting options that deserve a careful study:

Looking at the above picture you see in Object Explorer that I’m connected to two SQL database servers. The first one is a remote SQL Server (version 10.0.2775) and the other one is my local SQL Server Express (version 10.50.1617) instance.

With this configuration I have the perfect scenario to visually show you how the Import/Export Data… menu options work.

In this blog post I’ll execute the Export Data task. This allows me to test my web application using my local SQL Server instance with real world/production data.
The Import Data… task does practically the same thing but in reverse, logically. I’ll let the execution of the Import task to you dear reader!

OK… If I proceed and select the Export Data… menu option I’ll be able to bring new data to the database that is located on my machine. Data comes from the remote/production server to my local copy of the same database.

After clicking Export Data…, SQL Server Import and Export Wizard pops up. I then select the Data source/Server name (from which Data source type and Server I want to get the data to be imported). In this case, the data comes from the remote SQL server. I use SQL Server Authentication, fill the user name and password and select the Database that’ll provide the data. In Server name field I used the IP address of my remote server.

Now is the time to choose the Destination. In this case it’s my local SQL Server instance. LENIEL-PC is my Windows 7 machine name. SQLEXPRESS is the default SQL Express server name. I also selected the Database I want to receive the imported data. As you see both databases (source and destination) have the same name to make things easy.

The wizard then asks if I want to copy data directly from table to table or if I want to write a custom SQL query to shape the data according to specific needs. I’ll select the first option to keep things simple.

In the next screen we get a beautiful list of available tables and views. I’ve selected two tables that I want to copy.

If you click the Edit Mappings… button (which applies to each table selected in the grid), I can also specify if I want to delete rows before importing data or if I want to append rows to the destination table. I’ll append rows because I have already erased both tables (exams and files) on my local database.

Table files is linked to table exams through a foreign key. I disabled this foreign key constraint in my local database to avoid conflicts/errors during the import process. Beware because conflicts do appear most of the time. Conflicts are generally related to key violation/duplication.

As I’m using the free SQL Server Express Edition, the following screen has an interesting information:

In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.

This is telling me that I’ll have to go over the wizard (#7 steps) again if I happen to execute this very import process. Really annoying… … but that’s the price you must pay when using free tools.

Next button once more (the last one hallelujah) and we’re ready to go. It’s just a matter of reviewing what’ll be done and pressing the finish button.

After clicking Finish I got this last window:

Hope this simple and visual guide helps you understand how the data Import and Export processes work when dealing with a SQL Server database inside SSMS.

Free tools
Microsoft SQL Server Express Edition
Microsoft SQL Server 2008 Management Studio Express (SSMSE)

Last time I showed you how to Automate tasks in Mac OS with Automator. I used Automator to create a simple workflow that helps moving MP3 files to iTunes folder. Check that here.

Now I have another task to Automator.

This is the description of what I want to accomplish or the problem description if you prefer:

I often download some free legal MP3 files from the internet for evaluation, more specifically from Indie Rock Cafe (great great site by the way :D - check it out if you like Indie Rock music). I use IndieRockCafe mainly to discover bands that I’ve never heard about. It’s being a great experience so far. I’ve come to know some really good bands that, else I would never hear a song of theirs.

To download those MP3s I use DownThemAll that is a really nice piece of software that works beautifully with Firefox. I wrote about DownThemAll in Automate the download of a list of URLs/links. Although I didn’t write how to download only MP3 files with DownThemAll, this post gives you an idea about the purpose of DownThemAll. I’ll write about how to download only specific kind of files (MP3 in this case) using DownThemAll, but that’s another post. Probably I’ll detail this download process I’m mentioning here.

As I was telling you, I often do the same task, that is, I go to IndieRockCafe, click on DownThemAll icon in my Firefox toolbar and tada, the download of IndieRockCafe’s recently added MP3s just start. DownThemAll will download only page links that point to MP3 files saving these MP3 in a single folder called IndieRockCafe.

After the download I used to select all the files within that folder and drag and drop them inside an iTunes playlist called IndieRockCafe. iTunes is wise enough to tell me that some files are already part of the playlist and gives me the option to skip them adding only new files into that playlist. Doing so I always have a fresh playlist with the latest files of IndieRockCafe. It works, but it has a lot of manual steps.

Yesterday I thought: the above steps are a perfect fit to be automated with Automator.

Let’s create a workflow:

1 - Go to the Applications folder and select Automator.

2 - You’ll be presented with the following screen to choose a template for your workflow. Select Folder Action as the template.

Figure 1 - Types of templates available to create an Automator workflow (Folder Action)

3 - In Folder Action receives folders and files added to, select the folder you want. In my case it is the IndieRockCafe folder.

4 - Now select Music in Library list and then select Import Files into iTunes under the Actions list. Drag this action to the workflow area in the right.

5 - Select Existing playlist and the playlist you want the files to go to. As I wrote above I already have a Playlist called IndieRockCafe inside iTunes. So I selected it.

6 - Go to the File menu and select Save. Give the workflow an appropriate name, e.g. IndieRockCafe.

The following screenshot shows the Folder Action workflow configured:

Figure 2 - IndieRockCafe.workflow configured according to the six steps described above

7 - Now that the workflow is created, there’s a last step required to orchestrate things: go to the IndieRockCafe folder and right-click it. Select Services > Folder Actions Setup… Make sure you attach the IndieRockCafe workflow to this folder as shown in Figure 3:

Figure 3 - Attaching IndieRockCafe.workflow in Folder Actions Setup

Make sure you click the Enable Folder Actions checkbox too:

Figure 4 - Enabling Folder Actions and turning IndieRockCafe.workflow ON for the IndieRockCafe folder

… and we’re done! As you see this is totally life saver.

Every new MP3 that gets added in my IndieRockCafe folder through DownThemAll or that I manually place in this folder will be automatically added in IndieRockCafe playlist.

Task successfully automated!

I’m a music lover and I hope you can take advantage of it too.

Important
DownThemAll creates file segments ( *.dtapart files ) while downloading. DownThemAll splits the file into several parts and then downloads each segment of the file individually, which gives you better speed from servers, especially those that choose to limit your download speed. This behavior will cause the workflow created above to fail because iTunes won’t recognize those parted files when trying to import them. To solve this problem, do the following:

In Firefox Tools menu choose More dTa Tools and then select Preferences.

In tab Advanced and under Temporary files choose a directory to store those dtapart files. See screenshot below to have an idea:

Figure 5 - Using a temporary folder to store DownThemAll file parts or segments

Doing the above, DownThemAll will store those partial files in a separate folder. When it finishes downloading a file it will join its parts and then will move that file to the IndieRockCafe folder I specified in the workflow. Now iTunes will import the MP3.

Note
Folder action workflows are saved in
/Users/YourUserName/Library/Workflows/Applications/Folder Actions

Download
You can download this workflow at:
https://sites.google.com/site/leniel/blog/IndieRockCafe.workflow.zip

This one is about an incredible application that comes with Mac OS X. Its name is Automator.

Jesus Christ, this is a life saver app!

This is the description of Automator:

With Automator, you can accomplish time-consuming, repetitive manual tasks quickly, efficiently, and effortlessly. Automator lets you skip the complex programming and scripting that is normally required to create automations. Instead, you assemble individual steps into a complete task by dragging these actions into an Automator workflow. Automator comes with a library of hundreds of actions. And with the Watch Me Do action, you can record an action — such as pressing a button or controlling an application without built-in Automator support — and replay it as an action in a workflow.

I have lots of MP3 files that I need to go through analyzing if I really want to keep them in my media library. During the last year I stored all those MP3 in a folder and such a folder is now 55.56 GB and contains 11840 files to be precise. That’s a lot of MP3! I keep postponing this open/listen to task but today I thought I’d start. That’s where Automator fits the job.

In this post I’ll show you how to create a simple workflow that helps moving the MP3 files to iTunes folder /Users/leniel/Music/iTunes/iTunes Media/Automatically Add to iTunes. The folder Automatically Add to iTunes is a special folder that iTunes keeps watching for new files added to it. When a file is added in this folder, iTunes automatically adds it to the media library using MP3 metadata to organize the library. When added files will reside in /Users/leniel/Music/iTunes/iTunes Media/Music.

Let’s create the workflow:

1 - Go to the Applications folder and select Automator.

2 - You’ll be presented with the following screen to choose a template for your workflow. Select Service as the template.

Figure 1 - Types of templates available to create an Automator workflow

3 - In Service receives selected, select audio files. “In” select Finder.

4 - Now select Files & Folders in Library list and then select Move Finder Items under the Actions list. Drag this action to the workflow area in the right.

5 - In Move Finder Items select the folder where you want the files to be moved to. You also have the option of showing the action when the workflow runs.

6 - Go to the File menu and select Save. Give it an appropriate name as Add to iTunes and you’re done.

The following screenshot shows the Service workflow configured:

 
Figure 2 - Add to iTunes.workflow configured according to the six steps described above

Now, let’s use this service workflow. To do this, go to Finder and open any folder that contains audio files such as MP3. Right click the MP3 file and voila. Now there’s an extra context menu option called Add to iTunes. What a marvelous thing.

Figure 3 - Add to iTunes context menu option in Finder when right clicking MP3 file(s)

What happens when Add to iTunes is clicked? The workflow we created will be executed and the selected file(s) will be moved to the folder specified in the workflow, in this case /Users/leniel/Music/iTunes/iTunes Media/Automatically Add to iTunes.

As you see this is totally life saver.

I can play the MP3 in iTunes and if I decide that I want to keep it in my media library I just have to select Add to iTunes.

The possibilities with Automator are endless given the amount of options in its Library and Actions lists and workflow template types.

I hope you could get an idea of what Automator can do.

Updated on 12/16/2010

If you make a slight change in step 3 above you can have this workflow add not only audio files but even entire folders to iTunes and better yet, iTunes will ask if you want to replace existing files so that you don’t end up with duplicate files in your iTunes library. This is great.

So what do you have to do? Instead of audio files, select files or folders. Just this. Save the workflow. Now right click over any folder in Finder and you’ll see that you get a new menu option under Services called Add to iTunes.

Figure 4 - Add to iTunes context menu option in Finder when right clicking a folder

Notes
Service workflows are saved in /Users/YourUserName/Library/Services.

In prior versions of Mac OS, there was an option to save the workflow as a plug-in. This was necessary so that you could have a context menu option (right-click) in Finder to run the workflow. I tried to go this way but this option isn’t available in Mac OS X 10.6.4. I realized that I had to create a Service workflow when I read this question at Apple’s Support site: No Automator plug-in in 10.6?

Download
You can download this workflow at:
https://sites.google.com/site/leniel/blog/AddtoiTunes.workflow.zip

References
Automator - Your Personal Automation Assistant

Automator - Learn by example

Automator at Apple’s Mac OS – All Applications and Utilities

Automator article at Wikipedia

On the last day of May I wrote about how to calculate prime numbers with LINQ in C#. To close that post I said that I’d use the PrimeNumbers delegate to evaluate PLINQ (Parallel LINQ) and measure the performance gains when the same calculation is done in parallel instead of in a sequential fashion.

PLINQ is LINQ executed in Parallel, that is, using as much processing power as you have in your current computer.

If you have a computer with 2 processor cores like a dual core processor you'll get your Language Integrated Query operators do the work in parallel using both cores.

Using "only" LINQ you won't get as much performance because the standard Language Integrated Query operators won't parallelize your code. That means your code will run in a serial fashion not taking advantage of all your available processor cores.

There are lots of PLINQ query operators capable of executing your code using well known parallel patterns.

After this brief introduction to PLINQ let’s get to the code.

As promised, today I show the performance gains when the PrimeNumbers delegate is run in 2 cores (parallel) instead of only 1 core (sequential).

Here’s the delegate code:

Func<int, IEnumerable<int>> PrimeNumbers = max =>
from i in Enumerable.Range(2, max - 1)
where Enumerable.Range(2, i - 2).All(j => i % j != 0)
select i;  

To make it a candidate to parallelization we must just call the AsParallel() extension method on the data to enable parallelization for the query:

Func<int, IEnumerable<int>> PrimeNumbers = max =>
from i in Enumerable.Range(2, max - 1).AsParallel()
where Enumerable.Range(2, i - 2).All(j => i % j != 0)
select i;  

I set up a simple test to measure the time elapsed when using the two possible ways of calling the delegate function, that is, sequentially in one core and parallelized in my two available cores (I have an Intel Pentium Dual Core E2180 @ 2.00 GHz / 2.00 GHz).

Let’s calculate the prime numbers that are less than 50000 sequentially and in parallel:

IEnumerable<int> result = PrimeNumbers(50000);

Stopwatch  stopWatch = new Stopwatch();

stopWatch.Start();

foreach(int i in result)
{
    Console.WriteLine(i);
}

stopWatch.Stop();

// Write time elapsed
Console.WriteLine("Time elapsed: {0}", stopWatch.Elapsed);

Now the results:

1 core
Time elapsed: 00:00:06.0252929

2 cores
Time elapsed: 00:00:03.2988351

8 cores*
Time elapsed: 00:00:00.8143775

* read the Update addendum bellow

When running in parallel using the #2 cores, the result was great - almost half the time it took to run the app in a sequential fashion, that is, in only #1 core.

The whole work gets divided into two worker threads/tasks as shown in Figure 1:

Figure 1 - The Parallel Stacks window in Visual Studio 2010 ( #2 cores )

You can see that each thread is responsible for a range of values (data is partitioned among available cores). Thread 1 is evaluating the value 32983 and Thread 3 is evaluating 33073. This all occurs synchronously.

If I had a computer with 4 cores, the work would be divided into 4 threads/tasks and so on. If the time kept decreasing I’d achieve 1.5 seconds to run the app. Fantastic, isn’t it?

The new Microsoft Visual Studio 2010 (currently in Beta 2) comes with great debugging tooling for parallel applications as for example the Parallel Stacks shown in Figure 1 and the Parallel Tasks window shown in Figure 2:

Figure 2 - The Parallel Tasks window in Visual Studio 2010 ( #2 cores )

This post gives you a rapid view of PLINQ and how it can leverage the power of you current and future hardware.

The future as foreseen by hardware industry specialists is a multicore future. So why not get ready to it right now? You certainly can with PLINQ. It abstracts all the low level code to get parallel and let’s you focus on what’s important: your business domain.

If you want to go deep using PLINQ, I advise you to read Patterns for Parallel Programming: Understanding and Applying Parallel Patterns with the .NET Framework 4 by Stephen Toub.

Updated on February 15, 2013

Running this same sample app on a Intel Core i7-3720QM 2.6GHz quad-core processor (with #4 cores and #8 threads) this is the result:

Time elapsed: 00:00:00.8143775

This is on a par with the #1 core and #2 cores tests shown above. The work is being divided "almost" evenly by 8 if we compare with the first benchmark ( only #1 core ).

00:00:06.0252929 / 8 = 0.7525

Of course there’s been lots of improvements between these different processor generations. The software algorithms used to parallelize the work have also been improved (now I’m running on Visual Studio 2012 with .NET 4.5). Both hardware/software specs are higher now. Nonetheless these numbers give a good insight about the performance gains both in terms of hardware and software. Software developers like me have many reasons to celebrate!

Figure 3 - The Parallel Stacks window in Visual Studio 2012 ( #8 threads )

If I take out the .AsParallel() operator, the program runs on a single core and the time increases substantially:

Time elapsed: 00:00:03.4362160

If compared with the #4 cores benchmark above, we have:

00:00:03.4362160 / 4 = 0.8575

0.8575 – 0.8143 = 0.0432 (no difference at all)

Note: this faster processor running on a single core has a performance equivalent to the old Intel #2 core processor. Pretty interesting.

References
Features new to parallel debugging in VS 2010
Debugging Task-Based Parallel Applications in Visual Studio 2010 by Daniel Moth and Stephen Toub

Great lecture on what to expect from the multicore and parallel future…
Slides from Parallelism Tour by Stephen Toub

PLINQ documentation on MSDN
http://msdn.microsoft.com/en-us/library/dd460688%28VS.100%29.aspx

Parallel Computing Center on MSDN
http://msdn.microsoft.com/en-us/concurrency/default.aspx

Daniel Moth’s blog
http://www.danielmoth.com/Blog/index.htm

Microsoft Visual Studio 2010
http://www.microsoft.com/visualstudio/en-us/products/2010/default.mspx