0

I am quite new in Entity Framework, but not new in databases. Could you please help me, because I am so tired in searching and reading info and examples.

Actually I only need to select and show data from simple oracle view It runs really fast, but when I want to display in form, I meet some problems. LinQ query works too slow :(

I've tried million of selects. Now I wrote this:

My View has no PrimaryKey, SavKod and PlateNo are varchars.

using (var modelContext = new DataModelContext())
{
    var result = from tr in modelContext.MyView select tr;

    if (!String.IsNullOrEmpty(SearchPlateNo))
    {
        result = result.Where(tr => tr.PlateNo.Contains(SearchPlateNo));
    }

    if (!String.IsNullOrEmpty(SearchAsmKod))
    {
        result = result.Where(tr => tr.SavKod.Contains(SearchAsmKod));
    }

    MyList = result.ToList();
}

Select takes 30s if I search by AsmKod, 8s by PlateNo, 5s if I search by two arguments.

Manfred Radlwimmer
  • 13,257
  • 13
  • 53
  • 62
Songaila
  • 219
  • 1
  • 5
  • 14
  • Check the actual query that is generated by Linq (executed in the database). Another point - when you access your DataModelContext at the first time, it takes some time to prepare it (depending on your model). Try to execute it twice in a row to see if there's any difference. – Sergey L May 12 '16 at 08:41
  • Actually not a big difference between first and second time... and how to "Check the actual query" ? – Songaila May 12 '16 at 11:00
  • For MSSQL there's SQL Profiler (that allows to trace all queries to a database), I believe the same tool should exist for Oracle. Another option is tor use IntelTrace but it depends on your VS edition (It available in upper editions). See http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework and http://stackoverflow.com/questions/18237312/get-sql-query-from-linq-to-sql – Sergey L May 13 '16 at 08:15
  • So if we are talking about simple select from view, it is normal that select tooks 70s from about 6000000 records? – Songaila May 16 '16 at 08:13
  • `SavKod.Contains(SearchAsmKod)` is translated to SQL command `SavKod LIKE '%SearchAsmKod%'` and in this case any indexes on this field won't be used. If you search for full field not a part of a string you can use `SavKod.Equals(SearchAsmKod)`. In this case indexes will be used for this search. – valex May 16 '16 at 15:03
  • I understand this... For a while I changed .Contains to .StartWith, also tried .Equal, so it works good... but in the future I need to use .Contains and I wonder if i\there is a way to solve this problem – Songaila May 18 '16 at 10:57

0 Answers0