Solved

Speeding up LINQ query

Posted on 2013-01-25
4
239 Views
Last Modified: 2013-02-08
Hi guys,

I have the below query, which I want filtered on every keystroke in a textbox. Ive got it working but its painfully slow. SQ_SysproHis_Step3 is a view on the server, when run on the server it takes a good 15 seconds to run, but my understanding is that entity framework will then store alot of the data locally so i would expect the load to possibly be slow but the filtering to be quick? But this isnt the case?

 private void LoadGrid()
        {
            using (SmartQuoteEntities CounterfeitContext = new SmartQuoteEntities())
            {
                var counterfeits = from d in CounterfeitContext.SQ_SysproHis_Step3
                                   where d.StockCode.Contains(txtStockCode.Text)
                                   //orderby d.Supplier
                                   select new
                                   {
                                       d.Supplier,
                                       d.SupplierClass,
                                       d.SupplierName,
                                       d.Customer,
                                       d.Name,
                                       d.Manufacturer,
                                       d.PurchaseOrder,
                                       d.OrderDate,
                                       d.StockCode,
                                       d.CustXRef,
                                       d.SalesPerson,
                                       d.Sales_Area,
                                       d.PurchOrderQty,
                                       d.Purchase_ForeignPrice,
                                       d.Purchase_Currency,
                                       d.SoldPricePerUnit__GBP_,
                                       d.Sales_Currency

                                   };

                flx_SysproArchive.ItemsSource = counterfeits.ToList();

            }

        }



        private void txtStockCode_TextChanged(object sender, TextChangedEventArgs e)
        {
            LoadGrid();
        }

Open in new window


Many Thanks,
Dean.
0
Comment
Question by:deanlee17
  • 2
4 Comments
 
LVL 14

Expert Comment

by:binaryevo
ID: 38819415
See how long it takes to pull in the data into an IQueryable.  Looks like this:

var test = CounterfeitContext.SQ_SysproHis_Step3.AsQueryable();

Open in new window

0
 
LVL 4

Expert Comment

by:Srinivasulu Muppala
ID: 38820681
Do the testing of such a way:

First you the SQ_SysproHis_Step3 view in SSMS find how many seconds it's taking to complet

if it is not taking 15 seconds better to go by datareader concept that will reduce your execution time because Entrity framework is pure ORM in huge relational db schema little pain.
0
 

Author Comment

by:deanlee17
ID: 38824112
binaryevo, How can I test how long that takes to load?
0
 
LVL 14

Accepted Solution

by:
binaryevo earned 500 total points
ID: 38830795
The simplest way is to just set a breakpoint and step through your code.  Here is a pretty cool utility and it still looks like its got all the features it used to and more:

http://www.linqpad.net
http://stackoverflow.com/questions/11345886/linqpad-tutorial

Let me know how long the query takes and we can further dig into it a little more.  Also do what srinipro suggested and run the equivalent sql query in mgmt studio so we can use these metrics to help us figure out where the bottleneck is.  Also how many records are you pulling on average?
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

810 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