468,510 Members | 1,735 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,510 developers. It's quick & easy.

Slow in fetching records from table using left join

I have a table Multiple user at same time perform multiple operation on that table like insert , update , fetch etc. During fetching records i use many left outer join which decrease the performance. Is there is any solution to fetch the records fast. or i have to change the structue of table using normilisation.

any alternate of join.?

this is final query that executed..


Expand|Select|Wrap|Line Numbers
  1. select `customer`.`customerid` AS `customerid`,`customer`.`customername` AS `customername`,`customer`.`customeraddress1` AS `address1`,
  2.        `customer`.`customeraddress2` AS `address2`,`customer`.`customeraddress3` AS `address3`,`customer`.`customeraddress4` AS `address4`,
  3.        `customer`.`customerincomingno` AS `contact1`,`customer`.`customerphone2` AS `contact2`,`customer`.`customerphone3` AS `contact3`,
  4.        `customer`.`customeremailid` AS `emailid`,`customer`.`customerphone4` AS `contact4`,`customer`.`entrydate` AS `entrydate`,
  5.        `area`.`area` AS `area`,`pincode`.`pincode` AS `pincode`,`city`.`city` AS `city`,`city`.`stdcode` AS `stdcode`,
  6.        `tehsil`.`tehsilname` AS `tehsilname`,`district`.`districtname` AS `districtname`,`state`.`statename` AS `statename`,
  7.        `country`.`countryname` AS `countryname`,`customer`.`calldate` AS `calldate`,`order`.`advance` AS `advance`,
  8.        `order`.`paymentdate` AS `paymentdate`,`order`.`returndate` AS `returndate`,`order`.`dispatcherremark` AS `dispatcherremark`,
  9.        `order`.`remark` AS `remark`,`order`.`quantity` AS `quantity`,`order`.`price` AS `price`,`order`.`pnhcharges` AS `pnhcharges`,
  10.        `order`.`tax` AS `tax`,`order`.`discount` AS `discount`,`order`.`total` AS `total`,`order`.`tilldate` AS `tilldate`,
  11.        `order`.`statusdate` AS `statusdate`,`order`.`onlinestatus` AS `onlinestatus`,`callhour`.`displaycallhour` AS `displaycallhour`,
  12.        `channel`.`channelname` AS `channelname`,`order`.`orderid` AS `orderid`,`order`.`orderdate` AS `orderdate`,`order`.`avdate` AS `avdate`,
  13.        `order`.`ocdate` AS `ocdate`,`order`.`bcdate` AS `bcdate`,`order`.`docketno` AS `docketno`,`paymentmode`.`paymentmode` AS `paymentmode`,
  14.        `extension`.`extensionname` AS `extensionname`,`callcenter`.`callcentername` AS `callcentername`,`orderdetail`.`avquantity` AS `avquantity`,
  15.        `orderdetail`.`ocquantity` AS `ocquantity`,`product`.`productname` AS `productname`,       
  16.        case fkdispatchtypeid when 3 then 'Branch' when 5 then 'Courier' when 6 then 'Franchisee' end as `Dispatch By`,
  17.        case fkdispatcherid when `courier`.`courierid` is not null then `courier`.`couriername` 
  18.             when `franchisee`.`franchiseeid` is not null then `franchisee`.`franchiseename` 
  19.             else `branch`.`branchname` end AS `dispatchername`,
  20.             if(`order`.isverified=1,'Y','N') as isverified,
  21.         if(`order`.isbillmade=1,'Y','N') as isbillmade,
  22.         if(`order`.isoutcallconfirm=1,'Y','N') as isoutcallconfirm,
  23.         if(`order`.ispaymentreceived=1,'Y','N') as ispaymentreceived,
  24.         if(`order`.isreturn=1,'Y','N') as isreturn,
  25.         if(deliverypriority=1,'Normal','Urgent') as deliverypriority,
  26.         if(orderstatus=1,'Dispatch',if(orderstatus=2,'Do Not Dispatch',if(orderstatus=3,'Dispatch Till Date','Cancel'))) as orderstatus,
  27.         if(specialinstruction=1,'Home Delivery','Office Hold') as specialinstruction,
  28.  avexecutive.username,ocexecutive.username,bcexecutive.username,payexecutive.username,retexecutive.username
  29.  
  30.        from  `order`
  31.        join `customer` on`customer`.`customerid` = `order`.`fkcustomerid`
  32.        join `orderdetail` on`order`.`orderid` = `orderdetail`.`fkorderid` 
  33.        join `callhour` on`customer`.`fkcallhourid` = `callhour`.`callhourid` 
  34.        join `channel` on`customer`.`fkchannelid` = `channel`.`channelid` 
  35.        join `paymentmode` on`order`.`fkpaymentmodeid` = `paymentmode`.`paymentmodeid` 
  36.        join `callcenter` on`order`.`fkcallcenterid` = `callcenter`.`callcenterid` 
  37.        join `extension` on`order`.`fkextensionid` = `extension`.`extensionid` 
  38.        join `product` on`orderdetail`.`fkproductid` = `product`.`productid` 
  39.        left join `franchisee` on`franchisee`.`franchiseeid` = `order`.`fkdispatcherid` 
  40.        left join `courier` on`courier`.`courierid` = `order`.`fkdispatcherid` 
  41.        left join `branch` on`branch`.`branchid` = `order`.`fkdispatcherid` 
  42.        join `area` on`customer`.`fkareaid` = `area`.`areaid` 
  43.        join `pincode` on`area`.`fkpincodeid` = `pincode`.`pincodeid` 
  44.        join `city` on`area`.`fkcityid` = `city`.`cityid` 
  45.        join `tehsil` on`city`.`fktehsilid` = `tehsil`.`tehsilid` 
  46.        join `district` on`tehsil`.`fkdistrictid` = `district`.`districtid`      
  47.        join `state` on`district`.`fkstateid` = `state`.`stateid`            
  48.        join `country` on`state`.`fkcountryid` = `country`.`countryid`
  49.        left join `user` avexecutive on avexecutive.userid=`order`.fkavexecutiveid
  50.        left join `user` ocexecutive on ocexecutive .userid=`order`.fkavexecutiveid
  51.        left join `user` bcexecutive on bcexecutive.userid=`order`.fkavexecutiveid
  52.        left join `user` payexecutive on payexecutive.userid=`order`.fkavexecutiveid
  53.        left join `user` retexecutive on retexecutive.userid=`order`.fkavexecutiveid
  54.   order by `order`.`orderid`,`product`.`productid`
is five left join on same table user make it slow.?

left join `user` avexecutive on avexecutive.userid=`order`.fkavexecutiveid
left join `user` ocexecutive on ocexecutive .userid=`order`.fkavexecutiveid
left join `user` bcexecutive on bcexecutive.userid=`order`.fkavexecutiveid
left join `user` payexecutive on payexecutive.userid=`order`.fkavexecutiveid
left join `user` retexecutive on retexecutive.userid=`order`.fkavexecutiveid
Oct 3 '12 #1
6 3481
zmbd
5,400 Expert Mod 4TB
ipskalsi,
Just one table in the database?
Oct 3 '12 #2
Rabbit
12,513 Expert Mod 8TB
It's hard to say anything without knowing anything about the data and its structure.
Oct 3 '12 #3
Its has 1 parent table and many child tables. all the insert update delete fetch commands execute on parent table.
Oct 3 '12 #4
zmbd
5,400 Expert Mod 4TB
Yes,
No wonder your query is so slow...... that'll take hours to read thru!
Oct 3 '12 #5
is there is any option to make is fast.?
Oct 3 '12 #6
Rabbit
12,513 Expert Mod 8TB
Good lord! You don't have just 5 outer joins. You have 15 inner joins and 8 outer joins. Every join will slow down a query. There's not much you can do about that except to create the appropriate indexes and join only on numeric fields. But even with the indexes and proper joins, 23 joins is going to be slow no matter what you do.

I seriously doubt you need a query of this size. You seem to want to pull in every single piece of information you can. But I doubt that the users need to see this level of detail. You really should break it up into more palatable chunks of data in a variety of reports.
Oct 3 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by StealthBananaT | last post: by
reply views Thread by The Wanderer | last post: by
reply views Thread by Marek Lewczuk | last post: by
2 posts views Thread by Vector | last post: by
2 posts views Thread by Ryan | last post: by
!NoItAll
4 posts views Thread by !NoItAll | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.