Monday, July 19, 2010

A friend of mine had a very very strange problem. Suddenly on his external hard disk there appeared a bunch of very strangely named folders like "%/sl94..." and other symbols which windows was not able to display. They occupied 300GB and clearly my friend wanted to delete them; unfortunately windows was not able to delete them. It gave always an error like "Path not found" or "Invalid path symbols".I tried then to delete them with different data shredders but none of them was able to successfully delete them. I have then run also different virus and spy-ware scanners over them but none of them found something.I was stuck and at the end with my knowledge.

I was wondering for a very very long time what all these "battels" around direct sql, views and stored procedures are about. I read several articles on the Internet where people were claiming that nowadays there is almost no difference anymore between these three database "constructs". I saw many discussions and blogs where people tested the performance of these three with different results and that made me even more confused.

So as I am curious and the fact that I need to know this as a software developer I decided to try this out by myself. At work I had the chance to test this against a very large amount of real data (I needed to optimize this query anyway); Sometimes the sample databases which are used for theses kinds of tests are either very small or very simple or even both which makes not really sense in my eyes.

So the query consisted of joins of multiple tables with a case in the select statement. I run the query 300 times with every "construct" and then I took the average value for all three of them in order to reduce network and SQL Server workload "noise".

The result of my test (in Ticks):

Direct SQL

23077

View

19363

Stored procedure

3438

So what I found out for myself is that it really does matter what kind of "construct" you use to query your data; especially on a large amount of data.On large data you should also take into consideration that a big performance impact may also have where you set your indexes and which statistical data you let create by SQL Server.

If you made other experiences on this, please let me know and leave a comment...

Recently I worked with the entity framework (3.5) and at some point I had the need to execute a custom SQL command. Fortunately does the object context provide the underlying DBConnection and with that you can simply execute SQLCommands.

I read on the Internet that the new Version (4.0) has already integrated something similar.

This is an extension Method that I wrote which makes it easy to execute these queries:

Monday, July 5, 2010

Sometimes it happens to me that I have to copy the path of a file to the clipboard and paste the it somewhere (console, visual studio, etc.); but it is not as easy to get the path as it is with folders where you can simply get it from windows explorer.

Windows has a hidden shortcut to get the path; when you press "shift" and click with the right mouse button on a file then in the context menu you get an additional entry which is called

"Als Pfad kopieren" or "Copy as path". Afterwards you can simply paste wherever you need it.