Sunday, August 10, 2014

Test File Thru=Put

Really nice file throughput tool to check read, write and dir listing, great tool to utilize to verify copy speeds between drives or networks:
https://www.raymond.cc/blog/12-file-copy-software-tested-for-fastest-transfer-speed/

Friday, August 8, 2014

JS or JQuery Get Query String Value

In JS:
var queryString = url.substring( url.indexOf('?') + 1 );
or

function queryString(keyName) {
    var query = window.location.search.substring(1);
    var vars = query.split('&');
    for (var i = 0; i < vars.length; i++) {
        var pair = vars[i].split('=');
        if (decodeURIComponent(pair[0]) == keyName) {            return decodeURIComponent(pair[1]);
        }
    }
    return null;
}

In JQuery:

Get Query String Vals:

e.g if the below mentioned is url with querystring

http://localhost/MyPage.aspx?Node1=9000%20&Node2=3000

var url = window.location.href; // or location.href
var Node1= null;
var Node2= null;

if (url.indexOf(‘?Node1=’) != -1)
{

Node1= $.query.get(‘Node1’);
Node2= $.query.get(‘Node2′);

}

Script References for For $.query.get() function :

Thursday, August 7, 2014

SQL Server Case Sensitivity and SSIS and SSAS

Currently SQL Server default is normally 'SQL_Latin1_General_CP1_CI_AS', the _CI means "case insensitive" - for case-sensitive we would see _CS in its place,

If we were to change the collation, that may break the ETL if you have any, as you may currently not use case sensitive searches for MERGES, it would take a re-write, re-test ect to make it work,

Build DIM Tables:

Collation can be set on Server, DB or by Query:

I would recommend using the "Per Query" collation, we can simply apply a "hint" to our query, and then the query would do a case sensitive search instead of the default, case insensitive, See here for detail:

http://www.mssqltips.com/sqlservertip/1032/case-sensitive-search-on-a-case-insensitive-sql-server/

An example for OLS:

-- case Insensitive DEFAULT

SELECT d.*, b.*, a.*, c.*

....

where value = 'pass'

-- case Sensitive DEFAULT

SELECT d.*, b.*, a.*, c.*

FROM..

Where

Value LIKE '%PASS%' Collate SQL_Latin1_General_CP1_CS_AS

SSIS LookUps

Options include:

1. Another option is to keep your Select DISTINCT, and then in your SSIS lookup, tell SSIS to "do the lookup in the SQL Server", I believe you can do this as follows:

"Set the CacheType property of the lookup transformation to Partial or None, the lookup comparisons will now be done by SQL Server and not by the SSIS lookup component. "

http://sqlblog.com/blogs/jorg_klein/archive/2009/04/14/ssis-lookup-is-case-sensitive.aspx

2. Also, in Dimenion processing later on, SSAS takes its collation from the DB default: "SSAS dimension will inherits its collation from underlying database and if you don't change the setting it might use case sensitive collation"

http://www.rad.pasfu.com/index.php?/archives/86-SSAS-Dimension-Processing-Be-Careful-about-Case-Sensitivity.html

3. Use LOWER() or UPPER() functions in query and pipeline.

To me this is an SSIS issue, I would err on the side of simplicity and maintainable because in the future someone would need to maintain the solution and the simpler the solution the quicker someone can fix it. In SSIS Ive always seen either lookups with Upper()/Lower(), by setting the cache type or pulling the lookup out of SSIS and use T-SQL.

Monday, August 4, 2014

Performance Counter Example

using System;
using System.Diagnostics;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DTech.Processor
{
/// <summary>
/// Helper class to demonstrate the setup of performance counters.
/// </summary>
public static class PerformanceCounterHelper
{
public static readonly string CATEGORY = "IFP";

public static readonly string COUNTER_FILES_RECEIVED = "Total Files Processed";
public static readonly string COUNTER_Transactions_RECEIVED = "Total Transactions Processed";
public static readonly string COUNTER_DUP_Transactions_RECEIVED = "Total Duplicates Transactions Processed";

public static readonly string COUNTER_AVG_FILE_DURATION = "Avg time to process file";
public static readonly string COUNTER_AVG_FILE_DURATION_BASE = "Avg time to process file Base";

public static readonly string COUNTER_IFP_ERRORS = "Total IFP Errors";

// Total number of tests executed
private static PerformanceCounter _TotalFiles = null;
// Total Transactions
private static PerformanceCounter _TotalTransactions = null;
// Total dup Transactions
private static PerformanceCounter _TotalDupTransactions = null;
// avg files
private static PerformanceCounter _AvgFiles = null;
// avg files
private static PerformanceCounter _AvgFilesBase = null;
// avg files
private static PerformanceCounter _TotalErrors = null;

/// <summary>
/// Constructor
/// </summary>
static PerformanceCounterHelper()
{
try
{
// Set up the performance counter(s)
if (!PerformanceCounterCategory.Exists(CATEGORY))
{
// Create the collection container
CounterCreationDataCollection counters = new
CounterCreationDataCollection();
addCounter(counters, COUNTER_FILES_RECEIVED, COUNTER_FILES_RECEIVED, PerformanceCounterType.NumberOfItems64);
addCounter(counters, COUNTER_Transactions_RECEIVED, COUNTER_Transactions_RECEIVED, PerformanceCounterType.NumberOfItems64);
addCounter(counters, COUNTER_DUP_Transactions_RECEIVED, COUNTER_DUP_Transactions_RECEIVED, PerformanceCounterType.NumberOfItems64);

addCounter(counters, COUNTER_IFP_ERRORS, COUNTER_IFP_ERRORS, PerformanceCounterType.NumberOfItems64);

addCounter(counters, COUNTER_AVG_FILE_DURATION, COUNTER_AVG_FILE_DURATION, PerformanceCounterType.AverageTimer32);
addCounter(counters, COUNTER_AVG_FILE_DURATION_BASE, COUNTER_AVG_FILE_DURATION_BASE, PerformanceCounterType.AverageBase);

//// Create counter #1 and add it to the collection
//CounterCreationData tests = new CounterCreationData();
//tests.CounterName = "Total Tests Executed";
//tests.CounterHelp = "Total number of tests executed.";
//tests.CounterType = PerformanceCounterType.NumberOfItems32;
//counters.Add(tests);

//// Create counter #2 and add it to the collection
//CounterCreationData testsPerSec = new CounterCreationData();
//testsPerSec.CounterName = "Tests Executed / sec";
//testsPerSec.CounterHelp = "Number of tests executed per second.";
//testsPerSec.CounterType =
// PerformanceCounterType.RateOfCountsPerSecond32;
//counters.Add(testsPerSec);

//// Create counter #3 and add it to the collection
//CounterCreationData avgTest = new CounterCreationData();
//avgTest.CounterName = "Average Test Duration";
//avgTest.CounterHelp = "Average time to execute a test.";
//avgTest.CounterType = PerformanceCounterType.AverageTimer32;
//counters.Add(avgTest);

//// Create counter #4 and add it to the collection
//CounterCreationData avgTestBase = new CounterCreationData();
//avgTestBase.CounterName = "Average Test Duration Base";
//avgTestBase.CounterHelp = "Average time to execute a test base.";
//avgTestBase.CounterType = PerformanceCounterType.AverageBase;
//counters.Add(avgTestBase);

// Create the category and all of the counters.
PerformanceCounterCategory.Create(CATEGORY,
"IFP Counters.",
counters);
}

_TotalFiles = buildPerfCounter(COUNTER_FILES_RECEIVED);
_TotalFiles.RawValue = 0;

_TotalTransactions = buildPerfCounter(COUNTER_Transactions_RECEIVED);
_TotalTransactions.RawValue = 0;

_TotalDupTransactions = buildPerfCounter(COUNTER_DUP_Transactions_RECEIVED);
_TotalDupTransactions.RawValue = 0;

_TotalErrors = buildPerfCounter(COUNTER_IFP_ERRORS);
_TotalErrors.RawValue = 0;

_AvgFiles = buildPerfCounter(COUNTER_AVG_FILE_DURATION);
_AvgFiles.RawValue = 0;

_AvgFilesBase = buildPerfCounter(COUNTER_AVG_FILE_DURATION_BASE);
_AvgFilesBase.RawValue = 0;
}
catch (Exception exc)
{
Debug.Write("PerformanceCounterHelper: ERROR-> " + exc.Message);
}

}

/// <summary>
/// Increment the CodeGuru sample counters.
/// </summary>
/// <param name="Ticks">Timing interval</param>
public static void IncrementFiles()
{
try
{
checkReset();
if (_TotalFiles.RawValue >= Int64.MaxValue || rF)
{
_TotalFiles.RawValue = 0;
rF = false;
}
else
_TotalFiles.Increment();
}
catch { }
//_TestsPerSecond.Increment();
//_TotalDupTransactions.IncrementBy(Ticks);
//_TotalDupTransactionsBase.Increment();
}

public static void IncrementTransactions()
{
try
{
checkReset();
if (_TotalTransactions.RawValue >= Int64.MaxValue || rS)
{
_TotalTransactions.RawValue = 0;
rS = false;
}
else
_TotalTransactions.Increment();
}
catch { }
}

public static void IncrementDupTransactions()
{
try
{
checkReset();
if (_TotalDupTransactions.RawValue >= Int64.MaxValue || rDS)
{
_TotalDupTransactions.RawValue = 0;
rDS = false;
}
else
_TotalDupTransactions.Increment();
}
catch { }
}

public static void IncrementErrors()
{
try
{
checkReset();
if (_TotalErrors.RawValue >= Int64.MaxValue || rE)
{
_TotalErrors.RawValue = 0;
rDS = false;
}
else
_TotalErrors.Increment();
}
catch { }
}

public static void IncrementAvgFiles(long ticks)
{
try
{
_AvgFiles.IncrementBy(ticks);
if (_AvgFilesBase.RawValue > Int64.MaxValue)
_AvgFilesBase.RawValue = 0;
else
_AvgFilesBase.Increment();

}
catch { }
}

public static void IncrementAvgFiles(long ticks, int howmany)
{
try
{
_AvgFiles.IncrementBy(ticks);
_AvgFilesBase.IncrementBy(howmany);

}
catch { }
}

private static void addCounter(CounterCreationDataCollection counters, string counterName, string help, PerformanceCounterType type)
{
// Create counter #1 and add it to the collection
CounterCreationData tests = new CounterCreationData();
tests.CounterName = counterName;
tests.CounterHelp = help;
tests.CounterType = type;
counters.Add(tests);
}

private static PerformanceCounter buildPerfCounter(string name)
{
PerformanceCounter r = new PerformanceCounter();
r.CategoryName = CATEGORY;
r.CounterName = name;
r.MachineName = ".";
r.ReadOnly = false;
return r;
}

private static DateTime lastResetDate = DateTime.Now;

private static bool rF = false;
private static bool rS = false;
private static bool rDS = false;
private static bool rE = false;

private static object _lock = new object();
private static void checkReset()
{
lock (_lock)
{
if (DateTime.Now.Subtract(lastResetDate).TotalHours > 24)
{
Debug.WriteLine("RESET Perf Counters");
rF = true;
rS = true;
rS = true;
rE = true;
lastResetDate = DateTime.Now;
}
}
}
}
}

Task Cancellation Example

private void startRoute1(FileRouteFactory.FileRouteType frType, List<string> files, List<BaseTypes.FileType> fts, IFileHelper io)
{

const int numTasks = 9;

// Set up the cancellation source and get the token.
CancellationTokenSource tokenSource = new CancellationTokenSource();
CancellationToken token = tokenSource.Token;

// Set up the tasks
Task[] tasks = new Task[numTasks];
for (int i = 0; i < numTasks; i++)
tasks[i] = Task.Factory.StartNew(() => new Worker().PerformTask(token), token);

// Now the tasks are all set up, show the state.
// Most will be WaitingToRun, some will be Running
foreach (Task t in tasks.OrderBy(t => t.Id))
Console.WriteLine("Tasks {0} state: {1}", t.Id, t.Status);

// Give some of the tasks a chance to do something.
Thread.Sleep(1500);

// Cancel the tasks
//Debug.WriteLine("Cancelling tasks");
//tokenSource.Cancel();
//Debug.WriteLine("Cancellation Signalled");

try
{

int idx = 0;

do
{
if (this.CheckStop())
{
DiagnosticHelper.DebugWriteLine(this.DisplayString() + " -> " + frType.ToString() + " , calling CancellationTokenSource.Cancel...");
tokenSource.Cancel();
Task.WaitAll(tasks);
break;
}
else
{
if ((idx % 5) == 0)
{
DiagnosticHelper.DebugWriteLine(this.DisplayString() + " -> " + frType.ToString() + " -> Task.WaitAll, Waiting...");
}
}
idx++;
if (idx > 1000)
idx = 0;

} while (!Task.WaitAll(tasks, 1000));

// Wait for the tasks to cancel if they've not already completed
//Task.WaitAll(tasks);
}
catch (AggregateException aex)
{
aex.Handle(ex =>
{
// Handle the cancelled tasks
TaskCanceledException tcex = ex as TaskCanceledException;
if (tcex != null)
{
Debug.WriteLine("Handling cancellation of task {0}", tcex.Task.Id);
return true;
}

// Not handling any other types of exception.
return false;
});
}

// Show the state of each of the tasks.
// Some will be RanToCompletion, others will be Cancelled.
foreach (Task t in tasks.OrderBy(t => t.Id))
Debug.WriteLine("Tasks {0} state: {1}", t.Id, t.Status);

Debug.WriteLine("Program End");
}