Crossed Logic

DaytaBase.org

2011-09-17T13:01:00.003-04:00

I will be blogging SQL syntax at http://DaytaBase.org from now on, but may make plans for Crossed Logic in the future. For now, this will be an archive to maintain any links from Articles. For new content, please go to http://DaytaBase.org.

Thanks!

A MySQL Tidbit: Dynamic Export To_XML Stored Procedure

2010-09-11T08:57:00.004-04:00

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL

Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of. So the topics of parsing and manipulating XML, importing and exporting XML files, etcetera using SQL are pretty commonplace here at Experts Exchange.

Consequently, in two of my previous MySQL tidbits, I covered some real questions from EE's Q&A forum:

A MySQL Tidbit: In-line XML Parsing

A MySQL Tidbit: In-line CSV Parsing (Using XML)

While writing those articles and participating in the mentioned questions, I was urged by a fellow database expert to write some tips on the importing and exporting of XML, specifically in SQL using MySQL database server. As a result, this particular installment of my SQL tidbits will cover: (1) dynamically constructing XML elements from a given table structure; and (2) exporting a result set to XML file.

The end product will be a technique you can customize and test for your own XML needs. This technique will be shown in a stored procedure. For beginning MySQL readers, I will be providing some background tips -- explanations on some of the inner workings of the SQL code in the procedure...

Read more of "A MySQL Tidbit: Dynamic Export To_XML Stored Procedure" on Experts-Exchange.com/articles...

Enjoy!

A SQL Tidbit: Conditional Aggregates

2010-08-07T12:38:00.002-04:00

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time!

By the end of my Experts-Exchange Article, A SQL Tidbit: Conditional Aggregates, it is my intention to bring the meaning and value of the above quote to those who chose to read this whether as a beginning SQL programmer or an intermediate one inexperienced with this little tidbit of SQL syntax: conditional aggregation.

Conditional aggregation is simply the use of aggregates under one or more conditions, thus, potentially altering your results of functions like SUM(), COUNT(), etcetera in a particular column while allowing you to analyze your record set as a whole...

Expiration Date Validation Example

2009-07-27T16:11:00.003-04:00

Here is a simple example of a utility that can validate expirations dates like '0609' or '0710' as being valid expiration dates (i.e., is the date before the current month or not).


/**
 * Utility function to determine if a four character expiration date is valid OR not!
 * 
 * @param s
 * <p>Date string in format MMyy.  e.g., '0609' as date 'June 2009'.</p>
 * @return <code>boolean</code> flag indicating if expiration date is valid or not.
 */
public static boolean isValidExpiration(String s) {

    // use SimpleDateFormat to parse values like '0609' as date 'June 2009'
    java.text.DateFormat sdf = new java.text.SimpleDateFormat("MMyy");
    
    // establish current date as last day of previous month at 23:59:59
    java.util.Calendar now = java.util.Calendar.getInstance(); 
    now.set(now.get(java.util.Calendar.YEAR), now.get(java.util.Calendar.MONTH), 0, 23, 59, 59);

    try {
        // actual parsing of the date, wrapped in try/catch 
        // parses as 1st day of month at midnight since only providing month and year
        // e.g., '0609' becomes 'June 01, 2009 00:00:00'
        java.util.Date exp = sdf.parse(s);

        // if parsed date is before current month, then return invalid!
        if (exp.before(now.getTime())) return false;
    } catch (java.text.ParseException e) {
        return false;  // if not MMYY then reject
    }

    // valid if made it this far, can do in reverse by using !exp.before and return true above.
    return true; 
}

Java Custom Comparators: Sorting Text and Numbers Independently

2008-11-11T19:59:00.003-05:00

Comparing strings and sorting lists of strings in Java is normally very simple as the API provides a nice means of comparing two strings as text. However, the need occasionally arises when we have values stored that can be either numeric or text or a combination thereof.


String[] inputStringArray = { "1", "19", "2", "3pt1", "3", "5pt1", "6pt2", "7pt3", "7pt3", "10", "10x1", "15", "7", "A7", "B87", "10pt5" };

// The input is a String array. Make that an ArrayList:
java.util.List<String> inputList = new java.util.ArrayList<String>(java.util.Arrays.asList(inputStringArray));

Say we have the above input array of string values that we have converted to an ArrayList as shown and now want to sort. In steps custom comparators. The easiest way to implement one is using an anonymous nested class at the point of use which will allow the flexibility of sorting differently each time Collections.sort() is called. If you find you need this more often, then convert this to a standalone class that implements the Comparator interface.


public static java.util.List<String> sort(java.util.List<String> list) {
    java.util.Collections.sort(list, new java.util.Comparator<String>() {
        /**
         * Custom compare to sort numbers as numbers.
         * Strings as strings, with numbers ordered before strings.
         * 
         * @param o1
         * @param o2
         * @return
         */
        @Override
        public int compare(String o1, String o2) {
            boolean isFirstNumeric, isSecondNumeric;
        
            isFirstNumeric = o1.matches("\\d+");
            isSecondNumeric = o2.matches("\\d+");
        
            if (isFirstNumeric) {
                if (isSecondNumeric) {
                    return Integer.valueOf(o1).compareTo(Integer.valueOf(o2));
                } else {
                    return -1; // numbers always smaller than letters
                }
            } else {
                if (isSecondNumeric) {
                    return 1; // numbers always smaller than letters
                } else {
                    isFirstNumeric = o1.split("[^0-9]")[0].matches("\\d+");
                    isSecondNumeric = o2.split("[^0-9]")[0].matches("\\d+");
                
                    if (isFirstNumeric) {
                        if (isSecondNumeric) {
                            int intCompare = Integer.valueOf(o1.split("[^0-9]")[0]).compareTo(Integer.valueOf(o2.split("[^0-9]")[0]));
                            if (intCompare == 0) {
                                return o1.compareToIgnoreCase(o2);
                            }
                            return intCompare;
                        } else {
                            return -1; // numbers always smaller than letters
                        }
                    } else {
                        if (isSecondNumeric) {
                            return 1; // numbers always smaller than letters
                        } else {
                            return o1.compareToIgnoreCase(o2);
                        }
                    }
                }
            }
        }
    });
    return list;
}

The above does the following:

Determines if the strings being compared are numeric values or not, using regular expression to match on \d which signifies a numeric digit.
If both values are numeric, then we compare as integers using its already implemented compareTo function.
If only one of the values is numeric, then it is the smallest as we want the numbers to sort first.
If both are non-numeric, then we simply compare as strings. However, to show extended support for our third case of blended numbers and text, we go through same logic as above on the two string values: if both begin with numbers, then compare as integers with difference that equal numbers then need to sort by text; if one begins with a number, then it is smaller; if both are text, then just compare as strings.

Hopefully that is clear, but to test the results, you can use the following code:


java.util.List<String> sortedList = sort(inputList);

System.out.println("Numbers and text sorted!");
for (String s : sortedList) {
    System.out.println(s);
}

Hope that helps.

Happy coding!

References:

String to Value Algorithm

2008-10-12T21:15:00.001-04:00

Well continuing on the thought of conversion routines, here is an example of taking a string value in Transact-SQL on Microsoft SQL Server (may work on other platforms that support same functions) and creating a unique integer value.

Code listing:


DECLARE @text nvarchar(100), @value bigint
SET @text = 'Smith'
SET @value = 0
WHILE LEN(@text) > 0
BEGIN
SET @value = @value + ASCII(LEFT(@text, 1)) * square(LEN(@text))
SET @text = RIGHT(@text, LEN(@text) - 1)
END
SELECT @value

Very simple hopefully. The basis is using the length of the string, create a large integer value that is multiplied against the ascii value of each letter in the string. I was asked this as a question, so I have not explored all of the uses of a function like this; however, as a quick hash it seems to work fine.

Again, just posting for the learning of some of the tools available in SQL like the ASCII and SQUARE functions. Hopefully making the life of some other DBA or programmer a little easier.

References:

Converting Between String, ASCII, And Hex In .NET

2008-09-21T01:43:00.006-04:00

Welcome again to my twisted mind. This post is just a quick example on converting between string, ascii and hexadecimal values using the .NET, specifically visual basic .NET programming.


Dim strOrig As String = "Hello"
Dim decimals As New List(Of Decimal)()
Dim hexStrs As New List(Of String)()
Dim intCurrent As Double

For Each c As Char In strOrig.ToCharArray()
    intCurrent = Strings.Asc(c)
    decimals.Add(intCurrent)
    hexStrs.Add(Conversion.Hex(intCurrent))
Next

If hexStrs.Count > 0 Then
    Console.Write("Hex: " & hexStrs(0))

    For i As Integer = 1 To (hexStrs.Count - 1)
        Console.Write(" " & hexStrs(i))
    Next

    Console.WriteLine("")
End If

If decimals.Count > 0 Then
    Console.Write("Decimals: " & decimals(0))

    For i As Integer = 1 To (decimals.Count - 1)
        Console.Write(" " & decimals(i))
    Next

    Console.WriteLine("")
End If

Console.ReadLine()

As you can see above, .NET provides some nice conversion utilities for ascii value of a character and converting a decimal value to hexidecimal making the solution very straightforward as we just need to create a character array from string value and then convert each character to its ascii equivalent. Furthermore, we can quickly get the hexidecimal value for the character.

This is not rocket science by any stretch, but like posting things like this even as simple as it is so I for one don't forget it and to help another programmer get started on something more complex.

Anyway, for now that is it.

Happy programming.

References:

How to convert string to hexadecimal and vice versa (C#)
Conversion.Hex: http://msdn.microsoft.com/en-us/library/f480a85s.aspx
Strings.Asc: http://msdn.microsoft.com/en-us/library/78sb8546.aspx

Using SQL To Find Work Days In Date Range II

2008-09-19T15:58:00.002-04:00

In Using SQL To Find Work Days In Date Range, we created our fn_GetWorkDaysInRange user defined function in Microsoft SQL Server 2005. However, through our research into VB.NET and other simpler algorithms if you have been reading along, if we can do it better why not learn how.

So not to leave well enough alone, here is the code listing our fn_GetWorkDaysInRange revisited:


ALTER FUNCTION [dbo].[fn_GetWorkDaysInRange]
(
    @startDate datetime    -- first datetime in range
    , @endDate datetime -- last datetime in range (can be in past)
    , @includeStartDate bit -- flag to include start date as a work day
    , @firstWkndDay int -- first day of weekend (e.g. Day(datetime))
    , @lastWkndDay int -- last day of weekend (e.g. Day(datetime))
)
RETURNS int
AS
BEGIN
    -- variables used in processing
    DECLARE @workDays int, @sign int

    -- parse input and calculate direction of date range
    SET @firstWkndDay = Coalesce(@firstWkndDay, 0)
    SET @lastWkndDay = Coalesce(@lastWkndDay, @firstWkndDay)
    SET @startDate = Coalesce(@startDate, getdate())
    SET @endDate = Coalesce(@endDate, @startDate)
    SET @sign = Sign(DateDiff(dd, @startdate, @enddate))
   
    -- set initial value of work days result based on include start date value
    -- we use sign so that end dates older than start return negative work days
    -- for work days to come up as positive value no matter what, sign usage can be replace by 1
    SET @workDays = CASE @includeStartDate WHEN 0 THEN 0 ELSE
        CASE
            WHEN DatePart(dw, @startDate) IN (@firstWkndDay, @lastWkndDay)
            THEN 0
            ELSE Case @sign When 0 Then 1 Else @sign End
        END
    END
   
    -- while end date is not equal to start date add sign (-1/1) number of days
    -- and add to work days total if not a weekend
    WHILE DateDiff(dd, @startDate, @endDate) <> 0
        BEGIN
        SET @startDate = DateAdd(dd, @sign, @startDate)
        SET @workDays = @workDays +
            CASE
                WHEN DatePart(dw, @startDate) IN (@firstWkndDay, @lastWkndDay)
                THEN 0
                ELSE @sign
            END
        END

    -- return working days result to caller
    RETURN @workDays
END

The number of lines look very similar, but if you closely inspect this new version there are many changes/simplifications. The table variable is no longer needed, removing further dependence on new version(s) of SQL. The logic is reduced to while loop with two execution lines: increment/decrement date value; add to work days total if the new date value is a working day. The extra code is for readability of case logic.

To have this reflect how clean it really is, we can abstract out the logic for weekend which gets rid of the parameters and set statements for weekend day along with extensive case logic. We could then extend that separate function named something like fn_IsNotWeekDay to include logic to check date against our holiday table returning a bit flagging weekend/holiday. With CLR based user defined functions like we explored using VB.NET, this logic can be as complex as we are capable of coding in either SQL or .NET.

Until the next learning adventure.

Keep the code alive!

Related Articles/References:

Converting Between Calendar And Working Days In VB.NET

2008-09-17T21:12:00.008-04:00

With all the fun of creating functionality similar to this in SQL, made some optimizations within original VB.NET functions for converting between calendar and working/business days and got to thinking it would be good to start taking advantage of the fact that SQL 2005 supports CLR assemblies. Instead of having to do overly complex SQL functions/queries/procedures, use SQL CLR as it will make your life much easier and you will look cool doing it.

Furthermore, is too easy not to. To convert my utility class already containing public shared functions for dealing with going back and forth between working and calendar days, I simply added the two imports shown in the code listing below; Partial identifier to my class; <SqlFunction()> to each method I want exposed to CLR. Even if I went the next step to convet to SqlTypes defined in the first import, that is not too difficult.

Well let's dive into the code listing:


Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class MyFirstUDFn
    ''' <summary>
    ''' Get calendar days from today equivalent to working days specified.
    ''' </summary>
    ''' <param name="wrkDays"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <SqlFunction()> _
    Public Shared Function CalendarDaysFromWorkDays(ByVal wrkDays As Integer) As Integer

        'Using integer division, get the whole number of work weeks 
        Dim calDays As Integer = (wrkDays \ 5) * 7
        calDays += (wrkDays Mod 5) 'Add back the remaining work days

        'Adjust result to fall on working day as originally intended
        While IsWeekend(DateAdd(DateInterval.Day, calDays, Date.Now()))
            calDays += Math.Sign(wrkDays)
        End While

        Return calDays
    End Function

    'Create list of weekend days - for US this is Saturday (7) and Sunday (1)
    Private Shared ReadOnly WEEKEND_DAYS As New List(Of Integer)(New Integer() {7, 1})
    ''' <summary>
    ''' Returns flag indicating if date is a weekend day or not.
    ''' </summary>
    ''' <param name="calDate"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <SqlFunction()> _
    Public Shared Function IsWeekend(ByVal calDate As DateTime) As Boolean
        Return WEEKEND_DAYS.Contains(Weekday(calDate))
    End Function

    ''' <summary>
    ''' Get working days from today equivalent to calendar days specified.
    ''' </summary>
    ''' <param name="calDays"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <SqlFunction()> _
    Public Shared Function WorkDaysFromCalendarDays(ByVal calDays As Integer) As Integer
        Dim wrkDays As Integer = 0

        If calDays > 0 Then
            For i As Integer = 1 To calDays
                If Not IsWeekend(DateAdd(DateInterval.Day, i, Date.Now())) Then
                    wrkDays += 1
                End If
            Next
        ElseIf calDays < 0 Then
            For i As Integer = calDays To (-1)
                If Not IsWeekend(DateAdd(DateInterval.Day, i, Date.Now())) Then
                    wrkDays -= 1
                End If
            Next
        End If

        Return wrkDays
    End Function
End Class

As you have noticed by now, the function logic look incredibly similar to the code we explored in our SQL adventure. As stated, the SQL code originated from the CalendarDaysFromWorkDays function and its counterpart; however, the code for WorkDaysFromCalendarDays is more like our SQL solution that its original just simplier form since these functions are used for a set number of days from current date versus a date range; however, the principles are the same.

One of the additions, is extending this to work with different weekend days and the bonus IsWeekend function that came as a result of implementing that. We could easily extend this to query for holidays and have a function for IsHoliday as well. See the reference to Using conn As New SqlConnection("context connection=true") within the Microsoft Solutions Developer Network (MSDN) link below.

Anyway, where do we go from here. Well the simple step is to create a SQL Server Project! For those of you using Visual Studio 2005/2008 Express as I am on one machine, you are probably asking yourself "What SQL Server Project?". Exactly. This is available in the full version of Visual Studio, so here is what I did to develop this in VS Express.

Create a new Class Library project in VS and ensure that you go into the properties of the project and remove the root namespace (you can leave this as-is or change to another namespace to have structured classes, but just take note of that for later as using methods within assemblies are of the format AssemblyName.ClassName.MethodName so if you have a class in a long namespace it must be declared like AssemblyName.[NamespaceName.ClassName].MethodName). Add a class item to your project and code away. Above code functions as-is and is a good example of all that is needed to make a normal VB function to SqlFunction or sub to SqlStoredProcedure.

Once compiled to a DLL, the do the following on the SQL Server:


sp_configure N'clr enabled', 1
go
reconfigure
go
CREATE ASSEMBLY MyAssembly FROM 'C:\SQLCLRProject.dll' WITH PERMISSION_SET = SAFE

Once you have the assembly created and CLR enabled, you can issue this SQL to test:


-- creates the function
CREATE FUNCTION udf_IsWeekend(@calDate datetime) RETURNS bit
AS
EXTERNAL NAME SQLUserDefinedFunctions.MyFirstUDFn.IsWeekend
GO

-- example usage
SELECT dbo.udf_IsWeekend(DateAdd(dd, 2, getdate()))

Now you are using your original .NET code in SQL server and no need to pull your here out finding SQL equivalents to pre-existing .NET functions. Although, that is fun in and of itself, sometimes you just need to save time and practicing code resuse is always a good thing.

Hope that helps!

Related Articles/References:

Using SQL To Find Work Days In Date Range
Converting Working Days To Calendar Days Using SQL
Using CLR Integration in SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms345136.aspx
SQL Server DBA Guide to SQLCLR: http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm

Converting Working Days To Calendar Days Using SQL

2008-09-17T20:26:00.004-04:00

In the Using SQL To Find Work Days In Date Range post I mentioned that I original thought it would be an easy conversion from calendar days to work days as using simple mathematical formula in SQL, you can get calendar days or a calendar date from working days. It was my thought that the opposite of this formula would work; however, that is another post you can read at your leisure. In this post, I figured I would share my simply formula. It is so straight forward that hopefully it will save you from wasting time going for a more complex approach.

Here is the code listing:


DECLARE @workDays int, @calDays int
SET @workDays = 3

-- using integer division to convert work weeks to calendar weeks
-- AND modulus division to get partial week's days
SELECT @calDays = @workDays / 5 * 7 + @workDays % 5
-- just double check that end result is not on a weekend
WHILE DatePart(dw, DateAdd(dd, @calDays, getdate())) IN (7, 1)
SET @calDays = @calDays + 1
-- select away you have your calendar days and date if you would like
SELECT DateAdd(dd, @calDays, getdate())

As I said, pretty straight forward. Since SQL, at least in Microsoft SQL Server, will treat division of int datatypes as integer division, you will get the whole number amount of weeks represented by work days (i.e. 8/5 = 1). For other platforms, just use FLOOR function or its equivalent in your code (e.g. Floor(@workDays / 5) or the integer division operator to achieve the same results. Subsequently, modulus is taken of working days to get the partial week days involved which should be 0 - 4 that we can add to calendar days we calculated from multiplying the number of weeks by 7.

You can just run in query window as above, but to complement our function/procedure to get working days from a set of calendar dates you can put this in a stored procedure or user defined function the returns the calendar days themselves or the resulting date.

Keep evolving development!

Using SQL To Find Work Days In Date Range

2008-09-17T17:15:00.006-04:00

I had a question come up today for Microsoft SQL Server 2005 on how to calculate the number of working/business days between two dates with a requirement that the answer must function in countries where weekend can be variable two days or even one. My first thought was that is simple. Famous last words!

Eight hours of research later, this article covers the calculation of working days in the date range which appears to work quite nicely. Here is a code listing:


CREATE FUNCTION [dbo].[fn_GetWorkDaysInRange]
(
    @startDate datetime    -- first datetime in range
    , @endDate datetime -- last datetime in range (can be in past)
    , @includeStartDate bit -- flag to include start date as a work day
    , @firstWkndDay int -- first day of weekend (e.g. Day(datetime))
    , @lastWkndDay int -- last day of weekend (e.g. Day(datetime))
)
RETURNS int
AS
BEGIN
    -- variables used in processing
    DECLARE @workDays int, @sign int
    DECLARE @table table (calendarDate datetime, isWorkDay bit)

    -- parse input and calculate direction of date range
    SET @firstWkndDay = Coalesce(@firstWkndDay, 0)
    SET @lastWkndDay = Coalesce(@lastWkndDay, @firstWkndDay)
    SET @startDate = Coalesce(@startDate, getdate())
    SET @endDate = Coalesce(@endDate, @startDate)
    SET @sign = Sign(DateDiff(dd, @startdate, @enddate))
 
    -- insert our starting date
    INSERT INTO @table
    VALUES (@startDate, Case @includeStartDate When 0 Then 0 Else NULL End)

    -- add dates into table from start to end date
    IF @sign > 0 -- use sign of date difference to determine direction
        BEGIN 
        WHILE (SELECT MAX(calendarDate) FROM @table) < @enddate
        INSERT INTO @table
        SELECT DateAdd(dd, 1, MAX(calendarDate)), NULL FROM @table
        END
    ELSE
        BEGIN
        WHILE (SELECT MIN(calendarDate) FROM @table) > @enddate
        INSERT INTO @table SELECT DateAdd(dd, -1, MIN(calendarDate)), NULL FROM @table
        END

    -- update table to tag work days
    UPDATE @table
        SET isWorkDay = CASE
            WHEN DatePart(dw, calendarDate) IN (@firstWkndDay, @lastWkndDay)
            THEN 0
            ELSE 1
        END
    WHERE isWorkDay IS NULL

    -- select the working days from our table into return variable
    SELECT @workDays = COUNT(calendarDate) FROM @table WHERE isWorkDay = 1
 
    RETURN (@workDays * @sign)
END

As stated above, this function will remove weekend days between the starting and ending range and thus return number of working days. Once we have that result, we can use a query to retrieve our holidays (or alternatively modify the above to take in a country code and lookup the weekend days and holidays returning the net business days).

Here is an example of this function's usage


-- create holidays table for testing data
CREATE TABLE [dbo].[Holidays](
    [day] [datetime],
    [holiday] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [country] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS
) ON [PRIMARY]

INSERT INTO Holidays
SELECT '12/25/2008', 'Christmas', 'USA'
UNION SELECT '12/26/2008', 'Day After Christmas', 'USA'
-- end creation of table for testing data

DECLARE @workDays int, @holidays int
DECLARE @startDate datetime, @endDate datetime

SET @startDate = '12/15/2008'
SET @endDate = '12/29/2008'

SELECT @workDays = dbo.fn_GetWorkDaysInRange(@startDate, @endDate, 0, 7, 1)

SELECT @holiDays = COUNT([day])
FROM [Holidays]
WHERE [country] = 'USA' AND DatePart(dw, [day]) NOT IN (7, 1)
AND [day] BETWEEN @startDate AND @endDate

PRINT (@workDays)
PRINT (@holidays)
PRINT (@workDays - @holidays)

Results come out 10, 2, and 8 for each of the three print statements, respectively. Exactly what we wanted! It is a joy when it all works.

Hopefully this post will save you as long journey, but leave enough uncharted territory to have a little fun with in customizing to your own environment. I have even played with this myself to replace some logic I was using for determining shop working days, so enjoy. For those of you not on Microsoft SQL Server 2005, please keep in mind that other versions of Microsoft SQL Server that support user defined functions should work. Consequently, for other platforms or versions, the structure of this code can probably be manipulated to work in a stored procedure and/or using temporary table instead of a table variable and likewise for other features used not present in your system. The principles should be the same.

Hope this helps and happy coding!

DateSerial In Microsoft SQL Server 2005

2008-09-15T23:13:00.005-04:00

Well, in Group By Time: TimeSerial Makes A Return To Microsoft SQL 2005 we explored bringing TimeSerial functionality to SQL Server, but of course we can't stop there as the DateSerial function is pretty useful too.

There are probably a number of different methods to achieve this, but here is what I came up with.

Code listing:


CREATE FUNCTION dbo.DateSerial(@year int, @month int, @day bigint)
RETURNS datetime
AS
BEGIN
    DECLARE @date datetime

    -- catch invalid year entries and default appropriately
    SET @year = CASE
        WHEN @year < 1900 Then 1900
        When @year > 9999 Then year(getdate())
        Else @year End
    
    -- convert date by adding together like yyyymmdd
    SET @date = Cast(Cast(@year * 10000 + 101 As varchar) As datetime)
    -- Alternative method of parsing year into base date
    -- SET @date = Cast('1/1/' + Cast(@year As varchar) As datetime)

    -- Add to date the proper months subtracting 1 since we used 1 as start instead of zero.    
    SET @date = DateAdd(mm, @month - 1, @date)
    -- Add to date the proper days subtracting 1 since we used 1 as start instead of zero.    
    SET @date = DateAdd(dd, @day - 1, @date)

    RETURN @date
END

First line is to avoid errors in incorrect starting year value, but can be adjusted according to your own needs. The months and days are added in through simple DateAdd which allows for positive/negative numbers in addition to not being bound by 12 or 31 respectively making this like our TimeSerial function in that it can be used to simply convert a year, month, and day into date or to do some date math on the fly.

Usage:


SELECT dbo.DateSerial(YEAR(GETDATE()), MONTH(GETDATE()), 1 - 35) AS dateSerialized

This will return the date 35 days prior to the first day of the current month in the current year. Moreover, since this solution takes advantage of user defined function only, implementing in SQL 2000 should not be an issue.

So there you have it, DateSerial in SQL Server.

Happy coding!

References:

Group By Time: TimeSerial Makes A Return To Microsoft SQL 2005
DateSerial in Access: http://office.microsoft.com/en-us/access/HA012288131033.aspx

Group By Time: TimeSerial Makes A Return To Microsoft SQL 2005

2008-09-07T13:31:00.008-04:00

Last time we looked at grouping by time for our customer calls statistics by the half an hour. We ended with this code based on SQL 2005 common table expression syntax.


WITH TableByTime As ( 
    SELECT Cast(DateName(hh, CallDateTime) As Int) As [Sort], 
        Right('0' + Case When DateName(hh, CallDateTime) = 0 Then '12' 
        When DateName(hh, CallDateTime) <= 12 Then DateName(hh, CallDateTime) 
        Else DateName(hh, DateAdd(hh, -12, CallDateTime)) End,  2)  + ':' + 
        Case When DateName(n, CallDateTime) >= 30 Then '30' Else '00' End +
        Case When DateName(hh, CallDateTime) < 12 Then 'AM' Else 'PM' End As [Hour], 
        Customer
    FROM CustomerCalls (NoLock)
)
SELECT Hour,  Count(*)
FROM TableByTime
GROUP BY Sort, Hour
ORDER BY Sort, Hour

We can be satisfied with this, but why waste a perfectly good opportunity to keep learning. In all seriousness, breakdowns by date/time are statistics I often have to get, so if you are anything like me it would be good to explore making this more reusable and streamlined. Since what we have above is very clean compared to the starting point, what we have left to do is create a function to emulate the TimeSerial function from Microsoft Access that does what our case statements are doing and more.

In summary, TimeSerial, takes in hour in military notation (i.e. 14 for 2PM), minutes, and seconds and translates to appropriate time in format hh:mm:ss with AM/PM indicator. In addition to straight time display, it could do calculations for you based on varying inputs and use of negatives. If you want more information on how TimeSerial functions, see reference for function in Microsoft Access below.

So diving in, we can write a function like this that adds TimeSerial to Microsoft SQL Server using a user defined function.


CREATE FUNCTION dbo.TimeSerial (@hrs int, @min int, @sec bigint)

RETURNS nvarchar(10)

AS

    BEGIN

    DECLARE @result nvarchar(10), @total bigint, @AMorPM nvarchar(2)

    DECLARE @hours int, @minutes int, @seconds int



    -- convert everything to seconds handling null params with isnull or coalesce

    SET @total = IsNull(@sec,0) + IsNull(@min,0) * 60 + IsNull(@hrs,0) * 3600

    SET @total = 86400 + @total % 86400 -- handle negative time relative to midnight



    -- calculate the hour portion

    SET @hours = 0

    IF (@total >= 3600)

        BEGIN

        SET @hours = floor(@total/3600) % 24

        SET @total = @total % 3600

        END



    -- set am/pm based on hours in HH format

    SET @AMorPM = 'PM'

    IF @hours < 12

        BEGIN

        SET @AMorPM = 'AM'

        END

  

    -- adjust hours to non-military time

    IF @hours > 12 OR @hours = 0

        BEGIN

        SET @hours = abs(@hours - 12)

        END



    -- calculate the minutes and seconds portion

    SET @minutes = 0

    IF (@total >= 60)

        BEGIN

        SET @minutes = floor(@total/60)

        SET @total = @total % 60

        END



    -- set seconds to remainder

    SET @seconds = @total



    SET @result = Cast(@hours As nvarchar(2)) + ':' + RIGHT('0'+Cast(@minutes As nvarchar(2)), 2)

    SET @result = @result + ':' + RIGHT('0'+Cast(@seconds As nvarchar(2)),2) + @AMorPM



    RETURN @result

    END

As you will see in the code above, which hopefully speaks for itself as to what it is doing, we can do a little more than just format our time so we abstract out the need for extensive case when logic and give ourselves a handy utility function for our SQL toolkit.

Putting it in place with our original query, we can immediately simplify our syntax to this.


WITH TableByTime As (

    SELECT Cast(DateName(hh, CallDateTime) As Int) As [Hr],

           FLOOR(Cast(DateName(n, CallDateTime) As Int)/30) * 30 As [Mi],

           Customer

    FROM CustomerCalls (NoLock)

)

SELECT    dbo.TimeSerial(Hr, Mi, 0) As [Hour],

        Count(*)

FROM TableByTime

GROUP BY Hr, Mi

ORDER BY Hr, Mi

Aside from the TimeSerial function addition to the code, you will notice a better algorithm to get to group time in 30 minute buckets using floor which gets the lowest integer count of 30 in the number of minutes currently in our time. Since Microsoft SQL will typically do integer division on two numbers that are int datatypes this is probably unnecessary, but I like to be very deliberate in code for nothing else than I will remember what I was thinking when I look at it a year later. The premise here is that we will only ever get 0 or 1 from the division and then multiplying by 30 will give us the correct bucket.

Well we are close to being golden, but we still have a little fluff just to extract the hour and minute portions of time as we have to get as string then cast, so we could write functions for those as well; however, with Microsoft SQL Server, you can utilize ODBC canonical functions for { fn HOUR() } and { fn MINUTE() }.


 dbo.TimeSerial({ fn HOUR(GETDATE()) }, FLOOR({ fn MINUTE(GETDATE()) } / 30) * 30, 0) AS HourBucket

As you see above, in combination with our user defined function for TimeSerial the ODBC canonical functions for HOUR and MINUTE make it very streamlined to get our hour bucket in one statement. For sorting purposes, it will probably still be a good idea to use the { fn HOUR() } in Hr column and { fn MINUTE() } in the Mi column in previous code using the common table expression we composed, then just use TimeSerial with those column values.

In conclusion, combining the flexibility of user defined functions and some of the tools in our SQL toolkit with Microsoft SQL Server, we can keep the user community happy with snappy time based reports. The common language runtime (CLR), which we didn't discuss here in detail, is another great means of adding user defined functionality. Anyway, happy coding.

References:

Original Post: "Group By Time: The Microsoft SQL Server 2005 Way"
Access TimeSerial: http://office.microsoft.com/en-us/access/HA012289251033.aspx

Range Searching Using Java

2008-09-06T03:48:00.003-04:00

Welcome! Intrigued by my findings in my last post regarding Range Searching Using Visual Basic .NET, I decided to try to reproduce the FindAll methodology as well as my binary search based on range for grins and again was surprised by my findings. Binary search worked the fastest/consistently within Java code where all three methods were faster which was not too much of a surprise other than the ported FindAll since it and the concept of Predicate match was not native (at least not to my knowledge) at the time of writing this post.

Background summary for those that haven't read the original article in VB, this started as research based on a question of finding the dates within a list that match a selected date range. The intent was to go beyond the simple iteration and match method and find a faster means through binary search only to find the VB FindAll and Predicate implementation eluded to above and slower performance from binary range functions created.

With that said, onward and upward!

The journey in Java begins with replicating our utility class Ranges that handles the simple match method utilized by looping through array as well as our binary search implementation based on a start and end of a range.

The code listing is below:


package com.blogspot.crossedlogic.rangesearching;





public class Ranges {

private static final int NOT_FOUND = -1;



/**

 * Binary search through a sorted array to determine first index 

 * greater or equal to start of range. 

 * 

 * @param array

 * @param rangePoint

 * @return integer index or (-1) when not found

 */

public static <T extends Comparable<T>> int binaryStartOfRangeSearch(T[] array, T rangePoint) {

int low = 0;

int high = array.length - 1;

int mid;

int comparedToHigh = rangePoint.compareTo(array[high]);



if (comparedToHigh == 0) { return high; }



if (comparedToHigh < 0) {

if (rangePoint.compareTo(array[low]) <= 0) { return low; }



while (low != high) {

mid = (int)Math.floor((low + high) / 2);



if (rangePoint.compareTo(array[mid]) <= 0) {

if (rangePoint.compareTo(array[mid - 1]) <= 0) {

high = --mid;

} else {

return mid;

}

} else {

if (rangePoint.compareTo(array[mid + 1]) <= 0) {

return ++mid;

} else {

low = ++mid;

}

}

}

}



return NOT_FOUND;

}



/**

 * Binary search through a sorted array to determine last index 

 * lesser or equal to end of range. 

 * 

 * @param array

 * @param rangePoint

 * @return integer index or (-1) when not found

 */

public static <T extends Comparable<T>> int binaryEndOfRangeSearch(T[] array, T rangePoint) {

int low = 0;

int high = array.length - 1;

int mid;

int comparedToLow = rangePoint.compareTo(array[low]);



if (comparedToLow == 0) { return low; }



if (comparedToLow > 0) {

if (rangePoint.compareTo(array[high]) >= 0) { return high; }



while (low != high) {

mid = (int)Math.ceil((low + high) / 2);



if (rangePoint.compareTo(array[mid]) >= 0) {

if (rangePoint.compareTo(array[mid + 1]) >= 0) {

low = ++mid;

} else {

return mid;

}

} else {

if (rangePoint.compareTo(array[mid - 1]) >= 0) {

return --mid;

} else {

high = --mid;

}

}

}

}



return NOT_FOUND;

}



/**

 * Check if an object falls within a specified range.

 * 

 * @param c

 * @param rangeStart

 * @param rangeEnd

 * @return

 */

public static <T extends Comparable<T>> boolean matchRange(T c, T rangeStart, T rangeEnd) {

if (c.compareTo(rangeStart) >= 0 && c.compareTo(rangeEnd) < 1) { return true; }



return false;

}

}

As stated in my last post, the use of generics expanded my research beyond Date types and provided a framework for searching different types as long as their range principals were the same as implemented and they implemented the Comparable interface. For more information on Generics, please see the reference(s) below; however, the matchRange function reads like this for example: taking in any type T denoted by the parameter c which implements the Comparable interface (T extends Comparable) and two other instances of type T representing a start and end of a range, return a true or false flag indicating existence within range.

Comparable interface is used the same here as the compareTo function has the same usage and return values (at least in typical implementations of primitive/standard types): -1 when first object is smaller than the one being compared to; 0, the two equal; 1, first is larger.

As you will see, the usage of these functions are virtually the same from VB implementation:


// simple loop and match method

for (String s : arrayOfObjects) {

if (Ranges.matchRange(s, RANGE_START, RANGE_END)) {

lst.add(s);

}

}



// binary range search method

Arrays.sort(arrayOfObjects);



int idxStart = Ranges.binaryEndOfRangeSearch(

arrayOfObjects, RANGE_START);

int idxEnd = Ranges.binaryEndOfRangeSearch(

arrayOfObjects, RANGE_END);



if (idxStart >= 0 && idxEnd >= 0) {

for (int i = idxStart; i <= idxEnd; i++) {

lst.add(arrayOfObjects[i]);

}

}

What will be a little different is the FindAll implementation as this was done using some non-native interfaces similar to methodology discussed in the reference section from faqs{dot}com. To get the same functionality, an interface for Predicate had to be added. In addition, a Predicates class was created that implemented a FindAll equivalent that requires an implemented Predicate to return a matching List of objects. Then using Java's anonymous class syntax, we implement Predicate's required match function as we would with delegate.


/**

 * Predicate interface used to emulate .NET <code>System.Predicate(Of T)</code>.

 */

package com.blogspot.crossedlogic.rangesearching;



public interface Predicate<T> {



/**

 * @param object to test against predicate criteria.

 * @return boolean flag indicating a match (true) or no match (false).

 */

public boolean isMatch(T obj);

}



/**

 * Predicates class containing static implementation of FindAll.

 */

package com.blogspot.crossedlogic.rangesearching;



import java.util.ArrayList;

import java.util.List;





public class Predicates {



/**

 * Replicate .NET findAll and <code>Predicate</code>.

 * 

 * @param <T>

 * @param array

 * @param match implementation of the Predicate interface.

 * @return

 * @see com.blogspot.crossedlogic.rangesearching.Predicate

 */

public static <T> List<T> findAll(T[] array, Predicate<T> match) {

List<T> lst = new ArrayList<T>();



for (T obj : array) {

if (match.isMatch(obj)) { lst.add(obj); }

}



return lst;

}

}



// Example usage of FindAll for range searching.

lst.addAll(Predicates.findAll(arrayOfObjects,

new Predicate<String>() {



@Override

public boolean isMatch(String s) {

return Ranges.matchRange(s, RANGE_START, RANGE_END);

}

}));

Not one line, but it is just one code statement as the solution is to utilize the range add capabilities of the List interface couples with the List response of the findAll function using a Predicate which again we implement as a simple call to our static matchRange function coupled with constants for start and end of range.

Well, I have kept you waiting long enough, here is how the results stack up:


--------------------------------------------------

Start processing by going through each item

End processing: 37ms - 9311

--------------------------------------------------

Start processing by using FindAll.

End processing: 36ms - 9311

--------------------------------------------------

Start processing by binary range search.

End processing: 14ms - 9311

--------------------------------------------------

Start processing by going through each item

End processing: 1ms - 9311

--------------------------------------------------

Start processing by using FindAll.

End processing: 3ms - 9311

--------------------------------------------------

Start processing by binary range search.

End processing: 2ms - 9311

--------------------------------------------------

Start processing by going through each item

End processing: 1ms - 9312

--------------------------------------------------

Start processing by using FindAll.

End processing: 3ms - 9312

--------------------------------------------------

Start processing by binary range search.

End processing: 2ms - 9312

--------------------------------------------------

Start processing by going through each item

End processing: 1ms - 9313

--------------------------------------------------

Start processing by using FindAll.

End processing: 3ms - 9313

--------------------------------------------------

Start processing by binary range search.

End processing: 3ms - 9313

--------------------------------------------------

Start processing by going through each item

End processing: 2ms - 9314

--------------------------------------------------

Start processing by using FindAll.

End processing: 2ms - 9314

--------------------------------------------------

Start processing by binary range search.

End processing: 3ms - 9314

--------------------------------------------------

Aside from the first iteration where the static objects are created for the first time, you will see 1-3 millisecond (ms) response time from each of the three methodologies giving us two other tools in the toolkit for searching arrays/lists when dealing with large number of elements and ranges.

I am glad I took this journey on the VB.NET side as it inspired me to want to explore this in Java and I am very pleased with the results. Hopefully, it will be useful for you in your travels as well and thanks for reading. We covered a lot of different ground in Generics, Predicates, sorting Arrays, binary search algorithm, and so on so please use links for more information on things I didn't go into details on including previous post as I may have glossed over something I discussed further there.

Thanks and remember to keep learning and/or keep smiling; you never want to end up a sad fool!

Source code: Download com.blogspot.crossedlogic.rangesearching.zip

References:

Range Searching Using Visual Basic .NET

Generics in Java: http://java.sun.com/j2se/1.5.0/docs/guide/language/generics.html

Predicate in Java: http://www.faqs.org/docs/javap/c12/ex-12-4-answer.html

Java does have a native Predicate interface under the javax.sql.rowset package, but, since not same as .NET predicate in our context, custom Predicate was used above.

Range Searching Using Visual Basic .NET

2008-09-05T03:40:00.002-04:00

The other day, I was asked a question about comparing dates in an array to a date range. The basic answer is to iterate through the array and compare each date to the beginning and ending of the range; however, with some curiosity exacerbated by another question planting the seed of binary searches in my head, I was determined to write a binary search algorithm for determining a value in a range. Along the way, I was enlightened/awoken to the concept of predicates in VB.NET and the System.Array.FindAll method. So this post will try to provide an overview of how to perform some range searching in .NET as I took research a step further and abstracted functionality to be more generic than with Date types.

With that said, here is the basic comparison to any range of objects that implement the IComparable interface.


Function MatchRange(Of T As IComparable)(ByVal c As T, ByVal rangeStart As T, ByVal rangeEnd As T) As Boolean
    If c.CompareTo(rangeStart) >= 0 And c.CompareTo(rangeEnd) < 1 Then
        Return True
    End If

    Return False
End Function

As long as you are comfortable with generics, the above is pretty straight forward (for more details on generics, see MSDN link in references) as we can now pass in any class that implements IComparable and take advantage of the public interface method CompareTo(Object) which will return one the following values: -1 when first object is smaller than the one being compared to; 0, the two equal; 1, first is larger. An easy demonstration of how this is used is MatchRange(5, 1, 10) will return true. So for back to original scenario of needing to determine all the values of a list that fall within a range, we get code like the following:


Dim lst As New List(Of String)()

For Each s As String In arrayOfObjects
    If Ranges.MatchRange(s, RANGE_START, RANGE_END) Then
        lst.Add(s)
    End If
Next

Where Ranges is the name of utility class I placed my Public Shared Function MatchRange and RANGE_START and RANGE_END are string constants representing the beginning and ending value of the range we are searching within.

Speaking of the Ranges class, here is its full listing to dive right into the next step which was to implement a better range search using binary search algorithm:


Option Explicit On
Option Strict On

Namespace CrossedLogic.BlogSport.RangeSearching
    Public Class Ranges

        ''' <summary>
        ''' Binary search through a sorted array to determine first index 
        ''' greater or equal to start of range.
        ''' </summary>
        ''' <typeparam name="T"></typeparam>
        ''' <param name="array"></param>
        ''' <param name="rangePoint"></param>
        ''' <returns>integer index or (-1) when not found</returns>
        ''' <remarks></remarks>
        Public Shared Function BinaryStartOfRangeSearch(Of T As IComparable)(ByVal array As T(), ByVal rangePoint As T) As Integer
            Dim low As Integer = 0
            Dim high As Integer = array.Length - 1
            Dim mid As Integer
            Dim startComparedToHigh As Integer = rangePoint.CompareTo(array(high))

            If startComparedToHigh = 0 Then
                Return high
            End If

            If startComparedToHigh < 0 Then
                If rangePoint.CompareTo(array(low)) <= 0 Then
                    Return low
                End If

                While low <> high
                    mid = CInt(Math.Floor((low + high) / 2))

                    If rangePoint.CompareTo(array(mid)) <= 0 Then
                        If rangePoint.CompareTo(array(mid - 1)) <= 0 Then
                            high = mid - 1
                        Else
                            Return mid
                        End If
                    Else
                        If rangePoint.CompareTo(array(mid + 1)) <= 0 Then
                            Return mid + 1
                        Else
                            low = mid + 1
                        End If
                    End If
                End While
            End If

            Return -1
        End Function

        ''' <summary>
        ''' Binary search through a sorted array to determine last index 
        ''' lesser or equal to end of range.
        ''' </summary>
        ''' <typeparam name="T"></typeparam>
        ''' <param name="array"></param>
        ''' <param name="rangePoint"></param>
        ''' <returns>integer index or (-1) when not found</returns>
        ''' <remarks></remarks>
        Public Shared Function BinaryEndOfRangeSearch(Of T As IComparable)(ByVal array As T(), ByVal rangePoint As T) As Integer
            Dim low As Integer = 0
            Dim high As Integer = array.Length - 1
            Dim mid As Integer
            Dim endComparedToLow As Integer = rangePoint.CompareTo(array(low))

            If endComparedToLow = 0 Then
                Return low
            End If

            If endComparedToLow > 0 Then
                If rangePoint.CompareTo(array(high)) >= 0 Then
                    Return high
                End If

                While low <> high
                    mid = CInt(Math.Ceiling((low + high) / 2))

                    If rangePoint.CompareTo(array(mid)) >= 0 Then
                        If rangePoint.CompareTo(array(mid + 1)) >= 0 Then
                            low = mid + 1
                        Else
                            Return mid
                        End If
                    Else
                        If rangePoint.CompareTo(array(mid - 1)) >= 0 Then
                            Return mid - 1
                        Else
                            high = mid - 1
                        End If
                    End If
                End While
            End If

            Return -1
        End Function

        ''' <summary>
        ''' Check if an object falls within a specified range.
        ''' </summary>
        ''' <typeparam name="T"></typeparam>
        ''' <param name="c"></param>
        ''' <param name="rangeStart"></param>
        ''' <param name="rangeEnd"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function MatchRange(Of T As IComparable)(ByVal c As T, ByVal rangeStart As T, ByVal rangeEnd As T) As Boolean
            If c.CompareTo(rangeStart) >= 0 And c.CompareTo(rangeEnd) < 1 Then
                Return True
            End If

            Return False
        End Function
    End Class
End Namespace

And its usage:


Dim lst As New List(Of String)()

System.Array.Sort(arrayOfObjects)

Dim idxStart As Integer = Ranges.BinaryStartOfRangeSearch(arrayOfObjects, RANGE_START)
Dim idxEnd As Integer = Ranges.BinaryEndOfRangeSearch(arrayOfObjects, RANGE_END)

If idxStart >= 0 Then
    If idxEnd < idxStart Then idxEnd = arrayOfObjects.Length - 1
    For i As Integer = idxStart To idxEnd
        lst.Add(arrayOfObjects(i))
    Next
End If

First difference, you should see is that like regular binary search, the array must be sorted. Once we have a sorted array, we can use our binary search functions specific to beginning and ending of the range to get the respective index. Using the indices, we can add just those objects to the list we created to store results. Well a bit more code, but well worth it for the end game of faster searches of large object arrays like the example 13,149 string array used in the research for this post. Consequently, though, to my surprise, this code did not run faster mostly because of the requirement for sorting. With the original date question, even the initial solution above was sorted using System.Array.Sort; however, since that solution will yield the same list just unordered, for the sake of performance measurement I left array unsorted and then sorted as part of the binary search method.

Here are some of the results to illustrate difference:


---------------------------------------------------
Start processing by going through each item
End processing: 110ms - 9311
---------------------------------------------------
Start processing by using FindAll.
End processing: 13ms - 9311
---------------------------------------------------
Start processing by binary range search.
End processing: 59ms - 9311
---------------------------------------------------
Start processing by going through each item
End processing: 6ms - 9311
---------------------------------------------------
Start processing by using FindAll.
End processing: 14ms - 9311
---------------------------------------------------
Start processing by binary range search.
End processing: 47ms - 9311
---------------------------------------------------
Start processing by going through each item
End processing: 10ms - 9312
---------------------------------------------------
Start processing by using FindAll.
End processing: 8ms - 9312
---------------------------------------------------
Start processing by binary range search.
End processing: 50ms - 9312
---------------------------------------------------
Start processing by going through each item
End processing: 6ms - 9313
---------------------------------------------------
Start processing by using FindAll.
End processing: 8ms - 9313
---------------------------------------------------
Start processing by binary range search.
End processing: 51ms - 9313
---------------------------------------------------
Start processing by going through each item
End processing: 6ms - 9314
---------------------------------------------------
Start processing by using FindAll.
End processing: 8ms - 9314
---------------------------------------------------
Start processing by binary range search.
End processing: 55ms - 9314
---------------------------------------------------

As you will see above, the find by simple iteration of the array usually came in lower than the binary search method, although they both fluctuate, especially the simple loop method as it at times takes 100+ milliseconds (ms) while at others only 10 ms or below. Subsequently, you will notice that the FindAll although a few ms slower at times is very consistent and I found gives similar results regardless of the type of object in array whereas the other two vary as previously stated. So what is this FindAll method and how do I use it?

Well the FindAll method is a public shared function within System.Array. It's usage is not only the most consistent, but as you will see the most streamlined aside from delegate function you have to create. Here is a listing assuming availability of previously posted code:


lst.AddRange(System.Array.FindAll(arrayOfObjects, New Predicate(Of String)(AddressOf MatchRange)))

'Delegate implementation for Predicate needed in System.Array.FindAll
Function MatchRange(ByVal n As String) As Boolean
    Return Ranges.MatchRange(n, RANGE_START, RANGE_END)
End Function

Yes! One line is all, you are not reading that incorrectly. Taking advantage of the List's AddRange capabilities that will add all elements of an array to list and the FindAll implementation that will return an array of elements from the array argument that match our range through use of the Predicate(Of T) delegate implemented here by a local MatchRange function that takes type T which is String in this case and does logic to respond true or false for a match. Since I have already gone through the work of coding a functioning match range routine, our Predicate simply becomes a call to our shared MatchRange function.

The one caveat to note with this solution is that since it only takes in object to be matched, the criteria and variables needed have to exist globally or just within this function which you can observe by my having to use RANGE_START and RANGE_END constants in my match. Moreover, if you are already using List(Of T) versus and array, you can simply utilize FindAll function of List to get a subset List(Of T). See references for more on the List.FindAll method.

Well hopefully you have enjoyed this journey.

In summary, you can most definitely use binary search method for primitives and possibly some types, but for Dates and Strings the System.Array.FindAll/List.FindAll are good utilities for large collections and the simple but effective for each loop and comparison.

Happy coding!

Source code: Download CrossedLogic.BlogSpot.RangeSearching.zip

References:

Generics in .NET: http://msdn.microsoft.com/en-us/library/aa479866.aspx
Predicate in .NET: http://msdn.microsoft.com/en-us/library/bfcke1bz.aspx
List.FindAll: http://msdn.microsoft.com/en-us/library/fh1w7y8z(VS.80).aspx

Group By Time: The Microsoft SQL Server 2005 Way

2008-09-04T17:07:00.010-04:00

Statistics, we all seem to need them, but it is not always easy getting them exactly the way you would like. This article is going to explore the simple task of grouping data by hour spiced up a bit by displaying results in standard time format with AM/PM instead of military time and groupings every 30 minutes.
Say you have the following data.

Table: CustomerCalls

Desired Report

Starting with the basics, we do the following:


SELECT DateName(hh, CallDateTime) As [Hour], Count(*) As [Calls]

FROM CustomerCalls

GROUP BY DateName(hh, CallDateTime)

Using the DateName function, we grab the hour portion of datetime column in our table and group rows and count. All is well and we get results like this by military time hour of the day:

Great! But now we need to put in a little bit of fancy formatting to change 0100 to 01:00AM for the non-combat oriented among us.


SELECT     Right('0' + Case When DateName(hh, CallDateTime) = 0 Then '12'
    When DateName(hh, CallDateTime) <= 12 Then DateName(hh, CallDateTime)
    Else DateName(hh, DateAdd(hh, -12, CallDateTime)) End, 2) + ':' +
    Case When DateName(n, CallDateTime) >= 30 Then '30' Else '00' End +
    Case When DateName(hh, CallDateTime) < 12 Then 'AM' Else 'PM' End As [Hour],
    Count(*) As [Calls]
FROM CustomerCalls(NoLock)
GROUP BY Right('0' + Case When DateName(hh, CallDateTime) = 0 Then '12'
    When DateName(hh, CallDateTime) <= 12 Then DateName(hh, CallDateTime)
    Else DateName(hh, DateAdd(hh, -12, CallDateTime)) End, 2) + ':' +
    Case When DateName(n, CallDateTime) >= 30 Then '30' Else '00' End +
    Case When DateName(hh, CallDateTime) < 12 Then 'AM' Else 'PM' End

Life is all well and good again. Well maybe. That code is crazy as we must have the entire case logic in the group by statement to not violate SQL syntax rules when grouping, so what to do next. Well good news for us there is the WITH statement in SQL Server 2005 for creating Common Table Expressions (CTE). So diving into CTE, the code all together including some added functionality to sort in correct time of day order is below:


WITH TableByTime As (
    SELECT Cast(DateName(hh, CallDateTime) As Int) As [Sort],
    Right('0' + Case When DateName(hh, CallDateTime) = 0 Then '12'
    When DateName(hh, CallDateTime) <= 12 Then DateName(hh, CallDateTime)
    Else DateName(hh, DateAdd(hh, -12, CallDateTime)) End,  2)  + ':' +
    Case When DateName(n, CallDateTime) >= 30 Then '30' Else '00' End +
    Case When DateName(hh, CallDateTime) < 12 Then 'AM' Else 'PM' End As [Hour],
    Customer
FROM CustomerCalls (NoLock)
)
SELECT Hour,  Count(*)
FROM TableByTime
GROUP BY Sort, Hour
ORDER BY Sort, Hour

Wonderful! And code is not too unmanageable in my humble opinion. Probably can break it down some more, but as you see the benefits of CTE has already improved readability that I stopped here for implementation.

Let’s take a deeper look at the code since we glossed over this earlier. First column takes the normal military hour ensuring it is functioning as an integer for use as our sorting mechanism later since time in military notation goes from 0 – 23 which is nicely ordered for us already. Second column, [Hour], is our case when breaking down datetime column to standard time of day: 12:00AM – 11:30PM as we also group the times within the first and last 30 minutes of each hour. The third column (could be columns) is data we want from the table – used Customer which we didn’t really need for our aggregate which is only a count; however, if we were doing sums or other statistical analysis on numerical data in the CustomerCalls table we could add those columns here.

Now that we have our CTE, you can see the subsequent T-SQL statement where we actually implement the GROUP BY statement is drastically simplified as we have [Hour] column to call directly instead of the entire case logic. We ORDER BY our sort column and then by our hour and presto we have our results.

In conclusion, using Common Table Expressions in SQL Server 2005 adds a great deal of power and flexibility beyond my simple case here while streamlining SQL statements to be easier to maintain/read, so read up and enjoy the cool new features of Microsoft SQL Server.

References:

WITH common_table_expression: http://msdn.microsoft.com/en-us/library/ms175972(SQL.90).aspx

DateName function: http://msdn.microsoft.com/en-us/library/ms174395.aspx

Converting Delimited String To Separate Values In SQL

2008-09-01T21:40:00.002-04:00

Ever need to split a comma delimited string for use in SQL? If so, this post will go through a simple Microsoft SQL Server 2005 example of converting delimited text to a SQL table variable.


CREATE FUNCTION [dbo].[split] (@csv nvarchar(max), @delim varchar(1))
RETURNS @entries TABLE (entry nvarchar(100))
AS
BEGIN
    DECLARE @commaindex int
    SELECT @commaindex = CHARINDEX(@delim, @csv)

    IF @commaindex > 0
        BEGIN
            INSERT INTO @entries
            -- insert left side
            SELECT LTrim(RTrim(LEFT(@csv, @commaindex-1)))
            -- pass right side recursively
            UNION ALL
            SELECT entry
            FROM dbo.split(RIGHT(@csv, LEN(@csv) - @commaindex), @delim)   
        END
    ELSE
        BEGIN
            INSERT INTO @entries
            SELECT LTrim(RTrim(@csv))
        END

    RETURN
END

The above can be manipulated according to individual need as each entry may need to be more than a 100 character string or you may have a delimiter greater than one character. The key to this solution is the simplicity achieved through use of recursion. Instead of trying to parse through each position that delimiter could be in string from beginning to end, simply keep taking left portion off and passing right portion for further processing.

To take a string and get distinct values, the UNION ALL statement can be changed to UNION or simply select distinct in usage of function which is pretty straight forward as well.


SELECT DISTINCT entry FROM dbo.split('my;delimited;text;to;be;parsed', ';')

As you can see, the string doesn't have to be comma delimited either.

Of course this solution is very basic, but hopefully demonstrates some of the power of using user defined functions and recursion as a part of your SQL toolkit. Well keep learning and keep smiling.

Cross-Site Exploitation Presentation

2008-01-28T22:13:00.001-05:00

August 2007 presentation at the OISF meeting in Dayton OH.

{content/link moved to newly created presentations segment of the side bar.}

Free ColdFusion Server

2007-09-14T22:43:00.000-04:00

Since I have let the cat out of the bag that I am a bit of a Java enthusiast, I may as well admit to being a fan of ColdFusion which integrates with Java real well. You can use object-oriented constructs in ColdFusion as well as use Java to extend functionality or provide framework that can be executed easily from the web interface.

Anyway, I just saw that New Atlanta released a new version of their ColdFusion server which is free. There are some restrictions on tag support that can be overcome by buying their full blown version with J2EE capabilities or Adobe's ColdFusion server.

Either choice, one good point is that ColdFusion and Java integrate real well to allow designers use to the web interface niceties of ColdFusion Markup Language (CFML) while leveraging business logic code written in Java.

For example you can create a normal Java class as follows:


package com.mydomain;
public class MyClass {
     public int getInt(){ return 1; }
}

Then you can create and use the object in CF through a few tags:


<cfset mc = createObject("java","com.mydomain.MyClass") />
<cfset myint = mc.getInt() />

CFScript can be pretty useful in making this flow similar to syntax from Java itself, but using the CF tags aren't a bad way to go either especially if the point is separating duties between developers use to Java code and database design from web designers use to HTML style.

Happy coding!

Concurrent Threading Series (J# Revisited)

2007-09-06T23:15:00.000-04:00

After discussing the .NET approach to threading and asynchronous processing, I figured I would circle back to my goal of showing in J# how threading would be accomplished in the same manner it was in our Java application example. Here is what the code looks like (mostly from the different Microsoft references on MSDN web-site. The interesting thing I found is that the use of IAsyncResult is slightly different in Jsharp and requires the use of an additional object System.Runtime.Remoting.Messaging.AsyncResult.


import System.Threading.ThreadPool;
import System.Threading.WaitHandle;
import System.Runtime.Remoting.Messaging.AsyncResult;
import System.IAsyncResult;
import System.AsyncCallback;

/** @delegate */
public delegate String taskDelegate(String x);

public class Example
{
    public static int NUM_THRDS_CONCURRENT = 60;
    /** 
     * Initializations to be done only once by application
     * Setup thread pool globally.
     */
    static
    {
        int workerThreads = 0, completionPortThreads = 0;
        ThreadPool.GetMaxThreads(workerThreads, completionPortThreads);
        //If current pool size is less than our maximum, change the pool size
        boolean changePoolSize = false;
        if (workerThreads < NUM_THRDS_CONCURRENT)
        {
            workerThreads = NUM_THRDS_CONCURRENT;
            changePoolSize = true;
        }
        if (completionPortThreads < NUM_THRDS_CONCURRENT)
        {
            completionPortThreads = NUM_THRDS_CONCURRENT;
            changePoolSize = true;
        }
        if (changePoolSize) ThreadPool.SetMaxThreads(workerThreads, completionPortThreads);
    }

    public Example()
    {
        super();
    }

    public static void main() {
        Example instance = new Example();
        System.out.println("Processing started " + System.DateTime.get_Now().ToString());
        instance.execute();
        instance = null;
    }

    public void execute()
    {
        int NUM_THRDS = 100;
        taskDelegate tasks[] = new taskDelegate[NUM_THRDS];
        IAsyncResult ars[] = new IAsyncResult[NUM_THRDS];
        WaitHandle _handles[] = new WaitHandle[NUM_THRDS];
     
        for(int i=0; i<NUM_THRDS; i++) {
            tasks[i] = new taskDelegate(task);
            ars[i] = tasks[i].BeginInvoke("Thread #" + i,
                new AsyncCallback(aCallback), 
                tasks[i]
            );
            _handles[i] = ars[i].get_AsyncWaitHandle();
        }

        // use wait handles to ensure threads complete before continuing
        // in this example we are just printing data,
        //    but this is useful if after async calls you need to sort/manipulate results.
        for (int i = 0; i < NUM_THRDS; i++)
        {
            _handles[i].WaitOne();
        }

        try
        {
            Thread.sleep(5000L);
        }
        catch (InterruptedException e)
        {
            // exception handling
        }

        tasks = null;
        ars = null;
    }

    /* Asynchronous callback method used to call EndInvoke and process results */
    public void aCallback(IAsyncResult ar) {
        taskDelegate t = (taskDelegate)ar.get_AsyncState();
        AsyncResult aResult = (AsyncResult)ar;
        taskDelegate temp = (taskDelegate)(aResult.get_AsyncDelegate());
        System.out.println(temp.EndInvoke(ar));
        t = null; temp = null; aResult = null;
    }

    public String task(String x)
    {
        try {
            Thread.sleep(2000L);
        } catch(InterruptedException e) {
            // exception handling
        }
        return (x.trim() + ": " + System.DateTime.get_Now().ToString());
    }
}

I tried to correlate other examples/practices in here like the use of a static initializer to set the size of the pool or other data/objects you want done once, but the remainder of the code is straight from documentation on the different .NET classes as mentioned above. Note the use of the AsyncWaitHandles, as I have found it is a good way to signal the calling thread when all the Async activities have completed. Otherwise, you can start a loop from the number of total threads and do i-- (decrement counter) within AsyncCallback method to indicate the number of threads still running.

Bringing The Background To The Foreground

2007-09-06T19:53:00.000-04:00

One point to make on multi-threading applications if I have not done a good job of making it clear is that it should be done with a good deal of responsibility with regarding to synchronization or protection against threads colliding with each other, exception handling, and resource management.

With the discussion on using thread pool or normal threads mentioned at the end of my last post, I realized I used the term "background" threads a good deal, but did not show how to implement one or what the difference was between a thread running in the background according to my definition and a background thread as implemented in programming language.

A thread is either a background thread or a foreground thread. Background threads are identical to foreground threads, except that background threads do not prevent a process from terminating. Once all foreground threads belonging to a process have terminated, the common language run-time ends the process. Any remaining background threads are stopped and do not complete. -MSDN

Threads that belong to the managed thread pool (that is, threads whose IsThreadPoolThread property is true) are background threads. All threads that enter the managed execution environment from unmanaged code are marked as background threads. All threads generated by creating and starting a new Thread object are by default foreground threads. -MSDN

If you follow the first link above, the MSDN library entry has the following code for illustrating the difference in how the background and foreground threads are treated at termination of application.


Imports System
Imports System.Threading

Public Class Test

    <MTAThread> _
    Shared Sub Main()
        Dim shortTest As New BackgroundTest(10)
        Dim foregroundThread As New Thread(AddressOf shortTest.RunLoop)
        foregroundThread.Name = "ForegroundThread"

        Dim longTest As New BackgroundTest(50)
        Dim backgroundThread As New Thread(AddressOf longTest.RunLoop)
        backgroundThread.Name = "BackgroundThread"
        backgroundThread.IsBackground = True

        foregroundThread.Start()
        backgroundThread.Start()
    End Sub

End Class

Public Class BackgroundTest

    Dim maxIterations As Integer

    Sub New(maximumIterations As Integer)
        maxIterations = maximumIterations
    End Sub

    Sub RunLoop()
        Dim threadName As String = Thread.CurrentThread.Name

        For i As Integer = 0 To maxIterations
            Console.WriteLine("{0} count: {1}", _
                    threadName, i.ToString())
            Thread.Sleep(250)
        Next i

        Console.WriteLine("{0} finished counting.", threadName)
    End Sub

End Class

As stated previously, I have found "background" threads really useful for statistical analysis/logging activities that are not necessary for functionality of user request, but need data from request to get started. So the user doesn't wait for long time for a response, the application calculates results for user while spawning a background thread for performing business logic, file I/O, database calls, etc. necessary.

This works well keeping in mind whether or not it is important to you to maintain any statistical threads still running at application shutdown. With .NET typically being hosted on a Microsoft IIS server where application pools cycle periodically, the need to use foreground thread or programmatic handling of background threads on application end (global.asax) may be more prevalent.

I will try to be careful on how I use the term "background" as I tend to use this to refer to any thread that does not need to run while a user is waiting/watching.

Anyway, just a little tidbit of information to add to the arsenal.

Code Formatter for Blogs/Web Pages

2007-09-06T19:11:00.000-04:00

Since I have been copying and pasting in code recently pretty frequently and trying to format, I thought I should post a really cool web tool I found that is great for formatting code snippets for placement in blog posts or HTML in general. If you have not seen the new look on my previous posts that I have retrofitted using this tool, take a look and see.

http://www.manoli.net/csharpformat/

You will find the tool is good for VB and C#. Since Java is very close in color coding scheme to C#, I used the tool with C# selection and worked out decently.

The formatting uses CSS, so you will have to copy the following CSS code into the template page of your blog or update the linked CSS file from the site with this code and reference as indicated.


.csharpcode, .csharpcode pre
{
    font-size: small;
    color: black;
    font-family: Consolas, "Courier New", Courier, Monospace;
    background-color: #f4f4f4;
    white-space: pre-wrap;       /* css-3 */
    white-space: -moz-pre-wrap;  /* Mozilla, since 1999 */
    white-space: -pre-wrap;      /* Opera 4-6 */
    white-space: -o-pre-wrap;    /* Opera 7 */
    word-wrap: break-word;       /* Internet Explorer 5.5+ */
}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt
{
    background-color: #f4f4f4;
    width: 100%;
    margin: 0em;
}

.csharpcode .lnum { color: #606060; }

Update adds text wrap for browsers that don't automatically do so with use of the pre HTML element.

Calling All Threads Again (Async Revisited)

2007-09-06T17:42:00.001-04:00

overview, calling all threads

So in the last post, we illustrated how to recreate Java code for parallel processing in Visual Basic .NET. One of the design features used in the implementation of retrieving data from task submitted to thread pool work item queue was to use a wait object to ensure that the thread completed before trying to collect the results.

Well that is the point of asynchronous processing, right: invoke a thread and then continue to do other work in main thread until work is complete. In our case, the additional processing we want to do is to start more threads or at least submit them to the queue and then we are satisfied in waiting for the results. Here is a look at the .NET asynchronous programming methodology for accomplishing what was done in my implementation of ATask by creating my BeginTask and EndTask routines. This should look very similar as asynchronous methods within .NET employ the same logic of beginning/invoking action with one method then ending/responding through another. The difference is that .NET will create all these methods for us versus my manually coded ones in previous post.

...
    Delegate Function TaskDelegate(ByVal x As String) As String

    Sub Main()
        Dim str As String = "test"
        Dim t As TaskDelegate = New TaskDelegate(AddressOf ATask)
        Dim ar As IAsyncResult = t.BeginInvoke(str, Nothing, Nothing)
        Console.WriteLine(t.EndInvoke(ar))
        ar = t.BeginInvoke(str + " second run!", New AsyncCallback(AddressOf ACallback), t)
        System.Threading.Thread.Sleep(5000L)
    End Sub

    Function ATask(ByVal x As String) As String
        Return x.Trim()
    End Function

    Sub ACallback(ByVal ar As IAsyncResult)
        Dim t As TaskDelegate = DirectCast(ar.AsyncState, TaskDelegate)
        Console.WriteLine(t.EndInvoke(ar))
    End Sub

...

The first piece of this code starts with the creation of worker function/class like the ATask.Task method from my last post. Above, this is represented by my highly complicated method ATask(String). Secondly, a delegate is declared with the same signature as function/sub routine created to do work.

As stated earlier, .NET will take care of creating the BeginInvoke and EndInvoke methods appropriately. Subsequently, the first call to t.BeginInvoke starts the processing of our task with the specified parameter data contained in the variable str. The t.EndInvoke returns a String as we instructed through the signature of method, so in our simple example, the result is fed right to console.

For more of a "Fire and Forget" implementation, the remaining parameters, we initially ignored in t.BeginInvoke by passing "Nothing" or null, are used. The first of the two is used to specify a callback method that has signature like ACallback. This method handles calling the t.EndInvoke. The second omitted parameter is used to pass the invocation the delegate object itself. As you will see in the second call to t.BeginInvoke, we are able to start the processing of work and then the results will be collected when ready by the callback method.

Where's The Pool
If you have been following along and aren't experts at this, you may be wondering what happening to the thread pool as we have been promoting the use of pooling since our worker thread count can go from one to 10,000. The fixed thread pool is great for ensuring that our system/application stays stable, so why eliminate it. Well, from what I have gathered, asynchronous threads are always run in the shared thread pool provided by the Common Language Runtime (CLR). Therefore, my understanding is that we do not need to submit these tasks to user work item queue.

With all this swimming around in a shared thread pool, there is a potential that managed code or other long running processes may be utilizing threads needed by this specific application or vice versa, so one thought is to use custom thread pooling. Custom thread pool development is not covered in this post, but I have found many different references to a fully implemented C# code from Mike Woodring. This would allow for processes using shared thread pool that we can't control to have adequate resources while ensuring that our application does not create an exorbitant amount of threads.

One interesting thought I had on this was to see if using standard threads if I could effectively code a mechanism in which I launch a finite number of threads (e.g. 15) and then monitor for the completion of any of the threads so that another iteration of the task can be spawned in that thread until all the tasks are complete. This is the concept behind thread pool, but just wondering if it can be done without the entire code associated with a full blown thread pool. This intrigues me as I can think of applications where it may be useful to spawn a set of processes within a thread pool and then to make them run faster spawn numerous non-pooled background threads. This eliminates some possibilities of deadlocks or issues with using shard pool, as our threads would not need to stay in the pool long as they would return rapidly. The pool would just be used for queueing the iteration of main working process while the background threads would be working on finite tasks within a working process instance.

Why not do this all with background threads if we can control their execution to be efficient?

We will see how my crazy mind develops this thought. May be easier just to grab the custom thread pool from Mike Woodring's site and make a reference to a compiled DLL that already has this work done. Why re-invent the wheel, right? Probably why I found other sites that had reference to DevelopMentor.ThreadPool as they just decided to copy his too.

The adventure continues...

Calling All Threads

2007-09-05T00:24:00.000-04:00

example source code, overview

There is always room for improvement, so I won't say we are at journey's end; however, this is at least in the right vicinity. The original intent of this series was to show concurrent programming in .NET as it was done in a real life Java application. There are components of this transition that are fairly straightforward as some objects conceptually map directly to that of original implementation, despite syntactical differences. My hope is that the information presented below will be useful in bringing the business and programming concepts together as well as point out places where the implementation is not as obvious.

Firstly, let's begin with the creation of the "thread" class. Microsoft .NET does not allow the inheritance of the Thread class or have Runnable or Callable interfaces to implement. Instead, using an additional class or member function within the main object, threads are created through use of a delegate function. Threads are instanced by calling the constructor of System.Threading.Thread, passing it a reference to the delegate function.


Dim Thread1 as New System.Threading.Thread(AddressOf delegateFunction)
Thread1.Start()

However, as we discussed in previous posts, we want to use pooled threads versus multiple threads called in succession. With that said, the format to spawn a thread in .NET into a pool requires the submission of a WaitCallback delegate, which has a signature of delegateFunction(Arg As Object), to the shared System.Threading.ThreadPool object that can be likened (hopefully without ridicule) to the fixed thread pool ExecutorService we utilized in Java. The object argument passed to the delegate function holds both the input parameters and variable(s) for output after thread processing has completed. See the AStateObject class in the linked source files as an example.

The general setup of a delegate function and use of thread pooling is shown below:

...
Sub BeginTask(ByVal thrdNum As Integer, ByVal isVarCalcTime As Boolean)
   state = New AStateObject(thrdNum, isVarCalcTime)
   'ThreadPool is a shared/static system object
'that can be likened to the Java ExecutorService object
   ThreadPool.QueueUserWorkItem(New WaitCallback(AddressOf Task), state)
End Sub

Function EndTask() As Object
   semaphore.WaitOne()
   Return state.Status
End Function

Sub Task(ByVal StateObj As Object)
   Dim varCalcTime As Long = AStateObject.DEFAULT_VAR_CALC_TIME
   Dim thrdnum As Integer = AStateObject.DEFAULT_THRD_NUM
   Dim isVarCalcTime As Boolean = AStateObject.DEFAULT_IS_VAR_CALC_TIME
   ' Use the state object fields as arguments.
   Dim StObj As AStateObject = CType(StateObj, AStateObject)
   isVarCalcTime = StObj.IsVarCalcTime
   thrdnum = StObj.ThrdNum
   If isVarCalcTime Then varCalcTime = AStateObject.getRandomCalcTime()

   'Can use SyncLock or other synchronization techniques
   'for real life calc that may hit backend systems
   'Data that may be touched by multiple tasks simultaneously
   Thread.Sleep(2000L + varCalcTime)
   'SyncLock StObj
   StObj.Status = Date.Now() 'Return value
   'End SyncLock
   semaphore.Set()   ' Signal that the thread is done.
End Sub

...

The Task subroutine is the delegate that does the processing we want. This can be coded to perform business logic directly or call a web service or other API that does the processing/calculations for us. The object passed to the delegate is really done by reference despite the method signature as what is passed by value is the address of the object itself; therefore, interactions with this object like setting Status which is the return data object set values in the original instance of AStateObject in our case. The thread pool is then passed a user work item through WaitCallback delegate and the AStateObject associated.

As with the Java implementation, it is most efficient to submit all the threads to the pool for processing then circle back and retrieve results once each thread is ready to yield it. If we wait for thread to finish to get result at same time we add to user work item queue, then we may as well submit threads sequentially or allow the main process to iterate task without overhead of creating more threads. Assuming we want the efficiency, what you will see in the class ATask is that the invocation/queuing of task is done in a BeginTask and then the response is queried through calling EndTask.

The calls from the main process occur in this phased fashion as well, very similar to how it was implemented in Java as we also used three sections there: create threads; read results/wait; display results.

...
    Dim NUM_THRDS As Integer = 100, I As Integer
    Dim tasks(NUM_THRDS) As ATask
    Dim status(NUM_THRDS) As Object

    'Start threads
    For I = 0 To (NUM_THRDS - 1)
        tasks(I) = New ATask()
        tasks(I).BeginTask(I, True)
    Next

    'Get the result of the threads
    For I = 0 To (NUM_THRDS - 1)
        Try
            'Not using any processing that will throw an error,
            'but just wanted to show as an example
            status(I) = tasks(I).EndTask()
        Catch e As Exception
            status(I) = Nothing
        End Try
    Next

    For I = (NUM_THRDS - 1) To 0 Step -1
        If status(I) = Nothing Then status(I) = "Empty"
        Console.WriteLine("Thread #" + I.ToString + ": " + status(I).ToString())
    Next

    'Clean-up objects used
    status = Nothing
    tasks = Nothing

...

The significance to this approach is that after the process has been put into a thread pool, the results cannot be extracted until after the process has fully completed. Therefore, the EndTask function waits on the thread making the middle block of code very much sequential, but since the threads run concurrently before hand it adds minimal if any processing overhead as you should never wait any longer than the longest running thread in the bunch. Since "run time" in the context of longest running process includes the amount of time a thread must sit in the queue, now may be a good time to show the syntax for increasing the thread pool size.


ThreadPool.SetMaxThreads(workerThreads, completionPortThreads)

Both parameters are integers representing the quantity of each type of thread available in the pool. Since .NET employs a shared thread pool, this follows the principal of finding the right number of threads for all concurrent users of the application. As the application is idle, users will have a faster response, then will slow down as load grows to ensure stability of system as too many threads can be as lethal as not enough.

Putting it all together, we have an application that emulates our Java example and yields pretty equal results (see example output). Hopefully this was successful in meeting my intent, which was to place links and thoughts around multi-threading in .NET (for Java developers) especially around concurrency. In trying to stick closely to the layout/concepts in the Java implementation, I am sure I have put together some odd .NET constructs, so all you VB/.NET purists out there must forgive me.

10,000 threads are still better than one if used tactically, especially as we move to a world of even faster response times with asynchronous interfaces. A follow-up to this exercise may be to show how to improve the user interface portion of the application to display some of the results immediately to browser/screen like how many items are in stock today as the program processes the results for items that need to be built for example in our item availability service. We'll see if I can wrap my brain around using the IAsyncResult process in-line with the other concepts here, but, until then...

keep evolving development

Migrating to J# From Java

2007-09-01T18:46:00.000-04:00

overview

Microsoft's J# is like Cappuccino, it is pretty much Java with some extra frosting on top.

Microsoft's J#, syntactically, is very much the same as Java; therefore, when this adventure to migrate the availability code to .NET presented itself, my first thought was "convert to J#." Unfortunately, with the features we explored previously being functionality of JDK level 5.0, J# could not actual accomplish this task.

According to Microsoft, J#'s run-time libraries will remain frozen at the JDK 1.1.4 level; there are no plans to emulate later versions of the JDK. If J# catches on, it's likely that users will fill in the gaps over time. Microsoft considers JDK compatibility largely irrelevant. The current level supports Visual J++ projects, and whatever is missing from the JDK compatibility libraries is covered by the .Net framework.
J#'s interface to the .Net framework is solid, but not as seamless as C#. In particular, J# code cannot define new .Net properties, events, value types, or delegates. J# can make use of these language constructs if they are defined in an assembly written in another language, but its inability to define new ones limits J#'s reach and interoperability compared to other .Net languages.
-Tom Yager, Infoworld

Presumably due to legality issues restricting Microsoft from copying Java bytecode for bytecode, coupled with strategic desire to push developers towards using core .NET functionality to accomplish the tasks done in later Java versions, the J# support for newer JDKs will probably not be updated to cover the holes in it current capabilities.

With that said, for my case study, I have come to the realization that code must be re-written in .NET and it will be in Visual Basic. C# is probably the more powerful than Visual Basic .NET; however, the target business system being written in Visual Basic makes it the more desirable for this project.

Don't get me wrong, though, there is a good deal of power in being able to compile java directly into .NET using J#, but just be aware of the limitations. It took me a while to stop seeing 1.1.4 and thinking 1.4, so for the application I was converting there was too much of a gap.

For you developers who have Java applications that would be hard to re-write, J# .NET may fit the need, especially when using tools such as IKVM or JBImp that allow conversion of Java bytecode to Microsoft intermediate language (MSIL). However, this may also be a good argument for using C#, which syntactically is close to that of Java as C/C++ is. After converting Java classes or archive (JAR) files to .NET libraries, migration of application to C# becomes easier.

Jump In The Pool, The Threads Are Fine

2007-08-30T20:41:00.000-04:00

example source code, overview, java part I

10,000 soldiers may be more powerful than one, but, if they march in a straight line towards enemy fire, they are no more effective.

Threads are the same. Marching them in regiments versus a line adds power and efficiency at the same time.

Thread Pooling
In Java 5.0 (JRE 1.5), the java.util.concurrent package was made available which provided API for concurrent processing/efficient thread pooling. For some good details on the API, please refer to Java/Sun publications on JSR 166. The Distributed Computing Laboratory at Emory University has further documentation as well as a back-ported version of the package for use with older versions of JVM.

To implement the concurrent/thread pool model to the ArrayOfThreads object we created in previous post, the ExecutorService is utilized to invoke a Callable object (introduced in the concurrent package). AThread happens to be a Callable object versus a simple sub-class of Thread object which just has a run() method without a return. Utilized the Callable even in the earlier examples as the end goal is to be able to gather data back from the execution of this process.

Consequently, after submission of threads for processing, the framework for concurrency provides a mechanism to retrieve returned data per thread using the Future object. The major difference this makes in the implementation is that the Future handles the waiting and gathering of thread data, so the main application thread spawning the new processes does not have to wait until each thread completes before moving on.


...

/* code snippet from ArrayOfThreadsPooled.java
* @see java.util.concurrent.Executors
* @see java.util.concurrent.ExecutorService
* @see java.util.concurrent.Future
*/
int NUM_THRDS = 100, NUM_THRDS_POOLED = 100;
ExecutorService tpes = Executors.newFixedThreadPool(NUM_THRDS_POOLED);
Future futures[] = new Future[NUM_THRDS];
AThread calculators[] = new AThread[NUM_THRDS];
Object status[] = new Object[NUM_THRDS];

...

Later on using a for loop to instantiate the threads, we submit each to pool (where i represents the integer index used in loop).


calculators[i] = new AThread(i);
futures[i] = tpes.submit(calculators[i]);

On the first implementation of ArrayOfThreadsPooled we see that the process impracticality of running multiple threads has been removed as the process time is about a hundredth of the time since all 100 threads execute simultaneously (see output example 1).

Don't Be A Resource Hog
However, the problem of scaling is still an issue as your business systems involved in processing and application server itself will probably not appreciate the execution of 10,000 threads. To remedy this per user, the ArrayOfThreadsPooledv2 is enhanced by illustrating the fact that the fixed thread pool does not need to have the same size as the required amount of Callables, Futures, et al. Therefore, the NUM_THRDS_POOLED value can be changed to a lower or higher number than 100.

For example I found that my application worked with a total number of 15 processors running at a time (see output example 2). To better understand the benefit, the AThread class provided includes the ability to provide variable processing times. With variance in execution times, the thread pool with stop and start threads appropriately thus adding even more efficiencies over executing x number of threads at a time with identical run times (see output example 3).

Back 2 The Future
Through the Future get() method wrapped with exception handling, we can retrieve the returned object/data from the Callable later on.

Since the Futures involved take care of getting data, as a developer you get the benefit of all the threads starting at once, but having a method of identifying which response goes to which request. With this information, you can then sort, analyze, manipulate or otherwise report the data however you see fit.

Some further enhancements to this would be o find the optimum amount of threads needed to support all requests from your client base and instantiate the ExecutorService as one static version defined globally for the application.


public static int NUM_THRDS_CONCURRENT = 60;
public static ExecutorService tpes =
Executors.newFixedThreadPool(NUM_THRDS_CONCURRENT);

Each instance of ArrayOfThreadsPooledv2 that were executed would, therefore, submit its number of Callable objects necessary into the shared pool.


futures[i] = ArrayOfThreadsPooledv2.tpes.submit(calculators[i]);

If no other users were on, the response may be as quick as first iteration of ArrayOfThreadsPooled while providing the stability and respect of resources provided by second implementation.

With the source code and detail to this point, I hope those of you on the journey towards creating a multi-threaded application in Java have found enough useful information to utilize the full power of Java's great addition. Keep in mind that further enhancements exist in JRE 1.6.

Keep evolving development!

10,000 Threads Are Better Than One

2007-08-30T17:14:00.000-04:00

example source code, overview

In this post we will be following the business logic for using multi-threading and concurrency as we explore the expansion of the availability service from its basic form to one extended for more practical application presented in the previous posting.

[basic availability service] an application that communicated with business systems (enterprise resource planning - inventory management in particular) to read current stock information, including current demand allocations; compare to new single demand; ultimately, displaying lead time to fulfill customer requirement whether from stock, assembling from stocked components on a bill of material, or purchasing/fabricating item through the portions if not all of its cumulative lead time...

Since the business world does not exist in a parallel universe where customer demand and actual supply are always in constant harmony, the basic implementation for availability above is insufficient as a final answer to a demand requirement that has differing priorities (e.i. fulfill two or more needs at once). To clarify, please re-read the following excerpt:

[extended availability service] As a customer, you may have an immediate need for five light bulbs or else you will be in darkness for the entire day, but you get cost efficiencies purchasing light bulbs in quantities of 1,000. The purpose of this extension to the availability service is to allow you as the customer understand that you can get 50 from stock today...

The first need of concern is to restore service: get the lights on! The second priority is to ensure that the effort to restore service is done in a fashion that positively impacts the bottom-line long term as well as lessen the likelihood of future outage. At its most basic sense, the response to the above variation in need could involve two calls to availability code: one returning lead time of quantity acquirable through stock (e.g. same day); the other, time through procurement/manufacturing processes (e.g. 60 days).

However, if you add usage and more complex items an scenarios to your thought process over my simple light bulb shortage, the application requirement slowly becomes (or at least it did in my case) one that asks the question "what is the individual lead time of each quantity of the total 1,000 light bulbs requested?"

An Array of Threads
Following the thought above, to answer the question, a user of the availability tool would run sequential requests with required quantities from 1 - 1,000. The first step is to change the process of making the requests an automated one versus having to make 1,000 separate requests. The user would like to query the system once and get 1,000 responses. With the potential for thousands or tens of thousands, the first logical conclusion I got was "10,000 threads are better than one."

The example thread AThread does a simple wait(x milliseconds) to simulate some processing time, but can easily be modified to be a synchronized (depending on locking requirements) block of code executing logic necessary to determine a specific item and required quantity availability. For example:


...

/* call() method for AThread.java
* @see java.util.concurrent.Callable#call()
*/
public Object call() throws Exception {
// Define your own result object and availability processor
LeadTimeResultObject ltro = new LeadTimeResultObject();
AvailabilityProcessor process = new AvailabilityProcessor();
// Use synchronized blocks for thread safety
synchronized(this){
   // Insert application logic to get lead time
ltro = process.getLeadTimeResult("some item id", "some quantity");
}
return ltro;
}

...

The ArrayOfThreads class illustrates how to implement calling the lead time availability code through use of an AThread array.


AThread calculators[] = new AThread[100];

Using a for loop to iterate from 0 to 99 index of array, you can use the Constructor of the AThread object to pass in data needed to make each thread calculation unique (e.g. item id and quantity equal to 1 to 100 - array index + 1). Although this accomplishes the first task of automating the requests, each thread must complete serially making the net result of the application performance exactly that of having the main thread executing one availability process 100 times (see output example 1: processing times go in sequence and so total processing time is long).

In addition, this will most likely crash your application and/or back-end systems as user quantity requirements scale upward.

I know this is not very practical for the use case we are exploring, but another component of the application I wrote was used to keep statistics on requests for availability. These statistics were needed for later analysis and did not need to be sent back to user. Applying the methodology of thread arrays for background processing that needs to be done at some point is a perfect fit. User leaves the site or at least is satisfied by a response, while additional business logic is applied as more data is gathered and stored in a business intelligence/reporting back-end.

Thread pooling or utilizing all the threads simultaneously would answer both the automation of user process and most efficient run time concerns. See next post in the concurrent threading series for a detail on the ArrayOfThreadsPooled object included in the source code available for download above.

The Adventure Begins (Concurrent Threading Series)

2007-08-30T16:48:00.000-04:00

Multi-threading background processes is very cool for spawning large calculations that write to statistical/business intelligence data stores while responding other data quickly to user from the main thread; however, when you need to do some medium to long running calculations multiple times and return a sorted/combined result set to your user without feeding into the attention deficit disorder inside all of us, multi-threaded background processes become even cooler.

Background
A practical or real life application for this would be an application that communicated with business systems (enterprise resource planning - inventory management in particular) to read current stock information, including current demand allocations; compare to new single demand; ultimately, displaying lead time to fulfill customer requirement whether from stock, assembling from stocked components on a bill of material, or purchasing/fabricating item through the portions if not all of its cumulative lead time.

Furthermore, the delivery method on this application to client was via the web, so a few seconds processing time is a long wait to many, so response time must be considered in any alterations.

Now consider a secondary request interface for this application that needs to determine separate responses for the same demand above. This was used by Customer Service/Sales Representatives who need to know the breakdown of lead time(s) from satisfying quantity of one to the total requested. As a customer, you may have an immediate need for five light bulbs or else you will be in darkness for the entire day, but you get cost efficiencies purchasing light bulbs in quantities of 1000. The purpose of this extension to the availability service is to allow you as the customer understand that you can get 50 from stock today, 250 built within a week, and 700 available two months from now. This is much more useful to satisfying both business needs than getting one response that indicates to get a complete order 1000 would be two months.

The Problem
Several months ago, during a migration from one business enabling system to another, I was faced with a need to architect this concurrent programming accomplished in Java as a .NET application. My initial thought was to leverage and reuse as much of the Java code I had already written to lessen the development cycle for this change and to ensure that the logic was implemented as close to original as possible then improve from there once integration was established. What did this mean? Microsoft J# to the rescue! Or at least so I hoped.

Microsoft’s J# does not support Java 5.0 as it is based on previous JRE version. This road blocked the effort immensely as it was not clear to me if Microsoft .NET even had the capability to perform the same concurrency programming and/or how, and so began the adventure to acquire knowledge.

The Adventure
The following set of posts try to go through thought process behind the multi-threading being done in the original Java implementation and hopefully arrive at the most efficient means of transforming to a .NET implementation. Whether or not my journey to converting from Java to .NET is successful, I figured this information may be helpful for new (or at least new to threading) developers that are looking for methodologies to accomplish what I have done in either language.

So buckle up and let’s explore together the migration of concurrent processing code from Java technology to J# and .NET in general.

Deleting Multiple Views/Tables by Pattern in Microsoft SQL Server

2007-06-18T15:05:00.000-04:00

If you have ever used Microsoft SQL Server tools to work with tables and/or views you will know that you cannot select multiple items for deletion (or at least not that I have found) through the UI. A good feature to ensure a unsuspecting user doesn't accidentally delete all the data from your production server, right?

Well, suppose you needed to delete 4000+ views from an application database that didn't belong there (long story).

The views all start with the same beginning text so they are right next to each other in the UI for SQL. Now the great feature is the bane of your existence as you hit delete, then confirm, delete, then confirm...

Here is one solution you can use with some simple VB.NET code.


...

   Dim PATTERNS() As String = {"prefix1%", "prefix2%", "prefix3%"}
   Dim strDatasource As String = _
"Data Source=dbsvr;Initial Catalog=db;User ID=usr;Password=pwd"
   Dim strSqlStatement As String = _
"SELECT name FROM sys.sysobjects WHERE (name LIKE '" & PATTERNS(0) & _
"' OR name LIKE '" & PATTERNS(1) & "' OR name LIKE '" & PATTERNS(2) & _
"') AND (xtype = 'V')"
'To make this flexible,
'you can loop through count of patterns items instead of hard coded usage
   Dim sqlConnection As New SqlClient.SqlConnection(strDatasource)
   Dim sqlCommand As New SqlClient.SqlCommand(strSqlStatement, sqlConnection)

   sqlConnection.Open()
   Dim rs As SqlClient.SqlDataReader = sqlCommand.ExecuteReader()
   Dim namelist As ArrayList = New ArrayList()

   While rs.Read()
       namelist.Add(rs("name").ToString)
   End While

   rs.Close()

   Dim i As Integer = 0
   Dim count As Integer = 0
   Dim names() As String = _
CType(namelist.ToArray(GetType(String)), String())

   For i = 0 To (names.Length - 1)
       If names(i).ToString <> "" Then
           sqlCommand.CommandText = _
"DROP VIEW [dbo].[" & names(i) & "]"
           count += sqlCommand.ExecuteNonQuery()
       End If
   Next i

   sqlConnection.Close()
   sqlCommand = Nothing
   rs = Nothing
   sqlConnection = Nothing
   System.Console.WriteLine(count & " affected!")
   System.Threading.Thread.Sleep(5000)
   Return

...

This was done quick and dirty as a command line application and so I used the console to write the number of objects actually affected by script. The Thread.Sleep(5000) was used to delay the console screen 5 secs on screen before going away so I could see the result.

This was quick and dirty, so for a production utility, you may want to add other features like exception handling, input validation, and backout capabilities. Input validation would be very key if the program is modified to take a user supplied pattern versus the hard-coded method above. The worry is that someone will send '%' and wipe entire set of views/tables by accident.

Anyway, the script uses the .NET objects for SQL connections to execute a bit of T-SQL which searches the sysobjects table for all the objects that match have a name like 'pattern' and xtype = 'V', restricting search to just views.

Since it is not known the total number to be found, I have opted to use an ArrayList (Vector) approach to allow me to add 0 to many results. Later, the ArrayList is converted to an array of strings which is in turn used in a loop to create dynamic drop statements that are executed on the same SQL server that was queried for the object list in the first place.

Hopefully this helps some poor DBA hitting the delete button 100 times at least once and I will be happy.