By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Slow in fetching records from table using left join

P: 3
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
Share this Question
Share on Google+
6 Replies


zmbd
Expert Mod 5K+
P: 5,397
ipskalsi,
Just one table in the database?
Oct 3 '12 #2

Rabbit
Expert Mod 10K+
P: 12,365
It's hard to say anything without knowing anything about the data and its structure.
Oct 3 '12 #3

P: 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
Expert Mod 5K+
P: 5,397
Yes,
No wonder your query is so slow...... that'll take hours to read thru!
Oct 3 '12 #5

P: 3
is there is any option to make is fast.?
Oct 3 '12 #6

Rabbit
Expert Mod 10K+
P: 12,365
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.