提高程序性能 [英] Increase the performance of program

问题描述

朋友，我在vb.net中有以下代码:

Friends I have the following code in vb.net :

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

问题是运行需要21秒.有人告诉我如何改善它的性能吗?

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

推荐答案

您要做的第一件事就是确定瓶颈在哪里.我的猜测是您的查询可以更好地构建，并且您确实应该使用存储过程，而不是直接从代码中进行存储.

顺便说一句，既然您的代码可以正常工作，则重构起来会变得更加容易.

站点下来，仔细考虑一下.

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.

本文 5种基本方式可以提高性能 [ ^ ]帮助您.

这篇关于提高程序性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！