Showing posts with label Syntax. Show all posts
Showing posts with label Syntax. Show all posts

Monday, November 11, 2013

Quick method to optimize your foreign key searching

A lot of people do not realize that creating a foreign key does not also create an index.  This is by design and actually a good thing.  Over indexing a table can actually slow querying as every insert or update causes indices to be recalculated.  Over indexing a table will also slow down select statements from that table as the query optimizer will struggle through evaluating all of the indices to pick the one it thinks is best suited for your current search. 

When working on building targeted foreign key indices to speed up a search, I came up with this code block to auto generate the script for me.

SELECT 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N''IX_' + FK.TABLE_NAME + '_' + CU.COLUMN_NAME + ''') 
BEGIN 
    CREATE INDEX IX_' + FK.TABLE_NAME + '_' + CU.COLUMN_NAME + '  ON ' + FK.TABLE_NAME + '(' + CU.COLUMN_NAME + ');
END
GO
print N''create IX_' + FK.TABLE_NAME + '_' + CU.COLUMN_NAME + ' done''; 
RAISERROR (N'' --------------------'', 10,1) WITH NOWAIT',
       FK.TABLE_NAME AS K_Table,
       CU.COLUMN_NAME AS FK_Column,
       PK.TABLE_NAME AS PK_Table,
       PT.COLUMN_NAME AS PK_Column,
       C.CONSTRAINT_NAME AS Constraint_Name
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C
       INNER JOIN
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK
       ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
       INNER JOIN
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK
       ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
       INNER JOIN
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
       ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
       INNER JOIN
       (SELECT i1.TABLE_NAME,
               i2.COLUMN_NAME
        FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1
               INNER JOIN
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2
               ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE  i1.CONSTRAINT_TYPE = 'PRIMARY KEY') AS PT
       ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE  PT.Column_name = 'ID'
       AND PK.Table_Name = '{COMMONLY QUERIED TABLE}'
       AND FK.TABLE_NAME <> PK.TABLE_NAME;  
In this statement, I would replace {COMMONLY QUERIED TABLE} with the table name holding the primary key that was used in queries often. This will generate statements to create an index if one does not exist. You could easily modify it to do a drop and add as well.

Wednesday, October 31, 2012

C# 5: Caller Information

.NET 4.5 has introduced a few new classes that will enable more detailed logging, debugging and tracing through your code.  Today, I will focus on the following classes:

1. The CallerMemberNameAttribute class is used by appending it as an optional parameter on a method.  When the method is called, it will contain the method or property name which made the current call into this method.
2. The CallerFilePathAttribute class is used by appending it as an optional parameter on a method.  When the method is called, it will contain the code file path to the C# source code file that made the current call into this method.
3. The CallerLineNumberAttribute class is used by appending it as an optional parameter on a method.  When the method is called, it will contain the code file line number within the C# source code file that made the current call into this method.

Grabbing all of this information will enable you to create very detailed logging and trace statements.  This will ultimately aid you in trouble-shooting code issues or errors encountered by users running your application.  Below is a usage example of these attributes and the run-time logging potential they make available.

namespace CallerInformation
{
    class Program
    {
        static void Main(string[] args)
        {   
            MethodA();
            MethodB();
            Console.ReadLine();
        }

        static void MethodA([CallerMemberName] string memberName = "",
               [CallerFilePath] string sourceFilePath = "", 
               [CallerLineNumber] int sourceLineNumber = 0)
        {
            InsertLog(memberName, "MethodA", sourceFilePath, sourceLineNumber); 
            MethodB();
        }

        static void MethodB( [CallerMemberName] string memberName = "",
               [CallerFilePath] string sourceFilePath = "",  
               [CallerLineNumber] int sourceLineNumber = 0)
        {
            InsertLog(memberName, "MethodB", sourceFilePath, sourceLineNumber);
        }

        static void InsertLog(string methodName, String calledMethodName, 
               String sourceFilePath, Int32 sourceLineNumber)
        {
            Console.WriteLine("{0} called {1} from file:'{2}' line: {3}",
              methodName, calledMethodName, 
              sourceFilePath, sourceLineNumber.ToString());

        }
    }
}

In the output from this application, notice that the calls to MethodB() both form the Main method as well as MethodA() contains the correct information regarding the source object, file and line number.


While possible to rely upon your exception handling plan to provide call stack information, you are also able to build on the base for your own purposes.  By accessing these values as well as any local data, variable values and session specific information, you can build and handle messages that present you with all of the information needed to troubleshoot user errors, debug intricate processes or supply more complex audit trail logging within your system.

Thursday, October 11, 2012

Async in C#

C# 5 adds some powerful asynchronous functionality as an expansion to the Task and Task<TResult> aspects of .NET 4.  The old model seemed to focus on the Asynchronous Pattern and the Event-based Asynchronous Pattern but the framework itself didn't bring much to the table for allowing developers with varied levels of knowledge access to an easily adopted methodology.  With the roll out of C# 5, .NET has provided us with a wrapped API for all asynchronous programming via library based method calls.  This is the first building block of the new features for asynchronous programming and it has been said that going forward, all asynchronous operations will work via a method that returns Task or Task<TResult>.

A key change for this version is the addition of the async keyword.  This contextual keyword enables an easy declaration of an asynchronous function. Note that all asynchronous functions must return either void, a Task, or a Task<T>.  When declaring a function with async, it is also required that there must be at least one await expression inside the function itself. 

The await keyword expression does not block the thread on which it is executing. Instead, it causes the compiler to sign up the rest of the async method as a continuation on the awaited task. Control then returns to the caller of the async method. When the task completes, it invokes its continuation, and execution of the async method resumes where it left off. An await expression can occur only in the body of an immediately enclosing method, lambda expression, or anonymous method that is marked by an async modifier. The term await serves as a keyword only in that context. Elsewhere, it is interpreted as an identifier. Within the method, lambda expression, or anonymous method, an await expression cannot occur in the body of a synchronous function, in a query expression, in the catch or finally block of an exception handling statement, in the block of a lock statement, or in an unsafe context.

// Keyword await used with a method that returns a Task&ltresult&gt.
TResult result = await AsyncMethodThatReturnsTaskTResult();

// Keyword await used with a method that returns a Task.
await AsyncMethodThatReturnsTask();

Here is a simple example, suppose we want to download a webpage as a string so we can check to see if it supports XHTML 1.0,. There is a new method added to WebClient: Task<string> WebClient.DownloadStringTaskAsync(Uri).  Since this returns a Task<string> we can use it within an asynchronous function. We could do this entire process with jsut a few lines of code.

private void button1_Click(object sender, RoutedEventArgs e)
{
   String url = "http://devstorm.blogspot.com";
   String content = await new WebClient().DownloadStringTaskAsync(url);
   textBox1.Text = String.Format("Page {0} supports XHTML 1.0: {1}",
      url, content.Contains("XHTML 1.0"));
}

By adding the async contextual keyword to the method definition, we are able to use the await keyword on our WebClient.DownloadStringTaskAsync method call. This means that when the user clicks the new method (Task<string> WebClient.DownloadStringTaskAsync(string)) is called.  By adding the await keyword, the runtime will call this method that returns Task<string> and execution will return to the UI.  This means that our UI is not blocked while the webpage is downloaded.  Instead, the UI thread will “await” at this point, and let the WebClient do it’s thing asynchronously.  When the WebClient finishes downloading the string, the user interface’s synchronization context will automatically be used to “pick up” where it left off, and the Task<string> returned from DownloadStringTaskAsync is automatically unwrapped and set into the content variable, thus allowing us to report it in the text box.

Notice how the code is obviously shorter and simpler because the initial synchronization context is used to continue the execution of this functionMeaning,  we don’t have to explicitly marshal the call that sets textbox1.Text back to the UI thread, it is just picked up via the framework.  This sort of abstraction of the difficulty is a strong step toward enabling a wide range of developers the ability to leverage asynchronous programming.  The will ultimately, as always, result in better software for the users.

Friday, July 27, 2012

Ordinals vs Column Names - Part Deux

Since the first post turned into a discussion of readability versus performance, maintainability versus speed and other very worthwhile topics, I decided to put this entire process to a road test.  I created unit tests that called a DAL services basically retrieving an entire table in each of the three presented methodologies.  To get the output, I enabled our handy-dandy unit test timer to trap the performance of the DAL list functions. In order for this to be good science, I restarted the database 3 times over the course of the testing and ran the tests in different orders each time. For retrieving just under 17000 rows consisting of 38 columns, here are the results and averages for the test run (values in Milliseconds).


Run  String  Dictionary Ordinal String Order Dictionary Order Ordinal Order Difference
1 2705 2469 2505 1 3 2 236
2 2648 2575 2506 2 3 1 142
3 2669 2520 2501 2 1 3 168
4 2570 2648 2445 3 1 2 203
5 2577 2566 2397 3 1 2 180
6 2450 2646 2575 3 1 2 196
7 2555 2606 2479 2 1 3 127
8 2843 2621 2446 1 2 3 397
9 2628 2485 2388 1 2 3 240
10 2580 2525 2400 3 1 2 180
Total 2622.50 2566.10 2464.20 0.21

So, at the end of the day, here is what I learned.  It really doesn't matter enough to worry about it.  Sure, the ordinals are consistently faster than the dictionary and the dictionary is faster than string, but the variance is hardly worth worrying about.  When retrieving 17000 rows, the average difference from the best performance to the worst performance was a matter of 2/10 of one second.  I think I got wrapped up in a common distraction know as micro-optimization, where we spend a ton of mental energy and rewrite code to gain a millisecond on a routine. Yes, you should care about performance, and yes, faster code should always be your goal, but there comes a point in time where it stops being worth the development time.  Yes, we should avoid the blatantly poor performing code mistakes that everyone knows about. But after that, we should be equally worried about the scalability, portability, maintainability and readability of our code. We should ask if saving 2/10 second while retrieving 17000 rows is the matter to discuss, or should we be discussing why we would ever be retrieving 17000 often enough in our application to have 2/10 second be an issue.  Yes, I started the discussion, thinking it was a creative way to gain performance.  Honestly, I enjoy trying to work through things like this too, which fed the distraction.  I believe now that these mental exercises should always be framed with realistic improvement potential weighed against the time and effort of the pursuit. At that point, only pursue until it starts becoming a net loss of productivity.  On this particular subject, after doing this research, count me firmly in the "It really doesn't matter, aim at code readability and object design" camp.

Tuesday, July 24, 2012

Synchronization Context and Callbacks

SynchronizationContext 
The SynchronizationContext behavior is basically a configuration that allows the asynchronous and synchronization operations of the CLR to act appropriately while being used within various synchronization models. It also allows for a simple configuration of applications to work correctly under the different synchronization environments. This gives a service a quick way of associating itself with a particular synchronization context and then allowing WCF to detect that context and automatically marshal the call from the worker thread to the service synchronization context. The default value of UseSynchronizationContext is true. Affinity between the service, host and synchronization context is set when the host is opened. If the thread opening the host has a synchronization context and UseSynchronizationContext is true, WCF will establish an affinity between that synchronization context and all instances of the service hosted by that host. WCF will automatically marshal all incoming calls to the synchronization context. If UseSynchronizationContext is false, regardless of any synchronization context the opening thread might have, the service will have no affinity to any synchronization context. Interestingly enough, if UseSynchronizationContext is true but the opening thread has no synchronization context, the service will still not have one. By default, when executing the code below the client thread will be blocked while the return value is received from the service.

serviceProxy = new SomeService(new InstanceContext(this));
serviceProxy.Open();
MyObject = serviceProxy.CreateMyObject(new MyObject(1));

This is all fine on the surface. But, what would happen if the CreateMyObject function sends a callback? The client thread would be blocked. We can handle this with the callback behavior aspect of WCF. CallbackBehaviorAttribute.

UseSynchronizationContext
As a refresher, the CallbackContract property of a ServiceContract specifies the interface to define callback operations. This will create a dependent relationship between the interfaces. Once a CallbackContract is specified, the client will have to implement the callback functions in order to interact with the service at all. The CallbackBehavior setting for UseSynchronizationContext basically governs the affinity between the service and the client. You can easily override the automatic association of synchronization contexts with a simple decoration. By setting the UseSynchronizationContext property of the CallbackBehavior attribute to false, WCF will no longer guarantee a particular thread to be responsible for processing service requests. Instead, the operations will be automatically delegated to worker threads.

[CallbackBehavior(UseSynchronizationContext = false)]

When not using synchronization context on callback behavior, you may run into issues trying to directly update the UI, since those callbacks will no longer be on the UI thread.  One way around that would be to use a SendOrPostCallback delegate.

Thursday, July 19, 2012

Ordinals vs. Column Names on DataReader

There is some contention on whether you should use the name of a column versus hard coding the ordinal when using a DataReader to retrieve data from a procedure call. While using the name is more easily maintained and agnostic on the order of columns, there is an inherent overhead with using the column name. While I understand the performance hit of using the named column, I find the value in code readability and column ordering independence worth it.  While, the hit is negligible on a single row result set, the compounding nature may result in a performance change worth avoiding. I came up with a neat little trick that can be used prior to looping through the result set of a list return.  I created a helper class that will give you the ordinals back locally and allow them to be used on each loop.

public Dictionary<String, Int32> GetOrdinals(IDataReader reader)
{
   Dictionary<String, Int32> retVal = new Dictionary<String, Int32>();
   for (int i = 0; i < reader.FieldCount; i++)
   {
     retVal.Add(reader.GetName(i), i);
   }
   return retVal;
}
 
This enables us to hit the reader row one time and bring back a local variable containing all of the column names as the key of a dictionary with ordinal as a value.  At this point, you can simply use the keyed reference as follows and get the best of both worlds when retrieving large result set.

Boolean hasData = reader.Read();
List<Contract> listItems = new  List<Contract>();
Dictionary<String,Int32> ordinals = null;
if (hasData)
{
   ordinals = GetOrdinals(reader);
}
 
while (hasData)
{
   Contract item = new Contract();            
   item.ID =  Convert.ToInt64(reader[ordinals["ID"]]);
   item.Name = reader[ordinals["Name"]].ToString();
   listItems.Add(item);
   hasData = reader.Read();
}

This method would be a performance hit when using against a single row return value. And the break-even point of a large result set may take a few rows to gain back the hit of the first loop. However, it is a nice little trick to get the best of both worlds when hitting a large query.

Monday, June 25, 2012

Dynamic Types Using C#


I ran across a scenario the other day for creating a function to perform similar work on similar classes without having to be tightly coupled to any concrete type.  I found a few viable options and will be doing some examples of each to work through the exercise completely.  The first I want to talk through is the use of the dynamic keyword.

In short, using dynamic tells the compiler to ignore types at compile time and instead determine dispatch based on the actual type at run time.  Static binding of types does the exact opposite and performs a dispatch based on the concrete type.  Since code always illustrates these academic discussions more clearly, I created a simple project.  I have two examples of dynamic typing built into this single example.  I created some classes to illustrate a few types that are completely unrelated to each other. I also created a single class that was an example of type inheritance.

public class Account
{
    public String Name { get; set; }
    public Double Balance { get; set; }
    public Int16 AccountType { get; set; }
    public Account()
    {
        Name = "Account";
        Balance = 100.00;
    }
}
 
public class Customer
{
    public String Name { get; set; }
    public Double Balance { get; set; }
    public String CustomerType { get; set; }
    public Customer()
    {
        Name = "Customer";
        Balance = 900.00;
    }
}
 
public class Employee
{
    public String Name { get; set; }
    public Double Balance { get; set; }
    public String Department { get; set; }
    public Employee()
    {
        Name = "Employee";
        Balance = 500.00;
    }
}
 
public class Payable : Account
{
    public Payable()
    {
        Name = "Payable";
        Balance = -100;
    }
}

As you can see, the classes are completely independent of each other, but they have similar members.  Using the dynamic keyword, we can create a function that deals with these types at runtime and interacts with expected members at that time.

static void WriteDynamicObject( dynamic thing)
{
    Console.WriteLine("Name: "+ thing.Name + ", Balance: " + thing.Balance.ToString());
}
 
As long as the types I pass to this function have publicly accessible members named Name and Balance, this code will work with a complete disregard for compilation of any known type.  Next, to illustrate using the dynamic keyword for instructing the runtime to resolve the type regardless of declaration, I created overloaded functions that take a concrete type at each level of inheritance.

static void WriteConcrete(Account thing)
{
    Console.WriteLine("I am an Account Thing");
}
static void WriteConcrete(Payable thing)
{
    Console.WriteLine("I am a Payable Thing");
}

Finally, a simple console application that illustrates how these items work and are either statically or dynamically resolved.  The difference between the 'Concrete' functions is shown by the dynamic keyword telling the runtime to resolve this class at run time regardless of it's declaration.  Also, notice the last item is not even a class at all, but rather a dynamic type declared simply to be passed to this helper function.


static void Main(string[] args)
{
    // call writedynamicobject function for all concrete classes
    Account a = new Account();
    WriteDynamicObject(a);
            
    Employee e = new Employee();
    WriteDynamicObject(e);
            
    Customer c = new Customer();
    WriteDynamicObject(c);
 
    //call function for a concrete class, but resolve type at runtime
    Account p = new Payable();
    WriteConcrete(p);
    WriteConcrete((dynamic)p);
    // dynamically create values and pass as a dynamic type.
    WriteDynamicObject(new {Name="TotallyDynamicNonClass", Balance=250});
 
}
 
The output of this project illustrates how the runtime deals with each of these scenarios.


If you are like me, the first thought you have on using the dynamic typing pattern is along the lines of, "Couldn't you just use an interface or inheritance to accomplish the same thing?"  The answer is yes, assuming you are able to know those types at compile time.  But, there are also times where you can't know the definitions at compile time.  You can't compile the internet, but you can interface with it.  I would be remiss if I didn't get into the downsides of this practice as well.  This can open you up to having a program that is hard to maintain, difficult to debug and unit test and many similar issues. But, guns don't kill people, people with guns do.  Dynamic typing doesn't make code difficult to manage, a programmer does those things with his or her architecture.  This is not a magic bullet and should be used with great caution. The reason for using it is that some problems are inherently dynamic (e.g. web requests).  While you may use reflection for this sort of problem today, you may find dynamic typing a more expressive approach to the same problem.  Simply being dynamic in itself is a compelling argument. Reflection introduces a tightly coupled dependency on a static mechanism.  Dynamic methodologies are intent-based and trust the receiver to act upon the passed intent which creates a scalability unachievable by other means.  The source files are available for download here

Saturday, April 14, 2012

Coalesce and Inline Operators

As part of a platform review, I have been seeing a lot of code lately that has been in production for a while.  I know that sometimes people are just creating the code they know how to create and making the project run.  That is all good.  Sometimes it is good to try and learn a better way way to do some very basic things.  I know that personally the following options make code a bit more easy to follow and maintain.

Coalesce
There are a lot of long if statements that seem to be looking for the first usable value. (this is a composite of the idea...)


if (value1 != null)  
    variable = value1;  
else if (value2 != null)  
    variable = value2;  
else if (value3 != null)  
    variable = value3;  
else  
    variable = DefaultValue;  

Just to simplify and make more readable, try using the ?? (coalesce) operator.  Notice the nullable type restrictions, etc.


variable = value1 ?? value2 ?? value3 ?? DefaultValue;   


Inline If
Here is another one (again, just a composite)

if (something == somethingelse)  
  value = "1";  
else  
  value = "2";     

try making this one line, more readable.

value = (something == somethingelse) ? "1" : "2";      


I know that these are some basic ideas, but judging by the code bases I have been checking out, they are not widely used.  Perhaps, looking into some of these items as best practices would be beneficial.