CODEWITHSUNDEEP
vb.netperformancegridviewdata-binding
Rodrigo Abib
Rodrigo Abib

Reputation: 83

Databind() in GridView VERY slow!! VB.NET

I´m facing a terrible problem in my code. When I execute DataBind method, the page loads and loads and after almost 5 minutes the GridView is filled. It´s not a SQL query problem!

I tested with Visual Studio debuger and the code stops on DataBind()

Protected Sub btnShow_Click(ByVal sender As Object, ByVal e As System.EventArgs)

        /* This is not the correct code but at the end, the result will be same as below */

        Dim feedback As String = "positive"
        Dim date As String = "2013"

        bindDataShowDetails(feedback, date) // <----- I call this method ( below )

  End Sub

Protected Sub bindDataShowDetails(ByVal feedback As String, ByVal Data As String())

        feedbackGlobal = feedback

        Dim strSql As String = ""

        strSql = " select "
        strSql += " feedback, zendesk_ticket_id,feedback_text as Comment, date_ins as Ticket_date, date_feedback as Feedback_date, comment_review, review_status "
        strSql += "  from feedbacks_support "
        strSql += " where "
        strSql += " feedback = '" & feedback & "'" // <---- 'positive'
        strSql += " and YEAR(date_feedback) = " & Date // <---- '2013'


        Dim myreader As SqlDataReader = admin2.ExecReader(strSql) // <--- Class 'admin2' calls the method (ExecReader) thats executes the SQL query and return the result.

        GridView1.DataSource = myreader
        GridView1.DataBind() <----------- Problem is here!!!

        Me.ModalPopupExtender1.Show()


    End Sub

I ran the SQL query directy in SQL Server and it runs fine!

I really don´t know what is wrong! Thanks very much for your support!

Upvotes: 0

Views: 2266

Answers (1)

suff trek
suff trek

Reputation: 39777

It's not a good idea to display many thousand records at once - this could be major reason for performance issues. One way to alleviate this is to retrieve data into a DataTable, cache that datatable and use it in paging to display, say 50 records per page. (this way you won't have to hit database for every page change/rebind).

And even this approach works for limited number of records, if that number becomes huge - even retrieving all records into in-memory DataTable is not an option, you will have to implement server-side paging to retrieve only a portion of data from DB at a time.

That said, another common reason for slowdown when query is called from .NET code vs. direct execution in SSMS could be parameter sniffing. If SQL Server built and cached execution plan that is not optimal for curent query execution - it may really slow it down. Try adding following line at the end of your code that builds SQL Statement:

strSql += " OPTION (RECOMPILE) ";

to alleviate that problem.

Upvotes: 2

Related Questions