?
Solved

Speeding up LINQ query

Posted on 2013-01-25
4
Medium Priority
?
244 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
[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
  • 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

800 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