SQL Database Performance Tuning for Developers

Hi, Recently I came across a great post about SQL Performance Tuning Tips n Tricks, which outlines a good deal of information across all the common features we leverage off in our day to day business.

SQL Database Performance Tuning for Developers

This post has helped me also to gain insights into some of the areas of temp DB sql as well.

Hope it helps.

Model View Controller (MVC) implementation in Excel VBA

With this post I would like to illustrate the concept of Model View Controller (MVC) design pattern implemented in excel VBA with a simple and easy to understand example.

The model view controller design pattern has a benefit which provides separation of concern objectively while designing the solution. With this technique in hand, the data is modelled as an object and the form in which is represented is also modelled as object with relevant functions provided for the operations required, which is orchestrated by a controller class.


 

The attached example is a crude implementation of MVC a quick attempt of mine to achieve the same, though I do accept that this piece of code is far from perfection and has still room to improve upon.

Explanation:

The workbook has simple worksheet which on one side has form to display the data (left) and on (right) a table storing multiple values for the same. The workbook has two functionalities provided to get the relevant data based on the serial number provided and update functionality to update the corresponding the record.


 

Please check the workbook VBA code for MVC implementation details.

Download:


Download solution



Refrences:
Link: http://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller

Unlimited Function Arguments – ParamArray in VBA

Recently I stumbled upon an interesting functionality provided by VBA, termed by ‘ParamArray’ keyword.

This key word is used in to send unknown number of arguments in the form of variant array to the function/sub-procedure in VBA.

This can be illustrated via following code:

Option Explicit

'The following VBA illustrates the use of ParamArray functionality illustrated by a
'functiuon returning the count of arguments passed to the function.

Function param_array_function(ParamArray Args() As Variant) As Integer

    Dim iCount As Integer
    iCount = UBound(Args)
    param_array_function = IIf(iCount < 0, 0, iCount + 1)

End Function


Sub Test_Proc()

Debug.Print "Args :" & param_array_function(1, 2, 3, 4, 5)
Debug.Print "Args :" & param_array_function("amol", "pandey", "excel", "vba")
Debug.Print "Args :" & param_array_function(1, 2, 3)
Debug.Print "Args :" & param_array_function(1, 2)

End Sub

This functionality can be very handy to design function/sub-procedures which are generic enough to take in multiple arguments previously unknown and provides an alternative to send in arguments previously being packed in array/collection for the same purposes.

Refrences:
Link: http://www.cpearson.com/excel/optionalargumentstoprocedures.aspx

Time Slide Chart In Excel

Recently out of curiosity, I wanted to explore a time series data relationship with itself visually on a varying time scale. Hence, I wanted a time series plotted twice on the same chart, but one being flexible (sliding) to slide upon another (fixed).

These are the series of the following charts, I wanted to build in which it is clear that the Value (Blue) is fixed upon which the sliding series is the Slide Value (Grey):


 


 


 


 



The attached spreadsheet illustrates the complete mechanism of building the same, it uses couple of excel formulas and a form control slider. The slider in the workbook allows the flexibility to slide the time series in both forward and backward equally to the original count of the series points available. Additionally in spreadsheet there are no macros, all the functionality is built via default excel provided formulas and also had to use couple of na() formulas for chart to appear as required.

This technique can also be used to analyse additional statistics based upon sliding series. E.g. Max, Min, Average, Standard Deviation and more…

Feel free to contact me for more info upon its design.

Download:


Download solution