Last post Oct 08, 2016 08:12 AM by wmec
Sep 30, 2016 07:12 AM|iamdevloper91|LINK
select t1.MasterSpeck_id,t1.FEIN,t1.FileInfo as FileName,t1.UploadedDateTime
,t1.ProcessDateTime as ProcessDateTime,t1.fileType,t1.Version,t1.AnalyzeDateTime as AnalyzedDateTime,t1.Status,t1.ErrorStatus,t2.UserName as LastUpdatedUserName,
t3.PayDate,t4.PayrollCode,t4.id,(select(left(t4.TaxId,locate('-',t4.TaxId)-1)) from tbltaxcodes1 t4 where t4.MasterTaxId=t3.TaxCode) as AuthorityId
from tblcust_ptsimportcrosscheck t1 , tblbusinessbasicinformation t2, tblcust_payrolltaxdetails t3,tblcust_payrollheader t4
where t1.MasterSpeck_id=t3.MasterSpeck_id and t1.MasterSpeck_id=t4.MasterSpeck_id and t1.FEIN=t3.FEIN and t1.FEIN=t4.FEIN
and t1.BusinessPartnerId=t2.BusinessPartnerId and t1.BusinessPartnerId=t3.BussinessPathnerID and t1.BusinessPartnerId=t4.BussinessPathnerID and t2.BusinessPartnerId=_BusinessPartnerId
group by t1.FileInfo order by t1.UploadedDateTime desc;
this is my query its taking so much time to execute .is this correct way to write other wise modify and tell me the correct way.
Sep 30, 2016 07:36 AM|PatriceSc|LINK
This syntax (multiple tables in a from clause) is very old. Using
http://www.w3schools.com/sql/sql_join.asp is preferred (in some cases the order in which WHERE conditions are handled could alter the resullt, using a separate JOIN clause allows to better define when JOINs are done ie always before the final WHERE clause).
In addition to solving a posisnly ambiguity it is also likely much more easier to understand and change the query when coming back few months later.
That said it might be unrelated to your issue. instead you should check for example the query plan for execution problems. A basic measure is to make sure you have indexes on joined columns. Beyond don't guess but use tools to get some insight about how
the query runs.
Make sure also to fully understand what means "slow" (ie sometimes one complains about a query being slow because it shows millions of rows)
Edit: looks weird to have two from clause following one each other. Also I wanted to add that something that works quite well for me is to "reconstruct" the query step by step so that you can spot at which point it starts to perform poorly.
Sep 30, 2016 08:54 AM|Deepak Panchal|LINK
below are some general suggestion to improve the performance of query.
Oct 08, 2016 08:12 AM|wmec|LINK
Check if you have proper indexes created for matching columns of the tables.