Why VIEW takes long time to Execute

Here in this article we are discussing related to the performance of the views. Why the performance of the view is slow. Please note that I am not taking about the indexed views, it's another thing and out of the scope of this article.

Like stored procedure, the optimizers cache the execution plan in the case for further use.

Let's take an example:

We have a simple base table contains records and we just create a view from this base table objects and then execute both (Base table and View separately)

CREATE TABLE emp_Table

       (empid    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

        empname  VARCHAR(50) NOT NULL,

        empsal   DECIMAL(20,2))

       

GO

CREATE VIEW vw_empView

AS

SELECT empid, empname, empsal

FROM   emp_Table

GO

-- Example-1

SELECT * FROM emp_Table

GO

-- Example-2

SELECT * FROM vw_empView

GO

Here in this example which one is executed faster?

It's Example-1. That means direct query from Table objects. To get the better understanding related to execution we just look at the execution plan.

Both execution plans are same. So, why the execution of views take long time?

This is because it takes SQL Server extra work such as looking up data in the system tables before it can execute the view.

This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don't, because some of the work SQL Server is doing is hidden.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Is Table-Valued UDF is Performance Killer

One my previous article "Can view takes input parameters" I got some response from various sources like "Linked In" etc.

http://sqlknowledgebank.blogspot.in/2012/05/can-views-take-input-parameters.html

Here some of my readers give me some comments like

"A view might be more efficient than a table valued functions"

I searched different blogs and article related to it and find that there are a common misconnect is the Table value function is a poor performer.

So I decide to wire this article related to the performance factor of Table Valued function by collecting some notes from different sources.

How many type of Table valued function does MS SQL Server supports

There are 2 types of table valued function

1.    Single-statement table valued user defined functions, also called in-line

2.     Multi-statement table valued user defined functions

The simple example of both is mentioned bellow

Single Statement Table Valued function

CREATE FUNCTION dbo.fn_TableVal

                (@p_sal DECIMAL(20,2))

RETURNS TABLE

AS RETURN

(

      SELECT *

      FROM   emp_matsre

      WHERE  empSal>@p_sal

)

GO

Multi-statement table valued Function

CREATE FUNCTION dbo.fn_TableVal

                (@p_sal DECIMAL(20,2))

RETURNS @Ret_Table TABLE

        (idno    INT,

         empName VARCHAR(50),

         empSal  DECIMAL(20,2))

AS

BEGIN

    INSERT INTO @Ret_Table

      SELECT *

      FROM   emp_matsre

      WHERE  empSal>@p_sal

     

      RETURN

END

GO

The single-statement table valued function sometimes called the views with parameters.

Execution of these UDF's will be incorporated directly into the execution plan with the rest of the associated query. Indexes may be used if the query within the UDF is well constructed. It is generally safe constructs to use within your queries.

Multi-statement UDF's are built upon the structure of table variables.

These are not incorporated within the rest of the execution plan and usually show up as a table scan against a table with one row. The problem is your query may have more than one row. When the multi-statement UDF's are used on large sets of data they cause very serious performance problems. They won't scale well, and the performance issues are masked by the apparent low cost of the operations involved. We need to be extremely careful when using the multi-statement table valued UDF's. The problem is that the system sees the BEGIN/END and runs the PROCEDURE in a different context. Without it, it takes the subquery in the return() call and can simplify it out.

Non-inline functions do not contribute towards the cost reported by an execution plan. You need to use Profiler to see their effect.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Running Total

When I am reviewing a stored procedure, I find that to calculate the value of running total the stored proc contains CURSOR… WHILE Loop etc and contain complex queries.

In this article I am demonstrating a simple way to finding the running total in single select statements.

Let's start it with an example

-- Create Temp Table

CREATE TABLE #Tmp_Prod

       (ProdId    INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,

        ProdName  VARCHAR(50)  NOT NULL,

        ProdPrice DECIMAL(20,2)NOT NULL)

       

-- Inserting data into Temp Table

INSERT INTO #Tmp_Prod       

            (ProdName, ProdPrice)

VALUES ('Tooth Paste', 50),

         ('Tooth Brush', 20),

         ('Mouth Wash', 150),                       

         ('Face wash', 250),

         ('Saving Cream', 150),           

         ('Saving Brush', 25)  

                 

-- Display Records

SELECT * FROM #Tmp_Prod               

-- Result Set

ProdId      ProdName          ProdPrice

1           Tooth Paste       50.00

2           Tooth Brush       20.00

3           Mouth Wash        150.00

4           Face wash         250.00

5           Saving Cream      150.00

6           Saving Brush      25.00

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

-- Making the Running total by EXAMPLE-1

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

SELECT  a.ProdId, a.ProdName, a.ProdPrice,

        (SELECT SUM(b.ProdPrice)

         FROM   #Tmp_Prod  b

         WHERE  b.ProdId <= a.ProdId) As Running_Total

FROM   #Tmp_Prod  a

ORDER BY a.ProdId

-- Result Set

ProdId      ProdName          ProdPrice   Running_Total

1           Tooth Paste       50.00       50.00

2           Tooth Brush       20.00       70.00

3           Mouth Wash        150.00      220.00

4           Face wash         250.00      470.00

5           Saving Cream      150.00      620.00

6           Saving Brush      25.00       645.00

StmtText [ Execution plan information in Text ]

  |--Compute Scalar(DEFINE:([Expr1006]=[Expr1004]))

       |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[ProdId]))

            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Tmp_Prod] AS [a]), ORDERED FORWARD)

            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013] END))

                 |--Stream Aggregate(DEFINE:([Expr1012]=Count(*), [Expr1013]=SUM([tempdb].[dbo].[#Tmp_Prod].[ProdPrice] as [b].[ProdPrice])))

                      |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Tmp_Prod] AS [b]), SEEK:([b].[ProdId] <= [tempdb].[dbo].[#Tmp_Prod].[ProdId] as [a].[ProdId]) ORDERED FORWARD)

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

-- Making the Running total by EXAMPLE-2

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

SELECT a.ProdId, a.ProdName, a.ProdPrice,

       SUM(b.ProdPrice)As Running_Total

FROM   #Tmp_Prod a

       CROSS JOIN #Tmp_Prod b

WHERE (b.ProdId <= a.ProdId)

GROUP BY a.ProdId, a.ProdName, a.ProdPrice

ORDER BY a.ProdId

-- Result Set

ProdId      ProdName          ProdPrice   Running_Total

1           Tooth Paste       50.00       50.00

2           Tooth Brush       20.00       70.00

3           Mouth Wash        150.00      220.00

4           Face wash         250.00      470.00

5           Saving Cream      150.00      620.00

6           Saving Brush      25.00       645.00

StmtText [ Execution plan information in Text ]

|--Stream Aggregate(GROUP BY:([a].[ProdId]) DEFINE:([Expr1004]=SUM([tempdb].[dbo].[#Tmp_Prod].[ProdPrice] as [b].[ProdPrice]), [a].[ProdName]=ANY([tempdb].[dbo].[#Tmp_Prod].[ProdName] as [a].[ProdName]), [a].[ProdPrice]=ANY([tempdb].[dbo].[#Tmp_Prod].[ProdPrice] as [a].[ProdPrice])))

       |--Nested Loops(Inner Join, WHERE:([tempdb].[dbo].[#Tmp_Prod].[ProdId] as [b].[ProdId]<=[tempdb].[dbo].[#Tmp_Prod].[ProdId] as [a].[ProdId]))

            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Tmp_Prod] AS [a]), ORDERED FORWARD)

            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Tmp_Prod] AS [b]))

Here both Example-1 and Example-2 provide the same result set and solve the problem, but I personally preferred the Example-2 by using the CROSS join as it gives us better performance then Example-1.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Can Views take the Input Parameters

One of my friends is trying to passing some values in views. He told me that is there any options in SQL server to pass the parameters in views. 

The answer is NO. It is not possible at any version of the SQL server as the view is not build for that purpose.

But we have others ways to do that, and we can do it very easily by table value functions.  Here in this article I am trying to demonstrate it easily by taking an example.

-- My base table

CREATE TABLE my_Emplyee

      (ID         INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,

       EMP_NAME   VARCHAR(50)  NULL,

       SAL        DECIMAL(20,2)NOT NULL)

      

-- Inserting so records on it

INSERT INTO  my_Emplyee

             (EMP_NAME,  SAL)

VALUES('Sukamal jana', 40000),

        ('Manisankar Year', 30000),

        ('Tuhin Shina', 40000),    

        ('Sabgram jit', 30000),  

        ('Subrata Kar', 20000),   

        ('Debojit Ganguli', 20000) 

       

-- Display records

SELECT * FROM my_Emplyee

Output

ID    EMP_NAME          SAL

1     Sukamal jana      40000.00

2     Manisankar Year   30000.00

3     Tuhin Shina       40000.00

4     Sabgram jit       30000.00

5     Subrata Kar       20000.00

6     Debojit Ganguli   20000.00

I am again mentioned that the parameters cannot be passed in views.

Now I am moving to the solution of that by table value function and passing some parameters to get the desired result set.

IF OBJECT_ID (N'fn_EMP_VIEW') IS NOT NULL

   DROP FUNCTION dbo.fn_EMP_VIEW

GO

CREATE FUNCTION dbo.fn_EMP_VIEW

                (@p_Sal DECIMAL(20,2))

RETURNS TABLE

AS RETURN

(

      SELECT *

      FROM   my_Emplyee

      WHERE  SAL>=@p_Sal

)

GO            

             

We want to display the employee details that have salary 40,000 or more than that.

-- Execute it

SELECT *

FROM   dbo.fn_EMP_VIEW(40000)

Output

ID    EMP_NAME          SAL

1     Sukamal jana      40000.00

3     Tuhin Shina       40000.00

Hope you like it.

Posted by: MR. JOYDEEP DAS

  

Why nVARCHAR

One of my friends ask me a question why use nVARCHAR not the VARCHAR. He also told me when he makes any Table objects by nVARCHAR data types it takes double in length. I try to explain his query by T-SQL statements.

CREATE TABLE Table_1

       (

        Emp_name1 VARCHAR(20),

        Emp_Name2 nVARCHAR(20)

        )

GO

sp_columns Table_1

GO

COLUMN_NAME       TYPE_NAME         LENGTH

Emp_name1         varchar           20   

Emp_Name2         nvarchar          40

This article is dedicated to him to understand the difference between VARCHAR and nVARCHAR and why it takes double in length.

To explain it I am just inserting some values to my above table and observe the result closely.

INSERT INTO Table_1

            ( Emp_name1, Emp_Name2)

VALUES ('A', 'A')           

GO

SELECT DATALENGTH(Emp_name1) [VARCHAR_LENGHT],

       DATALENGTH(Emp_name2) [nVARCHAR_LENGHT]

FROM Table_1      

GO

VARCHAR_LENGHT    nVARCHAR_LENGHT

1                 2

If we observe the output closely we can see the VARCHAR takes 1 bytes and nVARCHAR takes 2 bytes.

VARCHAR is ASCII based and nVARCHAR is UNICODE based and 1 byte for ASCII and 2 byte for UNICODE.

So what it means, by using nVARCHAR we just loose the disk space?

In this article I am trying to explain the Difference UNICODE vs. ASCII and UNICODE advantage. To support my point I am gathering some facts that are mentioned bellow.

Here I am just mentioned some major differences only.

1.    UNICODE takes 2 byte. 1 byte for language page 1 byte for sign value.

2.     ASCII takes 1 byte. It doesn't contain info about language page and all bytes contain sign info.

3.     If we will use in our application different language in one time. I mean we can see record on English and Japan language - UNICODE can solve this problem as because it has language page info.

4.    If we will use in our application different language in one time. I mean we can see record on English and Japan language - ASCII can't solve this problem, because it can store info only about one language.

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS package

In my previous article I am trying to explain related to What is data warehousing. If you don’t read it please follow this link before going to this…

http://sqlknowledgebank.blogspot.in/2012/05/data-warehousing.html

In this article I am trying to explain related to SSIS package.

A Package is the core object within SQL server Integration Services (SSIS) that contains the business logic to handle workflow and data processing. SSIS package can be used to move data from source to destinations and also handle the timing precedence of when thing process.

**BIDS [ Microsoft SQL Server Business Intelligence Development Studio ]

SSIS package can be accomplished by two ways.

Built-in wizard
By using the Built-in wizard in SQL Server 2005 that asks you to move the data from source to destination and automatically generate the SSIS package.


SSIS BIDS
By explicitly create a project in SSIS BIDS. We need to create projects the new package is automatically created and developed.

So we now trying to discuss about our first option and that is

By Built-in Wizard

In SQL Server 2005 we can use the Import and the Export Wizard to Import and Export the data. For Import Wizard the source is the SQL Server 2005 table and destination should be SQL Server database, ORACLE database, Flat file, Microsoft Excel spread sheet, Microsoft Access database.

Exporting data with the wizard lets us send the data from SQL Server 2005 tables, Views or custom query to flat file or database connection.

Initialize the Import Export Wizard

To initialize, please follow this steps mentioned bellow.

What we want to do

We want to import a flat file to our existing database.

1.    Through the SSMS connects to the installed database engine. That should be your source or destination.

2.    Click on view menu select Object Explorer (or press F8). From the database folder select the desired database. Then right click of the desired database and select Tasks. From Tasks we can select Import or Export wizard.

3.    Select the Tasks. If the database is source of data that needed to send out to the different system, select the “Export Data” and if the database is destination for the file currently exists outside the system, than select “Import Data”. Here is this example we are choosing “Import data”.

Database is source of data  à Export Data

Database is destination for the file àImport Data

4.     If we choose any one the “Welcome to SQL Server Import Export Wizard” appears. Then click the next button on the wizard. “Choose the data source” allow you to specify from the data is coming from. Here in this example I am choosing Flat file source and brows the flat file. Please specify others options if needed.

“Choose a Destination” allow us to specify the destination where the data will be sending. We can choose the destination if needed. The server name and the security settings must be specified. If we select a relational database source that allow customer queries.

5.    For now in “Save and Execute” page of wizard we choose the options Execute Immediate for now. In the complete the wizard gives us all the information that we selected. If needed we can go back and modified it. Now use the SQL query to see the result output.

SELECT * FROM <table name>

In my next session we are discussing about saving and Editing Package created by wizard.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Data Warehousing

Lot of my friends and reader asking me to write a tutorial related to Microsoft BI tools. As I personally feel that the Data Warehousing is not just understand or practice via some Tools provided by Microsoft, it need deep understanding analysing with data. Well we can learn the tools very easily but sensing the data and information is quite tough to learn. It’s growing with maturity and hard work. 

Well if readers want me to write something, here I am trying to give them something by my article.

In this article I am trying to understand the concept behind data ware housing. Why we all think about it.

What is the Data Warehousing?

One of the main features of data warehousing is to combining data from heterogeneous data sources into one comprehensive and easily maintained database.

The common accessing systems of data warehousing includes


 Queries


Analysis


Reporting

As the number of source can be anything, the data warehouse creates one database at the end. The final result however, is homogeneous data, which can be more easily manipulated.

Data warehousing is commonly used by companies to analyse trends over time. Its primary function is facilitating strategic planning resulting from long-term data overviews. From such overviews, business models, forecasts, and other reports and projections can be made. Routinely, because the data stored in data warehouses is intended to provide more overview-like reporting, the data is read-only. If you want to update the data stored via data warehousing, you'll need to build a new QUERY when you're done.

We are not saying that data warehousing involves data that is never updated. On the contrary, the data stored in data warehouses is updated all the time. It's the reporting and the analysis that take more of a long-term view.

Data warehousing is not the be-all and end-all for storing all of a company's data. Rather, data warehousing is used to house the necessary data for specific analysis. More comprehensive data requires different capacities that are more static and less easily manipulated than those used for data warehousing.

Data warehousing is typically used by larger companies analysing larger sets of data for enterprise purposes.

Smaller companies wishing to analyse just one subject, for example, usually access data marts, which are much more specific and targeted in their storage and reporting. Data warehousing often includes smaller amounts of data grouped into data marts. In this way, a larger company might have at its disposal both data warehousing and data marts, allowing users to choose the source and functionality depending on current needs.

Hope you like it. In my next session I am directly jump over Microsoft BI tools Introduction and try to discuss when you used them.

Hope you like it.

Posted by: MR. JOYDEEP DAS