Albert's notes on development and performance

Rough counters on Google App Engine

2011-05-15T07:05:00.000-07:00

On Google App Engine the maximum frequency of updates for a single database entity is limited. If you need to count something with high frequency such as page views on a web site or similar you need to do some magic to manage it.

The solution found in documents by Google is to use sharded counters. The principle is that you have several database entities, pick one at random and update that entity. The number of updates possible scales with the number of entities you use. However to get the value of the counter you need to get all entities and sum them. Also the code to do this is a bit complex. You pay for that complexity with cpu-resources.

If you need a counter that allows a high update frequency but don't want the fuzz of sharded counters and it doesn't matter if you occasionally miss a count or two there is a better solution. For some of my applications I instead use rough counters. The basic principle is that you let your instance cache the counts locally and occasionally write the counter to the database. You'll waste far less resources, you can read the value of the counter directly.

Note: If your instance shuts down you loose the current counter value, the error whill likely be less than a tenth of a percent, but it isn't exact.

The code:


import javax.jdo.JDOObjectNotFoundException;
import javax.jdo.PersistenceManager;
import javax.jdo.annotations.IdGeneratorStrategy;
import javax.jdo.annotations.NotPersistent;
import javax.jdo.annotations.PersistenceCapable;
import javax.jdo.annotations.Persistent;
import javax.jdo.annotations.PrimaryKey;

@PersistenceCapable(detachable="true")
public class RoughCounter {
 @PrimaryKey
 @Persistent(valueStrategy = IdGeneratorStrategy.IDENTITY)
 private String mName;
 
 @Persistent
 private long mRoughCount;
 
 @NotPersistent
 private long mLastUpdate;
 
 public RoughCounter(String name) {
  mName = name;
  mRoughCount = 0;
  mLastUpdate = 0;
 }
    
 public void increase() {
      mRoughCount += 1;
      if (mLastUpdate > System.currentTimeMillis() - 10 * 1000) return;
      mLastUpdate = System.currentTimeMillis();
     
      long count = mRoughCount;
      mRoughCount -= count;
     
  PersistenceManager pm = PMF.get().getPersistenceManager();
  try {
   RoughCounter rc = pm.getObjectById(RoughCounter.class, mName);
   rc.mRoughCount += count;
   pm.makePersistent(rc);
  } catch (JDOObjectNotFoundException ex) {
   RoughCounter c = new RoughCounter(mName);
   c.mRoughCount += count;
   pm.makePersistent(c);
  } finally {
   pm.close();
  }
 }    
}

This counter writes the results to the database around once every ten seconds. The counting can occur at ANY speed, this is not depending on database performance, only on execution speed which is extremely high in comparison to database update performance.

Key value store on Google App Engine

2011-04-19T03:50:00.000-07:00

Just created a quick hack to use Google App Engine (GAE) as a simple key value store with a nice rest api. This allows usage as a data store for web applications and also a flexible way to serve web sites. It can also be used as an application server with similar thinking to CouchApp.

Have a look at:

https://github.com/AlbertBertilsson/GAE-KVS

Sums in Microsoft Excel

2011-01-20T04:10:00.000-08:00

I often use MS Excel (2007) to evaluate data... simple and convenient for many cases...

Unless you try to sum several integer values and they exceed 1'000'000'000'000'000. By then Excel starts losing precision. I'd expect 64bit integer math to work correctly, but it doesn't!

DropBox distributed computing

2010-11-23T04:01:00.000-08:00

Nothing beats a free lunch. One tool that is getting more and more important to me is DropBox. Great tool to use for storing and sharing your files across many desktops and project members.

Anything else... yes why not use it as a distributed computer? Because after all we all know that if you can leave GUI-stuff and infrastructure out of it many things are really simple.

What I did:

Create one folder DistributedComputer
This will be shared with everybody that runs a node. Share this with all persons trusted to run a computing node for you.

In it place all the stuff you need to pick a job start an executable that does the processing and then write the stuff to a directory.

I used one large text-file to contain all the parts of the problem and two directories for tracking: Started and Done. A simple program picks a random problem (important to make it random) and starts another program that then starts the calculation, a file is created in the Started folder with the problem id as name. When the calculation is finished a file is created in the Done folder with the problem id as name and the output as content.

Super simple and it works!

Some notes:
Picking a random problem is important. If a node loses network connection it can still keep work on randomly selected problems with small risk of duplicating work. If picking the problems in sequence all nodes without network connection would duplicate their work.

The program that picks the problem and starts the program to solve the program should of course verify that the problem isn't already started (known by checking the Started directory) or done. If there are no problems left that aren't started pick a problem that at least isn't done already.

The real beauty of it:

Coding some simple commandline tools that works with files is really simple. It is also much less work to make them platform independent if needed.
DropBox will handle tracking for you. Can see which computer created the file and when a problem started and was done.
You can easily continue with refinements and optimization of your program to solve the problem, each time a new problem is picked the executable is reloaded, all you need to do is put a new executable in the shared folder.
Zero code for communication and synchronization, only two directories to check for status.
All members can easily follow the project and participate. This is really nice since it makes it easy for everybody to follow the project and contribute with improvements.

Issues:

Size limits, 2GB isn't huge but it should cover a decent set of problems. For the results, if they are big just have two files in the done folder one to mark the problem done and one with the actual results. Then you can gather all the results and process them as they appear and then delete them.
Since the files are shared there is potential for users grab the komplete results.
There is also a risk for users to wreck your files and data, not very nice in a internet project with thousands of users, but quite ok if you are doing this with some friends.

jQuery download performance

2010-09-24T08:51:00.001-07:00

Bandwidth for free and more speed for everyone! Don't put the jQuery scripts on your own server, trust in Google and let them serve the scripts.

Link too:
http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js

Ref:
http://softwareas.com/google-jquery-cdn
http://royal.pingdom.com/2010/05/11/cdn-performance-downloading-jquery-from-google-microsoft-and-edgecast-cdns/

BusinessConnectorTest

2009-09-23T12:00:00.000-07:00

Warning! This note only makes sense for the Dynamics Ax developers out there. Specifically if you are setup up Enterprise Portal or something similar that uses the .Net Business Connector to connect to Ax.

A simple tool for verifying if you have a working Business Connector .Net setup.

Download it here. To run it you need the 13MB Microsoft.Dynamics.BusinessConnectorNet.dll, version 5.0.1 (2009, SP1) in the program directory.

Basically the code looks like this:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Dynamics.BusinessConnectorNet;

namespace BusinessConnectorTest
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                Microsoft.Dynamics.BusinessConnectorNet.Axapta DynAx = new Microsoft.Dynamics.BusinessConnectorNet.Axapta();
                Microsoft.Dynamics.BusinessConnectorNet.AxaptaRecord DynRec;

                DynAx.Logon(null, null, null, null);
                Console.WriteLine("Logon successful!");
                
                DynRec = DynAx.CreateAxaptaRecord("CustTable");
                Console.WriteLine("Create record successful!");
                
                DynRec.ExecuteStmt("select firstonly * from %1");
                Console.WriteLine("Select successful!");
                Console.WriteLine(string.Format("Recid: {0}", DynRec.get_Field("RECID")));
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
        }
    }
}

Very simple, but still handy to have. The catch is the good part :). Gives you an idea of what is wrong.

This code uses the current user to logon. Use "runas" to change user.

Find out the name of the AD server

2009-09-11T02:02:00.000-07:00

Ever wondered which machine is the AD server of the machine you are using?


echo %logonserver%

Will tell you the machine name of the AD server or simply give you the current machine name back if you are not connected to a domain.

SetSPN

2009-09-10T08:42:00.000-07:00

The lovely little tool setspn...

It has different command for different versions (new commands in windows server 2008).

It has very little error checking so it allows you to register duplicate SPN's which will ruin your kerberos authentication.

The nice view command "setspn -l " allows you to specify both a machine or a user as argument. The results are different and does not show the same information.

Beware!

Verify Kerberos in a SQL Server installation

2009-09-08T07:30:00.000-07:00

Use the SQL Server Management Studio, connect to the server (note you must NOT do this locally on the server). Execute this T-SQL:


SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid;

It will return either "NTLM" or "KERBEROS".

More on Kerberos will follow...

DYM - Did You Mean?

2009-04-04T11:32:00.001-07:00

I coded a web part to present the user with "Did you mean ...?" suggestions in Microsoft Search Server Express. For more information see the complete web part information at my home page. http://www.albert.nu/programs/dym

WSS safe upload hack

2009-04-01T00:39:00.000-07:00

Find the upload.aspx file in 12\TEMPLATE\LAYOUTS and change the "OverwriteSingle" checkbox to this:

...asp:CheckBox id="OverwriteSingle" Visible="false" Checked="false"...

The checkbox will not be visible and if a duplicate document is selected the user will get an error instead of overwriting the existing file.

NOTE: This is hack of the standard out of the box solution.

Stored procedure result into a table...

2009-03-18T14:18:00.000-07:00

Needed to kill a large range of spid's today... but how can that be done in a simple and flexible way?

Well, put the result of sp_who2 into a table, do a select from that table to generate one column with the text 'kill '. And then copy paste the result and run it.

declare @sp_who table
(
spid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blkby char(5),
dbname nchar(128),
cmd nchar(16),
cputime int,
diskio int,
lastbatch varchar(20),
program nchar(128),
spid2 smallint,
requestid int
)

insert into @sp_who execute sp_who2

select * from @sp_who

Modify the final select to suit your needs.

WSS Create Discussion Board item

2009-03-07T06:19:00.000-08:00

Creating and manipulating ordinary lists in WSS through the object model is easy, and finding examples on the web is also easy. But what about discussion boards?

Turns out that they are not as easily manipulated, and examples are very hard to find. After much browsing I found this information though:
http://blogs.msdn.com/sfellman/archive/2007/12/17/issue-programmatically-posting-wss-2007-discussion-list-items-shows-sender-as-system-account-in-outlook-2007.aspx

Use this code:
using Microsoft.Sharepoint.Utilities;

SPListItem item = SPUtility.CreateNewDiscussion(.Items, "");
item["Body"] = "";
item.Update();

There is a corresponding method to add discussion replies:
SPUtility.CreateNewDiscussionReply(item);

BizTalk installation/configuration

2009-03-03T23:46:00.000-08:00

During the installation of BAM Tools you have an option to "Enable Analysis Services for BAM aggregations". When I tried to configure BizTalk the other day this part simply wouldn't work.

The error displayed looked like this:
Either the '' user does not have permission to create a new object in '', or the object does not exist.

This seemed odd to me because the previous parts of the configuration had successfully created several databases with the same user account.

So what's the problem then? Almost embarrasing... analysis services uses its own port, not the standard 1433 sql server port. So after add the required port to the firewall rules everything worked as expected.

Use SQL Server trace to find problems on other servers!

2009-01-21T00:22:00.000-08:00

The trace tool in SQL Server is mostly used to find performance problems related to SQL Server. But it is also a great tool to find performance problems in application servers and web servers.

The general way of thinking is this: Even if your SQL Server is doing fine what impact does the information flow have on other parts of the system? It is not unusual that the SQL Server is the most powerful machine in the system, allowing it to cope with huge loads of requests.

If you see either large amounts of requests to SQL Server or fewer requests but with large amounts of data try to figure out how the application manages this information. The life of an application server is not that simple, by knowing the SQL Server requests you can figure out what it is doing and maybe make it easier on both the application server and SQL Server.

Filtered indexes in SQL Server 2008

2008-11-13T03:02:00.000-08:00

Ok, a great feature as it is... a great performance booster when applied correctly. I'm not gonna repeat what others have written, google it!

But what I really like about it is that is solves the age old problem of applying a uniqueness requirement to all but (and usually that is all but null values or something similar).

The classic solution to this problem is of course to use a trigger to verify uniqueness, to make that trigger run fast it requires an index. Instead of the mess of a complete index (larger than we need) and extra code in the form of a trigger that adds complexity and steals more performance we simply apply a filtered index.

Example, we want to make sure that socialid is unique if it is not null:
create table Person (id int identity(1,1) primary key, name nvarchar(100), socialid nvarchar(20))

insert into Person (name, socialid) values ('Albert', '1234'), ('Bengt', null), ('Christian', '2341'), ('Dan', null)

create unique index idx_socialid on Person (socialid) where socialid is not null

select * from Person

insert into Person (name, socialid) values ('Eric', '1234')
Inserting the person named Eric will fail since the socialid is not unique.

StatementCompare

2008-06-24T06:08:00.000-07:00

Since I often do optimization of procedures or other changes it is nice to be able to verify if a stored procedure returns the same data now as compared to another version. So I wrote a quick hack to make that comparison.

Checks select statements or multiple resultsets from stored procedures. Verifies order of tuples if checkbox option for that is checked. Doesn't verify return values or row counts from insert/update/delete.

Can be found here:
http://albert.nu/programs/statementcompare

When to optimize

2008-05-29T11:19:00.000-07:00

When getting an optimization task for someone I always try to start as wide as possibly. What type of machine are you running on? What is displayed by the task manager etc... Quickly start a trace to see what is actually happening and then view some DMV's to spot issues.

I've found that quite often the trace shows statements or procedures that by them selves eat up tons of resources. In case you find a top five of tasks that use in the range of 30% or more of cpu or io resources then optimize! Even if these tasks aren't causing locks or other problems it will be such a significant improvement to your client that they will notice.

As a side effect the entire database will run much smoother afterwards and with less load the risk of locks etc will decrease. These tasks are often easy to optimize since you will almost always benefit from trading insert/update/delete cost to select cost by removing or adding indexes.

Performance monitor doesn't lie!

2008-05-22T06:15:00.000-07:00

Yesterday I worked on optimizing a database for a client... I had a pretty nice optimization of a stored proceedure in place... that's what I thought at least. Using a computed column with an index I could do direct lookups instead of searching large amounts of text data and when testing the procedure in management studio the execution plan was perfect and IO was reduced from 50k to less than 50 :). But server load didn't drop as expected so I fired up performance monitor and started a trace... guess what the procedure was still running with poor performance.

After some tinkering it was obvious that the application code failed to get the correct execution plan for reasons still unknown (maybe access rights or some weird ansi-option related to the computed column, a did a simple workaround and it worked like out fine.

Lesson learned: There is a difference between successful optimization when running the procedure in management studio and actually applying it to a running application.

When in doubt trust performance monitor.

Drop a database

2008-05-15T23:05:00.000-07:00

The drop syntax is rarely useful since there is always someone using the database... and I always seem to forget the lines needed to set the database in single user mode.

This works:

ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE master

DROP DATABASE xxx

Really basic stuff...

2008-05-15T05:54:00.000-07:00

Remember that:
@Variable like Column
Is not the same as:
Column like @Variable

Really basic, but sometimes even basic stuff can cause a mess.

Comma separated string of values...

2008-04-04T05:12:00.000-07:00

/*
Note that if the values that are supposed to be comma separated can contain
comma signs it is a good idea to replace those with something else before
generating the list
*/

declare @list nvarchar(max)
set @list = ''
select @list = @list + ',' + replace(ColumnName,',','.') from TableName
set @list = substring( @list , 2, 10000000 )
select @list

--Simpler versions when handling id's
--Quite quick
declare @list varchar(max)
set @list = ''
select @list = @list + ',' + convert(varchar(10), ColumnName) from TableName
set @list = substring( @list , 2, 10000000 )
select @list

--Much quicker version (notable only when generating very long lists)
declare @list varchar(max)
set @list = (select ',' + convert(varchar(10), ColumnName) from TableName for xml path(''))
set @list = substring(@list , 2, 10000000 )
select @list

Reseeding those identity fields...

2008-01-14T09:38:00.000-08:00

DBCC CHECKIDENT ( table_name, [reseed / noreseed], [new_seed_value])

DBCC CHECKIDENT ( 'dbo.tTable', RESEED, 14000000)

Temporary tables again!

2007-12-23T09:04:00.000-08:00

I seem to find myself optimizing lots and lots of old Sql Server 2k code. Often these procedures are relying heavily on temporary tables.

Adding a clustered index to a temporary can be a real boost but sometimes other tools are needed. Indexes on temporary tables are possible by adding unique indexes during table definition. Unless the values are already unique this requires adding a duplicate key breaker example:


create table #temp
(
   x int,
   y int
)

Transforms into:


create table #temp
(
   id int identity(1,1) not null,
   x int,
   y int,
   primary key clustered (id),
   unique (x,id),
   unique (y,id)
)

Get reliable execution results

2007-12-23T09:02:00.000-08:00

To get komparable results remember to execute:
DBCC DROPCLEANBUFFERS
(Empty data cache)

and:
DBCC FREEPROCCACHE
(Empty SP cache)

Just a reminder!

2007-12-06T01:19:00.000-08:00

Don't forget that SQL server can work as your simple calculator or runtime environment to test out small pieces of code.

As calculator:

print 135 * 3.24

Prints:
437.40

Or figuring out if your idea of calculating factorial is ok:

declare @l int
set @l = 0

while @l <= 100
begin
 declare @res float
 set @res = 1.0

 declare @i int
 set @i = 1
 while @i <= @l
 begin
  set @res = @res * @i
  set @i = @i + 1
 end
 print str(@l)+'! = ' + convert(varchar(50),@res)
 set @l = @l + 1
end


Prints:
         0! = 1
         1! = 1
         2! = 2
         3! = 6
         4! = 24
         5! = 120
         ...

Batch aborting errors

2007-12-02T07:40:00.000-08:00

Some errors are so serious that they can't be handled like normal errors. Example:


begin try
   -- Do something (the task you want to perform)
end try
begin catch
   -- Do something (log error, rollback transaction etc)
end catch

You might think that any error will be caught in the catch-clause allowing you to log the error and rollback any open transaction. This is WRONG!!! Some errors are batch aborting, this means that immediatly when they are encountered they stop the processing, rollback any transaction and close the connection. An example of a batch aborting error is to access an object that doesn't exist.

This shouldn't be an issue since SQL Server will do necessary cleaning, only problem is that your fancy code for logging the error will not be executed.

I've seen one example when SQL Server for some reason doesn't manage to abort the transaction, this was a big problem. Taking a backup and restore it would solve this problem, strange... no errors where reported by DBCC CHECKDB but the restore helped.

More information on:
http://www.sommarskog.se/error-handling-I.html

Temporary tables and primary keys!

2007-11-22T12:22:00.000-08:00

Use primary keys without names for temporary tables!

create table #test
(
id int,
data int,
primary key clustered (id)
)

drop table #test

Naming the primary key causes issues because Sql Server can complain that the primary key already exists (funny for a temporary table).

Try this example in two different sessions:

Session 1:
create table #test
(
id int,
data int,
constraint PK_test primary key clustered (id)
)

Session 2:
create table #test
(
id int,
data int,
constraint PK_test primary key clustered (id)
)

Without a name for the PK it will work though.

So this is.... what!?

2007-11-22T12:01:00.000-08:00

A small set of useful notes for a particular Sql Server developer (me).

The point?

1. To have it easily accessible.
2. To possibly get some comments on the issues, best case scenario is to get better solutions as comments than the ones I find myself.
3. To spread some knowledge (who am I kidding?)