Data Management - Author(s): Christiaan Baes (chrissie1)

Another solution for my caching problem with servicestack.text, dapper and sql server.

Christiaan Baes (chrissie1) — Sat, 09 Mar 2013 16:23:00 +0000

Introduction

My problem is the speed of the datastore I am querying. I won't name the vendor but their API is horrible. If I want to get all 3000 objects of a certain type I need to first get all the Id's from the latest versions of those objects, then I have to get the properties of each of those objects, if I am unlucky those objects have subobjects and then I have to query those too. So to get 3000 objects with 2 subobjects I need to do 9001 queries. Needless to say this will be slow.

Last week I had a look at redis to easily cache these objects on my webserver.

Redis is a good solution but it is only production ready on Linux, and our ops-team was not really happy with it. Even though I got permission to set up the linux server on our ESX-servers, I was still looking at other solutions.

Today I might have found such a solution. I can use SQL-server and serialize the objects as json.

The database

Not really a database since it is just one table.

Code: tsql

CREATE TABLE [dbo].[objects] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[version] INT NOT NULL,
[objid] INT NOT NULL,
[language] INT NOT NULL,
[value] NVARCHAR(MAX) NOT NULL,
[objtype] INT NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

Not sure how this will scale but I am pretty sure that we will not get more than 1 million rows in there any time soon. At the moment it is less than 100k rows.

I can now use dapper and servicestack.text to get the data in and out of there.

The code

Code: text

Imports Dapper
Imports System.Data.SqlClient
Imports ServiceStack.Text

Module Module1

Sub Main()
Dim con As New SqlConnection("Server=christiaan-pc;Database=cache;Trusted_Connection=True;")
con.Open()
con.Execute("delete from objects")
Dim object1s = New List(Of object1)
Dim object2s = New List(Of object2)
Dim s As New Stopwatch
Console.WriteLine("Adding to database")
s.Start()
For i = 0 To 10000
object1s.Add(New object1() With {.id = i, .name = "object1_" & i, .version = 1})
object2s.Add(New object2() With {.id = i, .name = "object2_" & i, .version = 1})
Next
Dim objects = New List(Of databaseobject)
For Each object1 In object1s
objects.Add(New databaseobject() With {.language = 102, .objid = object1.id, .version = object1.version, .value = object1.SerializeToString(), .objtype = 1})
Next
For Each object2 In object2s
objects.Add(New databaseobject() With {.language = 102, .objid = object2.id, .version = object2.version, .value = object2.SerializeToString(), .objtype = 2})
Next
con.Execute("insert objects(objid, version, language, value, objtype) values (@objid, @version,@language,@value,@objtype)",
objects)
s.Stop()
Console.WriteLine(s.ElapsedMilliseconds)
Console.WriteLine("Fetching from database")
s.Restart()
Dim l = con.Query(Of databaseobject)("select objid, version, language, value, objtype from objects where objtype = 1")
Dim obj1 As object1
For Each m In l
obj1 = m.value.FromJson(Of object1)()
Next
l = con.Query(Of databaseobject)("select objid, version, language, value, objtype from objects where objtype = 2")
Dim obj2As object2
For Each m In l
obj2 = m.value.FromJson(Of object2)()
Next
s.Stop()
Console.WriteLine(s.ElapsedMilliseconds)
Console.ReadLine()
End Sub

Public Class databaseobject
Public Property id As Integer
Public Property objid As Integer
Public Property version As Integer
Public Property language As Integer
Public Property objtype As Integer
Public Property value As String
End Class

Public Class object1
Public Property name As String
Public Property id As Integer
Public Property version As Integer
End Class

Public Class object2
Public Property name As String
Public Property id As Integer
Public Property version As Integer
End Class
End Module

So I am adding 2 times 10k objects to the database and after that I rehydrate them again.

On my computer this is the result.

Adding to database
13317
Fetching from database
199

That's more then reasonable. Considering it takes 28 seconds to get 3000 objects from the datastore.

Conclusion

All though I think redis would be a good solution, I don't think it is an ideal solution for our situation.

Original post blogged on LessThanDot.

Redis and VB.Net

Christiaan Baes (chrissie1) — Sun, 03 Mar 2013 17:36:00 +0000

Introduction

So I thought I needed redis to do some much needed caching for an app I am writting. And I was right, as I often am.

So I installed redis and used the .net redis client from the always brilliant Demis Bellot.

Redis is a key-value database, a dictionary on steroids. It is great for caching. And it is fast at what it does by keeping most things in memory.

Redis

First I used the windows version of redis and that even worked, but it is not production ready. So I decided to install a linux VM with redis on it.

I downloaded mint 14 and did apt-get install redis-server. Then you need to change the file in /etc/res/redis.conf and comment the line bind 127.0.0.1 or change it to bind 0.0.0.0.

I used VMWare workstation 8 and I had to use NAT translation for the network and add forward the 6379 port.

you can check if your redis server works by doing redis-cli ping on the server. This should return PONG. To be sure the binding works you should try redis-cli -h ipaddress ping. where you change ipaddress with the ip-address of the linux machine. This should also give PONG.

You can now do the same on the host to check if you can connect.

If all works well you can start writing to your redis server. And we might do some reading to.

Redis-client

Here is our model.

Code: vbnet

Public Class Mandate
Public Property Id As String
Public Property Version As Integer
Public Property Name As String
End Class

Firstly you need to nuget servicestack.redis.

The following code writes some entries to our redis server.

Code: vbnet

Using redisClient = New RedisClient(ipaddressofthelinuxvm)
Dim client = redisClient.As(Of Mandate)()
client.GetAllKeys().Count.PrintDump()
For i As Integer = 1 To 500
client.SetEntry(i, New Mandate() With {.Id = i, .Version = 1, .Name = "Name" & i})
Next
Dim mandate5 = client.GetValue(5)
mandate5.Version += 1
client.SetEntry(5, mandate5)
client.GetValue(5).PrintDump()
client.GetAllKeys().Where(Function(x) Not x.Contains("ids")).Count.PrintDump()
client.Save()
End Using
Console.ReadLine()

If you run the above you will get.

0
{
Id: 5,
Version: 2,
Name: Name5
}
500

On a second run you will get.

501
{
Id: 5,
Version: 2,
Name: Name5
}
500

So I add 500 objects but it tells me I have 501. The 501st is the id of the mandate class.

I don't really need to do the save, since redis will do an autosave every so often (depends on your redis.conf).

Conclusion

It is here that I pretend that redis is easy to use and to set up. And it is, just not for me ;-).

I would like to thank Demis for setting me on the way to solving my connectivity problems with the VM.

The second time it won't add another 500 entries, it will update the ones that are there.

Original post blogged on LessThanDot.

Full outer join requires some thinking before use

Christiaan Baes (chrissie1) — Wed, 10 Oct 2012 06:03:00 +0000

I don't need Full outer joins all that often. So when I do I have to think on what it will return me.

You can go to wikipedia to find the full explanation on full outer joins if you want to.

But in short it is this.

Conceptually, a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee.

What I needed was to combine the result of two queries where I did a sum by year and join on the year.

Here is the testdate.

Code: tsql

CREATE TABLE #temp
(
Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL
, DateIn DATETIME NULL
, DateOut DATETIME NULL
)
GO

INSERT INTO #temp (DateIn, DateOut) VALUES ('2012-10-10 9:38:45.757',null)
INSERT INTO #temp (DateIn, DateOut) VALUES ('2011-10-10 9:38:45.757','2012-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES ('2010-10-10 9:38:45.757','2011-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES ('2010-10-10 9:38:45.757','2011-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES ('2012-10-10 9:38:45.757',null)
INSERT INTO #temp (DateIn, DateOut) VALUES ('2011-10-10 9:38:45.757','2012-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES ('2009-10-10 9:38:45.757','2010-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES ('2009-10-10 9:38:45.757','2010-10-10 9:38:45.757')
GO

SELECT Id, datein, dateout FROM #temp
GO

DROP TABLE #temp
GO

And the desired result was something like this.

Year	NumberIn	NumberOut
2009	2	0
2010	2	2
2011	2	2
2012	2	2

My first query was this.

Code: tsql

SELECT DateInData.YEAR, COALESCE(numberin,0) AS numberin, COALESCE(numberout,0) AS numberout
FROM
(SELECT YEAR(datein) AS YEAR, COUNT(*) AS numberin FROM #temp GROUP BY YEAR(datein) HAVING YEAR(datein) IS NOT NULL) AS DateInData
FULL OUTER JOIN
(SELECT YEAR(dateout) AS YEAR, COUNT(*) AS numberout FROM #temp GROUP BY YEAR(dateout) HAVING YEAR(dateout) IS NOT NULL) AS DateOutData
ON DateInData.[YEAR] = DateOutData.[YEAR]
GO

And hooray, that gives the correct results. So lets move on.

Hold on. In the above I am assuming that datein will always be filled in. But my table definition says it is not a mandatory field.

So what would happen if I had the following data in my table.

Code: tsql

INSERT INTO #temp (DateIn, DateOut) VALUES ('2012-10-10 9:38:45.757',null)
INSERT INTO #temp (DateIn, DateOut) VALUES ('2011-10-10 9:38:45.757','2012-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES (null,'2011-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES (null,'2011-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES ('2012-10-10 9:38:45.757',null)
INSERT INTO #temp (DateIn, DateOut) VALUES ('2011-10-10 9:38:45.757','2012-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES ('2009-10-10 9:38:45.757','2010-10-10 9:38:45.757')
INSERT INTO #temp (DateIn, DateOut) VALUES ('2009-10-10 9:38:45.757','2010-10-10 9:38:45.757')
GO

Then the result would look like the below.

Year	NumberIn	NumberOut
2009	2	0
NULL	0	2
2011	2	2
2012	2	2

See how we are missing a year? The user won't be happy if he has to guess the year.

The solution is simple.

Code: tsql

SELECT coalesce(DateInData.YEAR, DateOutData.Year), COALESCE(numberin,0) AS numberin, COALESCE(numberout,0) AS numberout
FROM
(SELECT YEAR(datein) AS YEAR, COUNT(*) AS numberin FROM #temp GROUP BY YEAR(datein) HAVING YEAR(datein) IS NOT NULL) AS DateInData
FULL OUTER JOIN
(SELECT YEAR(dateout) AS YEAR, COUNT(*) AS numberout FROM #temp GROUP BY YEAR(dateout) HAVING YEAR(dateout) IS NOT NULL) AS DateOutData
ON DateInData.[YEAR] = DateOutData.[YEAR]
GO

I added the coalesce(dateindata.year, dateoutdata.year) in other words if the year of dateindata is null then go for the year that is in dateoutdata.

And now the user no longer has to guess the year.

Year	NumberIn	NumberOut
2009	2	0
2010	0	2
2011	2	2
2012	2	2

So sometimes you just have to look at your table definition before making assumptions because your assumptions might be wrong, one would think that there is always a datein when there is a dateout but one can not be sure.

Original post blogged on LessThanDot.

SQLCop is now available via chocolatey

Christiaan Baes (chrissie1) — Wed, 16 Nov 2011 08:35:00 +0000

Here is the definition of chocolatey as stated on the chocolatey.org website.

Chocolatey NuGet is a Machine Package Manager, somewhat like apt-get, but built with windows in mind.

Chocolatey can be easily installed.

To install chocolatey now, open a powershell prompt (execution policy needs to be unrestricted - Set-ExecutionPolicy Unrestricted), paste the following and type Enter:

PS:\> iex ((new-object net.webclient).DownloadString("http://bit.ly/psChocInstall"))

And Chocolatey also has a GUI if you like those kinds of things. Yeah I wrote that GUI, sorry.

And now I added sqlcop to chocolatey which makes it dead easy to install.

Just type cinst sqlcop in your favorite command prompt or use the GUI and sqlcop will be there for you. It will also place a shortcut on your desktop for you to have easy access to sqlcop.

So now you have no more excuse not to install chocolatey and sqlcop.

Making a chocolatey package is easy BTW.

Original post blogged on LessThanDot.

Installing SQL Server Express 2008 Management Studio

Christiaan Baes (chrissie1) — Mon, 02 Nov 2009 08:11:16 +0000

Well now that we have the engine installed. We sometimes also want the GUI tools to go with it.

I would recommend a reboot however. That's what made it work for me.

Let's go to the Microsoft download page again and find the install button.

Click install and then you get this, since we already installed the web platform installer in a previous post:

Which will present you with this:

I'm sorry to say it doesn't install that for me. But you can download it here.

Many minutes later.

We can install Management studio.

And then I get this window.

So I click check run program anyway, we will do the update later.

BTW The update can be found here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en

And voila, I'm done. Not the best of User Experiences I ever had and in the end not much better then what it used to be.

Original post blogged on LessThanDot.

Installing sql server express 2008 is easier than it used to be

Christiaan Baes (chrissie1) — Mon, 02 Nov 2009 06:00:15 +0000

I remember having a hard time installing sql server express 2008 on windows XP.

So today I had to install it again on win7 I looked for the download and found it on the microsoft website. I was prepared for some more pain and a long wait like I was used to. But it started by installing the web platform installer.

So I downloaded that and installed it (not something that works by itself, it needs an extra click or two.

And then I could click on the "Finish your installation" thing. Which showed me this.

Which was telling me to click Web platform installer and then Ok. Easy

After that, I could go on with installing SQL Server 2008 Express. Because as soon as the web platform installer launched it showed me this:

Just click Install, and watch the progressbars move.

And after a short while it said it was installed. No reboots or anything.

Of course I'm sure that Win7 has all the prerequisites that XP hasn't but still I got SQL server 2008 Express installed in less than 20 minutes.

I like it.

Original post blogged on LessThanDot.

Installing SQL Server 2008 express on Windows XP SP 2 without reading the manual

Christiaan Baes (chrissie1) — Sun, 08 Feb 2009 16:11:51 +0000

Today I was going to write a blogpost about persisting the simple domainmodel using Nhibernate and Fluent Nhibernate (I will have to write this later). It turned out to be quite and adventure. First I finaly installed R# on this machine I've been having the license since last octobre just haven't used it yet on this or any other machine. That installation was pretty painless I must say.

Then I needed to install some kind of database. And I like shiny and new so I installed (or wanted to install) SQL server express 2008 on my Windows XP SP2 which is fully supported on this OS. It turned out to be a 5 hour plus ordeal and installing one program after the other. But lets not forget it's free. So I'm not complaining just venting. First thing it said (well after a couple of minutes of unzipping it's install files in some guid folder) was that I needed the 3.5 SP1 framework. Silly me only had 3.5. So I did that and restarted. At that moment I lost my first version of the blogpost. My bad. I only copied it to the clipboard.

Then I tried to install SQL Server Express again. And after deflating the files again in another GUID folder. It told me I should install Windows installer 4.5. Which of course I did.

Another restart later. I doubleclicked the SQL Server Express 2008 installer again and this time it actually started installing (I wonder what happened to the just_install_and_stop_nagging_me_button, I really miss that button). But no such luck. After the second next it said I had to install powershell for windows and the buttons back and next were disabled so after installing Powershell I had to restart the SQL Server 2008 Express edition again. And It was happy now. So I clicked the Third next button and now it complained that I had the wrong version of Visual Studio 2008. I needed SP1 for that too. So I installed that in two goes.

But not there yet. I got this errormessage.

TITLE: Microsoft SQL Server 2008 Setup
------------------------------

The following error has occurred:

The Windows Installer service cannot update the system file c:\WINDOWS\system32\msxml6r.dll because the file is protected by Windows. You may need to update your operating system for this program to work correctly.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.0.1600.22&EvtType=0xF45F6601%25401201%25401

Which led me to this page

Details
Product:
SQL Server
ID:
50000
Source:
setup.rll
Version:
10.0
Component:
SQL Server Native Client
Message:
A network error occurred while attempting to read from the file '%.*ls'.

Explanation
An attempt was made to install (or update) SQL Server Native Client on a computer where SQL Server Native Client is already installed, and where the existing installation was from an MSI file that was not named sqlncli.msi.

User Action
To resolve this error, uninstall the existing version of SQL Server Native Client. To prevent this error, do not install SQL Server Native Client from an MSI file that is not named sqlncli.msi.

Seriously. Are they trying to be funny? Would I ever install something that doesn't have the name sqlncli.msi? I don't think so.

So I uninstelled the two native clients on my machine and tried to install again. And finaly it worked

So after all this I have SQL server Express 2008 on my machine. What an adventure.

Now back to our regular program.

Original post blogged on LessThanDot.

SQL server Linked server between 2005 64bits and a 2000 32 bits server.

Christiaan Baes (chrissie1) — Fri, 09 Jan 2009 16:57:51 +0000

Today I wanted to import the last bits of data from the old server. The new server is a 2005 64 bits SQL server and the old server is a 2000 32 bits. I'm no DBA and everything is internal so I don't really care about service packs and I can't remember which has which allthough I'm sure the 2000 box has the latest SP but since they aren't even connected to the internet updating isn't easy.

So today I setup a linked server between the two. I used my desktop to connect to the new server and then setup the linked server. And surprise surprise it didn't work. This was more or less the error I got.

OLE DB provider "SQLNCLI" for linked server "servername" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I actually never solved this problem. I just gave ANONYMOUS LOGON rights on the database and deleted it shortly after. It is internal remember. That solved that error message. On to the next error message.

OLE DB provider "SQLNCLI" for linked server "servername" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "servername" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "servername". The provider supports the interface, but returns a failure code when it is used.

This one was solved by doing a google. And finding this.

At the same time Ted (onpnt) gave me this solution and he the asked why I hadn't told him it was a 64 bit 2005 and the 2000 was 32 bit. Well Ted you didn't ask ;-). So it's all Teds fault it took me more then an hour to figure this out. Well that's my story anyway.

The solution is to create this procedure.

Code: tsql

create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

because I was using the ANONYMOUS LOGON I had to give that exec rights on that newly created SP. After that everything worked like it should.

A little bird also told me that the windows authentication didn't work because of to many jumps. Next time I'll try to solve that if possible.

I would like to thank George and Ted for their patience and help in solving this problem.

Original post blogged on LessThanDot.

Altering the Schema of a stored procedure in SQL-server 2005

Christiaan Baes (chrissie1) — Tue, 06 Jan 2009 06:35:29 +0000

Today I had to change the schema of a stored procedure. I couldn't find a clicky click method to do this in SQL server management studio (which kinda worries me). So I looked on google for a method and found this. But not being an SQLguru I took a few seconds to sink in.

So the syntax is.

ALTER SCHEMA nameoftheschematotransferto TRANSFER nameandschemaoftheSP

For example:

We have an SP called username.spo1 and I want to change it to dbo.spo1 so I would change it from schema username to schema dbo. This would then mean

Code: tsql

ALTER SCHEMA dbo TRANSFER username.spo1

The funny thing is that not even SQL prompt from Redgate recognizes the word TRANSFER as a keyword. So I guess transfering schemas is not a very common event.

Original post blogged on LessThanDot.

SQL Dependency tracker and pretty pictures

Christiaan Baes (chrissie1) — Tue, 10 Jun 2008 11:14:56 +0000

Today I finally got SQL-Toolbelt from RedGate Software and I just had to play with it a bit.

I especcially liked the dependency tracker and the pretty picture it produced from my Database. Look.

Isn’t it pretty? It’s art .

Original post blogged on LessThanDot.