• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Speed up code

I asked a question a couple of days ago and recv'd a quick and complete response.
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21102410.html

Now the problem is with old DAO and vb6 I was able to load over 4k records in 1-3 secs. the answer I recv'd works perfect the only problem is that it takes 2-5 min to load the data.

I am faily new to ado.net and don't understand why I'm not able to create complex data tables like I did with DAO. somebody suggested we buy ComponentOne's ExpressData and DataComponents but I'm not to sure if my company will go for it.

the code I'm trying to speed up is:

        Dim strID As String
        Dim strLname As String
        Dim StrFname As String
        Dim rcdcnt As Integer
        Dim nIndex
        Dim nbar
        Dim i

        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=narvis.mdb;User Id=admin;Password=;"
        Dim OleDbCon As New OleDbConnection(ConStr)
        Dim SqlStr As String
        SqlStr = "Select * from tblagents"
        Dim OleDA As New OleDbDataAdapter(SqlStr, OleDbCon)
        Dim DS As New DataSet
        OleDbCon.Open()
        OleDA.Fill(DS)
        OleDbCon.Close()

        Me.ctSchedule0.DateStart = 38215

        ctSchedule0.AddColumn("Agent ID", 50)
        ctSchedule0.AddColumn("Last", 100)
        ctSchedule0.AddColumn("First", 100)
        Me.ctSchedule0.TimeType = 3

        Dim DR As DataRow
        For Each DR In DS.Tables(0).Rows
            strID = RTrim(DR.Item("agent_id"))
            strLname = RTrim(DR.Item("l_name"))
            StrFname = RTrim(DR.Item("f_name"))
            nIndex = Me.ctSchedule0.AddItem(strID & ";" & strLname & ";" & StrFname)

            SqlStr = "select TimeStart,TimeEnd,Date from tblTransactions where agent_id = " & strID
            Dim OleDbCmd As New OleDbCommand(SqlStr, OleDbCon)
            OleDbCon.Open()
            Dim OleDtRdr As OleDbDataReader = OleDbCmd.ExecuteReader()
            While (OleDtRdr.Read)
                nbar = Me.ctSchedule0.AddTimeBar(nIndex, OleDtRdr.GetInt32(0), OleDtRdr.GetInt32(1), OleDtRdr.GetValue(2), OleDtRdr.GetValue(2))
            End While
            OleDbCmd.Dispose()
            OleDtRdr.Close()
            OleDbCon.Close()
        Next
0
moralju
Asked:
moralju
  • 10
  • 9
  • 7
  • +2
1 Solution
 
gregoryyoungCommented:
why not read all of the transactions you need as well in 1 read and then use a datarelationship in your dataset as opposed to issuing 4000 queries ?
0
 
moraljuAuthor Commented:
one table contains all the agent records and the other table contains all the transactions.
I have to add one agent to the control and then add each transaction to that agent.
its not possible to do it in one sweep.. if it was then that would be awsome
0
 
gregoryyoungCommented:
it is possible to get all the records into a dataset at one time, then use a datarelation instead of requerying.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
navneet77Commented:
yes i think requerying for every agent row is making it slow.

just fill the two tables tblTransactions and tbl agents and add a datarelatio to the dataset

dim dr as new datarelation("agent-trans",DS.Tables(0).columns("agent_id"),DS.Tables(1).columns("agent_id"))

ds.relations.add(dr)

hope this helps
0
 
DotNetLover_BaanCommented:
ya, I thought the same while answering his first question. But Greg, there is a problem...
nIndex = Me.ctSchedule0.AddItem(strID & ";" & strLname & ";" & StrFname)
Here he needs DISTINCT Agent_Id... I mean each agent has unique nIndex.

But while adding to the "nbar"...
nbar = Me.ctSchedule0.AddTimeBar(nIndex, OleDtRdr.GetInt32(0), OleDtRdr.GetInt32(1), OleDtRdr.GetValue(2), OleDtRdr.GetValue(2)) .....    there he wants all the transaction for that agent.

You see my point ?

-Baan
0
 
gregoryyoungCommented:
did I say to JOIN them ? 2 reads ... 1 for each table, operate on your dataset with a datarelation.
0
 
DotNetLover_BaanCommented:
Ok, here is the PLAN of ACTION: (my suggestion, please correct me if I am wrong)
How about if we make two tables in the dataset... one with DISTINCT AgentID and the other one is basically all the rows of Transaction table. Now we will do something like...
for each row in first table...
 > nIndex = Me.ctSchedule0.AddItem(strID ...   , and get the nIndex.
 > Now if we can make filtered selection from the second table.... (We can filter the view .. right ?)
 > insert to "nbar"
Next Row

- Baan
0
 
gregoryyoungCommented:
is this not what I said ?
0
 
DotNetLover_BaanCommented:
cool....  Greg is agreed with me... "Wise Men think alike"... lol :))
0
 
Xentor_Commented:
I don't remember whether MDBs support subqueries, but if they do....

select agent_id, TimeStart,TimeEnd,Date from tblTransactions where agent_id IN (Select distinct agent_id from tblagents)

This way, you let the ADO engine do all the work.
0
 
DotNetLover_BaanCommented:
This will create a single table in stead of two tables. Our problem is still not solved...
0
 
gregoryyoungCommented:
use 2 SQL statements one for each table .....

SELECT * from tblagents

select agent_id, TimeStart,TimeEnd,Date from tblTransactions where agent_id IN (Select distinct agent_id from tblagents)

then create a data relation between the two tables in your dataset.
0
 
moraljuAuthor Commented:
why does something so simple seem so hard.

there has to be a way..
0
 
gregoryyoungCommented:
it is very simple ... I just told you how to do it.
0
 
moraljuAuthor Commented:
but the problem is that the DBI can not be bound to a dataset. I have to add the list of agents then add each time bar per agent.


0
 
moraljuAuthor Commented:
maybe I didn't understand you comment gregoryyoung.. I am faily new to vb.net and wasn't aware of a datarelation.
my mind seems to be stuck in the vb6 world. I talked my employer into upgrading to vb.net and now I'm faced with learning new code.
if possible can you please provide me with a sample code of a datarelation.
0
 
gregoryyoungCommented:
there are three ways you could do this ... one would using a datarelation ...

another would be to use DataTable.Select() to grab your rows

yet another would be to use a dataview to filter the data in the second table example ...

dim dv as DataView = new DataView(ds.Tables("Transactions"))

foreach row in ds..Tables("Agents").Rows
     dv.RowFilter = "agentid = " & currentrowsid
     'when looked at dv only contains the rows for this agent
next

this is a viable solution but the datarelation solution is a bit more elegant
0
 
DotNetLover_BaanCommented:
This is what I have in my mind...  Lets see what Greg says...

        Dim strID As String
        Dim strLname As String
        Dim StrFname As String
        Dim rcdcnt As Integer
        Dim nIndex
        Dim nbar
        Dim i

        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=narvis.mdb;User Id=admin;Password=;"
        Dim OleDbCon As New OleDbConnection(ConStr)
        Dim SqlStr As String
        SqlStr = "select agent_id, TimeStart,TimeEnd,Date from tblTransactions where agent_id IN (Select distinct agent_id from tblagents)"
        Dim OleDA As New OleDbDataAdapter(SqlStr, OleDbCon)
        Dim DS As New DataSet
        OleDbCon.Open()
        OleDA.Fill(DS)
        OleDbCon.Close()

        Me.ctSchedule0.DateStart = 38215

        ctSchedule0.AddColumn("Agent ID", 50)
        ctSchedule0.AddColumn("Last", 100)
        ctSchedule0.AddColumn("First", 100)
        Me.ctSchedule0.TimeType = 3

        Dim OldID As String = RTrim(DS.Tables(0).Rows(0).Item("agent_id"))
        'Dim NewID As String = ""

        strID = RTrim(DS.Tables(0).Rows(0).Item("agent_id"))
        strLname = RTrim(DS.Tables(0).Rows(0).Item("l_name"))
        StrFname = RTrim(DS.Tables(0).Rows(0).Item("f_name"))
        nIndex = Me.ctSchedule0.AddItem(strID & ";" & strLname & ";" & StrFname)

        Dim DR As DataRow
        For Each DR In DS.Tables(0).Rows
            strID = RTrim(DR.Item("agent_id"))
            strLname = RTrim(DR.Item("l_name"))
            StrFname = RTrim(DR.Item("f_name"))
            If strID <> OldID Then
                nIndex = Me.ctSchedule0.AddItem(strID & ";" & strLname & ";" & StrFname)
            End If
            nbar = Me.ctSchedule0.AddTimeBar(nIndex, RTrim(DR.Item("TimeStart")), RTrim(DR.Item("TimeEnd")), RTrim(DR.Item("Date")), RTrim(DR.Item("Date")))
            OldID = RTrim(DR.Item("agent_id"))
        Next

-Baan
0
 
DotNetLover_BaanCommented:
I guess it is better to sort the SELECTION first..

        SqlStr = "select agent_id, TimeStart,TimeEnd,Date from tblTransactions where agent_id IN (Select distinct agent_id from tblagents Order By agent_id)"

-Baan
0
 
moraljuAuthor Commented:
it doesn't find l_name, f_name

          strLname = RTrim(DR.Item("l_name"))
          StrFname = RTrim(DR.Item("f_name"))

thank you for all your help by the way..
0
 
DotNetLover_BaanCommented:
oops... sorry about that...  need to change the SELECT statement...
        SqlStr = "select A.agent_id, A.l_Name, A.f_Name,T.TimeStart,T.TimeEnd,T.Date From tblagents A,tblTransactions T where T.agent_id=A.agent_id Order By A.agent_id"
0
 
moraljuAuthor Commented:
OH MY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Finally!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 This website rocks...
My boss was about to cut my balls off.


only one little problem though it doesn't load the first lname and fname of the first agent
0
 
DotNetLover_BaanCommented:
ok... ya... you are right... the problem with the SQL statement we have is... if any agent doesn't have any transaction its not gonna select that agent id ....
Let me think about it...
0
 
moraljuAuthor Commented:
agent 1 does have a transaction. 4 of them to be exact..
0
 
DotNetLover_BaanCommented:
       strID = RTrim(DS.Tables(0).Rows(0).Item("agent_id"))
        strLname = RTrim(DS.Tables(0).Rows(0).Item("l_name"))
        StrFname = RTrim(DS.Tables(0).Rows(0).Item("f_name"))
        nIndex = Me.ctSchedule0.AddItem(strID & ";" & strLname & ";" & StrFname)

this part is for the first agent... I am surprised... why it is not doing...  are you getting the value of nIndex..  here ?
0
 
moraljuAuthor Commented:
I didn't notice I had removed some part of that while I was messing with the first version you gave me..
all fix

Let the celebration continue..


la la la la la

Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

0
 
DotNetLover_BaanCommented:
great... now the real issue....  is it faster now ?
0
 
moraljuAuthor Commented:
5000 records in .0001 second.


ROCKS!!!!

its even faster then in VB6
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 10
  • 9
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now