none
How can I make my adodc connect faster to my SQL Server? It's taking a minute (so long) before I can view thousand of record in my listview. RRS feed

  • Question

  • How can I make my adodc connect faster to my SQL Server? It's taking a minute (so long) before I can view thousand of record in my listview. Please anyone help me.

    I'm using this code:

    Public Class McheckpaymentNew
    
    Private cn As New ADODB.Connection
    Private rs As New ADODB.Recordset
    
    Private Sub McheckpaymentNew_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        Try
    
            cn.ConnectionString = "DSN=database; UID=user; PWD=password"
            cn.Open()
    
            rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
    
        Catch ex As Exception
            MsgBox("Failed to Connect!, Please check your Network Connections, or Contact MIS Dept. for assistance.", vbCritical, "Error while Connecting to Database.."
            End
        End Try
    
    End Sub
    End Class
    • Moved by Tina-Shi Tuesday, March 24, 2015 1:28 AM the issue is related to the ADO.NET
    Monday, March 23, 2015 2:26 AM

Answers

  • Why is the data access code in a .NET program using ADODB, a COM-based circa-VB6 API? And it's using the COM-based client cursor library to run the rows though. You might try rewriting it to use the SqlClient .NET data provider and see if its any faster, because it won't have to go through all the .NET/COM translations.

    It still shouldn't take a minute though, and I agree with the suggestion that you see how much of that time is spent connecting to SQL Server vs. executing the SQL statement, vs. retrieving the rows and populating the GUI component to see where the code is spending its time. You could do this by looking at the current time in between statements or by using a debugger.

    This isn't the forum for this problem, but there isn't really a forum that specializes in COM/.NET and ADO (classic) data access. So I'll move it to the closest forum (the one for client-side .NET data access. Even though you're not using a .NET data provider, you should be. It might also be possible to refer the question to the ODBC/SQL Native Client forum, because your actually using the OLE DB provider for ODBC. 

    Hope this helps, Bob 


    Monday, March 23, 2015 5:29 AM
  • Hello eprodil,

    >>How can I make my adodc connect faster to my SQL Server?

    From your provided code, it seems that you are use ADO api, I suggest that you could have a try with ADO.NET api which provided a better performance:

    ADO.NET

    Comparison of ADO.NET and ADO

    >> It's taking a minute (so long) before I can view thousand of record in my listview

    If you consider using the ADO.NET and these records are just used to be displayed, you could use the SqlDataReader to retrieve a read-only, forward-only stream of data from a database which is fast: https://msdn.microsoft.com/en-us/library/ms254931(v=vs.110).aspx or using SqlDataAdapter to Paging Through a Query Result

    If you want to use the ADO only, according to this link, you could ask it on the sqldataaccess forum.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 24, 2015 3:28 AM
    Moderator

All replies

  • How can I make my adodc connect faster to my SQL Server? It's taking a minute (so long) before I can view thousand of record in my listview. Please anyone help me.

    I'm using this code:

    Public Class McheckpaymentNew
    
    Private cn As New ADODB.Connection
    Private rs As New ADODB.Recordset
    
    Private Sub McheckpaymentNew_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        Try
    
            cn.ConnectionString = "DSN=database; UID=user; PWD=password"
            cn.Open()
    
            rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
    
        Catch ex As Exception
            MsgBox("Failed to Connect!, Please check your Network Connections, or Contact MIS Dept. for assistance.", vbCritical, "Error while Connecting to Database.."
            End
        End Try
    
    End Sub
    End Class

    • Moved by Bob Beauchemin Monday, March 23, 2015 5:32 AM Moved to the closest relevant forum
    • Merged by Fred BaoModerator Tuesday, March 24, 2015 7:40 AM Duplicate
    Monday, March 23, 2015 2:27 AM
  • What are you executing against SQL Server? Did you execute the query within SQL Server client to check how fast you are getting the results back?
    Monday, March 23, 2015 3:08 AM
  • i dont know where i can post my problem regarding to vb.net..how to do that?..
    Monday, March 23, 2015 3:15 AM
  • Did you debug within VB.net to figure out whether the connection to SQL Server is taking time or getting the results back from SQL Server? 
    Monday, March 23, 2015 3:22 AM
  • yes it is..any suggestions?
    Monday, March 23, 2015 5:13 AM
  • Why is the data access code in a .NET program using ADODB, a COM-based circa-VB6 API? And it's using the COM-based client cursor library to run the rows though. You might try rewriting it to use the SqlClient .NET data provider and see if its any faster, because it won't have to go through all the .NET/COM translations.

    It still shouldn't take a minute though, and I agree with the suggestion that you see how much of that time is spent connecting to SQL Server vs. executing the SQL statement, vs. retrieving the rows and populating the GUI component to see where the code is spending its time. You could do this by looking at the current time in between statements or by using a debugger.

    This isn't the forum for this problem, but there isn't really a forum that specializes in COM/.NET and ADO (classic) data access. So I'll move it to the closest forum (the one for client-side .NET data access. Even though you're not using a .NET data provider, you should be. It might also be possible to refer the question to the ODBC/SQL Native Client forum, because your actually using the OLE DB provider for ODBC. 

    Hope this helps, Bob 


    Monday, March 23, 2015 5:29 AM
  • can you give me the steps to do that?..
    Monday, March 23, 2015 5:45 AM
  • yes it is..any suggestions?

    There were two options in that question. Which one? Is it the Open statement that is taking a long time?

    Displaying a thousand rows in a ListView could also take a while, and there is generally no reason to fetch that many because they won't be visible on the screen.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, March 23, 2015 2:49 PM
  • Hi eprodil,

    Thank you for posting in MSDN forum.

    Since this issue is related to the ADO.NET, so we will move this case to this ADO.NET forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders , you will get better support.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 24, 2015 1:27 AM
  • Hello eprodil,

    >>How can I make my adodc connect faster to my SQL Server?

    From your provided code, it seems that you are use ADO api, I suggest that you could have a try with ADO.NET api which provided a better performance:

    ADO.NET

    Comparison of ADO.NET and ADO

    >> It's taking a minute (so long) before I can view thousand of record in my listview

    If you consider using the ADO.NET and these records are just used to be displayed, you could use the SqlDataReader to retrieve a read-only, forward-only stream of data from a database which is fast: https://msdn.microsoft.com/en-us/library/ms254931(v=vs.110).aspx or using SqlDataAdapter to Paging Through a Query Result

    If you want to use the ADO only, according to this link, you could ask it on the sqldataaccess forum.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 24, 2015 3:28 AM
    Moderator
  • Hello sir, thank you for the reply, can you please convert my coding into ADO.NET?..i dont know where to start.
    Friday, March 27, 2015 12:50 AM
  • Hello eprodil,

    We will not help convert the code, it is beyond the scope of our support for this forum. Maybe a sample could help you understand it:

    ADO.NET Code Examples

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, March 27, 2015 9:45 AM
    Moderator