Click here to Skip to main content
14,982,875 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
   
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
   
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%'
Mukesh Pr@sad 12-May-16 7:30am
   
It is taking time because of the last join . If I am removing the last join then it is working fine.

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.
   
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()
   
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 :-)
   

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