Code from the everyday working developer
Header image

T-SQL Table Variable vs. Temporary Table

Posted by Jens in MS SQL - ()

Most of the time you don’t gain very much performance from obsessing about details but instead from focusing on finding flaws in the general design.. Once in a while it’s still fun to obsess about the details though, and this post is about just that, details.

I find myself using both temporary tables and table variables in MSSQL without any preference for either or, so I decided to dive a little deeper into if there is a difference, and more importantly when I should be using what.

The biggest difference between the two is that temporary tables are actually created on the disk like a regular table. This also in turn means that temporary tables are pretty much like any other table and support all the things you are used to. Table variables are instead stored in memory and suffer from limitations with the usage of constraints, default values on columns, as well as no indexes.

When reading the above, temporary tables seemed like a clear winner in most any situation, but as with all things first impressions can be deceiving. The “truth” is that temporary tables are not the best option when dealing with smaller sets of data as there is a good chance that the sql server will decide to update statistics and force a recompile. So in the case of smaller datasets table variables should actually be faster.

To get some real numbers on what google was telling me I created the simple test case below, and as predicted the table variable was faster when doing a insert and select with 100k rows (small data set).
But as with all things results will probably vary so try the code out for yourself, and let me know if you come to a completly different conclusion.

(more…)

, , ,

Installing Apache2 PHP5 MySQL PHPMyAdmin in Debian

Posted by Jens in Linux - ()

This website is running on a debian server and a few days ago I had to reinstall it because of a minor miss on my part. When I was reinstalling I was happy enough to have saved the line below which installs everything you really need to get going with a linux web server.

apt-get install apache2 mysql-server php5 php5-mysql
  php5-cli php5-imagick php5-gd php5-mcrypt php5-curl phpmyadmin

Apart from this you may want to install nano (if its not already installed), the best texteditor for debian in my opinion.

apt-get install nano

You configure Apache2 by going into

#switch directories
cd /etc/apache2
# list directory contents
ls
# results in
apache2.conf  envvars     mods-available  ports.conf       sites-enabled
conf.d        httpd.conf  mods-enabled    sites-available

and modifying ‘apache2.conf’ to your liking.

nano apache2.conf

You may also want to note the mods-available / enabled and sites-available / enabled

You can check out an in depth explanation of how to set up your server with multiple web sites here:
http://www.debian-administration.org/articles/412

, , ,

SQL 2008 and the Merge statement

Posted by Jens in Microsoft | MS SQL | SQL Server | Windows - ()

I recently had the pleasure of playing around with the Merge statement which was introduced in SQL Server 2008 and I am pretty impressed by it. Although it dosen’t add any new functionality it makes the task of synchronizing two tables in or between databases much faster (and simpler).

Here is a self contained example of how the Merge command can be used to move data from one table to another, inserting new values and updating old ones.
(more…)

, ,

XNA – Alpha Mapping with Pixel Shaders

Posted by Jens in Windows | XNA - ()

I’ve recently been playing with an idea to make a simple space shooter for the xbox with Micorsofts XNA framework.

As always I don’t want to take the simplest path when doing things, and instead try to do something the hard way without shortcuts and hopefully learn something I dont know while doing it.

So far in this project I’ve been able to implement Farseer (a very good physics engine for C#) and have added a few boxes, a destructible planet with gravity and a simple sprite effect (bullet trails and engine smoke).

Of those things the destructible terrain was a bit difficult but the most difficult was making the terrain of the texture disappear as it is destroyed.

There are several places on the net where you can find people doing this using the SetData and GetData methods (Riemers XNA Tutorials explains this in detail). However, when there is alot going on you will notice slowdowns with this method. The simple reason behind the slow downs is that the Set/Get -Data method brings the texture back from the GPU to your RAM, and the calculations are then done on the CPU.

So thinking there should to be a better way to do this I googled alot and found that what I want to do is create a  alpha map pixel shader…

(more…)

, , , ,

SQL Server 2008 and CLR Procedures – Simple Example

Posted by Jens in Microsoft | MS SQL | SQL Server | Windows - ()

The general idea behind CLR procedures is that you can execute code from the SQL server. This is usefull when you reach the limits of what T-SQL can do without making your eyes bleed or as in my case need to interact with webbservices.

So just to get started with this I wanted to create a simple Hello World Example using Visual Studio 2010 that would take an argument, and return a slightly modified value.

First off we need to create a new project. So load up Visual Studio 2010 and create a new project of the type:
Database > SQL Server > Visual C# SQL CLR Database Project.

Make sure you select .NET 3.5 as SQL 2008 can not use CLR procedures compiled using .NET4.0

clrhelloworld_createproject

(more…)

, , , , ,

A Visual Explanation of SQL Joins

Posted by Daniel in MS SQL - ()

A few years ago I found a visual description of how SQL Joins works and a few days ago when a colleague asked for help with joins, I just had to look up the description again. So here’s a link to a visual description of how SQL Joins works. (LEFT, RIGHT, INNER and CROSS JOIN)

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

MVC3: Only drop and recreate tables when model changes.

Posted by Jens in ASP.NET - MVC3 - ()

In a recent project I ran in to an interesting problem related to what MVC3 does when the database model changes.

If you have worked with the Code First approach with MVC3 you have probably at some point during the project changed some part of the model, when you do this MVC3 realizes that the model you have defined in the code and one saved in the database differ in some way. The framework knows this by checking the table [EdmMetadata] in the db. [EdmMetadata] contains one single row with a hash calculated on the model, when the hash saved in the database differs from the one the framework calculates at each request it knows that something is off.

When that happens MVC3 can’t magically fix whats wrong so the only option you are left with if you want to automate the change is to use:

Database.SetInitializer<DatabaseModel>
(new DropCreateDatabaseIfModelChanges<DatabaseModel>());

Which drops and recreates the entire database when something in the model changes.

So what happens if you are using a webprovider which does not allow you to drop and recreate an entire database?
Well nothing really except that the code gives you an error saying that it has failed in a very bad way.

(more…)

, ,

Google Analytics + Google Charts

Posted by Daniel in ASP.NET - MVC3 | Google Tools - ()

How to generate a Google Analytics Chart on your site
(full source code at bottom)

For a couple of days ago i hade an idea of adding my Google analytics stats to my website (Pastebucket).
After googling on this i noticed there aren’t any good explanations on how to do this. So here we go…

Prerequisites:
Google Analytics account (With some visitor stats).
A Google Charts wrapper: http://code.google.com/p/googlechartsharp/
GData API: http://code.google.com/intl/sv-SE/apis/gdata/docs/client-libraries.html

In the following example I’ll show you how to generate a graph over visitors this month. The Google Charts wrapper we are using will generate an image URL to us that we will later on use in our HTML/Razor code. The graph will scale itself to the next higher 10′s, so you dont have to worry about this.

Chart

(more…)

Lync, and why it’s so good

Posted by Andreas in Uncategorized - ()

Maybe this post is a little bit rushed, but I just have to talk about Microsoft Lync Server 2010.

At the company where I am IT-manager we have used Cisco CallManager 6.0 with Unity and Presence Server to have a cheap and efficent IP-PBX. We have saved alot of money if we compare to a traditional PBX, but it hasn’t been without hick-ups.

First of all, Cisco is this huge network infrastructure slash software slash ten other things and all their software is at least ten year behind everything else and very expensive. I can appreciate that they focus on stability, that’s a very good quality in a networking product (or PBX) but that’s no excuse not to make a product usable and attractive. Cisco got a lot of criticism for not upgrading their VPN Client to support 64-bit OS, and rightfully so. They tried to force their customers to a new product, Cisco AnyConnect which in theory is better (for example it uses SSL with works with most firewalls) but due to several bugs in the IOS software it was unusable for the company I work at. Let’s just say we had a hard time with it.

We definitely had a hard time with the TAPI-client. A TAPI-client let’s you remote controll your phone with your computer, you can click to call and you can receive caller ID into the computer. At first we had Cisco CallManager Express, but that TAPI-driver was the most bloated and bad peice of software/drivers I’ve ever seen. We upgraded to the full version of CallManager and the TAPI-driver became a little bit more stable. Then we started using 64-bit clients. I don’t think Cisco has a 64-bit TAPI-driver to date. They do have other technologies, more “modern” but they are locking you to Cisco technology (and not very existing technology) and with the history I have with Cisco I’m not interested in that.

We actually started using Microsoft Office Communicator with Cisco by buying a Cisco Presence Server. We did it with RCC (click to dial), not with Ciscos bloated (and by Microsoft denounced) Cucimoc-plugin. It work, but we couldn’t catch the incoming numbers with the MOC API, and it sort of felt like we were halfway between two system. The users quickly identified what the liked the most with Office Communicator and it was the IM-part of it. Without no statistic to back my claim up, I think our communication got a lot more efficient with MOC.

Then Microsoft announced Lync Server 2010, the successor to Microsoft Office Communicator. Lync Server is a IP-PBX but also a IM-system, a videoconfecering, a webinar, a desktopsharing, presence and with Exchange 2010 it’s even a voicemail. All things integrated, modern, cheap, easy and accessable. I could talk for hours about all of it, but just let me say that it’s the system we have been waiting for.

But, of course, not without hickups, our current Telephony Service Provider doesn’t support a Direct SIP connection to Lync. We have a promise that they will, and we are pushing them to do with all means necessary.

Why Microsoft shouldn’t dump the name “Windows”

Posted by Andreas in Microsoft | Windows - ()

Paul Saurez at PC World (and in Swedish here) suggests Microsoft should drop the name Windows from it’s operating system lineup and lists several reasons why, like Windows versions that has flopped and that it’s a old name. I don’t think Paul Saurez knows much about marketing because I don’t but I still know that Windows is one, if not, the most recognized brands in the computer industry. It would be like telling Pepsi to drop the name Pepsi because it’s only third place in the Colawars nowadays. It’s just stupid. Why shouldn’t Microsoft drop the name Windows?

  1. It’s a simple name that describe exactly what their operating system is about, i.e windows in a OS.
  2. It’s old. That’s actually a good thing if you think about name and brand recognition.
  3. It’s actually starting to stand for something good because of the latest incarnations of the client and server versions of the OS. Windows 7 and Windows 2008 R2 is the best OSes out there for businesses and actually no one can compete with them. Not even Apple, because they do not care about servers that much.

What should Windows do to be cooler and sell more licenses? It’s simple, do what Apple did but better. The main reason why people start to buy Apple computers is in my opinion one of two:

  1. They have bought a iOS device and it’s simple to use and they want a simple and easy to understand OS. So make a phone that is better than iPhone, not 5 years behind.
  2. They like the simplicity and design of the products (I myself bought a Macbook Air just because it looks so very good). Make a PC that don’t have crappy quality and that looks nice and is cheaper than a Mac.

So Microsoft, make things easier but do not change any names.