Thursday, 22 December 2011

Recursion vs. Loops in VBA and SQL

Recursion, when I was first taught about it in our class rooms our professor mentioned, this is one topic which every student ask to repeat once again.

Though after a while in the business I am learning actually it’s not that bad too. But it’s just a concept which has to be used wisely. In the past the context I have used recursion is for file/directory searches and listing. But I was also taught to us in the context of factorial and mathematical concepts which could be achieved via loops.

Thus I am embarking in this post to figure out which is a better one to choose:

With recursion, though our mathematical models can be represented well in comparison to loops programmatically. The concept of recursion has some underlying concerns which also need to be taken into account while designing the programs.

The following concerns are sourced from MSDN (for further details please refer to the detailed MSDN documentation):

1. Limiting condition: This is the most important part of recursion and upon which many times my programs have crashed up. Design the recursion ending condition well for all scenarios of the input, if not you might end up into stack overflow error.

2. Memory Usage/Performance: This is one of the most important considerations to take into account while writing code for recursion. As the function/procedure calls upon itself each time the copy of local variable for each instance of execution is created on stack and the overhead of argument passing has to be taken.

3. Debug: Painful to debug in recursive codes.

So rather stick with Looping?? Well it’s an answer you as developer have to decide which route to take; personally I have used recursion for scenarios of file/directory listing arenas and mostly stick to the simple forms of looping for any other tasks of computations. But your comments are valuable for me in this space .. !!!

Below are the codes to illustrate the use of recursion in VBA and in SQL (too but only limited to 32 levels)

Option Explicit

Public Function recursive_fact(n As Integer) As Integer
    If n < 1 Then
        recursive_fact = 1
        Exit Function
    End If
    recursive_fact = n * recursive_fact(n - 1)
End Function

Public Function looping_fact(n As Integer) As Integer
    Dim jCount As Integer
    looping_fact = 1
    For jCount = n To 1 Step -1
        looping_fact = looping_fact * jCount
    Next jCount
End Function

Sub test_functions()
    Dim iCount As Integer
    Dim jCount As Integer
    iCount = 5
    jCount = 5
    iCount = recursive_fact(iCount)
    jCount = looping_fact(jCount)
    Debug.Print "Factorial of iCount: " & iCount
    Debug.Print "Factorial of jCount: " & jCount
End Sub
'Factorial of iCount: 120
'Factorial of jCount: 120
--Create a sample table
CREATE TABLE employee(
    first_name  VARCHAR(10),
    last_name   VARCHAR(10),
    salary      DECIMAL(10,2),
    start_Date  DATETIME,
    region      VARCHAR(10),
    city        VARCHAR(20),
    managerid   INTEGER
--Insert some records into it
INSERT INTO employee VALUES (1, 'Jason' ,  'Martin', 5890,'2005-03-22','North','Vancouver',3);
INSERT INTO employee VALUES (2, 'Alison',  'Mathews',4789,'2003-07-21','South','Utown',4);
INSERT INTO employee VALUES (3, 'James' ,  'Smith',  6678,'2001-12-01','North','Paris',5);
INSERT INTO employee VALUES (4, 'Celia' ,  'Rice',   5567,'2006-03-03','South','London',6);
INSERT INTO employee VALUES (5, 'Robert',  'Black',  4467,'2004-07-02','East','Newton',7);
INSERT INTO employee VALUES (6, 'Linda' ,  'Green' , 6456,'2002-05-19','East','Calgary',8);
INSERT INTO employee VALUES (7, 'David' ,  'Larry',  5345,'2008-03-18','West','New York',9);
INSERT INTO employee VALUES (8, 'James' ,  'Cat',    4234,'2007-07-17','West','Regina',9);
INSERT INTO employee VALUES (9, 'Joan'  ,  'Act',    6123,'2001-04-16','North','Toronto',10);
--Verify the data
SELECT * FROM employee;
--Create the procedure
CREATE PROC usp_FindBoss(
   @EmployeeID int
 DECLARE @ReportsTo int
   @ReportsTo = managerid
     Id = @EmployeeID
     @EmployeeID AS Employee,
     @ReportsTo  AS Manager
   EXEC usp_FindBoss
--Execute the procedure
SELECT * FROM employee
EXEC usp_FindBoss 2

Note: For SQL Stored Procedures CAN call Stored Procedures & Functions(Both), but Functions CAN call Functions (Only).

Link1 (MSDN Recursion):

Wednesday, 21 December 2011

Variable Declaration – Sounds Easy in VBA..

Well its easy up to the point you like to keep it easy by single individual variable declaration, but when the smart ways kicks in even simple things don’t remain easy in VBA.

Well some time I myself got confused about some codes of what’s happening and what these symbols interpret. So I wanted to clarify some bits of variable declaration styles in this post of mine.

Combined Declaration:

When multiple variables are declared in one Dim statement, the types have to be individually defined for each of them using the “as” operator.

Data Type Symbol declaration:

This is another way of declaring variables whereby you are escaped from writing a whole big line specifying the data type a variable is declared of.In this you just use the specified symbols and works done!!
The following, lists the symbols used for the same:

! = Single Precision
% = Integer
& = Long Integer
@ = Currency
# = Double Precision
$ = String

Also along with this post I am providing some quick referencing links for the data types and their capacity with byte consumption to efficiently design your code.

1. Quick ref 1:
2. Quick ref 2:

The following VBA code illustrates the declaration styles.

Public Sub dataTypes_Declarations()

    '--Old ways of declaration

    Dim r As Integer
    Dim s As Double

    Debug.Print "--Old ways of Declaration--"
    Debug.Print "r is of type: " & TypeName(r)
    Debug.Print "s is of type: " & TypeName(s)

    '--Combined declaration BEWARE

    Dim i, j, k As Integer

    Debug.Print "--Combined Declaration --"
    Debug.Print "i is of type: " & TypeName(i)
    Debug.Print "j is of type: " & TypeName(j)
    Debug.Print "k is of type: " & TypeName(k)
    '--Declaration Shortcuts
    '! = Single Precision
    '% = Integer
    '& = Long Integer
    '@ = Currency
    '# = Double Precision
    '$ = String

    Dim a!, b%, c&, d@, e#, f$

    Debug.Print "--Declaration Shortcuts --"
    Debug.Print "a (!) is of type: " & TypeName(a)
    Debug.Print "b (%) is of type: " & TypeName(b)
    Debug.Print "c (&) is of type: " & TypeName(c)
    Debug.Print "d (@) is of type: " & TypeName(d)
    Debug.Print "e (#) is of type: " & TypeName(e)
    Debug.Print "f ($) is of type: " & TypeName(f)
End Sub


Performance & Memory – Large Collection vs. UDT Array in VBA

This article is focused on the use of “UDT” user defined types in VBA. I came across this while looking upon performance and memory consumption issues in VBA while dealing with large datasets.

As classes in object oriented concept provide a wrapper and functionality for one or more related elements. So UDT in VBA is also a wrapper mechanism where it can encapsulate the properties of an object, subtracting the custom functionality as provided by the classes.

The primary utility of an UDT is that, it’s a very light weight composition of object (similar in lines to struct in C#). Its more performance oriented and can be used for representing data series for stocks (encapsulating ISIN, Price, volume and more).

This UDT mechanism is commonly used in counter part of collection object because of the following 2 reasons:

1. VBA Collection contains nothing but variant data types, which, as you know, have one of the highest costs with regard to system resources.

2. Collection object uses a linked list storage structure that also adds to its overhead.

As illustrated in the code below a similar structure of data when implemented using user defined type (UDT) against classes, the performance was considerably higher with the UDT mechanism.

Main Module (Module.bas) :

Option Explicit
'--Declaration for getting millisecond count since system startup --
Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

'--Define the Max Loop count for the interation to measure performance --
Private Const MAXCOUNT As Integer = 30000

'--User Type Defination--
Public Type StockPrice
    Price As Integer
    volume As Long
End Type

'--Routine creating and adding elements to array/collections
Sub compare_UDT()

    Dim stock_array(MAXCOUNT) As StockPrice
    Dim iCount As Integer
    Dim stock_collection As Collection
    Dim stock As clsStock
    Dim stTime As Long

    Set stock_collection = New Collection

    'User Defined Type
    stTime = GetTickCount
    For iCount = 0 To MAXCOUNT
        With stock_array(iCount)
            .Price = Rnd
            .volume = Rnd
        End With
    Next iCount
    Debug.Print GetTickCount - stTime & " Milliseconds for UDT"

    'Class Objects
    stTime = GetTickCount
    For iCount = 0 To MAXCOUNT
        Set stock = New clsStock
        With stock
            .Price = Rnd
            .volume = Rnd
        End With
        stock_collection.Add stock
    Next iCount
    Debug.Print GetTickCount - stTime & " Milliseconds for Collection"

End Sub
'--Results --
'31 Milliseconds for UDT
'171 Milliseconds for Collection

Class Object (clsStock.bas):

Option Explicit

Private m_iPrice As Integer
Private m_lvolume As Long

Public Property Get volume() As Long

    volume = m_lvolume

End Property

Public Property Let volume(ByVal lvolume As Long)

    m_lvolume = lvolume

End Property

Public Property Get Price() As Integer

    Price = m_iPrice

End Property

Public Property Let Price(ByVal iPrice As Integer)

    m_iPrice = iPrice

End Property 

Filter Unique items list in VBA

Many times I have been presented with this challenge to extract a unique item list from a pre-defined array containing non unique items.
For the purposes I tend to use a collection object in VBA and use its “key” element to uniquely identify my items being added to the collection and extracting it back to another unique array.

This functionality should be implemented as function, its a subroutine only for illustration purposes.
Sub uniqueItems()

    Dim repArray(10) As Integer
    Dim unqColl As Collection
    Dim unqArray() As Integer
    Dim iCount As Integer

    '--Allocate repArray
    repArray(0) = 2
    repArray(1) = 2
    repArray(2) = 2
    repArray(3) = 2
    repArray(4) = 2
    repArray(5) = 2
    repArray(6) = 2
    repArray(7) = 2
    repArray(8) = 2
    repArray(9) = 2
    repArray(10) = 20

    '--Filtering via Collection
    Set unqColl = New Collection

    On Error Resume Next
    For iCount = 0 To UBound(repArray)
        '--The identifying key has to be string
        unqColl.Add repArray(iCount), CStr(repArray(iCount))
        '--In case of refrential object variables can also use VarPtr for idnetifying unique address
    Next iCount
    On Error GoTo 0

    '--Resize the unique array based upon the unique items in the collection
    ReDim unqArray(unqColl.Count - 1) As Integer
    '--Populate the unique array
    For iCount = 0 To (unqColl.Count - 1)
        unqArray(iCount) = unqColl(iCount + 1)
    Next iCount

    '--unqArray Content
    'unqArray(0) = 2
    'unqArray(1) = 20

    '--Free up the space
    Set unqColl = Nothing
    Erase repArray
    Erase unqArray

End Sub

Tuesday, 20 December 2011

Pointers in VBA.. Huhh !!! (delegates in VBA)

“Pointers“ – aaah the word which is not pleasing to most of the programmers is a very powerful functionality in programmers world. (Rightly said power and pain goes along)

I thought working in VBA arena one would be safe from the world of pointers and I wouldn’t have to deal with it. But embarking on voyage to explore the object oriented nature of VBA and its support to OOP’s (Object oriented programming) I stumbled upon an article and resources in line of using pointers in VBA.

So keywords associated with pointers in VBA are as follows:

Note: All the Address values of pointers are stored in the “Long” data type

1. AddressOf: This functionality in VBA provides the addresses of objects (i.e. module, projects, but Not Classes), functions & procedures address.
2. VarPtr: Returns the address of a variable.
3. VarPtrArray: Returns the address of an array.
4. StrPtr: Returns the address of the UNICODE string buffer.
5. VarPtrStringArray: Returns the address of an array of strings.
6. ObjPtr: Returns the pointer to the interface referenced by an object variable.

Using the concept of pointers I have designed a VBA code to illustrate the implementation of “delegate” (function pointers) in VBA and also illustrated the use of the above mentioned keywords.

Also to mention this concept of pointers is also associated with the Callback functionality of VBA with external DLL’s.

Delegate Sample Code:
Option Explicit

'-----External Library Declaration which helps call the Proc by Address -----
Private Declare Function CallWindowProc _
                          Lib "user32.dll" Alias "CallWindowProcA" ( _
                              ByVal lpPrevWndFunc As Long, _
                              ByVal hwnd As Long, _
                              ByVal msg As Long, _
                              ByVal wParam As Long, _
                              ByVal lParam As Long) As Long

'-----This is the main function calling upon the proc via pointer -----
Public Sub test_delegate()
    Dim sMessage As String
    Dim nSubAddress    'As Long

    'This message will be passed to our Sub as an argument
    sMessage = InputBox("Please input a short message")
    'Get the address to the sub we are going to call
    nSubAddress = ProcPtr(AddressOf ShowMessage)
    'Do the magic! Function Called via Pointer...
    CallWindowProc nSubAddress, VarPtr(sMessage), 0&, 0&, 0&
End Sub

'-----This is the subroutine we want to call by address-----
Private Sub ShowMessage( _
        msg As String, _
        ByVal nUnused1 As Long, _
        ByVal nUnused2 As Long, _
        ByVal nUnused3 As Long)
'This is the Sub we will call by address
'it only use one argument but we need to pull the others
'from the stack, so they are just declared as Long values
    MsgBox msg
End Sub

'-----This function is used to extract the address of value to long -----
Private Function ProcPtr(ByVal nAddress As Long) As Long
'Just return the address we just got
    ProcPtr = nAddress
End Function

Pointer Sample Code:
Option Explicit

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
                               (ByVal dst As Long, _
                                ByVal src As Long, _
                                ByVal nBytes As Long)

Sub pointerDemo()

    Dim iCount As Double
    Dim jCount As Double
    Dim ptr_iCount As Long
    Dim ptr_jCount As Long

    iCount = 100.23
    jCount = 200.45

    ptr_iCount = VarPtr(iCount)
    ptr_jCount = VarPtr(jCount)

    '--Common mistake: This will only overwrite the pointer variables
    '--The underlying values refrneced by the pointers will remina the same.
    'ptr_jCount = ptr_iCount

    '--The below mentioned method is the correct version to change the
    '--value of the underlying variables refrenced by the pointers.(2 version)
    'CopyMemory VarPtr(jCount), VarPtr(iCount), Len(iCount)
    CopyMemory ptr_jCount, ptr_iCount, Len(iCount)

    Debug.Print iCount
    Debug.Print jCount

End Sub


Download Solution
Download solution


Function Overloading – VBA

Function overloading, another wonders of the object oriented world where by the same name functions with different signatures provide the respective functionality based on the invoking arguments passed and their types.

Picking upon this and some google’ing I attempted to implement a function overloading mechanism via the help of “Variant” within VBA, as sadly function overloading is not inherently supported by VBA and it screams error of “Ambiguous names detected …”

Thus in order to implement the function overloading, we would have first construct a wrapper function which evaluates the signature of arguments sent and match to the respective function call and based upon which it executes and return the respective function.

Not very optimistic and delightful way of doing things but it’s something good to have in the repository for just one day when everything else fails.

'This enforces all the variables used within this module mush have their declartions explicitly
Option Explicit

'This enforces all the arrays implemented within this module starts from 0 'Zero'
Option Base 0

'This prevents the avilabiltiy of the function within this module to external refrencing projects
'and UDF's
Option Private Module

'Constant declaration
Private Const ConstIntVal As Integer = 10
Private Const ConstStrVal As String = "Hello"

'----- Overloading Implementation ------
Public Function addValue(Optional args As Variant) As Variant
    'Function signature identification and call relevant function component
    If IsMissing(args) Then
        addValue = addValue_Null
        Exit Function
    End If
    If Not IsArray(args) Then
        If TypeName(args) = "Integer" Then
            addValue = addValue_Int_Const(CInt(args))
            Exit Function
        ElseIf TypeName(args) = "String" Then
            addValue = addValue_Str_Const(CStr(args))
            Exit Function
        End If
        If TypeName(args(0)) = "Integer" Then
            addValue = addValue_Int(CInt(args(0)), CInt(args(1)))
            Exit Function
        ElseIf TypeName(args(0)) = "String" Then
            addValue = addValue_Str(CStr(args(0)), CStr(args(1)))
            Exit Function
        End If
    End If
End Function

'------ OverLoad Components ------

'Return 0 'Zero' for no arguments
Private Function addValue_Null() As Integer
    addValue_Null = 0
End Function

'Returns the constant added value to the argument (Integer)
Private Function addValue_Int_Const(val As Integer) As Integer
    addValue_Int_Const = val + ConstIntVal
End Function

'Returns the constant added value to the argument (String)
Private Function addValue_Str_Const(val As String) As String
    addValue_Str_Const = ConstStrVal & val
End Function

'Returns addtion of two integers
Private Function addValue_Int(val1 As Integer, val2 As Integer) As Integer
    addValue_Int = val1 + val2
End Function

'Returns concatenation of two strings
Private Function addValue_Str(val1 As String, val2 As String) As String
    addValue_Str = val1 + val2
End Function

Attached file illustrates the function overload mechanism.

Download Solution
Download solution


“Implements” Keyword, Interface – Class in VBA

Recently I was puzzled with a question about the levels of Object oriented structure can VBA support?
Suddenly my thoughts shifted to the key word I was always suspicious about but didn’t found time to explore. This post is all about key word “Implements” in VBA.

Learning about “Implements” in the context of VBA I am quite amazed about its potential. This keyword is used in conjunction to provide the interface-class implementation structure to VBA.
The code in the attached file is separated into 3 class objects (out of which 2 are implementation of the 1 interface) and 1 module object to illustrate the usage.

Code Explanation:
The attached file along with this post creates the following VBA structure:
1. IHuman (Interface)
2. clsMan (Class implementing IHuman)
3. clsWomen (Class implementing IHuman)
4. modTest (Module illustrating the usage of the class/interface)

The code also illustrates the additional method defined in the implementing classes and their usage patterns. Please explore the attached file VBA for the entire code.


Download Solution
Download solution


Late & Early Binding VBA

Recently I came across this term of bindings (Late/Early). Being a VBA developer for past few years and having worked with the concept numerous times, I realized lately it as Late/Early Binding. (I am bad with names and terms, I must confess)
So the concept goes as follows according to MSDN:

Early binding (MSDN):
An object is early bound when it is assigned to a variable declared to be of a specific object type

Sub test_LateEarlyBindings()

     'Early Binding
     Dim fs As Scripting.FileSystemObject

     Set fs = New Scripting.FileSystemObject

End Sub

Late Binding (MSDN):
An object is late bound when it is assigned to a variable declared to be of type Object.

Sub test_LateEarlyBindings()

    'Late Binding
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")

End Sub

The advantage of early binding over late binding is they allow compiler to allocate memory and perform optimization before an application executes. In addition to this when objects are declared explicitly as their types the VBA editor provide a helpful intellisense to assist the developer with object associated methods and properties, which becomes quite difficult in terms of late bound objects.


Then why use late binding ???

I came to know the utility of the late binding when I had to develop VBA based solutions which addressed users with varying office application versions (2003/2007).
Sub test_LateEarlyBindings()

    'Early Binding
    Dim outlookApp As Outlook.Application
    Set outlookApp = New Outlook.Application

End Sub

In the above code the instantiation of the Outlook object within VBA if flexible to work with different version of outlook installed on user PC (2003/2007) as this is instantiated as late bound object to the variable. This code relives the developer to explicitly add the outlook reference to the solution as it is picked up at run time and provides higher flexibility to the solution. This case was also sometimes used by myself when referencing the PDFCreator library for generating PDF reports.

Coding Tip (Late bound object):

As we discussed earlier since the late bound object doesn’t offer intellisense help while coding, I becomes tedious to develop the functionality and call upon the right name of methods and properties associated with the object.
A simple tip to overcome this problem is, initially develop the code as an early bound object and once the required functionality works well replace the object instantiation part by “CreateObject” method and uncheck the external references relating to the object from the reference library of the VBA project.


Monday, 17 October 2011

Excel – iTunes: Let’s account for our music

Recently I was bit lost managing my iTunes songs and its playlist. So I thought excel would lend me a hand to get hold of my music analytics. So here I have created an excel VBA based solution to get out my list of all the songs present in my iPod.

Additionally I have also created a utility to create playlist via VBA for my iPod. Hope this tool helps many others to enjoy their music excelly.  

Songs list from iPod to Excel Sheet

Creating Playlist in iPod from Excel

Result in iTunes


Download Solution
Download solution


MySQL: User Activity Tracking (Bandwidth)

Recently I was presented a question whether it would be possible on MySQL to track the user activity in terms of queries and the amount of data downloaded by the individual user from the database. 


Thus in the quest I came across a brilliant tool named “MySQL Proxy”. This tool provides a very strong functionality for MySQL database, which includes multiple MySQL server load balancing, query injection, and much more. This tool provided a very robust solution for the request with the usage of Lua scripting. Rather me extending more benefits about the tool in the post, I provide all the references below for the readers to explore more benefits of the tool themselves and here I would like to present my solution to record user activity in MySQL using MySQL Proxy with the following Lua script.

Note: This script also provides a section where user can be prohibited to download more data than the specified limit.
-- measures bandwidth by user = or {}

local session_user 
local sqlQuery
local before_bytes_sent = 0
local after_bytes_sent  = 0
local before_bytes_recevied = 0
local after_bytes_recevied = 0

function read_auth()
    session_user = proxy.connection.client.username[session_user] =[session_user] or 0

function read_query (packet )
    -- just to show how we can block a user query
    -- when the quota has been exceeded
    if[session_user] > 10000 
       and session_user ~= 'root'
        return error_result('you have exceeded your query quota')  
    sqlQuery = string.sub(packet, 2)[session_user ] =[session_user] + packet:len()

    proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "SHOW SESSION STATUS LIKE '%Bytes%'", {resultset_is_needed = true} )
    proxy.queries:append(2, packet,{resultset_is_needed = true})
    proxy.queries:append(3, string.char(proxy.COM_QUERY) .. "SHOW SESSION STATUS LIKE '%Bytes%'", {resultset_is_needed = true} )

    return proxy.PROXY_SEND_QUERY

function read_query_result(inj)
 if ( == 4) then
     return proxy.PROXY_IGNORE_RESULT
        if ( == 1) or ( == 3) then
                for row in inj.resultset.rows do
                        if (row[1] == "Bytes_sent") and ( == 1) then
        before_bytes_sent = row[2]
   if (row[1] == "Bytes_sent") and ( == 3) then
        after_bytes_sent = row[2]
   if (row[1] == "Bytes_received") and ( == 1) then
        before_bytes_recevied = row[2]
   if (row[1] == "Bytes_received") and ( == 3) then
        after_bytes_recevied = row[2]
             if ( == 3) then
     print("Bytes sent before: " .. before_bytes_sent)
     print("Bytes sent after: " .. after_bytes_sent)
     print("Bytes received before: " .. before_bytes_recevied)
     print("Bytes received after: " .. after_bytes_recevied)
     print("Net Bytes sent: " .. (after_bytes_sent - before_bytes_sent))
     print("Net Bytes received: " .. (after_bytes_recevied - before_bytes_recevied))
     print("Username: " .. session_user)
     print("Query: " .. sqlQuery) 
     print("DateTime: " .."%Y-%m-%d %H:%M:%S"))
     insert_log_query(session_user,"%Y-%m-%d %H:%M:%S"), sqlQuery, (after_bytes_sent - before_bytes_sent), (after_bytes_recevied - before_bytes_recevied))
                return proxy.PROXY_IGNORE_RESULT

function insert_log_query(username, date_time, query , net_bytes_sent, net_bytes_recived)
      print(username, date_time, query , net_bytes_sent, net_bytes_recived)
      proxy.queries:append(4, string.char(proxy.COM_QUERY) .. "INSERT INTO `employees`.`user_log` (`username`, `date_time`, `query`, `bytes_sent`, `bytes_recived`) VALUES ('" ..
      username .. "','" .. date_time .. "',\"" .. query .. "\"," ..  net_bytes_sent .. "," .. net_bytes_recived .. ");", {resultset_is_needed = true})
      return proxy.PROXY_SEND_QUERY 

This script uses the "SHOW SESSION STATUS" results for the recording of bytes sent and recevied. This also illustrates the power of MySQL Proxy which enables use to inject addtional queries to the database and process their results to our needs without any affects visible to the end user.


Download Solution
Download solution


Sunday, 16 October 2011

MySQL Large Table: Split OR Partitioning???

For couple of past months this question has puzzled me at my work. And seeking answers over the internet, I wasn’t able to find a specific answer to the question. So in this post I would only like to highlight my experiences so far with my decision, with no bias to any particular method.

Table Split Vs Partitioning, this decision should be primarily based on the context of usage pattern of the database and type of queries being executed on the database on regular basis/users of the database.

When to split table into smaller tables:
• If the queried table is being scanned on non regular columns (i.e. the queries “Where” clause always changes to different columns within the table)
• If the queries are analytical in nature and direct users of the database are business users.
• If the partition mechanism has to span more than 1024 partition (MySQL limitation)

The disadvantage of splitting the table into multiple tables, highlight problems relevant to querying the database upon multiple tables (with usage of dynamic SQL within stored procedures), complex logic, creation of large number of tables and further more. But these problems outweigh the benefits achieved for analytical purposes once the system is set, keeping it in simple terms with each query upon spliced tables has fewer rows to scan physically and hence forth the results are faster with union all’ed result presented and consistent across any column scan involved in the query.

When to partition a table:
• If the queries are mostly regular in nature or database acts as a backend to the business system (i.e. the majority of queries “Where” clause is using the same column for scan within the table).
• The use of database if limited to storing of records and retrieval of records on standard parameters (i.e. non analytical purposes).
• Where database is being utilized by ORM mechanisms like ADO.NET/Hibernate.
• Foreign keys are not supported on partitioned table.

The disadvantage of partitioned table within an analytical environment is some times more detrimental in terms of performance than the advantages it results into. This is due to the fact when the column scans is performed on the partitioned table upon which the table is not partitioned is employs mysql more effort to scan the each partition of the table for the results and query execution is slower than the table split. But also to mention in spite of the partitioning mechanism used one should also take care of the mechanism of “Partition Pruning” related to the where clause in the select queries illustrating the mysql which partitions to scan for the result.

Performance Results:

In the experiment table contains 28,44,042 rows with "from_date" being indexed:
Note: all the tables in the example are partitioned on the “from_date” column in the table. 

#**Simple Table **
CREATE TABLE `salaries` (
 `emp_no` INT(11) NOT NULL,
 `salary` INT(11) NOT NULL,
 `from_date` DATE NOT NULL,
 `to_date` DATE NOT NULL,
 PRIMARY KEY (`emp_no`, `from_date`),
 INDEX `emp_no` (`emp_no`)

#**Partition by month**
CREATE TABLE `salaries_copy` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`)
/*!50100 PARTITION BY HASH (Month(from_date))

#**Partition by Range**
CREATE TABLE `salaries_copy_1` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`)
/*!50100 PARTITION BY RANGE (to_days(from_date))
(PARTITION p0 VALUES LESS THAN (to_days('1985-01-01')) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (to_days(‘1986-01-01’)) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (to_days(‘1987-01-01’)) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (to_days('1988-01-01')) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (to_days('1989-01-01')) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (to_days('1990-01-01')) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (to_days('1991-01-01')) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (to_days('1992-01-01')) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (to_days('1993-01-01')) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (to_days('1994-01-01')) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (to_days('1995-01-01')) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (to_days('1996-01-01')) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (to_days('1997-01-01')) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN (to_days('1998-01-01')) ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN (to_days('1999-01-01')) ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN (to_days('2000-01-01')) ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN (to_days('2001-01-01')) ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (to_days('2002-01-01')) ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN (to_days('2003-01-01')) ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN (to_days('2004-01-01')) ENGINE = InnoDB,
 PARTITION p20 VALUES LESS THAN (to_days('2005-01-01')) ENGINE = InnoDB,

#********* Test 1: Queries scanning the partitioned column: from_date ***********
Select SQL_NO_CACHE * From salaries tbl
where tbl.from_date >= '2000-03-15' and tbl.from_date < '2000-09-25';
#Duration for 1 query: 0.016 sec. (+ 2.137 sec. network)

Select SQL_NO_CACHE * From salaries_copy tbl
where tbl.from_date >= '2000-03-15' and tbl.from_date < '2000-09-25';
#Duration for 1 query: 2.106 sec. (+ 5.288 sec. network)

Select SQL_NO_CACHE * From salaries_copy_1 tbl
where tbl.from_date >= '2000-03-15' and tbl.from_date < '2000-09-25';
#Duration for 1 query: 0.063 sec. (+ 1.185 sec. network)

Select SQL_NO_CACHE * From salaries_1985
where salaries_1985.from_date >= '2000-03-15' and salaries_1985.from_date < '2000-09-25'
Select * From salaries_1986
where salaries_1986.from_date >= '2000-03-15' and salaries_1986.from_date < '2000-09-25'
Select * From salaries_2005
where salaries_2005.from_date >= '2000-03-15' and salaries_2005.from_date < '2000-09-25';
#Duration for 1 queries: 1.638 sec. (+ 0.484 sec. network)

#********* Test 2: Queries scanning the non partitioned column: to_date ***********
Select SQL_NO_CACHE * From salaries tbl
where tbl.to_date >= '2000-03-15' and tbl.to_date < '2000-09-25';
#Duration for 1 query: 0.109 sec. (+ 2.762 sec. network)

Select SQL_NO_CACHE * From salaries_copy tbl
where tbl.to_date >= '2000-03-15' and tbl.to_date < '2000-09-25';
#Duration for 1 query: 1.201 sec. (+ 6.521 sec. network)

Select SQL_NO_CACHE * From salaries_copy_1 tbl
where tbl.to_date >= '2000-03-15' and tbl.to_date < '2000-09-25';
#Duration for 1 query: 7.472 sec. (+ 3.058 sec. network)

Select SQL_NO_CACHE * From salaries_1985
where salaries_1985.to_date >= '2000-03-15' and salaries_1985.to_date < '2000-09-25'
Select * From salaries_1986
where salaries_1986.to_date >= '2000-03-15' and salaries_1986.to_date < '2000-09-25'
Select * From salaries_2005
where salaries_2005.to_date >= '2000-03-15' and salaries_2005.to_date < '2000-09-25';
#Duration for 1 query: 1.670 sec. (+ 0.483 sec. network)

Comparison Table:

Query on “from_date Query on “to_date Description
Indexed column Non-Indexed column -
0.016 sec. (+ 2.137 sec. network) 0.109 sec. (+ 2.762 sec. network) Simple table
2.106 sec. (+ 5.288 sec. network) 1.201 sec. (+ 6.521 sec. network) Partition by HASH (Month(from_date))
0.063 sec. (+ 1.185 sec. network) 7.472 sec. (+ 3.058 sec. network) Partition by RANGE (to_days(from_date))
1.638 sec. (+ 0.484 sec. network) 1.670 sec. (+ 0.483 sec. network) Table Split by year(from_date)

Though I do not wish to end this trail in here and I would like to know reader opinions and thoughts about this topic and shed more light whether the table split is better than partitioning or vice versa.

Look forward to your comments …


Thursday, 6 October 2011

Dynamic VBA ... Why.... ???

Recently i came up with a thought in my free mind, if we can use dynamic SQL for many kinds of SQL servers, there must be a way to execute the dynamic VBA. But then lately i am very confused as how can and it would be useful in any scenario. As we can have multiple alternatives than to dynamically plug the VBA code in and execute it.

But any ways here the solution.. if some one please find a sensible usage of this method please leave a comment for me to know its utility. 

Download Solution
Download solution


Tuesday, 4 October 2011

VBA tooo WCF tooo C#

Recently I came across an issue where a VBA application model had to trigger a job in remotely running C# application. Suddenly polling via DB and creating a queue based mechanism was on cards, but this process involves so many elements and is resource intensive. So I thought there should be a better mechanism for the same.

And WCF was to answer my problem. I have seen some solutions online but was based on the WCF as a service but in my context I had to have a win form/console application and VBA had to trigger a job in those applications based remotely.

So I ported the service based solution to my needs and below the code illustrated and sample files for the C# and VBA solution.

(Note: Sometimes if the VBA call fails please instead of localhost use the ip-address of the PC on which the C# application is running.)

Sub testWcf()

Dim addr As String
Dim i As Long
addr = "service:mexAddress=""net.tcp://localhost:9001/hello/mex"","
addr = addr + "address=""net.tcp://localhost:9001/hello"","
addr = addr + "contract=""IHelloWorldService"", contractNamespace="""","
addr = addr + "binding=""NetTcpBinding_IHelloWorldService"", bindingNamespace="""""

Dim service1 As Object
Set service1 = GetObject(addr)

For i = 0 To 10

Debug.Print service1.SayHello("Test Message: " & i)

Next i

End Sub

And the associated C# model is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel.Channels;
using System.Text;
using System.ServiceModel;
using System.ServiceModel.Description;

namespace ConsoleApplication1

    public interface IHelloWorldService
        string SayHello(string name);

    public class HelloWorldService : IHelloWorldService
        public string SayHello(string name)
            Console.WriteLine("The param passed is :" + name);
            return string.Format("Hello {0}", name);

    class Program

        static void Main(string[] args)
            BindingElement bindingElement =
                new TcpTransportBindingElement();
            CustomBinding binding =
               new CustomBinding(bindingElement);
            Uri tcpBaseAddress =
               new Uri("net.tcp://localhost:9001/hello");
            ServiceHost host =
               new ServiceHost(typeof(HelloWorldService), tcpBaseAddress);
            ServiceMetadataBehavior metadataBehavior;
            metadataBehavior =
            if (metadataBehavior == null)
                metadataBehavior = new ServiceMetadataBehavior();
            host.AddServiceEndpoint(typeof (IHelloWorldService), new NetTcpBinding(), tcpBaseAddress);
               typeof(IMetadataExchange), binding, "MEX");
            Console.WriteLine("Server started @." + tcpBaseAddress);

Download Solution
Download solution 


Wednesday, 29 June 2011

Meeting Roomss... Lets book them - Outlook Meeting Room Finder

Hi today i would like to introduce to a very handy tool which me and my office personnel's are quite linking.
Recently i joined my new office and its quite beautiful, but i soon found big beautiful things have some problems which VBA can solve in many times. 

The issue is we have 20-30 meeting rooms in the office and they all can be booked via sending the appointment item to a certain patterned email address each unique for individual meeting room's.

To cut short the story this tool analyses all the meeting room's shared calender selected on the basis of the patterned email address assigned to each one of them. And it collate a list scanning those shared calenders illustrating the time periods between which the required time slot (duration input by user in mins) can be booked and in which meeting room the appointment can be booked.

This entire code is written in VBA within outlook. Give this handy tool a check and alter it to your requirements and make it work for you so that you are never late in you next appointment.

Also the code includes a feature for minimizing the user form in VBA.

Download Solution
Download solution

Outlook Calender Scan:
UserForm Minimize:

Saturday, 5 March 2011

SSH Tunneling (Port Forward via VBA) W/O PUTTY

Recently i faced a situation where I had to connect to MySQL server via VBA to extract the data for reporting.
(Sounds simple .. Think again..)

Standing inbetween was a technique commonly used in banks and secure insitutuions to establish secure communication to remote location called "Tunneling". In order to tunnel to remote location for establising MySQL database connection (via technique called PORT Forwarding) in which a local port of YOUR PC is MAPPED to connect to remote location port via TUNNEL using tools like PUTTY.


Let me make it simple:

1. I need to connect to MySQL DB located in remote location via VBA.
2. The remote location details for MySQL server are as follows:

Remote Server IP :
Remote MYSQL Port: 3306 (Standard MySQL Port)

3. I open putty and connect to remote server via PUTTY with password verification and forwarding my local port to Map to remote port

Local Mapped Port : 8585 (Can be any number)

This local port now represent the MySQL service on remote machine via secure tunnelled channel on which we would be required to obtain the data.

Now my trouble was the existing practise of invoking the Putty via VBA Shell  command for establishing the channel and killing the process for closing the same was unreliable as there is no way to confirm if the PORT forward had been established unless via On Error cluases in VBA for connection open menthod.

In order to over come this issue i exposed the methods from C# library (Tamir Gal SSH.NET) to VBA and also provided a Fix for the library usage for the corporate environment in which the Admin priveliges are least granted for the users to deploy this solution. This solution is deplyed and useable in the folllowing manner:

1. Save the complete unzipped folder to your C:\ drive.

2. Run the included .reg file in the package to create the entries for the dll in the registry (where in the secure corporate environments where access to the local machine branch of registry is prohibited 'HKLM' this .reg file registers the classes in the 'HKCU' branch this allowing easy deployment for secure environments.

3. Now from the VBA editior the library can be refrenced as Tools -> Refrences

4. And can be used before the connection open to establish the PORT forward via boolean to indicate the sucess/failure of the operation.

Sub TunnelExample()
    Dim SSh As SSHTunnelVBA.VBATunnel
    Dim bln As Boolean

    Set SSh = New SSHTunnelVBA.VBATunnel

    bln = SSh.TunnelConnect("UserName", "Password", "Source/RemoteIP Address",
 "LocalPort No to Forward", "Local Port IP", "Remote Port to map")

    If bln Then

        'Port forwarded succesfully.


        'Port forwarded error.

    End If

    bln = SSh.TunnelDisConnect

End Sub

5. And finally the tunnel can be disconneted via following command again returning boolean.

I hope you would find this solution very useful as it does its job very well and takes away the complexity of PUTTY shell commands via VBA and unreliability making our VBA code more robust, clean and secure.

Download Solution
Download solution

Tunneling :
Port Forwarding :
Putty :

Monday, 7 February 2011

HTTP File Download using VBA (Password Protected Site)

Recently I embarked on a project involving the file download from a password protected site (http location), though with some troubles to get the Internet Transfer Controls setup on my PC. I found the following solution amazingly brilliant to complete the task.

The following solution uses the Internet Transfer Control object to navigate to the http location using the authentication protocols and details and obtains the files in byte format which is then placed at the location of your choice using the file operation in VBA.

The code is illustrated as follows:

Sub HTTPDownloadFile(ByVal URL As String, ByVal LocalFileName As String)
    Dim HTTP As Inet
    Dim Contents() As Byte

    Set HTTP = New Inet
    With HTTP
        .Protocol = icHTTP
        .URL = URL
        .UserName = "**UserName**"
        .Password = "**Password**"
        Contents() = .OpenURL(.URL, icByteArray)
    End With
    Set HTTP = Nothing

    Open LocalFileName For Binary Access Write As #1
    Put #1, , Contents()
    Close #1
End Sub

Note: To install the Internet Transfer Control for the Vista x64 , Just copy and paste the "msinet.ocx" file into the "C:\Windows\SysWow64" folder and run the "regsvr32" which should successfully install the Internet transfer control on your system.

Office One:

SubVersion : VBA Code (Export VBA to text files..)

I would like to wish all my blog readers a very happy and a prosperous New Year. I am posting this post after a while, as recently in the past month I was on my new career move in a new role.
Recently upon joining, I had the opportunity to design the office application setup on board the department. Among which one of the key issue came by was to maintain the VBA code in subversion and use subversion capabilities of code compare to its fullest which requires code to be in clear text file. And as XL save as everything in binary it was little tough to code compare the binaries.. !!

So the solution was to design a tool to export all the VBA code modules in text format which could be easily then imported back into excel.

The attached tool takes in the path of the folder from where the source files of excel whose code needs to be extracted, along with the path navigating the destination location for the code exported to be saved. And clicking on the “Extract Code..” iterates all the excel files present in the source folder location and correspondingly creates folder in the destination location with the exported set of code modules within the relevant corresponding folders in relation to the source file names.

Hope this tool helps you to, utilize the full potential of Sub versioning VBA…

Download Solution
Download solution