Tuesday, July 29, 2014

Running Total

Often when you are creating reports you want to do the Running Total  eg. Sum = Current Sum + (Sum of all the preceding values).

There are many ways to do this. I just wanted to outline some of the standard techniques used to do this and the new feature in 2012 which makes it very easy and fast.

1) Using Non-Equi Joins

Select A.PhysicalFileId,A.BytesWritten,A.CreatedDate, 
Sum(B.BytesWritten)as RunningTotal from #X A
inner join #X B  on B.PhysicalFileId <= A.PhysicalFileId
group by A.PhysicalFileId, A.BytesWritten,A.CreatedDate
2) My personal favorite -- Correlated Query

Select A.PhysicalFileId, A.BytesWritten,A.CreatedDate , 
(Select Sum(B.BytesWritten) from #X B where B.PhysicalFileId <= A.PhysicalFileId) as RunningTotal
from #X  A
3) Available in SQL 2012 and beyond and fastest of all, using Window Functions

Select A.PhysicalFileId,A.BytesWritten,A.CreatedDate, Sum(A.BytesWritten) Over( 
Order by A.PhysicalFileId
) As RunningTotal from #X AS A

You can find the sql queries at :: http://sqlfiddle.com/#!6/40d6e/3/0

Until next time!

-Yash

Sunday, July 27, 2014

Pivot table and its uses

Recently I started working on a BI project, which is a new domain for me. I always wanted to work in a Business Intelligence project since that was one area I didn't cover. I have been learning lot of new concepts which are interesting. We generate lot of reports which are used by the management teams to make proper business decisions and for that we use Self Service Power BI. If you have done reporting you will know, Pivot table is one of the most fundamental way of generating Cross-Tabular Reports.

Pivot tables are basically used for 2 purpose

1) To generate cross tabular reporting i.e converting the rows into columns.
2) With Entity-Attribute table ( which is a Key-Value kind of table, like a dictionary)

As an example ::  I have 2 table with one to many relationship, which are used to record Api Success/Failure .

[dbo].[ApiLookUp]  (1) 
[dbo].[ApiHealthMonitoring]   (Many)

You can see the Table Schema and Queries here  :: http://sqlfiddle.com/#!6/d5fbf/3

The best way in my opinion to write Pivot query is to follow these steps

1) Write the Base query i.e the table for which you want to convert rows into columns .
     This becomes your Base Query
2) Select the Aggregate Column from one of the columns of the table, created by the Base query, for the columns you want to pivot on.
3) Select the Columns as the output of the  Pivot Query









One thing worth mentioning about Pivot table is  that Columns in the Pivot table need to be hardcoded. So if the values of the Column on which you are pivoting then you need to either change the query frequently or use dynamic sql.

This same thing can be achieved very easily using the Excel - Power Pivot feature. It is very powerful and simple to use. All you need to do, is to import the tables from the Sql Server Database. Add a pivot table and select the appropriate columns. You can even generate interactive graphs and publish it to SharePoint using power query and power pivot. The deployment story for Excel reports is very powerful and easy to use by even non programmers.

You can also add slicers to the table which help in slicing/dicing of the data. Pretty powerful.








Until next time!

-Yash





Sunday, April 6, 2014

Love for Powershell!

This is a quick blog entry, to appreciate my love for powershell. I have been running into Low disk space on my work computer, and I wanted to fix this issue.

Powershell to the rescue!

Simple powershell command and I was able to get the files which is occupying max disk space and then go from there.

Get-ChildItem -Path C:\ -Recurse | Where-Object {$_.PSIsContainer -eq $false} | 
sort-Object -Property Length -Descending |
Select-Object -First 10 | 
Format-List -Property FullName,Length,
@{name = "SizeInMB";expression = {$_.Length / 1MB}}

I have always been a commadline guy and I really enjoy writing small useful scripts!


Thanks,
Yash

Saturday, January 11, 2014

AutoResetEvent And ManualResetEvent


AutoResetEvent and ManualResetEvent are one of the low-level Thread signaling mechanism in C#. The main reason for writing this blog entry is to differentiate between these 2 WaitHandles.

AutoResetEvent is the like the devices you see in Airport parkings, where you punch you card, and then you can get in, but as soon as you get in, entry closes. The next person has to again punch the card.
You can see from the output of the program, there are 2 Threads and both are waiting on a Set Signal. Since only 1 set signal is received, only one thread can resume the operation while the second thread continues to wait.

ManualResetEvent are like a door. Once you open the door, the door is kept open till someone closes it. So in the code below, both theads are waiting on a Set signal to resume there operation. As soon as the Set Singnal is received, both the thread resume there operations.
       
        static void Main(string[] args)
        {
            ManualResetEvent manualResetEvent = new ManualResetEvent(false);
            AutoResetEvent autoResetEvent = new AutoResetEvent(false);

            Thread t1 = new Thread(() =>
            {
                Thread.Sleep(1000); for (int i = 0; i < 10; i++)
                {
                    Console.WriteLine(i);
                    if (i == 5)
                    {
                        Console.WriteLine("Waiting to be signalled -- Thread 1");
                        //manualResetEvent.WaitOne();
                        autoResetEvent.WaitOne();
                        Console.WriteLine("Signal Received, continuing -- Thread 1" );
                    }
                }
            });
            t1.Start();

            Thread t2 = new Thread(() =>
            {
                Thread.Sleep(1000); for (int i = 101; i < 120; i++)
                {
                    Console.WriteLine(i);
                    if (i == 110)
                    {
                        Console.WriteLine("Waiting to be signalled -- Thread 2");
                        //manualResetEvent.WaitOne();
                        autoResetEvent.WaitOne();
                        Console.WriteLine("Signal Received, continuing -- Thread 2");
                    }
                }
            });
            t2.Start();


            Thread.Sleep(1000);
            Console.WriteLine("In the Main function");
            Thread.Sleep(1000);
            //manualResetEvent.Set();
            autoResetEvent.Set();
            //autoResetEvent.Reset();
            Console.ReadLine();
        }

Tuesday, December 10, 2013

Saturday, September 14, 2013

LogParser and IIS Logs

Sometimes in production environment, when you are debugging some issue and you want to get something from a  IIS logs it becomes difficult to get it because you have so many machines in prod.

I use logparser and powershell together to get the results from all the machines like below. Logparser is a great tool and it pretty fast!!.

IIS Logs are stored by the Site Id. i.e if you go to the advancesSettings of a particular website, you can see a ID column. that's how the folders are arranged in IIS log directory.



$machines = @('Yash000226','Yash000227','Yash000228','Yash000229','Yash000220',
'Yash000231','Yash000232','Yash000226')

foreach ($machine in $machines)
{
$path = "\\$($machine)\d$\data\Logs\IISLogs\W3SVC1\u_ex*.log"

.\LogParser.exe -i:IISW3C -e:50 -o:CSV file:..\Query.sql?path=$($path)+url=/admin/services/configdelta.aspx -stats:off >> output.csv
 
}


Queries ::  Put the below query in a separate file.

sc -->  Server to Client
cs --> Client to Server

date  -- Date
time  -- Time
s-ip  -- Server IP Address
 cs-method -- Client to server Method
 cs-uri-stem  -- Client to server uri stem  for eg /a.aspx etc
cs-uri-query  -- Client to Server Uri query string
s-port -- server port
cs-username -- client to server username
 c-ip -- client ip address
cs(User-Agent) -- client user agent string
 sc-status -- server to client HTTP Status code
sc-substatus  -- server to client HTTP Sub status code
sc-win32-status -- server to client win32 status code
 time-taken -- timetaken to process the request

select sc-status as httpstatusCode,sc-substatus as httpSubStatusCode
from %path%
where (to_lowercase(cs-uri-stem) = to_lowercase('%url%'))
Hope it helps someone!

-Yash

Sunday, September 8, 2013

Random Stuff worth remembering

These are just something's you need to keep in mind while coming up with a SQL query and not just run for joins :)

1) While doing aggregates (SUM,COUNT,AVG), and if you are doing SQL Joins, you need to careful about getting duplicate rows.
2) Figure out the tables that will give you information, then figure out if you need joins, pay attention to inner or outer joins.
3) Pay attention to GroupBy and Ranking Functions in SQL.
4) You can use different techniques like
    a) Sub Query
    b) Correlated Subquery in the Where Clause or Select Clause
    c) Temp Table approach
    d) Derived Table approach

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

Triggers are an very useful feature provided by SQL Server. These can be used to great effect for Auditing purpose.  For eg : To find out who changed a certain value. In this case you can use an Trigger and insert the previous and new values in the Auditing table etc.

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

Performance Counter ::

When you are debugging an esoteric issue , you can take the help of Performance Counter.  Add the required set of performance counter that you need for debugging the issue for eg: Page Splits, Cache Hits, CPU usage, Memory counters and see for any unusual behaviors.

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

When you are doing SQL updates on a table, which is heavily used always try to do it in batches, this will help avoid SQL Deadlocks. 

Something like this might help to put it in a loop.
Declare @Some Table
(
  SomeId int
)

-- Extract the data and put it in temp table
 INSERT INTO @Jobs
Select X from Y where X.A= B

WHILE EXISTS (SELECT * FROM @Jobs)
BEGIN
 SET @SomeId = (Select top(1) SomeId from @Some)
-- Do your operations here
DELETE FROM @SomeWHERE SomeId= @SomeId
END

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