Click here to Skip to main content
15,445,641 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I have four tables in my database , I am using linq query to retrieve data from all the tables. I did left join to join all the tables it is giving the correct result but it is taking too much time. how to optimize this query,

What I have tried:

I have tried the below query but it is too much time like more than 10 minutes for 30000 records.

C#
var searchedData = (from a in objContext.FileProgresses
     join pg in objContext.V01_PG on a.ProDocsId equals (int?)pg.ID into pgs
     from m in pgs.DefaultIfEmpty()
     join pr in objContext.V01_PR on m.ID equals pr.PAGE into prs
     from p in prs.DefaultIfEmpty()
     join ds in objContext.DOCSTATs on p.DOCSTAT equals ds.ID into docs
     from docst in docs.DefaultIfEmpty()
     where a.FullPath.Contains(txtSearchText.Text)
     select new
     {
          a.Id,
          a.FullPath,
          a.Filename,
          a.Extension,
          a.Received,
          a.Downloaded,
          a.Unsupported,
          a.ProcessByAbbyy,
          a.AvailableInProDocs,
          a.AvailableInDocs2Go,
          a.Done,
          p.BARCODE,
          m.DOCNO,
          p.REVISION,
          docst.NAME
     }).ToList();


any help would be appreciated.
Thanks,
Mukesh
Posted
Updated 16-Jun-16 3:45am
v3
Comments
Rob Philpott 11-May-16 12:01pm    
How long does it take, if you execute an equivalent SQL query in SSMS or whatever?
Mukesh Pr@sad 12-May-16 2:20am     CRLF
more than 10 minutes
Rishikesh_Singh 12-May-16 3:08am    
Can you check what is the difference in your query when you run directly in ssms and query generated by linq in sql profiler
Mukesh Pr@sad 12-May-16 6:17am    
Sorry But it is taking same time at both the places . means More than 10 minutes.
Mukesh Pr@sad 12-May-16 6:34am     CRLF
Below is the equivalent sql query which is taking 13:03 minutes to retrieve the data. is there any query better than this. Please help. select FileProgress.Id, fullpath,Extension, Received, Downloaded,Unsupported,ProcessByAbbyy,AvailableInProDocs,Done,DOCNO,REVISION,DOCSTAT.NAME From FileProgress Left Join V01_PG on FileProgress.ProDocsId=V01_PG.ID Left Join V01_PR on V01_PG.ID = V01_PR.PAGE Left Join DOCSTAT on V01_PR.DOCSTAT = DOCSTAT.ID where FileProgress.FullPath like '%2014AL0206%'

Hi,
C#
var searchedData = (from a in objContext.FileProgresses
     join pg in objContext.V01_PG on a.ProDocsId equals (int?)pg.ID into pgs
     from m in pgs.DefaultIfEmpty()
     join pr in objContext.V01_PR on m.ID equals pr.PAGE into prs
     from p in prs.DefaultIfEmpty()
     join ds in objContext.DOCSTATs on p.DOCSTAT equals ds.ID into docs
     from docst in docs.DefaultIfEmpty()
     select new
     {
          Id = a.Id,FullPath = a.FullPath etc.
}).AsQueryable().AsNoTracking().Where(x=>x.FullPath.Contains(Searchtext)).ToList();


Can you try this code bro ?
I'm wait result. Please,feedback.
Have a good day.
 
Share this answer
 
v2
Comments
Mukesh Pr@sad 12-May-16 9:42am    
AsNoTracking() is giving error.
Sinan Ergin 12-May-16 9:58am    
Hmm. Look at I'm Solution 2.
Rishikesh_Singh 12-May-16 22:31pm    
Instead of creating multiple solution , edit the existing solution.
You can try bro.
Just necessary columns and tables.
C#
var searchedData = 
  (from a in objContext.FileProgresses
     where a.FullPath.Contains(txtSearchText.Text)
     select new
     {
          ItemA = a,
          IEnumerable<otheritems> = 
                from m in objContext.V01_PG
                join pr in objContext.V01_PR on m.ID equals pr.PAGE 
                      into prs from p in prs.DefaultIfEmpty()
                join ds in objContext.DOCSTATs on p.DOCSTAT equals ds.ID 
                      into docs from docst in docs.DefaultIfEmpty() 
                where (int?)m.ID == a.ProDocsId 
                select new { p.BARCODE,  m.DOCNO, p.REVISION, docst.NAME }
  }).ToList()
 
Share this answer
 
v3
below is the working code. forgot to post.

C#
var DistincItem = (from a in objContext.FileProgresses
                                    join pg in objContext.V01_PG on a.ProDocsId equals (int?)pg.ID into pgs
                                    from g in pgs.DefaultIfEmpty()
                                    join pr in objContext.V01_PR on g.ID equals pr.PAGE into prs
                                    from p in prs.DefaultIfEmpty()
                                    where a.FullPath.Contains(extension)
                                    select new
                                    {
                                        a.Id,
                                        a.FullPath,
                                        a.Filename,
                                        a.Extension,
                                        a.Received,
                                        a.Downloaded,
                                        a.Unsupported,
                                        a.ProcessByAbbyy,
                                        a.AvailableInProDocs,
                                        a.AvailableInDocs2Go,
                                        a.Done,
                                        p.BARCODE,
                                        g.DOCNO,
                                        p.REVISION,
                                        a.NotAllowed,
                                        p.DOCSTAT
                                    }).ToList();


Thanks for the help :-)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900