SolvedPrivate

horrible performance with a EF query

Posted on 2014-12-05
7
39 Views
Last Modified: 2016-02-18
I have a remote database that has sort of slow performance, I am wanting to only query it once then work off the results.

I have tried a couple of different things, I am using EF and I create the context:
dim db as new firstChoiceEntities

Then I get all of the stop info, this goes fairly quickly:
   Dim stopAddressList = From s In db.stop_address_details Select s

But then in a loop against about 1,700 items I don't want it to go back to the database everytime so I tried this:
Dim foundRecord = From f In stopAddressList Where f.customer_reference_no = customerReferenceNo Select f
if foundRecord.count > 0
.
.

It runs very slow, so I thought, OK...put everything  into a array:

 Dim stopAddressList = From s In db.stop_address_details Select s
                              
        dim countArray =stopaddresslist.count
        dim foundAddresses(countArray-1)
        foundAddresses=stopAddressList.ToArray

Open in new window


but that, while loading the array correctly (meaning in debug it has all the elements and each element has the data in it -- I can't get to address it -- meaning:
 Dim foundRecord = From f In foundAddresses() Where f.
the "f." part I was expecting to be able to put the names of the elements but they don't show up.

Any help would be great.
0
Comment
Question by:Joe Ruder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40484132
Can you post a screenshot of the database table/s that you wish to work with and on what columns you wish to filter the results?
0
 

Author Comment

by:Joe Ruder
ID: 40484148
Hello;

on the attached I am needing to query the stop_adresss_details table and do a lookup on the customer_reference_no against about 1,700 other records that are coming from the following -

I am using a reader because I am needing to hit a postgresql database as well.

thank you...

 While reader.Read
              countit2 +=1
                Dim customerReferenceNo, stopName, recBy As String
                customerReferenceNo=reader.GetValue(0).ToString
                stopName=reader.GetValue(1).ToString
                recBy=reader.GetValue(5).ToString
                    
                'go see if this is one of the sites we are monitoring
                Label1.Text = "starting"
                Application.DoEvents
               'Dim foundRecord = From f In stopAddressList Where f.customer_reference_no = customerReferenceNo Select f
               'Dim foundRecord = From f In db.stop_address_details Where f.customer_reference_no = customerReferenceNo Select f
             Dim searchfor = From f In foundsome Where f.customer_reference_no = customerReferenceNo Select f

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40484166
Is this line of code suppose to be getting the data from the database table stop_adresss? If so what is foundsome in the following statement?

Dim searchfor = From f In foundsome Where f.customer_reference_no = customerReferenceNo Select f

Lets work on the Entity Framework query. Please post the diagrams / Screenshot of the tables from Entity Designer you wish to interact with so I can help formulating a query that will return the needed information. Also state how you wish to filter the result set to bring back only those rows which are needed and not all 1,700 records.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Joe Ruder
ID: 40484216
OK,

What I have is two databases, one a postgresql that I have to query one record at a time and compare it to a subset of data from the SQL (which is sitting behind a slow connection)

The solution I have in place is:

 
          Dim searchAddressList = stopAddressList.ToArray
          searchAddressList.Clone

Then I am searching the searchAddressList and this is working very fast.

Do you see a problem with this solution?

If not I will accept yours as correct as I am confident that you would have helped me find a solution but if this works I am going to move forward.
0
 

Author Comment

by:Joe Ruder
ID: 40484220
here is the complete code, it may make more sense than me chopping it up - it is the "using reader" part that is stepping through about 1,500 records - no way to make that smaller that is simply a full days worth.

Below is forcing it to work on things in memory and seems to be working correctly and very fast.

BTW -- thank you very much for your quick responses and assist!

'get all of the stop address details
        Dim stopAddressList = From s In db.stop_address_details Select s
        Dim searchAddressList = stopAddressList.ToArray
        searchAddressList.Clone
  
        DataGridView1.DataSource=stopAddressList.ToArray
        command.CommandText="select  distinct on (customer_reference) customer_reference, stop_name, stop_address, actual_service_date, Actual_arrival_time, stop_signature from cops_reporting.distribution_stop_information where customer_no = '91000' and stop_signature <> '' and datetime_updated > '"& lastDatetimeUpdated & "' and datetime_updated < '" & endDate &"'" 
        TextBox3.Text = command.CommandText.ToString        
        PgSqlConnection1.Open
        Using reader As PgSqlDataReader =command.ExecuteReader()
          
            While reader.Read
                countit2 +=1
                Dim customerReferenceNo, stopName, recBy As String
                customerReferenceNo=reader.GetValue(0).ToString
                stopName=reader.GetValue(1).ToString
                recBy=reader.GetValue(5).ToString
                    
                'go see if this is one of the sites we are monitoring
                Label1.Text = "starting"
                Application.DoEvents
              
                Dim searchfor = From f In searchAddressList Where f.customer_reference_no = customerReferenceNo Select f
           
                label1.text = countit2.ToString              
                Application.DoEvents

                If searchfor.Count>0
                    TextBox1.AppendText ("Stop at " & customerReferenceNo & " for " & stopName & " signed for by " & recBy & vbCrLf)
                End If
                   
            End While
            Label1.Text = "record count: " & countIt & " out of " & countit2
        End Using

Open in new window

0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40484246
Hi Joe;

Well now that I have a better idea of what you are trying to do I do have a couple of suggestions.

First I would look at getting rid of the Application.DoEvents out of your code and go a different way such as using a BackgroundWorker thread. Every time through the loop you do two context switching which is going to slow down the process and may be the reason for what you are describing as horrible performance.

The next thing I would do is to get all of the info from the postgresql database into a custom class creating a List of them. Once you have that done extract the customerReferenceNo from the custom class into a List of its own to pass that list into the EF query and get all the matching records. With the results of that you can find out the information that is needed to be displayed in the text box.
0
 

Author Comment

by:Joe Ruder
ID: 40484250
hello;

The application.doevents is gone, it was just for testing purpose.  The final code will not actually update any screen objects, just monitor a table then pull the records into another table to be acted on later.

I am going to mark this as solved and open up a new ticket on how to do the custom class part of things -- do you mean to do that in place of stepping through the reader one at a time?  It would be better if I could just open the connection, read the entire result set into a class then close it.

Thank you,

Joe
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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