Solved

Speed up code

Posted on 2004-08-22
28
217 Views
Last Modified: 2010-04-23
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
Comment
Question by:moralju
  • 10
  • 9
  • 7
  • +2
28 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11866865
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
 
LVL 1

Author Comment

by:moralju
ID: 11866951
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11867308
it is possible to get all the records into a dataset at one time, then use a datarelation instead of requerying.
0
 
LVL 2

Expert Comment

by:navneet77
ID: 11867452
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11869362
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11870046
did I say to JOIN them ? 2 reads ... 1 for each table, operate on your dataset with a datarelation.
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11870295
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11870596
is this not what I said ?
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11870630
cool....  Greg is agreed with me... "Wise Men think alike"... lol :))
0
 
LVL 5

Expert Comment

by:Xentor_
ID: 11872745
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11873321
This will create a single table in stead of two tables. Our problem is still not solved...
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11873527
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
 
LVL 1

Author Comment

by:moralju
ID: 11873800
why does something so simple seem so hard.

there has to be a way..
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11873826
it is very simple ... I just told you how to do it.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:moralju
ID: 11876189
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
 
LVL 1

Author Comment

by:moralju
ID: 11876293
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11876383
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
 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 500 total points
ID: 11876506
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11876524
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
 
LVL 1

Author Comment

by:moralju
ID: 11876751
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11876792
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
 
LVL 1

Author Comment

by:moralju
ID: 11876853
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11876890
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
 
LVL 1

Author Comment

by:moralju
ID: 11876900
agent 1 does have a transaction. 4 of them to be exact..
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11876911
       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
 
LVL 1

Author Comment

by:moralju
ID: 11876952
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
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11876969
great... now the real issue....  is it faster now ?
0
 
LVL 1

Author Comment

by:moralju
ID: 11877021
5000 records in .0001 second.


ROCKS!!!!

its even faster then in VB6
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now