Solved

Code Performance

Posted on 2008-10-09
10
198 Views
Last Modified: 2010-04-21
Hello,

Is there any way that i can make the following code and stored procedure run faster by modifying it or does it look okay?

Stored Procedure:
ALTER  PROCEDURE [dbo].[PSSA_GradeReport]

@grd_id int

As

select s.student_id, s.firstname, s.lastname, s.dob, s.gender, s.ethnicity, s.phone, s.entrydate, s.pasecureid, s.spedaccnotes, s.teacher, s.loginuser, s.lastupdated, dbo.StudentTests(s.student_id) testname, s.grd_id, g.grd_id, g.grd_level, i.iep_id, i.iep_type
From pssa_students as s
Join pssa_grades as g On s.grd_id = g.grd_id
Join pssa_iep as i On s.iep_id = i.iep_id
where g.grd_id = @grd_id

CODE:
Protected Sub GradesReport(ByVal s As Object, ByVal e As EventArgs) Handles Me.Load

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("PSSA").ConnectionString
        Dim conn As SqlConnection = New SqlConnection(connectionString)

        ' Create Querystring
        Dim grd_id As Int32 = Request.QueryString("@grd_id")

        Dim greportComm As SqlCommand

        greportComm = New SqlCommand("PSSA_GradeReport", conn)
        greportComm.CommandType = CommandType.StoredProcedure

        ' Create Parameter for Querystring
        greportComm.Parameters.Add("@grd_id", SqlDbType.VarChar)
        greportComm.Parameters("@grd_id").Value = Request("grd_id")

        Try
            conn.Open()

            Dim dr As SqlDataReader = greportComm.ExecuteReader()

            greportList.DataSource = dr
            greportList.DataBind()

        Catch ex As Exception

        Finally
            conn.Close()
        End Try

    End Sub
0
Comment
Question by:asp_net2
  • 5
  • 5
10 Comments
 
LVL 18

Expert Comment

by:Richard Lee
ID: 22679698
Everything you have shown here for the most part looks fine. With regards to the the stored procedure you might want to try various indexes and check the performance. You will want to put indexes included a clustered index on the grades tables (I recommend clustered because I assume this table is fairly small and does not change much). Place the clustered index on the grd_id column. I am not sure what the pssa_iep table contains but you may want to look into the same thing if it meets the criteria.

You can also try adding the NOLOCK hint to the select statement for more scalability.

select s.student_id, s.firstname, s.lastname, s.dob, s.gender, s.ethnicity, s.phone, s.entrydate, s.pasecureid, s.spedaccnotes, s.teacher, s.loginuser, s.lastupdated, dbo.StudentTests(s.student_id) testname, s.grd_id, g.grd_id, g.grd_level, i.iep_id, i.iep_type
From pssa_students as s (Nolock)
Join pssa_grades as g On s.grd_id = g.grd_id
Join pssa_iep as i On s.iep_id = i.iep_id
where g.grd_id = @grd_id


In your VB code since you do not do anything in the CATCH block you might simply want to use the USING keyword to open the connection and remove the exception handling code.

using conn As New SqlConnection(connectionString)
            conn.Open()

            Dim dr As SqlDataReader = greportComm.ExecuteReader()

            greportList.DataSource = dr
            greportList.DataBind()
End Using

You will need to rearrange your code to use this construct this way.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 22679963
DaTribe,

Ok, a couple questions i have.

What does NOLOCK do? How do i place a clustered index on a PK within my tables? Also most importantly i have seen examples of the using block but don't fully understand the differece from the way i have it now. If it's a performance boost then i will switch to it. Can you help me with that by changing with what i have now?

Thanks in advance!!!!
0
 
LVL 18

Expert Comment

by:Richard Lee
ID: 22680438
OK here goes:

1. NOLOCK is a hint to SQL server asking it if its possible not to lock the table and allow other queries to access it. Therefore more queries can run simultaneously.

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220,00.html#block

The rest of the article has performance tips

2. Follow the instructions in this link to add a clustered index:

http://msdn.microsoft.com/en-us/library/ms188245.aspx

3. The try catch block has an overhead therefore the more you can avoid it the better. It is probably a much better option to put the block within your business logic rather than your data access layer, leaving your data access layer to run as fast as possible.

The USING keyword provides a way to create an object - a class that implements the IDisposable interface -, in this case the SqlConnection and performs a dispose of the object at the end of the block. Therefore no matter how that using block is exited the SqlConnection will be disposed of (eliminating the finally block) and the connection will be closed.


Protected Sub GradesReport(ByVal s As Object, ByVal e As EventArgs) Handles Me.Load
 

    ' Get database connection string

    Dim connectionString As String = ConfigurationManager.ConnectionStrings("PSSA").ConnectionString
 

    ' Create Querystring

    Dim grd_id As Int32 = Request.QueryString("@grd_id")
 

    Using conn As New SqlConnection(connectionString)
 

        Dim greportComm As New SqlCommand("PSSA_GradeReport", conn)

        greportComm.CommandType = CommandType.StoredProcedure
 

        ' Create Parameter for Querystring

        greportComm.Parameters.Add("@grd_id", SqlDbType.VarChar)

        greportComm.Parameters("@grd_id").Value = Request("grd_id")
 

        conn.Open()

        greportList.DataSource = greportComm.ExecuteReader()

        greportList.DataBind()
 

    End Using
 

End Sub

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 22683223
DaTribe,

Thanks for the info! Just a couple things that i noticed that will help me in the future that i'm sure you can answer.

When should i use a try catch block within my code since it has a high overload?

Is it always a good idea to use the Using Block of code all the time and if so is it only recommended for the conn object? I still don't fully understand, sorry i'm still learning :(

Also, is there a reason that conn.Open is there and why is it needed without the conn.Close?

Thanks in advance!!!
0
 
LVL 18

Expert Comment

by:Richard Lee
ID: 22685359
The try catch block is a very good thing to use. You always want to provide the best user experience and handling unexpected exceptions is one way to do that.

The overhead for exception is not that bad, simply there is one. To be a better developer you consider these things and then make the best choice for your application. I suggested that it is better to handle exceptions in your business layer and leave your data access layer/logic to run as fast as possible.

It is always good idea to use the USING block if possible. Remember the using block can only be used on classes that implement the IDisposable interface like the SqlConnection or MemoryStream, etc. What happens is that whenever execution of the code exits the USING block the Dispose method is called and for the Connection object this method with close the connection.

I think its even possible not to call the conn.Open method (verifying is needed) since I believe the Command object will open it when executing.

Hope this helps.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Author Comment

by:asp_net2
ID: 22698956
DaTribe:

>> I suggested that it is better to handle exceptions in your business layer and leave your data access layer/logic to run as fast as possible

what is the difference between the business layer and the data access layer? Also, when you use the USING block why don't you need to add DIM either before or after it?

Is it a good idea to use USING for everything like DataSet, DataTable, and DataAdapter?

THANK YOU VERY MUCH IN ADVANCE!!
0
 
LVL 18

Accepted Solution

by:
Richard Lee earned 500 total points
ID: 22700804
A review of this article will give you and idea on tiered architecture.

http://en.wikipedia.org/wiki/Multitier_architecture

The USING block is a construct within the language and these are the rules when you employ it. They have chosen not to include the DIM here (I guess its redundant).

If you are confused about the IDisposable Interface the have a look at this article.

http://msdn.microsoft.com/en-us/library/system.idisposable.aspx

You use the USING block for classes that implement the IDisposable Interface.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 22702893
Hi DaTribe,

I only have one more question before i close this post and that is, how can i find out what classes implement the IDisposable interface.
0
 
LVL 18

Expert Comment

by:Richard Lee
ID: 22708946
A comprehensive way to locate classes which implement the IDisposable interface is to use the Object Browser Ctrl+W,J. You should search All Component for IDisposable and the right click on the IDisposable interface and "find usages advanced". Select extending interfaces and Solution and Libraries.
0
 
LVL 4

Author Closing Comment

by:asp_net2
ID: 31504691
THANK YOU FOR ALL YOUR HELP AND PATIENCE!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Handle null when using linq in this line 1 26
ASP.NET 5 Templates 2 66
Pagebreak issue while printing the aspx page 3 16
Need a starter for ETL protocol? 4 43
One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now