Monday, 9 March 2009

R script to look at data with a lot of columns

I had some performance data that I needed to analyse that had over 1000 rows. Excel cannot load such a file, so I resorted to some tools that mathematicians and scientists use. In this case I used the following R script. There are some interesting twists, for example when R comes across a character it does not recognise then you need to replace the character with ".". For example R does not like spaces, "(", ")" etc... Anyway this script is very usefull for analysing large csv files...

data <- read.csv(file="C:/Data/Work/EXposure+IT/ITBermuda/NetApp/PartnerRe_000005.csv",header=TRUE,sep=",")colnum <- c(116,137,97,118)

sstr <- c(

results <- list()
for(i in 1:length(sstr)){
results[[i]] <- grep(sstr[i],names(data))

# make a time series#options("digits.secs") <- 3
#datetime <- as.character(data[,1])
#datetime <- strftime(datetime, format="%m/%d/%Y %H:%M:%S")time <- seq(1,1565)

for(i in 1:length(results)){


Saturday, 7 March 2009

Database structure optimization log

We have just started a database optimization exercise. This is a log of the first steps that we took in doing this. Here is a query that lists the number of seeks, scans, lookups and updates that are made on indexes for tables starting with "MOD_"

-- Index usage
object_name(a.object_id) AS table_name,
COALESCE(name, 'object with no clustered index') AS index_name,
type_desc AS index_type,
FROM sys.dm_db_index_usage_stats a INNER JOIN sys.indexes b
ON a.index_id = b.index_id
AND a.object_id = b.object_id
WHERE database_id = DB_ID('CatFocus')
AND (user_seeks = 0 OR user_scans= 0)
AND object_name(a.object_id) LIKE 'MOD_%'
AND a.object_id > 1000
ORDER BY user_updates

The query lists all accesses since the last reboot, here is an extract.


There are a lot of updates to these indexs but they are never used. The next step is to time the delete statement several times with and without the index. It took 3062 ms with the index and 718 without.

The next part of the procedure was to setup a test server where we can make a controlled load. We monitored the server using the SQL PRofiler and then looked through the logs.

Here is a sumary of the findings:
- At the start there where a lot of logins
- There where several places where the same select statement was being executed
- There where a lot of updates related to creating a cumulative frequency
- Connections where not closed.

We where able to fix the login and connections problem by modifying the based class used in our data access layer to:
Private IsInTransaction As Boolean = False
Public Function OpenConnection() As OleDbConnection
REM Establish connection to database
If objConn Is Nothing Then
objConn = New OleDbConnection
End If
If objConn.State <> ConnectionState.Open Then
objConn.ConnectionString = ConnStr
End If
Return objConn
End Function

Public Sub CloseConnection(ByRef objConnection As OleDbConnection)
REM Close connection to database
If (Not IsInTransaction) Then
End If
End Sub
Public Function BeginTransaction() As Boolean Implements IDataBase.BeginTransaction
REM Function to to start a transaction
IsInTransaction = True
Return ExecuteSQL("Begin Transaction ")
End Function
Public Function EndTransaction() As Boolean Implements IDataBase.EndTransaction
REM Function to to start a transaction
IsInTransaction = False
Return ExecuteSQL("commit ")
End Function

The problem with the cumulative frequency calculation was made with a stored procedure.

The next step was to look at index fragmentation. The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent may be acceptable. All methods of reducing fragmentation,
such as rebuilding, reorganizing, or re-creating, can be used to reduce these values

object_name(a.object_id) AS table_name,
a.object_id AS objectid,
a.index_id AS indexid,
name AS index_name,
partition_number AS partitionnum,
FROM sys.dm_db_index_physical_stats (DB_ID('CatFocus'), NULL, NULL , NULL, 'LIMITED') a INNER JOIN sys.indexes b
ON a.index_id = b.index_id AND a.object_id = b.object_id
WHERE avg_fragmentation_in_percent > 10.0 AND a.index_id > 0
ORDER BY table_name, avg_fragmentation_in_percent;

Here is an extract of what was found

CED_CEDANT 1758629308 6 IX_CED_CEDANT 1 66.6666666666667
CED_CEDANT 1758629308 1 PK_CED_CEDANT_1 1 83.3333333333333
DSP_PRICING 1609772792 1 PK_DSP_PRICING 1 42.3267326732673

The solution is to include index defragmentation in the backup maintenance plan

We knew that one of our tables has some columns that are not often used. Here there is a possibility to use "Sparse Columns" available in SQL 2008. Sparse Column has the 8% more CPU overhead, and it achieve the better performance result for Warm Run and Cold Run.
LOSS_ZONE_xx must be filled with NULL instead of 0

As we investigated the queries looking at the actuall execution plan in SQL 2008 there is an output that makes suggestions over any missing indexs that can be used to optimize the select statement
eg Missing Index (Impact 98.0075) : CREATE INDEX ......

Improving the speed of DataGridViews by switch of the RaiseListChangeEvent

Here is an example of how to improve the speed of updates to data gridviews in Windows Forms

Control.CheckForIllegalCrossThreadCalls = False
MyDataBindingSource.RaiseListChangedEvents = False

REM Make update to datagrid
MyDataBindingSource.RaiseListChangedEvents = True

Base classes for WPF

Here is an example of how to implement base classes for Windows in wpf

using System.Windows;
namespace PartnerRe.WPF.Utilities
public class BaseWindow : Window
public BaseWindow()
this.ShowInTaskbar = false;
this.WindowStartupLocation = WindowStartupLocation.CenterOwner;
System.Drawing.Color color = System.Drawing.SystemColors.Control;
this.Background = new System.Windows.Media.SolidColorBrush(System.Windows.Media.Color.FromRgb(color.R, color.G, color.B));
protected override void OnInitialized(System.EventArgs e)

src:BaseWindow x:Class="WinMyWindow"

Single Click edit in WPF DataGridView

If you have a datagrid view with a check box the fefault behaviour is that you need to click 2 times to check a check box. 1 click it to select the cell and the second to check the box. To get arround this:

In the XAML File:
. Style TargetType="{x:Type my:DataGridCell}"
EventSetter Event="PreviewMouseLeftButtonDown" Handler="DataGridCell_PreviewMouseLeftButtonDown" /EventSetter

In the CS-File:
using Microsoft.Windows.Controls;...
private void DataGridCell_PreviewMouseLeftButtonDown(object sender, MouseButtonEventArgs e)
PartnerRe.WPF.Utilities.Tools.SingleClickEditing(sender as DataGridCell);

Commar format in WPF

Add the following reference
Then add the following static resource
sys:String x:Key="CommaFormat" {0:#,0} /sys:String
Then within the binding element of the Text use String Format
TextBlock Foreground="Black" Text="{Binding COVER, StringFormat={StaticResource CommaFormat}}" HorizontalAlignment="Right"/
There is no way to directly add the string format into the Text property
The alternative using PropertyConverters requires a new class and therefore much more code than this...
Notes from
Console.WriteLine("{0:g}", DateTimeKind.Utc); // general
Console.WriteLine("{0:f}", DateTimeKind.Utc); // string
Console.WriteLine("{0:d}", DateTimeKind.Utc); // integer
Console.WriteLine("{0:x}", DateTimeKind.Utc); // hex

Console.WriteLine("{0} {1} {2}",100,200,3000);
100 200 3000
Console.WriteLine("{0:X} {1:N1} {2:#,#}",100,200,3000);
64 200.0 3,000
Console.WriteLine("(amount):{0,30:C}", 1123.27);
(amount): £1,123.27
Console.WriteLine("{0,-30:C}(amount)", 1123.27);
£1,123.27 (amount)
double neg = -1000.123;
double pos = 1000.123;
double z = 0;
Console.WriteLine("{0:A)0.00;B)#.##}", pos);
Console.WriteLine("{0:A)0.00;B)#.##}", z);
Console.WriteLine("{0:A)0.00#;B)#.##}", neg);
double x = 67867987.88666;
Console.WriteLine("£{0:N}", x);
Console.WriteLine("£{0:#,#.###}", x);
// No number specifier
Console.WriteLine("a) {0}", numSingle);
// Round up, ignore any decimal points.
Console.WriteLine("a) {0:##}", numSingle);
// Round up to 1 dp.
Console.WriteLine("b) {0:.#}", numSingle);
// Round up to 2 dp.
Console.WriteLine("c) {0:.##}", numSingle);
// Round up to 3 dp.
Console.WriteLine("d) {0:.###}", numSingle);
// Floats work up to 7 digits, then any extra digit after the decimal place is rounded up, or ignored.
// So 4 decimal places is the maximum a number up to 1000 can store, e.g. 999.1234.
// It's recommended to always use double (Double) or decimal (Decimal) for applications involving currencies.
double numDouble = 1000000.5664;
float numSingle2 = 999.9999f;
Console.WriteLine("e) {0:.####}", numDouble);
Console.WriteLine("e2) {0:.####}", numSingle2);
Console.WriteLine("e3) {0:##}", numSingle2);
a) 10.566
a) 11
b) 10.6
c) 10.57
d) 10.566
e) 1000000.5664
e2) 999.9999
e3) 1000
// In built formatters. These can have digit specifiers after them, such as {0:D5}
Console.WriteLine("a) [C]urrency - {0:C}", 1123.27); // Very similar to N (but no rounding), but with the currency symbol. See the section on regions.
Console.WriteLine("b) [D]ecimal - {0:D5}", 1123); // Integers only. The '5' sets the number of zeros to pad to the left.
Console.WriteLine("c) [F]ixed - {0:F3}", 1123.275); // Fixed decimal places including rounding.
Console.WriteLine("d) [G]eneral - {0:G}", 1123.275); // Scientific or decimal notation, whichever is shorter. Has a long set of rules in MSDN.
Console.WriteLine("e) [N]umber - {0:N}", 1123.275); // Similar to fixed, but includes ',' thousand seperator and is rounded to 2 dp by default.
Console.WriteLine("f) [P]ercent - {0:P}", 0.2345); // Percentage to 2 dp. So 0.99998 is 100%
Console.WriteLine("g) [P]ercent - {0:#%} ", 0.915); // This shows the percentage but as a whole number, rounded
double d1 = 1234.9998;
string rd = string.Format("{0:R}", d1);
double d2 = Double.Parse(rd);
Debug.Assert(d1 == d2);
Console.WriteLine("h) [R]ound trip - {0:R}", 0.9998); // Allows you to parse the resulting string using Single.Parse() or Double.Parse()
Console.WriteLine("i) he[X]adecimal - 0x{0:X}", 255); // Integers only
Console.WriteLine("j) [E]xpontial - {0:E}", 10000000);
a) [C]urrency - £1,123.27
b) [D]ecimal - 01123
c) [F]ixed - 1123.275
d) [G]eneral - 1123.275
e) [N]umber - 1,123.28
f) [P]ercent - 23.45 %
g) [P]ercent - 92%
h) [R]ound trip - 0.9998
i) he[X]adecimal - 0xFF
j) [E]xpontial - 1.000000E+007
// --- Default formats ---
// Tokens:
// dD tT fF gG rsu M Y
// d = short/long [d]ate
// t = short/long [t]ime
// f = short/long [f]ull
// g = short/long [g]eneral or default
// rsu = RFC or ISO format, last 2 are sortable
// M = day & month
// Y = month & year
// --- Custom formats ---
// dd ddd dddd
// 15 Mon Monday
// 06 Jun June
// yy yyyy
// 08 2008
// hh HH
// 01 13
// mm
// ss
// tt
// AM or PM

public class FormatProvider : IFormatProvider
public object GetFormat(Type formatType)
if (formatType is IFormatProvider)
return this;
return null;
public string ToString(string format, IFormatProvider formatProvider)
// handle format
public class User : IFormattable
private int _age;
private string _name;
/// Gets/sets the Name.

public string Name
get { return _name; }
set { _name = value; }
/// Gets/sets the Age.

public int Age
get { return _age; }
set { _age = value; }
/// Object.ToString() implementation

public override string ToString()
return string.Format("'{0}', age {1}", Name, Age);
/// Just a format string, functionality you find in DateTime.Now.ToString()

public string ToString(string format)
return ToString(format, null);
/// IFormattable.ToString() implementation

public string ToString(string format, IFormatProvider formatProvider)
// Default format
if ( string.IsNullOrEmpty(format) format == "G" )
return string.Format("'{0}', age {1}", Name, Age);
if (format == "name")
return Name;
else if (format == "age")
return Age.ToString();
// The IFormattable example shows branching based on the format. This is fine if the class
// is being used with composite formatting, however to support the DateTime.Now.ToString("xxx")
// style formatting, we need the below.
string result = format;
result = result.Replace("name", Name);
result = result.Replace("age", Age.ToString());
return result;
static void Main(string[] args)
User user = new User() { Name = "Chris", Age = 30 };
Console.WriteLine("Default format: {0}", user.ToString());
Console.WriteLine("Composite format: {0:name} is {0:age}",user);
Console.WriteLine("ToString(format): {0}", user.ToString("This user (name) is age"));
Default format: 'Chris', age 30
Composite format: Chris is 30
ToString(format): This user (Chris) is 30