dcsimg
Results 1 to 3 of 3
  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2016
    Posts
    20

    Data Retrieving is slower in nodes

    Here is my data retrieving code :


    Code:
     Dim vDT1 As New DataTable
    
              vSqlText = "SELECT * from From B_RecMast where SOMECONDs order by RecID"
                
              If RecordCNT(vSqlText) = 0 Then
                    MsgBox("No Record is available", vbExclamation)
                    Grid_Reset(Grid1)
                    Exit Sub
                End If
    
                cmd = New OleDbCommand(vSqlText, cnnRice2)
                vRs = cmd.ExecuteReader()
    
                Do While vRs.Read
                    
                    Dim vNR As DataRow
                        vNR = vDT1.NewRow()
                        vNR.Item("ReceiptNo") = vRs("ReceiptNo")
                        vNR.Item("ReceiptDate") = vRs("RectDate")
                        vNR.Item("Mode") = vRs("Mode")
                        vNR.Item("Amt") = vRs("AmtRecdThisRecpt")
                        vNR.Item("Name") = vRs("Name")
                        vDT1.Rows.Add(vNR)
                Loop
    
                Grid1.DataSource = vDT1
    This code retrieves hundreds of records in few seconds in the server PC where the database (Access2007) is there.
    Other PCs connected to this Server PC in LAN environment.
    In the other PCs retrieving takes several minutes. Why this delay is happening ?

    Note : Application folder is Shared in the server PC

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    98,915

    Re: Data Retrieving is slower in nodes

    Firstly, don't populate a DataTable that way. Either use an data adapter and call Fill or, if you're going to use a data reader, call Load on the data reader.

    Secondly, I have a sneaking suspicion that you are actually executing your query twice. What exactly does that RecordCNT method do? I don't see how it could be getting the number of records retrieved by a query without executing the query, which would be a very silly thing to do. Even if it doesn't do that you should still get rid of that section. If you want to know whether your result set is empty or not then you test the HasRows property of the data reader or, if you use a data adapter, the value returned by Fill. That Grid_Reset method seems pointless too as you can simply bind the empty DataTable and the grid will be empty.

    As for your issue, if the code works as it should then the code is fine. If it takes more time to retrieve data over the network than locally then I'd say that there's a fair chance that it's related to the network. Using Access databases over a network is just a bad idea. If you used a "proper" database, e.g. SQL Server, then things could well be better, unless there genuinely is an issue with the network unrelated to the use of an Access database. The Express edition of SQL Server is free and can support databases up to 10 GB, so there's usually not a good reason to not use it.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Oct 2016
    Posts
    20

    Re: Data Retrieving is slower in nodes

    @ JMC
    Thank you so much. Here is my stand why I use the Access DB

    1. I need to put the database in my client’s PC itself. (They work offline only.)
    2. If I use the Access database with PW protection no one can open it and view my tables.
    3. On the other hand if I use the Sql Database, I don’t know how to secure my Database as above.

    How to make it in such a way that no one be able to view the Table schemas even in windows authentication mode in case I use SqlServer ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.