Click here to Skip to main content
15,169,285 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Friends I have the following code in vb.net :

VB
Private Sub btnSetGrade_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSetGrade.Click
       Dim CollIndex As Integer = 0, TempID As Integer = 0, SaveID As Integer = 0
       Dim ColName As String = "", TempIDTotal As String = "", QryGetTot As String = ""
       cmdExecute("delete from tbTestHeading")
       cmdExecute("insert into   tbTestHeading (tempID) values (1)")

       '==================Get Subject Name of Current Session=================================
       Dim dsSession As DataSet = DirectCast(cmdGetData("SELECT DISTINCT [session],class_id FROM  testmaster  WHERE   (Srno IN (SELECT srno  FROM  testdetail WHERE (stcode =" & txtStudentCode.Text & ") ORDER BY srno)) order by [session]"), DataSet)
       Dim sessionID As String = "", classID As String = ""
       For i = 0 To dsSession.Tables(0).Rows.Count - 1
           sessionID = sessionID & "'" & dsSession.Tables(0).Rows(i)(0) & "',"
           classID = classID & dsSession.Tables(0).Rows(i)(1) & ","
       Next
       sessionID = sessionID.Substring(0, sessionID.Length - 1)
       classID = classID.Substring(0, classID.Length - 1)

       '============Get Test names and Insert in table====================
       Dim dsTestName As DataSet = DirectCast(cmdGetData("SELECT DISTINCT testmaster.test, testgrade.testOrder FROM  (testmaster INNER JOIN  testgrade ON testmaster.test = testgrade.testName) WHERE  (testmaster.class_id IN (" & classID & ")) AND (testmaster.[session] IN (" & sessionID & ")) ORDER BY testgrade.testOrder"), DataSet)
       If dsTestName.Tables(0).Rows.Count > 0 Then
           For iTN = 0 To dsTestName.Tables(0).Rows.Count - 1
               CollIndex = CollIndex + 1
               cmdExecute("update tbTestHeading set R" & CollIndex & "='" & dsTestName.Tables(0).Rows(iTN)("test") & "' where tempID=1")
               QryGetTot = QryGetTot & "R" & CollIndex & ","
           Next
           QryGetTot = "R" & CollIndex + 2 & "," & QryGetTot
       End If
       '=============================End Test Name=============================================

       '======================Get Max Temp ID============================================
       Dim MaxID As Integer = Convert.ToInt32(getMaxID("tempID", "tbTestHeading"))


       For iSn = 0 To dsSession.Tables(0).Rows.Count - 1
           MaxID = MaxID + 1
           '==========Get year on based of Session and save===================
           Dim dsYear As DataSet = DirectCast(cmdGetData("SELECT startdate,enddate  FROM SessionMaster where srno=" & dsSession.Tables(0).Rows(iSn)("session")), DataSet)
           Dim StartYear = Year(dsYear.Tables(0).Rows(0)("startdate"))
           Dim EndYear = Year(dsYear.Tables(0).Rows(0)("enddate"))
           Dim Session = StartYear & "-" & EndYear
           cmdExecute("insert into   tbTestHeading (tempID,sessionName) values (" & MaxID & ",'" & Session & "')")
           SaveID = MaxID
           TempIDTotal = TempIDTotal & MaxID & ","
           '=====================End Year========================================

           '===================Get Subject name of Current session========================
           Dim dsSubjectName As DataSet = DirectCast(cmdGetData("SELECT DISTINCT Subject_master.shortName FROM  ((testdetail INNER JOIN  testmaster ON testdetail.srno = testmaster.Srno) INNER JOIN Subject_master ON testmaster.sub_id = Subject_master.srno) WHERE (testdetail.stcode =" & txtStudentCode.Text & ") AND (testmaster.[session] = '" & dsSession.Tables(0).Rows(iSn)("session") & "') "), DataSet)
           For iSubN = 0 To dsSubjectName.Tables(0).Rows.Count - 1
               MaxID = MaxID + 1
               cmdExecute("insert into   tbTestHeading (tempID,subjectName) values (" & MaxID & ",'" & dsSubjectName.Tables(0).Rows(iSubN)("shortName") & "')")

           Next 'Subject Name For End
           '======================End Subject Name====================

           '===========Get Marks of Student in Particular Session====================
           Dim dsMarks As DataSet = DirectCast(cmdGetData("SELECT testdetail.marks, testmaster.test, Subject_master.shortName, testmaster.class_id, testmaster.[section], testdetail.[max] FROM (((testdetail INNER JOIN  testmaster ON testdetail.srno = testmaster.Srno) INNER JOIN Subject_master ON testmaster.sub_id = Subject_master.srno) INNER JOIN testgrade ON testmaster.test = testgrade.testName) WHERE (testdetail.stcode = " & txtStudentCode.Text & ") AND (testmaster.[session] = '" & dsSession.Tables(0).Rows(iSn)("session") & "') ORDER BY testgrade.testOrder, Subject_master.srno "), DataSet)
           Dim MaxMarks As Integer = 0

           '===========Get Maximum marks =======================================
           Dim dsMax As DataSet = DirectCast(cmdGetData("SELECT DISTINCT testmaster.test, testmaster.[max] FROM  (testmaster INNER JOIN  testdetail ON testmaster.Srno = testdetail.srno) WHERE  (testdetail.stcode =" & txtStudentCode.Text & ") AND (testmaster.[session] = '" & dsSession.Tables(0).Rows(iSn)("session") & "') AND (testmaster.[section] = '" & dsMarks.Tables(0).Rows(0)("section") & "') AND (testmaster.class_id =" & dsMarks.Tables(0).Rows(0)("class_id") & ")"), DataSet)
           For iMax = 0 To dsMax.Tables(0).Rows.Count - 1
               Try
                   MaxMarks = MaxMarks + dsMax.Tables(0).Rows(iMax)("max")
               Catch ex As Exception
                   MaxMarks = 0
               End Try
           Next
           '=============End Maximum Marks=============================================

           For iM = 0 To dsMarks.Tables(0).Rows.Count - 1

               '============================Get Column Name===================================
               Dim dsColumnName As DataSet = DirectCast(cmdGetData("select * from tbTestHeading where tempID=1"), DataSet)
               For iCN = 0 To dsColumnName.Tables(0).Columns.Count - 1
                   If Convert.ToString(dsMarks.Tables(0).Rows(iM)("test")) = Convert.ToString(dsColumnName.Tables(0).Rows(0)(iCN)) Then
                       ColName = dsColumnName.Tables(0).Columns(iCN).ColumnName
                       Exit For
                   End If
               Next 'Column Name For End

               '================Change Here==========================
               '================Get Grade according Marks==============
               Dim Grade As String = ""
               Dim gradeMarks As Decimal = 0
               If Convert.ToInt32(dsMarks.Tables(0).Rows(iM)("marks")) <> 0 Then
                   gradeMarks = Convert.ToInt32(dsMarks.Tables(0).Rows(iM)("marks")) * 100 / Convert.ToInt32(Convert.ToInt32(dsMarks.Tables(0).Rows(iM)("max")))

                   Dim dsGrade As DataSet = DirectCast(cmdGetData("select * from SessionGrade"), DataSet)
                   For iG = 0 To dsGrade.Tables(0).Rows.Count - 1
                       If gradeMarks >= dsGrade.Tables(0).Rows(iG)("mf") And gradeMarks <= dsGrade.Tables(0).Rows(iG)("mt") Then
                           Grade = "\" & dsGrade.Tables(0).Rows(iG)("grade")
                           Exit For
                       End If
                   Next
               Else
                   Grade = ""
               End If
               '==============End Grade============================
               '================End Change=============================

               '================Set Marks of Current subject in curretn Test in Current Session===================

               '=============== Change here =====================
               Dim qry As String = "update tbTestHeading set " & ColName & "='" & dsMarks.Tables(0).Rows(iM)("marks") & Grade & "' where subjectName='" & dsMarks.Tables(0).Rows(iM)("shortName") & "' and tempID>" & SaveID
               '================End Change=========================
               cmdExecute(qry)
               cmdExecute("update tbTestHeading set R" & CollIndex + 2 & "='" & MaxMarks & "' where subjectName='" & dsMarks.Tables(0).Rows(iM)("shortName") & "' and tempID>" & SaveID)


           Next 'Marks For End

       Next 'Session For End

       '==========================Total=================================================
       CollIndex = CollIndex + 1
       cmdExecute("update tbTestHeading set R" & CollIndex & "='Total' where tempID=1")
       cmdExecute("update tbTestHeading set R" & CollIndex + 1 & "='Percntage' where tempID=1")
       TempIDTotal = TempIDTotal.Substring(0, TempIDTotal.Length - 1)
       QryGetTot = QryGetTot.Substring(0, QryGetTot.Length - 1)
       Dim qryTot As String = "select tempID," & QryGetTot & " FROM  tbTestHeading WHERE  (NOT (tempID IN (1," & TempIDTotal & ")))"
       Dim dsTotal As DataSet = DirectCast(cmdGetData(qryTot), DataSet)
       For iTR = 0 To dsTotal.Tables(0).Rows.Count - 1
           Dim TotMarks As Decimal = 0
           For iTC = 2 To dsTotal.Tables(0).Columns.Count - 1

               '===================== Change here =======================
               Dim MarksR As String = Convert.ToString(dsTotal.Tables(0).Rows(iTR)(iTC))
               If MarksR <> "" Then
                   Dim a = MarksR.IndexOf("\")
                   MarksR = MarksR.Substring(0, a)

                   Dim marks As Decimal = 0
                   Try
                       marks = Convert.ToDecimal(MarksR)
                   Catch ex As Exception
                       marks = 0
                   End Try

                   TotMarks = TotMarks + marks
               End If
               '=================End Change==============================
           Next


           '==============Set Total Marks===================
           cmdExecute("update tbTestHeading set R" & CollIndex & "='" & TotMarks & "' where tempID=" & dsTotal.Tables(0).Rows(iTR)(0))

           '===================Set Average======================================
           Dim Avg As Decimal = TotMarks / Convert.ToInt32(dsTotal.Tables(0).Rows(iTR)(1)) * 100
           Avg = Math.Round(Avg, 2)
           cmdExecute("update tbTestHeading set R" & CollIndex + 1 & "='" & Avg & "' where tempID=" & dsTotal.Tables(0).Rows(iTR)(0))
       Next
       MsgBox("Success")
   End Sub


The problem is that it is taking 21 seconds to run. Is anybody tell me how can I improve the performance of it
Posted

The first thing you have to do is determine where the bottlenecks are. My guess is that your queries can be better constructed, and you really should be using stored procedures instead of directly from code.

BTW, now that you have code that works, it will be easier to refactor to make it better.

Site down and think about it for a bit.
   
This article 5 Basic Ways to Improve Performance[^] might help you.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900