Difference between Delete and Truncate command in SQL

Today, I am going to discuss about the facts behind the delete query and difference between delete query and truncate query.

Now you will come to know about why truncate query is faster than the delete query and what happens when you fire a delete query on a table.

DELETE

Let me first brief you about delete query, Delete command is used to delete rows from the table you can also filter rows to delete from the table using where clause.

Syntax is shared below:

Delete from users where name = ‘vishal’

Where users is table name and  name is column of the table and using where clause I am filtering rows from users to delete from the table with name vishal.

And if query is fired without any where clause all the rows from the table will be deleted after delete command you need to fire rollback or commit to make the changes permanent or undo it.

Note: this command will cause all the delete triggers to fire on the table.

Truncate

In case of truncate command all the rows from the table are removed and we cannot rollback the changes on the table because no DML triggers are fired on to the table.

Truncate is a DDL command whereas Delete is DML command and truncate is much faster than delete command.

Reason:

When you fire delete command all the data gets copied to Rollback tablespace first then delete operation gets performed that is why when you fire rollback you get data back (the system get it from the rollback table space) . All this process takes time but when you fire truncate command it removes data directly from the table and you cannot retrieve data back from the rollback table space. That is why truncate is faster than delete command.

 

 

 

Concurrency in Java

Concurrent collections are introduced in java 5 to increase scalable multithreaded java applications.

Prior to java 5, if you have to deal with collections in multithreaded application you either used synchronized collections or regular collections.

synchronized

  • HashTable
  • Vector

Regular

  • Regular
  • HashMap

Both of these can be synchronized where HashTable and vector are already synchronized and arrayList and HashMap can be synchronized using utility class under java.collections package.

Before java 5, if one thread is reading a collection resource and the same time when second thread tries to update it then you get “ConcurrentModificationException” which is not desirable.

To overcome this problem, the concept of concurrency come into picture. It provides the following methods:

  • Concurrent HashMap.
  • CopyOnWriteArrayList
  • CopyOnWriteArraySet

ConcurrentHashMap uses concept of lock stripping so while one thread is iterating the collection and another  thread is updating the collection, they will sync up at some point.

Where, CopyOnWriteArrayList  and CopyOnWriteArrayList keeping cloning the collection every time when a write is made to the collection and then at some point they will sync up.

So using concurrent collections, we can use collections across multiple threads.

Note: use concurrent collections when you are using less writes and more read through the collection object.

Disadvantages :

When you are using a lot of writes in application you will clone every time the collection object which will degrade the performance of the program.

Why we need MVC in ASP.NET

Before reading what ASP.NET MVC is, it’s better to understand why Microsoft has introduced MVC. Let’s try to understand this by comparing MVC and Webforms.

ASP.NET MVC vs WebForms

Success of Microsoft programming languages is due to RAD (Rapid application development) and visual programming approach. Visual programming was so much addressed and successful in Microsoft that literally they named their IDE’s as “Visual Studio”.

By using visual studio, developers where able to drag drop UI elements on a designer area and visual studio generates C# or VB.NET code for those elements. These codes where termed as “Behind Code” or “Code Behind”. In this code behind Developers can go and write logic to manipulate the UI elements.

So the Visual RAD architecture of Microsoft has Two Things.

  • UI
  • Code Behind

What are the problems with Asp.Net Web Forms?

So when ASP.NET Webform was so successful, why Microsoft thought of creating ASP.NET MVC. The main problem with ASP.NET Webform is performance, performance and performance.

In web application there are two aspects which define performance.

  • Response time: – How fast the server responds to request?
  • Bandwidth consumption: – How much data is sent?

Response time issues:-

Why response time is slower, when it comes to ASP.NET Webforms. Consider the below UI code and Code behind for that UI.

<asp:TextBox ID="TextBox1"  runat="server"></asp:TextBox >

In the code behind you have written some logic which manipulates the text box values and the back ground color.

protected void Page_Load(object sender, EventArgs e)

{
 TextBox1.Text = "Make it simple"; TextBox1.BackColor = Color.Aqua;
}


See the HTML output by doing view source it looks something as shown below.

<input name="TextBox1" type="text" value="Make it simple" id="TextBox1" style="background-color:Aqua;" />

Now the points of discussion are:

  • Is this a efficient way of generating HTML? Do we really need to make those long server trips to get that simple HTML on the browser?
  • Can’t the developer write HTML straight forward, is it so tough?

For every request there is a conversion logic which runs and converts the server controls to HTML output. This conversion gets worse and heavy when we have grids, tree view controls etc., where the HTML outputs are complicated HTML tables.

Due to this unnecessary conversion the response time get affected.

Solution for this problem: – “GET RID of CODE BEHIND”, means work with pure HTML.

Bandwidth Consumption Issue.

Viewstate has been a very dear and near friend of ASP.NET developers, because it automatically saves states between post backs and reduces our development time. But this reduction in development time comes at a huge cost as it increases the page size considerably.

The size increases, because of extra bytes generated from viewstate. Lot of people can argue that viewstate can be disabled but then we all know how developers are; if there is option given they would definitely try that out.

Solution for this problem: – “GET RID of SERVER CONTROLS”.

HTML Customization:-

In ASP.NET web server controls, we have “NO IDEA” what kind of HTML can come out and how efficient they are. For example see the below ASPX code, can you guess what kind of HTML it will generate.

<asp:Label ID="Label1" runat="server" Text="I am label"> <asp:Literal ID="Literal1" runat="server" Text="I am a literal"> <asp:Panel ID="Panel1" runat="server">I am a panel

Will Label generate DIV tag or SPAN tag? If you run the above code below are the respective generated HTML.

Label generates a SPAN,

Literal generates simple text,

Panel generates DIV tag and so on.

<span id="Label1">I am label</span> I am a literal

I am a panel

So rather than generating HTML using server controls, how about writing HTML directly and taking complete control of HTML.

Solution for this problem is “DO NOT USE SERVER CONTROLS” and work with direct HTML.

The other great benefit of working directly with HTML is that your web designers can work very closely with the developer team. They can take the HTML code put in their favorite designer tool like dream weaver, front page etc. and design independently.

Important Point to understand:-

If you watch any professional ASP.NET Webform project you will notice that code behind class is where you have huge amount of code and the code is really complicated. Now this code behind page class inherits from “System.Web.UI.Page” class. This class is not a normal class which can be reused and instantiated anywhere. In other words you can never do something as shown below for a Webform class:-

WebForm1 obj = new WebForm1(); obj.Button1_Click();

Because, the “WebForm” class cannot instantiate without “request” and “response” object. If you have ever seen the “ButtonClick” events of “WebForm” they are as shown in the code below.

protected void Button1_Click(object sender, EventArgs e)

{

// The logic which you want to reuse and invoke

}

Solution is we need to move the code behind to a separate simple class library and get rid of ASP.NET Server controls and write simple HTML.

How Microsoft Asp.Net MVC tackles problems in Web Forms?

This is the current WebForm architecture which developers are using in its mostly 3 layer architecture. This three layer architecture comprises of UI which has ASPX and the CS code behind.

This UI talk’s with .NET classes which you can term as middle layer, business logic etc. and the middle layer talks with data access layer (DAL).

So Asp.Net MVC comprises of three sections Model, View and Controller(MVC).

  • The code behind logic goes in to the controller.
  • View is your ASPX i.e. pure HTML.
  • Model is your middle layer.

So if you see there are two major changes VIEW becoming simple HTML and code behind moving to simple .NET classes termed as controller.

Asp.Net MVC request flow in general.

Step 1:- The first hit comes to the controller.

Step 2:- Depending on the action, controller creates the object of the Model. Model in turn calls the data access layer which fetches data for the Model.

Step 3:- Fetched data filled in Model is then passed to the view for display purpose.

GDP (Gross Domestic Product)

Values of all the goods and services produced within the country and also termed as, Measure of market value of all the final goods and service produced in a period. GDP value comes on the quarterly basis and on yearly basis GDP is described for whole year.

Statistician say, when a country’s GDP is high it means that the country is increasing the amount of production that is taking place in the economy and the citizens have a higher income and hence are spending more.

 

GDP is abbreviated in three parts as,

1. Gross 2. Domestic 3. Product

Gross

In the production of goods there are several factors such as-:

1. Labour 2. Machinery 3. Land 4. Building

All the above factors excluded in the pricing of goods then the final cost that remains out of this is called as Gross.

Domestic

Value of all the goods and services produced within the country. Income from abroad is excluded.

Product

Product is the goods and services itself.

Why we need GDP

GDP estimates are commonly used to determine the economic performance of a whole country or region and to make international comparisons. Therefore using a GDP per capita basis is arguably more useful when comparing differences in living standards between various nations.

How to calculate GDP of a country

GDP can be determined in three ways -:

Production approach

  • Estimate the gross value of domestic output out of the many various economic activities.
  • Determines the intermediate consumption i.e. the cost of material, supplies and services used to produce final goods or services.

Income approach

  • The sum of primary incomes distributed by resident producer units.
  • Hire – wages for Labour, interest for capital, rent for land and profits for entrepreneurship.

Expenditure accounts

  • Wages, salaries and supplementary Labour income.
  • Corporate profits.
  • Interest and miscellaneous investment income
  • Farmer’s incomes
  • Income from non-farm unincorporated business

GDP is sum of Consumption(c), Investment (I), Government spending (G) and Net Exports (X M).

Y = C + I + G + [X – M]

C – Consumption

  • Examples include food, rent, jewelry, gasoline and medical expenses.

I – Investment

  • Includes, for instance, business investment in equipment

G – Government spending

  • Is the sum of government expenditures on final goods and services.
  • It includes salaries of public servants, purchases of weapons for the military and any investment expenditure by a government.

X – Exports

  • Represents gross exports.

M – Imports

  • Represents gross imports

Imports are subtracted since imported goods will be included in terms of G, I or C and must be deducted to avoid counting foreign supply as domestic.

 

OSI Reference Model

OSI (Open Systems Interconnection) is a reference model to define application communication flow over a network and is also defined as conceptual framework for understanding relationships.

In other words, to divide the interrelated aspects of Network operation into less complex operations and to defines standard interfaces to achieve compatibility and multi vendor integration.

Layer 7 Application
Layer 6 Presentation
Layer 5 Session
Layer 4 Transport
Layer 3 Network
Layer 2 Data Link
Layer 1 Physical

 

Application Layer:

  • Provides interface between OSI RM and end user application.
  • Provides network services to user client/server-based application.
  • Establishes and defines program-to-program communication.
  • Identifies availability of intended communication partner.
  • Examples include ftp, tftp, http, www browsers, DNS, SMTP, telnet.

Presentation Layer

  • Defines data format for transmission.
  • Ensures arriving data from the network can be transmitted by the application and information sent by the application can be transmitted on the network.
  • Performs encryption and decryption.
  • Example representation includes ASCII, JPEG, HTML etc.

Session layer

  • Defines how to strat, control and end session.
  • RPCs (Remote Procedure Call) operate at this layer.
  • Logon validation happens at this layer.
  • Named Pipes defined at this layer – Named Pipes use TCP to guarantee communication.
  • Session layer organizes communication through simple, half-duplex or full-duplex.
  • Example protocols include SQL, RPC, Named Pipes etc.

Transport Layer

  • Segmented data to be passed down to Network layer and reassembles data for session and upper layers.
  •  Provide the choice of connection-oriented and guaranteed (TCP) or connectionless and non-guaranteed(UDP) delivery of data .
  • Provides end to end transport services.
  • Provides flow control to overcome congestion in network.
  • Provides 3 flow control mechanism:
  1. Buffering: Each computer has enough buffer space to hold data before it os processed.
  2. Congestion Avoidance: Receiving computer notices its buffers are filling quickly and sends a stop message to the sending host to temporarily stop transmitting while it processes data already received. Then signals that it is ready for more data. Eg. Protocols – Synchronous Data Link Control (SDLC), Link Access Procedure, Balanced (LAPB), ICMP Source Quench (slows down rate instead of stopping it).
  3. Windowing: Defines maximum number of bytes it is willing to send at one time before an acknowledgement is expected.

Network Layer

  • Defines the network address.
  • Routers operate at this layer.
  • Segments from the transport layer are placed into and passed down to Data Link Layer.
  • Network layer routes data from one node to another.
  • Determines the best path/ route to destination device to use for routing data on the inter network- this is done using hop count or tick where 1 tick = 1/18th of a second.
  • Network layer maintains routing table.

Data Link Layer

  • Provides error- free link between 2 devices- CRC used for error checking.
  • Packets from the Network layer are placed into frames.
  • Data Link layer handles physical transmission of data from one node to another.
  • Handles error notification.
  • IEEE subdivided this layer into 2 sub layers
  1. Logical Link Control (LLC): Uses destination Service Access Point (DSAP) and Source Service Access Points (SSAP) to help lower protocols access Network layer protocols.
  2. Media Access Control (MAC) : build frames from bits performs CRC Handles MAC address- first digits of 12 hex defines vendor Id, next 6 defines serial no of that vendor Id.

Physical Layer

  • Defines connections – RJ-45, RJ-11, BNC etc.
  • Places Frames, represented as bits, onto media as electric signals or pulses of light.
  • Hubs are repeaters operate at this layer.

Synchronization in Multithreading

When two or more threads need access to a shared resource, they need some way to ensure that the resource will be used by only one thread at a time. The process by which this is achieved is called synchronization.

In other words,

Thread Synchronization is defined as a mechanism which ensures that two or more concurrent processes or threads do not simultaneously execute some particular program segment known as critical section.

Key to synchronization is the concept of the monitor. A monitor is an object that is used as a mutually exclusive lock. Only one thread can own a monitor at a given time. When a thread acquires a lock, it is said to have entered the monitor. All other threads attempting to enter the locked monitor will be suspended until the first thread exits the monitor. These other threads are said to be waiting for the monitor. A thread that owns a monitor can reenter the same monitor if it so desires.

You can synchronize your code in either of two ways. Both involve the use of the synchronized keyword, and both are examined here.

Real Life example of thread safe

If you are having a bank account and you have two debit cards. Consider one card is being used by you and another is used by your wife. Your bank account balance is 5000. One day you need money and you go to ATM and at the same time your wife is also going to use their ATM. Now, if you both make transaction at same time for 2000 out of whole money. Now, what should happen? Should it show the remaining balance 3000?

Turns out, No!

Because, if you are allowed to do so at the same time without taken care your account will not be then synchronized with number of transactions you made.

To do so we should have taken care of synchronization.

Now let’s see a case when synchronization is not there:

// This program is not synchronized.

class Callme {

    void call(String msg) {

        System.out.print("[" + msg);

        try {

            Thread.sleep(1000);

        } catch(InterruptedException e) {

            System.out.println("Interrupted");

        }

        System.out.println("]");

    }

}
//creating a thread class implementing Runnable interface

class Caller implements Runnable {

    String msg;

    Callme target;

    Thread t;

    public Caller(Callme targ, String s) {

        target = targ;

        msg = s;

        t = new Thread(this);

       //starting thread

       t.start();

    }

    // implementing runnable method.

    public void run() {

        target.call(msg);

    }

}
//creating main class

class SynchController {

    //main method

    public static void main(String args[]) {

        Callme target = new Callme();

        Caller ob1 = new Caller(target, "Hello");

        Caller ob2 = new Caller(target, "Synchronized");

        Caller ob3 = new Caller(target, "World");

        // wait for threads to end

        try {

            ob1.t.join();

            ob2.t.join();

            ob3.t.join();

        } catch(InterruptedException e) {

        System.out.println("Interrupted");

    }

}

}

Here is the output produced by this program:

#Output : [Hello[Synchronized[World]

]

]

Note:   join( ) method wait for a thread to finish .This method waits until the thread on which it is called terminates. Its name comes from the concept of the calling thread waiting until the specified thread joins it. By calling join method our currently running thread waits for the joined thread to complete.

As you can see, by calling sleep( ), the call( ) method allows execution to switch to another thread. This results in the mixed-up output of the three message strings. In this program, nothing exists to stop all three threads from calling the same method, on the same object, at the same time. This is known as a race condition, because the three threads are racing each other to complete the method which can cause a program to run right one time and wrong the next.

To fix the preceding program, you must serialize access to call( ). That is, you must restrict its access to only one thread at a time. To do this, you simply need to precede call( )’s definition with the keyword synchronized, as shown here:

class Callme {

synchronized void call(String msg) {

...

This prevents other threads from entering call( ) while another thread is using it. After

synchronized has been added to call( ), the output of the program is as follows:

#output : [Hello]

[Synchronized]

[World]

 

Table Variables

Table variables are table type variables which are used to create table like structure. They are partially stored in both memory and in tempdb database of a database server. Once the execution is finished, table variables are dropped automatically.

Why we need Table variables

When you are executing code in T-SQL, tables are necessary to store data temporarily to smooth your development, raise code maintainability and re usability.

Syntax

As other variables, table variables are declared inside the code block of batch or stored procedures. Table variable store a set of records, so naturally the declaration syntax looks very similar to create table statement, as you can see in following example:

DECLARE @usersTblVar TABLE

(

Id int,

Name varchar(200)

)

Insert, update, delete in table variable is same as we do with normal Table.

  • INSERT INTO @usersTblVar(id, name) values(1,’raam’)
  • UPDATE @usersTblVar SET name =’shyam’ where id =’1’
  • DELETE FROM @usersTblVar where id =’1’

You might think table variable work just like temporary tables, there are differences.

Note: you cannot use table variable like input and output parameter but you can return a table variable from a user defined function. The restricted scope of a table variable gives are database server some liberty to perform optimization.

Performance

Because of scope definition concept, a table variable use fewer resources than a temporary table. Table variables only last only duration of update on table variable which reduces locking and logging overhead. It also reduces the problem of recompilation of stored procedures which occurs with temporary tables.

 

You can also insert into a table variable in bulk as well for example:

INSERT INTO @usersTblVar

SELECT * FROM usersBackup

It will insert data into table variable in bulk from usersBackup table.

Note: use table variable without @ in case of MySql.

Disadvantages of using table variables

  • You cannot create a non-cluster index on a table variable.
  • database server does not maintain statics on table variable.
  • Table definition of table variable cannot be changed after declaration.

Multithreading

Multithreading is a widespread programming and execution model that allows multiple threads to exist within the context of one process. These threads share the common resources of a single process and are able to execute independently. The threaded programming model provides developers with a useful abstraction of concurrent execution. Multithreading is widely used in processes which require one process to enable parallel execution on a multiprocessing system.

Where a thread of execution is a smallest sequence of programmed instruction that can be managed independently, typically the part of platform on which they are running either on JVM or on operating system itself.

Each of the threads can run in parallel. The OS divides processing time not only among different applications, but also among each thread within an application.

For example, the transmission rate of data over a network is much slower than the rate at which the computer can process it. Even local file system resources are read and written at a much slower pace than they can be processed by the CPU. And, of course, user input is much slower than the computer. In a single-threaded environment, your program has to wait for each of these tasks to finish before it can proceed to the next one—even though most of the time the program is idle, waiting for input. Multithreading helps you reduce this idle time because another thread can run when one is waiting.

Multithreading in java can be achieved via two ways:

  1. Via extending Thread class:
class MultithreadingDemo extends Thread{

//run method of Thread class is overridden here

    public void run(){

        System.out.println("hi, I am running");

    }

    public static void main(String[] args){

       MultithreadingDemo multiThreadingObj = new MultithreadingDemo();

       //start method of Thread class starts the current tread

       multiThreadingObj.start();

    }

}
//##output :  hi, I am running

Thread class provides constructor and method to create and perform operation on a Thread.

2  Via implementing Runnable interface:

class MultithreadingDemo implements Runnable{

//implement run method of Runnable interface

    public void run(){

        System.out.println("hi, I am running");

    }

    public static void main(String[] args){

        MultithreadingDemo multiThreadingObj = new MultithreadingDemo();

        //create object of Thread class

        Thread threadObj = new Thread(multiThreadingObj);

        threadObj.start();

    }

}
//##output :  hi, I am running

Class implementing Runnable interface, need to provide definition of run method of Runnable interface.

Note: when start method of newly created thread is called then it enters into runnable states.

Merits of Multithreading

  1. Threads work independently and if any exception occurs it affects only that thread.
  2. Threads are light weight.
  3. Cost of communication is low between treads.
  4. It saves time of achieving multiple tasks done.

Demerits of Multithreading

  1. It is complex to debug and test the program while using Multithreading.
  2. Chances of deadlock to occur, increases.
  3. Need to replace those functions which are not thread safe.
  4. Need to remove static variables.
  5. Not easy to port existing code into Multithreading.