Solved

Speeding up LINQ query

Posted on 2013-01-25
4
243 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 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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 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