[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Speeding up LINQ query

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
deanlee17
Asked:
deanlee17
  • 2
1 Solution
 
binaryevoCommented:
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
 
Srinivasulu MuppalaLead DeveloperCommented:
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
 
deanlee17Author Commented:
binaryevo, How can I test how long that takes to load?
0
 
binaryevoCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now