SQL Server Management Studio crashed!

Hi everybody,

Ever experienced having SQL Server Management Studio crash on you after a long day of coding and when you start it up again it doesn't offer you to recover the files you were working on? Well that just happened today and I was a bit worried when I wasn't offered with a recovery. So I googled a bit and found this handy solution: http://www.sqlservercentral.com/Forums/Topic500268-149-1.aspx

In short you can go to your document folder, enter the "SQL Server Management Studio" folder and enter "Backup Files". There should be a folder called "Solution1" and in that folder you should find all your lost work.

Now...back to work.

Tackling timeouts

Working with databases under load sooner or later leads to timeouts. Sometimes they occur for a good reason, sometimes not. Either way, you have to cope. The reason for the timeout is very rarely the wrong timeout period value.

Good reasons for timeouts: You have a long-running task, such as taking a backup, restoring some important data, or any other kind of task that simply will take time.

Bad reasons: You have a poorly written (or poorly optimized, as it often happens) query, or you are placing too much load on the server(s).

As mentioned, either way you have to cope, when it happens. But in the case of the bad reasons, you really should try and remove the reason for the timeout.

I won't dive into the universe of sql query optimizing here.

When a timeout occurs, the reason is that the server is busy for some reason, and you basically have to wait, and check again later, if it's free. It's like when a person is busy, if you keep poking them to ask if they're done yet, chances are it will just delay them. And when one timeout happens, more will follow. Many times I've seen timeouts occuring on one part of the server, related to badly written queries in a totally different part of the server.

I see sometimes people suggest to increase the timeout period. But in my opinion, unless you have a ridiculously low timeout value, this is not a solution, simply a way of brushing it under the carpet, or treating the sypmtoms instead of the disease.

If you're queuing up your queries, or processing them async, and you increase your timeout you will have more queries in the pipeline when the timeout occurs, and you'll have a bigger mess to clean up.

You have to know your domain, and set your timeouts accordingly. Shorter timeout will at some point lead to more timeouts occuring, but they will occur sooner and you will spend less time cleaning up. If your query never should take more than 5 seconds to execute, and your timeout is set to 30 seconds, that's 25 seconds spent in vain. Which may sound picky, but it all adds up...

You should also know your system well enough to be able to prioritize the different queries - if something is not important, leave it for later (on a low priority queue, for example) or even just log it and move on. In fact, if logging it and moving on is sufficient, explore the option of removing the query altogether. It sounds like it may not be that important.

If it is important, and you really need to carry out the query (which is usually the case) then you have a few options.  In both of them I'd suggest catching SqlExceptions and then try to determine if they were caused by a timeout (or a deadlock - this approach also works for them). If that is the case, let the thread sleep for a short while (to allow whatever is causing the issue to resolve), then do one of two things.

With a thread processing a queue, you could peek to get the current object, and try and carry out the operation on it. Depending on the result, you could dequeue the element - or just return, and the next time the method is invoked, it will peek the same object from the queue and try to perform the same operation again.

If you're processing data directly, like calling a stored proc or performing a LINQ-to-SQL operation, you can try and call the method recursively. Yes, it doesn't feel as good as the other approach, however it's easy to implement in a smaller solution without refactoring to the queue processing solution. (One thing to be aware of here, is that if you call the method recursively enough times, you will end up with a StackOverflowException. You can avoid this by checking the current call stack, using System.Diagnostics.StackTrace.)

In the end, if you do have a lot of problems with timeouts, and (or) you really need to be sure that your SQL work is carried out, no matter what - you need to look to Microsoft Message Queueing, or MSMQ, which is Microsoft's suggested solution. Leave the critical SQL stuff to the MSMQ handler and let your application simply be a MSMQ messenger.

Mysterious user access errors in an SQL database?

Hi,

Ever experienced some strange error messages like "The table either does not exist or the current user does not have permissions..." when trying to run queries or stored procedures on a database with a user you're sure has access? You check the user's access right to specific objects and everything seems correct? Hmmm? Well if you do, use this simple command on the database in question to see if you have an orphaned user problem:

sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 

   @LoginName='<login_name>';

If you get some usernames returned then that means you have an orphaned user problem. This can happen sometimes when you backup from one server and restore to another, like your test server. What can happen is that if the SQL logins have different SID then the link between your sql login and your database login is lost.
So, to fix this you run this command on the database:


sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 
   @LoginName='<login_name>';



And that's how you fix that!

Automatic web deployment from TFS build

We have recently started using Team Foundation Server 2010, and having a CI build running for every check-in was one of the things we were really enjoying. Then I saw Scott Hanselman's talk on web deployment - if you're using Xcopy, you're doing it wrong!

Of course, once I saw that , I  wanted an automated deployment to the development environment every night or so. To get that going, I had to do some research.

Just so you know, the project I'm talking about consists of  three web applications, one DAL and two presentation layers. Of course all three need different configurations for the different environments (dev, test and production).

Web.config transformations
And as it turns out, there is a neat function in visual studio 2010 called "web.config transformations" for exactly this purpose. You can read about it here at Scott's blog.

The most obvious example for using web.config transformations is probably database connectionstrings, but you  can replace most things using this simple syntax:

<setting name="Your_Setting_name" serializeAs="String" xdt:Transform="Replace" xdt:Locator="Match(name)">        <value>Your value here!</value>      </setting>

The web.config translates directly to the build configuration. So if you're building in "Debug" you're going to use the web.Debug.config file for transformations.  In our setup, this is the CI build. For the nightly automated deploy, we're going to need a separate target, so we created one called  Dev-Snapshot, (perhaps it would have been even more apt with Nighly-Dev - but let's ute Dev-Snapshot for the remainder of this post).

Then you need a build defintion on the TFS that uses the new build configuration. For instance, call it Dev-Snapshot like the build configuration, under Process and "Items to build", select the correct project and your newly defined build configuration. Now whenever you build using the Dev-Snapshot in Visual Studio, the original web.config will be used. Transformations are only applied when you publish - it will automagically transform the web.config using the web.Dev-Snapshot.config into the final web.config inside the deployment package.

Publish settings
Now we have a working build of the development environment, tailored for deployment on our development server.  So how do we do the automatic deployment? Maybe there's a switch in TFS Build for it? No such luck.

The first thing you need to look at is your web project's publish settings. They too relate to the build configuration, so make sure you have selected the right one - again, for instance, Dev-Snapshot.

Most interesting bits: Under items to deploy, select "only files needed to run the application" - no .cs files, .csproj etc are not included in the package, just the "necessary files" (more on that later).

You can choose to include database settings (out of scope for this post) and run any setup or change scripts directly. However, this probably fits best for any single-server solution, where you have one database server and one web server - in a web farm, you pretty much need to update all nodes whenever you change the db, though I suspect you might be able to run the db package only for the first node or something similar.

Under Web Deployment Package settings you may choose to package as a zip file. I think this is useful. You need to specify where to create the package, and the name of the web site. This name must match the actual name of the web site you're planning to update on the server.

After these settings are set, you may choose to test that they are actually working. Do this by rightclicking the project and select "Publish". What the TFS will do later on is actually build a deployment package - which you can do by right-clicking the project and select "Build deployment package". Then go look in the destination you provided in the settings - you should have some files there.

Read more about this in Scott Gu's blog post.

Deploying from TFS
Now that we have a working web.config transformation and web publish settings, the stage is set for the TFS Build server (and MSBuild in the background). In Team Explorer, go to Builds and right-click the one you're working with. Then go to the Process section, and under "Items to build" make sure you have the correct project with the correct configuration selected (in this example - Dev-Snapshot configuration).

Open the "3. Advanced" section and go down to MSBuild arguments. Enter

/p:DeployOnBuild=True /p:IsAutoBuild=True

The first one tells MSBuild to build a deployment package after the build has completed, the second tells TFS that this is an automated build, as opposed to a manually triggered one.

If you try queueing this build now, it should be working fine. You can open the build drop location to see the deployment package. But there's one step missing  - the deployment itself! /p:DeployOnBuild=True  as mentioned, only tells MSbuild to create the deployment package. It does not run the deployment script. That's not really automatic deployment...

So what I found out is that you can use a post-build event to call the script. The post-build event is found by right-clicking the project and selecting Properties, then Build events. Surprise, surprise, the Build Events are common to all configurations! which means, you can't simply run the script here, you have to run it according to the configuration. Or else, you could end up with any build ending up on your production server. Bad news.

Enter a condition that will be true for your build - $(ConfigurationName) is a build variable that matches the Build configuration - and then create the command needed to execute the script:

if "$(ConfigurationName)" == "Dev-snapshot" "$(TargetDir)_PublishedWebsites\Projectname_Package\Projectname.deploy.cmd" /Y /u:publishuser /p:secret! /M:yourwebserver

Of course, your configuration will vary from this, but the basic idea should be clear, and while a little messy this can be extended to as many builds as you like, just keep on adding conditions.

If you have some common settings for some builds - let's say your development and test environment has some common tasks - you can distinguish them with the IsAutoBuild parameter - $IsAutoBuild == 'True'.

Now, if you queue up this build, you should actually have a ready published web site. Or a failed build. Make sure you have installed the web deployment package on the remote server, opened the correct port in the firewall and that the service is indeed running! Details here.

Missing files?
Well the deployment is now working - new files are appearing on your web server when you build. But in my case there were at least some files that weren't appearing. Also there were some empty directories, that would be populated at runtime, suddenly missing. Strange.

So you may remember that setting for "Only files needed to run the application". It determines by itself what is needed. Turns out, .class files, .pdf's, .zip and .csv files are "not needed". In addition, empty directories are not included in the deployment. It actually half makes sense, though there should be some way for the developer to specify the details.

However, there is not in the GUI - but there is in the build process, if you use the build targets inside the project file! Sam Stephens has a blog post on this. You can either add this directly to your project file, or you can create a separate .target file for tidyness. A word of caution for the latter, while more tidy, it requires you to close and reopen the solution (!) for changes to be effective. Inside the project file you "only" need to unload and reload the project.

Here's the target configuration for the missing files in my project (the \**\ just means all directories, recursively):


   <PropertyGroup>
    <CopyAllFilesToSingleFolderForPackageDependsOn>
      CustomCollectClassFiles;
      $(CopyAllFilesToSingleFolderForPackageDependsOn);
    </CopyAllFilesToSingleFolderForPackageDependsOn>
  </PropertyGroup>


<Target Name="CustomCollectClassFiles">
    <ItemGroup>
      <_CustomClassFilesForRootFolder Include=".\**\*.class;.\**\*.zip;.\**\*.csv;.\**\*.pdf">
        <DestinationRelativePath>%(RecursiveDir)%(Filename)%(Extension)</DestinationRelativePath>
      </_CustomClassFilesForRootFolder>
      <FilesForPackagingFromProject Include="%(_CustomClassFilesForRootFolder.Identity)">
        <DestinationRelativePath>.\%(RecursiveDir)%(Filename)%(Extension)</DestinationRelativePath>
      </FilesForPackagingFromProject>
    </ItemGroup>
  </Target>


That covers the missing files, but the empty folders need another target. Or, you can simply add an empty file in the folder for it to be picked up, but that is really a hack.

I found a better way of doing it through the AfterAddIisSettingAndFileContentsToSourceManifest - a target that is defined by the web deployment publishing pipeline. It's kind of hard to find a complete list of the targets involved, but feel free add one in the comments.


<PropertyGroup>
    <AfterAddIisSettingAndFileContentsToSourceManifest>
      MakeEmptyFolders
    </AfterAddIisSettingAndFileContentsToSourceManifest>
  </PropertyGroup>


<Target Name="MakeEmptyFolders">
    <Message Text="Adding empty folder to hold snapshots...$(_MSDeployDirPath_FullPath)\SnapshotImages" />
    <MakeDir Directories="$(_MSDeployDirPath_FullPath)\SnapshotImages"/>
  </Target>



There. Missing files and folders no more.

Minifying javascript Build-Time
Since this is a web application, I will add a final note about the minifying task. I used the Ajax Minify from Microsoft, but I'm sure the same applies to the other tools as well, just with a different command line. Before I started on the automatic deployment project, we were using the AfterBuild target for minify operations, which worked out well, when a batch script (using robocopy!) was doing the deployment.

But when the deployment package is built, the AfterBuild target is not yet entered. So we switched to BeforeBuild to get around that - and it works perfectly. Unless you are emitting javascript files from your build, the same approach should be fine for you.

Here's what happens: First, delete the old files. Then, concatenate any separate files into one big .js file. Then, minify that file. In addition, you obviously have to reference that minified .js from your master page or what ever other page you need it in. I'd also suggest renaming the .js file for each version to avoid caching problems, but that's another story.



<Target Name="BeforeBuild">
     <!-- we need to minify the .js files before build, because they must be included in the deployment package -->
    <Message Text="Going to delete old JS files..." />
    <Delete Files=".\Controls\Javascript\Concatenated.js;.\Controls\Javascript\Concatenated.min.js" />
    <Message Text="Concatenating JavaScript files..." />
    <ItemGroup>
      <InFiles Include=".\Controls\Javascript\*.js" />     
      <InFiles Include=".\Controls\JsFolder\*.js" />
    </ItemGroup>
    <ReadLinesFromFile File="%(InFiles.Identity)">
      <Output TaskParameter="Lines" ItemName="lines" />
    </ReadLinesFromFile>
    <WriteLinesToFile File=".\Controls\Javascript\Concatenated.js" Lines="@(Lines)" Overwrite="true" />
    <Message Text="Minifying JavaScript files..." />
    <ItemGroup>
      <JS Include=".\Controls\Javascript\Concatenated.js" />
    </ItemGroup>
    
    <!-- Minify javascript files in .\Controls\Javascript -->
    <AjaxMin SourceFiles="@(JS)" SourceExtensionPattern="\.js$" TargetExtension=".min.js" CollapseToLiteral="True" LocalRenaming="CrunchAll" OutputMode="SingleLine" RemoveUnneededCode="True" StripDebugStatements="True" EvalsAreSafe="True" InlineSafeSettings="True" CombineDuplicateLiterals="True" CatchAsLocal="True" />
  </Target>

Wake On Lan..not so easy.

Hi, just a quick tutorial on how to make you pc wake up remotely

from sleep mode. One would think that this is just one click

operation, but no:

NB: Mind you this is a ASUS P5Q deluxe motherboard- sure

settings are different on other mb.

1. BIOS: you need to enable wake on LAN or as in my case:

Power- APM configuration - Power on by PCI = Enabled

Advanced - Marvell LAN 2- LAN Boot Rom = Enabled

2. Win7 :

Control panel- Network and Sharing Center- Change adapter settings:

Right click on the "Local Area Connection" in use, then click configure.

Choose the Advance tab: "Energy Star" =Disabled,

"Wake From Shutdown" = On,

Power Management tab: All of the checkboxes should be checked.

3. Install a program which sends the magic packet.

I use this one : http://www.depicus.com/wake-on-lan/wake-on-lan-gui.aspx

simple, but does the job.

PS: there is also an Android app for this at Android Market.

NB -When using the phone you have to establish a VPN

connection before using the WOL application.

4. Now you can use Remote desktop or something similar to connect to your pc.

Ref this forumpost : http://vip.asus.com/forum/view.aspx?id=20080721172544250&board_id=1&model=P5Q+Deluxe&SLanguage=en-us&page=1

Entiy Framework Add Function Import - Get Column Information returns nothing..

I needed to update a sql stored procedure in our entity data model. In the Model Browser I clicked "Update Model from Database" and then double clicked on the SP under functions import to open the "Edit function import" wizard so I could make the complex return type from the SP. To my surprice the import wizard told me that "The selected stored procedure returns no columns"? I knew for a fact that the SP returned several rows.

After a little Google searching I found a solution in this forum post from "Brian”: Microsoft forum

Quote: "

it seems Entity Framework will try to get the columns by executing your Stored Procedure, passing NULL for every argument. You can see this by putting a trace on your SQL Server.

So - first make sure your S_P will return something under these circumstances. Note it may have been smarter for Entity Framework to execute the Stored Proc with Default Values for arguments, as opposed to NULLS. Never mind - nothing we can do about that!

However - before trying to run the Stored Procedure, ER does this

SET FMTONLY ON

This will break your stored procedure in various circumstances, in particular, if it uses a temp table.

So, add to the start of your Stored Procedure:

SET FMTONLY OFF;

This worked for me - hope it works for you too.

brian

"

One other work around or maybe a better solution is to not use temp tables at all, if you don’t need too index those tables that is.

If you use table variables, the SET FMTONLY ON; option will not break in your stored procedure. Performance wise, except indexing option on temp tables, they seem quite the same.

Ref this article:

SQL Rocks Temp Tables vs Table Variables

Cheat Sheets from AddedBytes

I don't work with regex every day, the same applies to many developers I guess.

So I was very happy when I found the excellent regex cheat sheets from IloveJackDaniels.com. But here one day it turned out that page was gone - so to save anyone else the trouble, they have moved to addedbytes.com.

And here's a link to their cheat sheets:

http://www.addedbytes.com/cheat-sheets/

You'll find two versions of Regular Expression cheat sheets, along with CSS, Python, Javascript and more.

ASP.NET Web service gotcha

The last 30 minutes spent debugging why suddenly my .asmx didn't show the default web service front end any more. I could not invoke the service through the browser.

Then I tried looking at the wsdl, which gave me a clue...

System.InvalidOperationException: Both System.String InsertNewUserInCRM(MBL.UserData.CobraMontelUser) and System.String InsertNewUserInCRM(System.String, System.String, System.String, System.String, System.String, System.String, System.String, System.String, System.String, System.String) use the message name 'InsertNewUserInCRM'.  Use the MessageName property of the WebMethod custom attribute to specify unique message names for the methods.

So now you see what I saw: I tried overloading web methods, which of course won't work. But there was no build error, no warning in VS and no error on the web page!

Fix: Either create a new name for the second method, or change the messagename, for example:

[WebMethod( MessageName ="InsertCobraMontelUser")]

Culture Performace .NET 3.5 vs .Net 4.0

After converting an application from .net 3.5 to .net 4, I noticed the performance was really reduced at the application startup, where it loads and processes lots of Quote data.

The Performance Wizard clearly showed that the function doing the most work was System.DateTime.Parse(string). If I changed the Target Framework for the application to 3.5, it was again back to normal behavior.

I then made a test console application with a single loop trying to parse 2000 dates to an array. The elements was processed at the same time in both frameworks, so I was clearly missing something.

Framework	Ms
.NET 3.5	4
.NET 4.0	3

I then noticed the only difference, was that I in the initial application changed the current culture in the function that was processing the data, like this: System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("nb-NO"); After adding this culture change to the test app, the .NET 4 framework was using considerable more time than in .net3.5:

Framework	Ms
.NET 3.5	398
.NET 4.0	2835

What they changed from .NET 3.5 to .NET 4 did not have time to figure out, but I found an easy solution. Instead of changing the culture of the thread, I set the culture when parsing like this:
.. = DateTime.Parse(feedarr[soucreIndex], CultureInfo.GetCultureInfo("nb-NO"));

It then used 5 ms

Framework	Ms
.NET 3.5	6
.NET 4.0	5

I then took it further in using parseexact: DateTime dd = DateTime.ParseExact(feedarr[1], "dd.MM.yyyy HH:mm:ss", CultureInfo.GetCultureInfo("nb-NO"));

It then took 3 ms in .NET 4.0.

Framework	Ms
.NET 3.5	6
.NET 4.0	3

In a perfect world we would be no regional settings, but until further we are stuck with parsing the date format. As this post shows it’s important to do it the right way … 3 ms is a lot faster than 2 seconds 2835 milliseconds

Happy programming
Petter Søreide

E-mail reporting from Maintenance tasks in SQL Server 2008

Just migrated two databases to a new 2008 server, when I discovered something weird: You cannot choose in any way to alert operators on error in the maintenance plan. Under reports settings, there is a checkbox, but it's disabled, and I found that other users are not able to check it either. Plus it only allows you to send a report every time. Which is not what I want...

The only workaround I found applies to scheduled tasks, which I guess is reasonable - if you run the job manually, you shouldn't need an e-mail to see if it failed. SQL Server creates a job for you, that runs the task, and for jobs, you can set the Notification properties to send an e-mail.

Sending XML input to a web method expecting a string

I'm working on a project where I receive data in XML format. So far I have been using the auto-generated .asmx front-end form to send inn small XML one-liners just for testing my parsing logic.

But when I wanted to use soap UI to send in larger portions, not to mention being able to save the whole scenario for later use, I ran into a problem. Whatever I sent in I only got "#status# HTTP/1.1 400 Bad Request".

Even just sending just the XML declaration gave me this problem!

But when I sent the exact same string from the auto-generated form, it was working perfectly. Which led me to believe there was (as usual) someone working behind the scenes, doing things without asking me.

Finally I found a post with the answer I was looking for.

In short, wrap your XML inside a CDATA section, and you're good to go.

How to get the values of an enum?

Sometimes you need to list the values of your enums. Typically, I have to represent them in dropdown-boxes for a web page. Here's my solution:

public static ListItem[] GetEnumValues(Type t)

{

List<ListItem> lc = new List<ListItem>();

lc.Add(new ListItem() { Text = "Choose an item", Value = "-1" });

if (t.UnderlyingSystemType.BaseType == typeof(System.Enum))

{

foreach (int value in Enum.GetValues(t))

{

lc.Add(new ListItem() { Text = Enum.GetName(t, value), Value = value.ToString() });

}

}

return lc.ToArray();

}

Then, to use it in a control, use the Items.AddRange() function:

_dropdown.Items.AddRange(SomeClass.GetEnumValues(typeof(myEnumType)));

Plain and easy, but as far as I know, not available directly from .NET.

Finding stuff in Visual Studio 2008

Usually when I try to find something in my code, instead of relying on my memory to help me locate it, I just press Ctrl+F on my keyboard, and let Visual Studio do the finding. If you didn't already know, you can also press Ctrl+H and go directly to the replace dialog (where you can search with wildcards, but you can't replace with them - that would have been a nice feature!).

However, the find and replace dialog is a pretty powerful tool to locate stuff in your code, if you know how to use the options: Match case, match whole word, Search up, Search Hidden text, and Use. The first four are pretty straight-forward but if you don't know them or understand them from their names, here's a very quick update:
Match case - if you write CONST, VS will not find const, Const or COnSt.
Match whole word - if you write sum, VS will only find that, and not summary.
Search up - default is down, but you can search up. "Search up" feels buggy for me if you search in anything else than current document.
Search hidden text - collapsed or otherwise hidden sections


Wildcards
If you check the "Use" checkbox, you can select an option from the dropdown, either Wildcards or Regular Expressions. Let's try the first one first, it is by far the easiest and for my part, most used. Wildcards are ?, * and #, ? representing a single character, # a single digit, and * being pretty much anything, like you would expect.

If you want to find a single digit, you can search for #, double digit is ##, and you get the picture. The same goes for ?, search for ?nt and get and, int, ent... I really don't use it that much. If you'd like to find all numbers in their forties, type 4# and there they are. Or use Int## if you would like to avoid other types of Int in your code. Might come in handy if you are looking for that special number...

The * is my favorite. If I'm looking through a project to find a string that says "Sql-something", I'll just write Sql* and go. That's not very much different from searching for Sql without checking "Match whole word" though, so try finding "*yste*" and see if you find System. Or combine with # and ? for interesting effects.

You can even look for things like using( ... ), search for "using(*)" will help you. Tip: What if you want to find places you aren't using using? Search for example for *Connection(); or *Context(); - the semicolon will not be there if there is a using( ) wrapped around it. Or even search for new *(); to find any instance of an object that's not inside a using block.

And if you do want to search for # or * or ? in your code, and still use the wildcards, you can escape the characters using the backslash (\).

Regular expressions
For the regular expressions, or regex as most developers call them, there is a very good cheat sheet available at addedbytes.com (previously ilovejackdaniels.com). It's a very powerful feature, but thankfully I don't come across many situations where I need it.

Microsoft has a reference to some short-hand expressions that VS understands,

You could imagine having to find e-mail addresses referenced in an old app that you have inherited from another developer, who was fond of using @ in his code, perhaps in sql script written inline? You could go with regex and find only valid e-mail addresses. Or hex numbers, or all caps or all lowercase or enclosed in quotes, any kind of text that matches a given pattern.

Performance testing of Dictionary, List and HashSet

Update June 4, 2009
The original post favored Dictionary as the fastest data structure both for add and contains. After some debate and some re-runs of my test code, I found the result to be wrong. HashSet is faster. Probably the results were affected by the workload on my computer. I ran one List test, then a Dictionary test and finally a HashSet test - I should have run them multiple times and at the same workload (i.e. no programs or processes running). Anyway, on to the article.

Still working with high-volume realtime datafeeds, I'm struggling to understand where the bottleneck in my code is. It's not with the database, it's not with the network - it's somewhere in the code. Now that doesn't help a lot.

So I decided to have a look at the different data structures that could be usable for my needs. Right now I'm using a List to keep track of my orders, and each time I get a new order, I check that list for the given ID.

So I'm starting to wonder, maybe there is a performance issue with the List data structure. So I made a small test with the following code:

static void TestList()
{

var watch = new Stopwatch();

var theList = new List<int>();

watch.Start();

//Fill it
for (int i = 0; i <>

{

theList.Add(i);

}

watch.Stop();

Console.WriteLine("Avg add time for List: {0}", (watch.Elapsed.TotalMilliseconds / noOfIterations));

watch.Reset();

watch.Start();

//Test containsKey

for (int j = 0; j <>

theList.Contains(j);

}

watch.Stop();

Console.WriteLine("Avg Contains lookup time for List: {0}", (watch.Elapsed.TotalMilliseconds / noOfIterations));

}

I created similar test code for Dictionary and HashSet. In all of the tests I used a loop where the given key always existed in the data structure. I used Add instead of Insert, because of tests I've seen online show that this is much faster. For example, this one.

First run, I used 1,000 for the noOfIterations variable and ran it three times.

The horizontal scale here is in milliseconds, so things are reasonably fast. A value of 0.2 gives you a possible 200 adds per second. As you probably can see without checking the numbers, dictionary is faster. List is slower for lookup, but HashSet suprises a little with the slow add function. So what happens when we go to 100,000 items?

OK, List is a lot slower for lookup still. Add seems to compare ok though. Let's see how it compares if we remove the lookup-time from the chart: This is wrong, see "The aftermath"

Now that's a result! Turns out List is your winner if fast adding is all you care about. If you want to look up your values later though, dictionary is your winner. Hope this will save some time for other developers in the same situation. Also feel free to comment if you find different results, or a problem with my test!

By popular request: The rest of the code!

static void TestDictionary()

{

var watch = new Stopwatch();

//Create Dictionary

var theDict = new Dictionary<int, int>();

watch.Start();

//Fill it

for (int i = 0; i <noofiterations;i++)

{

theDict.Add(i, i);

}

watch.Stop();

Console.WriteLine("Avg add time for Dictionary: {0}", (watch.Elapsed.TotalMilliseconds/noOfIterations));

watch.Reset();

//Test containsKey

watch.Start();

for (int j = 0; j <noofiterations;j++)

{

theDict.ContainsKey(j);

}

watch.Stop();

Console.WriteLine("Avg Contains lookup time for Dictionary: {0}",

(watch.Elapsed.TotalMilliseconds/noOfIterations));

}

static void TestHashSet()

{

var watch = new Stopwatch();

//Create List

var hashSet = new HashSet<int>();

//Fill it

watch.Start();

for (int i = 0; i <>

{

hashSet.Add(i);

}

watch.Stop();

Console.WriteLine("Avg add time for HashSet: {0}", (watch.Elapsed.TotalMilliseconds / noOfIterations));

watch.Reset();

//Test contains

watch.Start();

for (int j = 0; j <>

{

hashSet.Contains(j);

}

watch.Stop();

Console.WriteLine("Avg Contains lookup time for HashSet: {0}", (watch.Elapsed.TotalMilliseconds / noOfIterations));

}

The aftermath
Because of the controversy involved in HashSet being slower than Dictionary, despite having only one value and the Dictionary two, I re-ran the test on my computer, and on a colleagues. Instead of running the tests one by one, I ran three times HashTest and three times Dictionary, and picked the averages. The result you can see below, HashSet is faster than Dictionary both for Add and Contains methods.

Exchange ActiveSync on my SE W715

I got a new phone! After three years of windoze mobile, I decided to go back to a regular phone. But I wanted e-mail, or rather, exchange activesync. So I landed on a Sony Ericsson W715. But after completing my setup, the only message I got was "Session Failed". Huh?

I found a promising blog from a Swede, but even that (allowing non-provisional devices to use ActiveSync on the Exchange Server) was not enough.

Finally we found that the connection attempts were being blocked by the ISA server (Microsoft firewall). By allowing All users, not just Authenticated ones, to connect to OWA, I was able to synch my emails.

Vista, Hibernate and Windows Update

My laptop lives a boring life. Every morning, I check news and e-mail before going to work. Then I put the machine in hibernate, just to be able to switch it on in half a minute. Maybe I'll go online while watching tv in the evening, and then it's back to hibernate. But in the middle of the night, my laptop lives it's own life. I started suspected something when I woke up to an empty battery every day. Strange. Annoying.

I found the problem after some days, when not hibernating, the battery was happy. But what was happening? As far as I know, hibernate means putting the machine in a power-off state - but saving the contents of RAM, enabling us to start from where we left off when we resume. Turns out this can't be quite true. Cause every night at 01:00 AM my computer would turn itself on - because I'd set Vista to check for automatic updates at that time.

I wasn't quite sure if this was real or not until today, when my colleague Petter showed up at work, telling me had discovered the same thing. His laptop woke him up at 4AM to get the newest updates.

So if you're having mysterious battery-trouble with a laptop running Vista - check your update settings.

How do you backup an unknown number of SQL server databases

How do you backup an unknown number of SQL server databases?

Consider a scenario where an application you don’t control is using your SQL server as a place to store data, and this application is creating databases on the fly.

This can be the case for several types of applications.

Now, using the built-in maintenance-plan wizard will only get you so far, since it will only apply to the databases already existing on the server at time of creation, not to databases created after the plan, so it will have to be continuously maintained.

That’s a bad solution, and if forgotten, some databases will not be backed up.

I will choose to give you two alternatives to solve this problem, the first is using a cursor to accomplish what we want, - and yes, i can already hear you mumbling about “cursor” and words like “ never”, “devils work”, “performance” and “must be some other way” and so on.. – Well, there are several ways to accomplish what we want, and a cursor is one of them, so don’t sharpen the prongs of your pitchfork just yet, i will explain later. Another way is using 2005’s new row_number() function and a in-memory table variable, and a standard execute (@sqlcommand) and this is also a very effective way.

Using a cursor for a backup task that is to run maybe once every night, or every Sunday after midnight is quite ok, the performance problem with cursors is widely agreed upon and I will not argue that they are not, but this is not a procedure that will be called by sixty thousand web users every minute, if that was the case, I would also abandon cursors, but in this case, it’s not such a bad idea.
So to the first code snippet: we will call this: DoDatabaseBackup1

create proc DoDatabaseBackup1
as
declare @DBname varchar(254)
declare @Fixedpath varchar(254)
declare @FileName varchar(254)
set @Fixedpath = 'c:\sqlbackup\'

declare getDBName cursor for
select [name] from sys.databases
where [name] NOT IN ('master','model','msdb','tempdb')

open getDBName
fetch next from getDBName into @DBname
while @@fetch_status = 0
begin
set @fileName = @Fixedpath + @DBname + '_' + convert(varchar(8),getdate(),112) + '.bak'
backup database @DBname to disk = @fileName
fetch next from getDBName into @DBname
end

close getDBName
deallocate getDBName

This procedure will loop through any user-databases there is on the server and place a backup file of each on the location of your choice, simple as that. Not big, not difficult to understand and works like a charm. Create a scheduled job under sql-server agent and you should be home free. (and the cursor performance problem is not what will take time here, the backup of each database will take considerably more out of the server…)

But, since cursor IS the work of the devil :), to solution number two:

This one is called (who would guess): DoDatabaseBackup2

The logic is more or less the same, but instead of a cursor we use a table-variable and then use this to build a sql-command-string.

create proc DoDatabaseBackup2
as
declare @sqlstr varchar(max)
declare @userdbs int
declare @counter int
declare @fixedpath varchar(254)

declare @dbnames table (dbrow int,dbname varchar(254))

set @counter=1
set @sqlstr=''

set @fixedpath='c:\sqlbackup\'

insert into @dbnames
select row_number() over(order by [name]) as dbrow,[name]
from sys.databases
where [name] not in ('master','tempdb','model','msdb')

set @userdbs=@@rowcount

if @userdbs>0
begin
while @counter<=@userdbs begin select @sqlstr=@sqlstr+(select 'backup database '+dbname+' to disk = '''+@fixedpath+dbname+'_'+Convert(char(8),getdate(),112)+'.bak'';' from @dbnames where dbrow=@counter)
set @counter=@counter+1
end
end
exec (@sqlstr)

Both of this procedures will result in the same, a backup of all userdatabases regardless of name and numbers, and you don’t even have to know anything exept that there is space available on the receiving location..

Other solutions also exist, but this will do for now….

/Leo