Improve Query Performance by just a small change “OPTIMZE FOR”

 

Dear All,

If your SQL statement is slow then sometimes just a little change help you a lot in my case it was helpful hope it will helpful in your problem also.

Suppose you want to fetch  data from two large tables  for a specific constant value. Let take example of this

suppose you have two table 1. Student table 2. student enrollment table both table have large amount of data.

Now you want to fetch max record of enrollment id student wise for  particular status in Student enrollment  table.

Then for this you need to write following query .

SELECT ISNULL(MAX(EnrollmentHistoryId),0) AS maxenrmollmentHistoryId ,     
          se.StudentId      
      FROM dbo.StudentEnrollment se WITH (NOLOCK)       
      INNER JOIN dbo.student s WITH (NOLOCK) ON s.studentId = sh.studentId       
                            AND s. YearId = @YearID
      WHERE sh.Statusid = @StatusActivateId 
      GROUP BY sh.serializationid

Now if you find here the StatusActivateId  is fixed in this case so  we need to optimize this statement   here we used optimize for option 

SELECT ISNULL(MAX(EnrollmentHistoryId),0) AS maxenrmollmentHistoryId ,     
           se.StudentId      
       FROM dbo.StudentEnrollment se WITH (NOLOCK)       
       INNER JOIN dbo.student s WITH (NOLOCK) ON s.studentId = sh.studentId       
                             AND s. YearId = @YearID
       WHERE sh.Statusid = @StatusActivateId 
       GROUP BY sh.serializationid
     OPTION (OPTIMIZE FOR (@StatusActivateId =21))

if you  compare above statements you will find there is only one difference in last line which is option (OPTIMIZE FOR)

As i shared earlier we know the fixed value so we explicitly added the variable name and value for which we need to optimization for the query.

In SQL Server 2008  this option ins more robust which taking any unknown option.

I hope it will help you some where . Just hit and try.

Learn & Share

Thanks

Rajat Jaiswal

how to find column or text in entire stored procedure?

Hello Friends,

Sometime we want to search a particular column or some text in entire stored procedures. For this we can utilize following two simple approaches

Approach 1:-

BEGIN TRY

  DECLARE @strColumn VARCHAR(1000)

  SET @strColumn =’Rajat’

  SELECT DISTINCT o.name

  FROM sys.syscomments c

  INNER JOIN sys.objects o ON o.object_Id = c.Id

                 AND o.type =’P’

  WHERE text like ‘%’ + @strColumn +’%’

  ORDER BY o.NAME

END TRY

BEGIN CATCH

  SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()

END CATCH

Approach 2:-

BEGIN TRY

  DECLARE @strColumn VARCHAR(1000)

  SET @strColumn =’Rajat’

  SELECT SPECIFIC_NAME

  FROM INFORMATION_SCHEMA.ROUTINES

  WHERE ROUTINE_TYPE= ‘PROCEDURE’

  AND ROUTINE_DEFINITION LIKE ‘%’ + @strColumn +’%’

  ORDER BY SPECIFIC_NAME

END TRY

BEGIN CATCH

  SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()

END CATCH

Hope this will helpful to you somewhere.

 Thanks

Rajat Jaiswal

Call SQL Server Reporting Services by asp.net in 5 easy step

Hello friends,

Wish you Happy New Year.

Today I am going to share a simple way to use SQL Server Reporting Services locally. I am trying to share simple steps for creating report.

I am using North wind database and try to create a sample report which shows customer information.

Here we go

Step 1:- Create a project in my case I am using web project you can choose either web/window. In the project add a type dataset and drag drop customers table on XSD. (You can customize schema according to your need) as shown in below fig.

Step2:- Now add a report (.RDLC)  

Step 3:-  When you add  report then on right side a screen(Just like solution explore) will appear with name “ Web Site Data source “  in this you will find  the type dataset which we have added and  when you expand the  type dataset object you will find all the table schema as show in fig. 

 

Step 4: Now just drag drop a table from toolbars’ report item. And drag drop column from data source (type data set which shown in step3) to table column which you need to show in report. 

 

Step 5:- Once you done with step4 now next step to call report from “Asp.net”. For this just drag drop Microsoft reporting viewer and do following code.

Below is the code

Dim sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings(“connectionString”).ConnectionString.ToString())

            sqlcon.Open()

            Dim sqlcmd As New SqlCommand

            sqlcmd.CommandText = “SELECT * FROM Customers”

            sqlcmd.CommandType = CommandType.Text

            sqlcmd.Connection = sqlcon

            Dim sda As New SqlDataAdapter

            Dim ds As New myDs

            sda.SelectCommand = sqlcmd

            sda.Fill(ds, ds.Customers.TableName)

‘clearing report viewer

            Me.ReportViewer1.LocalReport.DataSources.Clear()

‘Create datasource for report

            Dim rds As New ReportDataSource()

            rds.Name = “myDs_Customers”

            rds.Value = ds.Customers

            Me.ReportViewer1.LocalReport.DataSources.Add(rds)

‘assigning report path.

            Me.ReportViewer1.LocalReport.ReportPath = “myReport.rdlc”

            Me.ReportViewer1.LocalReport.Refresh()

 Now run the page and you will get report .

 

so enjoy.

Thanks

Rajat Jaiswal 

How to write an insert statement for a table which has only single column and that column is auto incremented ?

Hello friends,
Recently a question asked to my friend in an interview “How to write an insert statement for a table which has only single column and that column is auto incremented?”
So here is the answer for it with sample
DECLARE @tblTest AS TABLE (id INT IDENTITY)
INSERT INTO @tblTest DEFAULT VALUES
INSERT INTO @tblTest DEFAULT VALUES
INSERT INTO @tblTest DEFAULT VALUES
INSERT INTO @tblTest DEFAULT VALUES
INSERT INTO @tblTest DEFAULT VALUES
SELECT * FROM @tblTest
But I don’t know what will be the use of this table?

Enjoy Query
Thanks & Regards
Rajat Jaiswal

Database designing best Practices

Hello friends,
Designing database is one of the most interesting works but on the same time it should be proper because it is base of any business application. We should follow Microsoft best practices while designing the database. Here I am with a simple database design (Address book) concept. I will try to put my best to use Microsoft best practices while designing database. I know you are thinking Address book is so much easy to design, so my answer is yes you are right you can design database easily but using Microsoft best practices it bit more important and I am trying for those best practices.
So first best practice is
1) Define proper  data type :-
Most of the time we design database but we ignore proper database but it should not because when you talking for very large scale database application (VLSDB)  then wrong data type will give you space problem. According to best practices choose smallest data type first (if it fit in your requirement).We understand this by an example suppose you designed a table with name “seed Data” in which column with name lngId having data type float and float consume 8 bytes and if the seed data cannot exceed more than 10 rows then we should use Tinyint data type instead of float data type because tinyint consume only 1 byte.
2) Define proper Primary & Foreign key:-
According to best practices we should make database normalized and for normalized database we should create proper Primary & Foreign keys. By creating proper primary & foreign keys we get two advantage 1) data anomalies chances reduce we get fast response as well after defining primary key (because cluster index created on primary key)
3) Define proper constraints :-
Microsoft best practices also suggest defining proper constraints for column like not null, unique key constraint on columns so that data anomalies will reduce and we get proper data.
4) Define partition :-
If your database is going to be very large then define proper partition both horizontally & vertically.
5) Normalization: – Database should be proper normalized.
In our Address Book database example we have categorized the data table in 3 categories 
1) Lookup table having prefix “lku”
The lookup table is mainly for seed value or we can say this table contains all the data which just act like pre pop data. We have following tables for our Address book
lkuCountryMaster,lkuStateMaster,lkuCityMaster,lkuPhoneType,lkuAddressType,lkuPrefixMaster,lkuURLType,lkuDesignationType,lkuRelationShipType
2) Link table having prefix “lnk”
By the name it is clear that its main purpose is making relation between main data record and other data. We have used following tables lnkPhoneToPerson,lnkAddressToPerson,lnkAddressToCompany,lnkURLToPerson,lnkPersonToCompany,lnkPersonRelations,lnkPhoneToCompany

3) Main table which start with prefix “tbl”
The main table indicates by “tbl” in our Address book database we have used following tables tblPerson, tblAddresses, tblPhotos and tblCompany.
In the entire lookup table we have preferred tiny int & Small int data type because we know the value in look up table could not go beyond the tiny int & small int max limit.
In similar way we have applied all the not null constraint to require field provide proper data type.
Please see fig below.

Address Book design using Best Practices

For more detail you can download script for Address book.

Thanks & Esteemed Regards
Rajat Jaiswal

Three tire code Generator using VB.NET

Hello Friends,
Today I am sharing vb.net code generator.  Its   interface is simple same as our stored procedure code generator. (**Important   I am using Microsoft Application block for Data Access Layer**)
You need to give user credential as shown in below fig.

Once the credential is successful you will get below screen.

Just select the table for which you want to generate code.    Now if you want dataset as entity then just check the use dataset checkbox.
Then press Generate button.

It will   generate y our windows form, Business manager class, Data Access layer class.
Just copy and paste in your project it will work fine with some minor changes.
You can download the code from below link
Download code
Enjoy coding!!
Thanks & Regards
Rajat Jaiswal

User Define Function Problems in SQL Server

Hello Friends,

First of all sorry for such a late post it just because I have lot of tension now a days 🙂

So today we start a new topic in which I stuck User Define Functions in SQL Server.

By the name use think User Define function is so easy just like store procedure.

So I will yes it just like sql server but it has some limitaion which I am facing.

These are

1)We can not use try catch Block in User define functions.

2) We can use Execute sp_ExecuteSQL in UDF.

3) we can not use Execute(sqlStatement) in UDF.

4) we can not use getDate() in UDF.

5) we can not call a store procedure however we can call extended store procedures.

6) We can not User Temporary table in UDF.

The solution of it which I used made a CLR function.

If you found another solution than share it with me.

Enjoy programming 🙂

Enjoy Sql Server 🙂

Thanks & Regards

Rajat Jaiswal