SolvedPrivate

horrible performance with a EF query

Posted on 2014-12-05
7
35 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
  • 4
  • 3
7 Comments
 
LVL 62

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 62

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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 62

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

23 Experts available now in Live!

Get 1:1 Help Now