Thursday, January 2, 2014

T-SQL query optimizations with data type mismatches

We were looking into a performance issue on a database. It was the classic "the more data you have the worse the performance" performance issue. As anyone knows, you add an index to go from a full table scan to a hash-like lookup.

Well we did that. It didn't help. After further investigations, one of my awesome engineers realized that the SQL QP was not engaging the index. The reason was silly IMO. The datatype of one column was NCHAR and the other was just CHAR. Of course. It's obvious :). As soon as he converted the data type, the index kicked in and the performance flattened. Yeah for SQL :(

Here's a side note. The thing I hate to admit is that the NCHAR/CHAR columns were used to store GUID values. Now, my team didn't design the database, but whoever did was not aware of SQL Server's native data type for GUID (and the benefits of doing compares on them). I wonder if it was laziness or neglect. Either way, living with a database wherein you are deeply worried about minimizing upgrade risks makes this a life-long ownership problem. If only data was like code and you could throw it out with each new version...

Tuesday, December 17, 2013

Where has the day gone...

I haven't blogged in years. Most of the time, I think I don't have much to say and that no one cares :) I have a renewed interest in writing some stuff that I think about and want to save somewhere. I've changed jobs and worked on a couple of different things at Microsoft since Oslo. I'm going to start talking about my most recent job and work. I'm a developer manager now running a team of engineers that ship a bunch of management products for supporting Windows clients in the Enterprise. They include the products MBAM (Bitlocker management), AppV (application virtualization), and UEV (user environment virtualization). It's an awesome job and great team. I'll probably talk more about that as I go. In the mean time, I figure I would wax on about how time flies. "Where has the day gone?". That's a funny saying in my family. Back when my mother-in-law was alive, one day, we were outside enjoying the sunset. She was busy "fixing herself" looking inside of her shirt, and made the statement "where has the day gone". We thought she was looking for it inside her shirt. In summary - it's been awhile world. But maybe it's time for a sunrise and a renewed commitment to blogging :)

Wednesday, November 18, 2009

DynamicObject over M values

If you've used our bits, then for sure you have played with the DSL features.

If you haven't, it lets you create a DSL to process text and produce structured data.

With dynamic object support in .Net 4.0, we can add the ability to interact with that data as if it were objects in the CLR.

The code below is just such a little library that lets you do so. Don wrote it last night to help out with our PDC talk today.

Try it out!

// implementation of .NET 4.0 dynamic object over Node/Edge data model

// provides a read-only object-based view over M values

namespace Microsoft.M

{

using System.Reflection;

using System.Dataflow;

using System.IO;

using System.Dynamic;

using System;

using System.Collections;

using System.Collections.Generic;

public class DynamicObjectNode : DynamicObject

{

Node node;

// we never want to double-wrap, so all construction

// goes through NodeToObject, which unwraps atomics

internal DynamicObjectNode(Node node)

{

this.node = node;

}

// this method is the only way to go from the low-level

// node world to the world of (dynamic) objects

public static object NodeToObject(Node node)

{

if (node.NodeKind == NodeKind.Atomic)

return node.AtomicValue;

else

return new DynamicObjectNode(node);

}

public static IEnumerable<dynamic> ReadValues(TextReader reader)

{

foreach (var edge in Node.ReadFrom(reader))

{

yield return NodeToObject(edge.Node);

}

}

public static IEnumerable<dynamic> ReadValuesFromString(string text)

{

foreach (var edge in Node.ReadFromString(text))

{

yield return NodeToObject(edge.Node);

}

}

// implement the obvious three...

public override bool Equals(object obj)

{

return node.Equals(obj);

}

public override int GetHashCode()

{

return node.GetHashCode();

}

public override string ToString()

{

return node.ToString();

}

public Identifier GetBrand() { return node.Brand; }

// special case conversion to IEnumerable to support foreach

// over lists and collections

public override bool TryConvert(ConvertBinder binder, out object result)

{

result = null;

if (binder.Type != typeof(IEnumerable))

return false;

switch (node.NodeKind)

{

case NodeKind.Collection:

result = WrapCollectionNodeAsEnumerable(node);

return true;

case NodeKind.List:

result = WrapListNodeAsEnumerable(node);

return true;

default:

return false;

}

}

IEnumerable WrapCollectionNodeAsEnumerable(Node node)

{

foreach (var item in node.Edges)

{

yield return NodeToObject(item.Node);

}

}

IEnumerable WrapListNodeAsEnumerable(Node node)

{

foreach (var item in node.ViewAsList())

{

yield return NodeToObject(item);

}

}

// support [int] over list and [string] over records

public override bool TryGetIndex(GetIndexBinder binder, object[] indexes, out object result)

{

result = null;

if (indexes.Length > 1)

return false;

if (node.NodeKind == NodeKind.List && indexes[0] is int)

{

int index = (int)indexes[0];

var list = node.ViewAsList();

if (index >= list.Count)

throw new IndexOutOfRangeException("Indexed past end of M list node");

result = NodeToObject(list[index]);

return true;

}

string name = indexes[0] as string;

if (name != null)

{

return TryGetMember(name, true, out result);

}

return false;

}

// support . over records and .Count/.IsReadOnly over collections/lists

public override bool TryGetMember(GetMemberBinder binder, out object result)

{

return TryGetMember(binder.Name, binder.IgnoreCase, out result);

}

bool TryGetMember(string name, bool ignoreCase, out object result)

{

result = null;

switch (node.NodeKind)

{

case NodeKind.List:

if (name == "Count")

{

result = node.ViewAsList().Count;

return true;

}

else if (name == "IsReadOnly")

{

result = true;

return true;

}

return false;

case NodeKind.Collection:

if (name == "Count")

{

result = node.Edges.Count;

return true;

}

else if (name == "IsReadOnly")

{

result = true;

return true;

}

return false;

case NodeKind.Record:

if (node.Edges.ContainsLabel(name))

{

result = NodeToObject(node.Edges[name]);

return true;

}

else if (ignoreCase)

{

foreach (var e in node.Edges)

{

if (string.Compare(e.Label.Text, name, StringComparison.InvariantCultureIgnoreCase) == 0)

{

result = NodeToObject(e.Node);

return true;

}

}

}

return false;

default:

return false;

}

}

}

public class Language

{

MImage image;

GraphStore store = new DefaultGraphStore();

string moduleName, languageName;

ParserFactory factory;

internal Language(MImage image, string moduleName, string languageName)

{

this.image = image;

this.moduleName = moduleName;

this.languageName = languageName;

this.factory = this.image.ParserFactories[moduleName + "." + languageName];

}

internal Language(Assembly assm, string moduleName, string languageName)

: this(MImage.LoadFromAssembly(assm), moduleName, languageName)

{

}

public MImage Image { get { return this.image; } }

public string ModuleName { get { return moduleName; } }

public string LanguageName { get { return languageName; } }

public static Language Load(MImage image, string moduleName, string languageName)

{

return new Language(image, moduleName, languageName);

}

public static Language Load(Assembly assm, string moduleName, string languageName)

{

return new Language(assm, moduleName, languageName);

}

public static Language LoadFromCurrentAssembly(string moduleName, string languageName)

{

var assm = Assembly.GetCallingAssembly();

return new Language(assm, moduleName, languageName);

}

public dynamic ParseString(string text)

{

return Parse(new StringReader(text));

}

public dynamic ParseString(string text, ErrorReporter errors)

{

return Parse(new StringReader(text), errors);

}

public dynamic ParseString(string text, ErrorReporter errors, string fileName)

{

return Parse(new StringReader(text), errors, fileName);

}

public dynamic ParseString(string text, ErrorReporter errors, string fileName, SourcePoint startLocation)

{

return Parse(new StringReader(text), errors, fileName, startLocation);

}

public dynamic Parse(TextReader reader)

{

return Parse(new TextReaderTextStream(reader), ErrorReporter.Standard);

}

public dynamic Parse(TextReader reader, ErrorReporter errors)

{

return Parse(new TextReaderTextStream(reader), errors);

}

public dynamic Parse(TextReader reader, ErrorReporter errors, string fileName, SourcePoint startLocation)

{

return Parse(new TextReaderTextStream(fileName, reader), errors, startLocation);

}

public dynamic Parse(TextReader reader, ErrorReporter errors, string fileName)

{

return Parse(new TextReaderTextStream(fileName, reader), errors);

}

public dynamic Parse(ITextStream stream, ErrorReporter errors)

{

var parser = CreateParser();

var result = parser.Parse(stream, errors);

return NormalizeResult(result);

}

public dynamic Parse(ITextStream stream, ErrorReporter errors, SourcePoint startLocation)

{

var parser = CreateParser();

var result = parser.Parse(stream, errors, startLocation);

return NormalizeResult(result);

}

System.Dataflow.Parser CreateParser()

{

var parser = factory.Create();

parser.GraphBuilder = new NodeGraphBuilder(store);

return parser;

}

dynamic NormalizeResult(object result)

{

if (result is Node)

return DynamicObjectNode.NodeToObject((Node)result);

return result;

}

}

}

Hanging out at PDC 09

Hey - I'm at PDC 09. Stop by the booth if you want to chat about all things "M".

Wednesday, August 12, 2009

PHP popularity...

Interesting thread on how PHP got to be popular.

In summary
- drop features if they contribute the least to producitivity
- fabulous docs
- "killer" framework/scenario (VB + Forms, Ruby + Rails, PHP + Web apps)

Languages and game development

I'm reading through an interesting ppt on a game developer's view of language problems with C++.

http://lambda-the-ultimate.org/node/1277

I found this interesting:

§ Usage of integer variables in Unreal:

90% of integer variables in Unreal exist to index into arrays

80% could be dependently-typed explicitly,
guaranteeing safe array access without casting.

10% would require casts upon array access.

The other 10% are used for:

Computing summary statistics

Encoding bit flags

Various forms of low-level hackery

§ “For” loops in Unreal:

40% are functional comprehensions

50% are functional folds

The Natural Numbers

Factoid: C# exposes more than 10 integer-like data types, none of which are those defined by (Pythagoras, 500BC).

In the future, can we get integers right?

And this:

Concurency in Numeric Computation

· These are essentially pure functional algorithms, but they operate locally on mutable state

· Haskell ST, STRef solution enables encapsulating local heaps and mutability within referentially-transparent code

· These are the building blocks for implicitly parallel programs

· Estimate ~80% of CPU effort in Unreal can be parallelized this way

Thursday, May 28, 2009

M interop with XML (and others)

Someone asked a great question on the MSDN forums
looking at the current bits i can see a transform from M to XML.
But what seems to be missing is a transform from (XSD+XML) => M Schema + M
Any plans for this?
I think you need to support this scenario as most metadata (or models as you call them)
is now encoded as XML


I like to think of M interop with other data formats in two very interesting and different scenarios. 

One is exactly what Tim asked about - how do we take M data (which we call MGraph) and interoperate with other data formats, such as XML? Truthfully the mapping is pretty straight forward and we've prototyped it enough and have an initial implementation. It needs improved engineering before we declare ship ready, but there you have it.

We also think about interop at the schema level. For example, if we transform an MGraph to XML, then wouldn't it be great to transform the representative schema for that data into XSD? That's something we haven't quite done yet, but again I don't think that it would be hard, just work. We don't really have that booked to implement right now. Do people see a huge need?

Also, there's interop at the grammar level. This is probably not something we talk about much because there you really need to consider functional mapping. In other words, a grammar is just a function from character* to structured data (MGraph). Mapping that from one language like M to another like XSLT is quite a complicated problem and not likely something we'll tackle outside of research interests.

Now, there's another really big set of scenarios, quite orthogonal to the external interop ones above. I call these internal interop, or how to enable embedded DSLs or user-defined data structure and types within M. Sounds quite mystical and abstract :)

It means a couple of things. First, what if you wanted to write your own syntax for values inside of an M program. For example, suppose we didn't have a GUID type nor a textual representation for GUID values. Wouldn't it be great if you could tell M - "hey this is my syntax for my data"? We're thinking about that scenario, but nothing is committed to implementation yet. 

Second, what if you could write your own DSL inside of M that would produce the equivalent of M program constructs, such as types and functions. This is what we call 'meta-programming'. We have lots of dreams to do this, and we're working towards that, but it's most likely a V2 priority not a V1 priority.

As always - I'd love your feedback on this.