3

I have a SQL that have condition IN is to long. And it take my SQL too much time to execute is there any way to improve the performance of it?

Here is the SQL

SELECT * FROM bi_bunrui_syouhin_shop_day_jyutyuu AS bi   
WHERE bi.bunrui_code IN     
('0001','1000','1001','1005','1006','1007','1008','1009','1010','1011','1012','1013','1014','1015','1016','1017','1018','1020','1021','1022','1023','1030','1031','1032','1033','1034','1036','1037','1038','1039','1040','1041','1043','1044','1045','1046','1050','1054','1055','1060','1061','1070','1080','1081','1082','1083','1084','1085','1086','1088','1090','1091','1092','1093','1094','1095','1096','1097','1100','1101','1102','1103','1104','1105','1106','1107','1108','1109','1110','1111','1112','1113','1114','1115','1116','1117','1118','1119','1120','1121','1122','1123','1130','1131','1132','1133','1134','1135','1136','1137','1140','1141','1142','1143','1145','1148','1150','1151','1152','1153','1154','1155','1156','1157','1160','1161','1162','1163','1164','1165','1166','1167','1168','1169','1170','1171','1180','1181','1182','1183','1184','1187','1189','1200','1201','1202','1203','1204','1205','1206','1207','1210','1211','1212','1220','2003','2009','2022','2028','2042','2048','2062','2068','2082','2088','2102','2108','2122','2125','2128','2129','2134','2141','2144','2161','2182','2186','2222','2228','2242','2253','2262','2268','2302','2511','2512','2513','3180','3207','3208','4023','4024','4028','4029','4030','4031','4032','4033','4034','4036','4040','4041','4042','4043','4044','4062','4064','4066','4067','4068','4069','4070','4071','4092','5013','5014','5015','5016','5017','5018','6200','6201','6202','6203','6204','6205','6220','6221','6222','6223','6224','6225','6226','6234','6235','6240','6241','6242','6243','6244','6245','6246','6247','6249','6250','6251','6260','6261','6262','6263','6264','6265','6280','6281','6282','6283','6284','6285','6286','6287','6288','6289','6290','6291','6292','6293','6300','6301','6302','6303','6304','6305','6320','6321','6322','6323','6324','6325','6340','6342','6343','6344','6345','6346','6347','6348','6349','6360','6361','6362','6363','6364','6365','6366','6367','6368','6369','6371','6372','6373','6374','6375','6376','6377','6378','6379','6380','6381','6382','6383','6384','6385','6400','6401','6402','6403','6406','6407','6408','6420','6421','6422','6423','6424','6425','6426','6427','6428','6429','6430','6431','6432','6433','6434','6435','6436','6440','6441','6442','6443','6444','6445','6446','6447','6448','6449','6450','6451','6452','6453','6454','6455','6456','6457','6458','6459','6460','6470','6471','6472','6473','6474','6475','6476','6477','6478','6480','6481','6482','6483','6484','6485','6486','6487','6489','6490','6500','6501','6502','6504','6505','6506','6507','6508','6509','6510','6511','6512','6513','6514','6515','6520','6521','6522','6523','6524','6525','6526','6527','6528','6529','6530','6531','6550','6551','6552','6553','6554','6555','6556','6557','6558','6559','6560','6561','6562','6563','6564','6565','6567','6568','6569','6570','6572','6573','6574','7694','7993','9200','9201','9202','9203','9204','9205','9206','9208','9310','9320')

Thanks for your help

Fabio
  • 23,183
  • 12
  • 55
  • 64
Long Vu
  • 195
  • 1
  • 2
  • 9
  • Do you have an index on the bunrui_code field? – Erel Segal-Halevi Jun 04 '13 at 04:59
  • 2
    you could create a table with one column to store those values and run a query against that...maybe create an index too :-) – go-oleg Jun 04 '13 at 04:59
  • Can recreate your table in http://sqlfiddle.com/ – Yogus Jun 04 '13 at 05:13
  • What is the source of the list of values contained within your "IN?" Is it hard coded? Are you sourcing it from a separate query? Is it parameters being passed in? How this data is made available may result in different options/solutions. – xQbert Jul 20 '21 at 15:13

2 Answers2

1

You should create a Temporary Table (see under Temporary Table), create a smart index on that temporary table, insert all the data into that temporary table, and then do an inner join between the temporary and the 'bi_bunrui_syouhin_shop_day_jyutyuu' table.

chamakits
  • 1,865
  • 1
  • 17
  • 26
0

If performance is an issue. You can replace IN with Exists and Try this. But truly It is not an exact way of using Exists.

Ex:

Select * from Table1 where id exists (Select id from Table 2)

You can use this as a reference.

Difference between EXISTS and IN in SQL?

Please let me know the result.

Thanks.

Community
  • 1
  • 1
RGV
  • 732
  • 3
  • 10
  • `SELECT * FROM bi_bunrui_syouhin_shop_day_jyutyuu AS bi WHERE bi.bunrui_code IN (SELECT ID FROM TABLE2)` --- You have to create a table Table 2 with all the numbers inserted into it. – RGV Jun 04 '13 at 06:40