How increase speed?
Hi, Next query: "SELECT data.code1, data.code2, "D" AS flow, "151/501" AS class, data2.group AS class1, data3.t_code AS class2, Sum(mb_data.deebet) AS summa, data.number AS number, "MB" AS source FROM data, data4, data2, data3 WHERE data4.period = data.period AND data3.code = data.code AND data2.c_code =data.c_code GROUP BY data.code2, data3.t_code , data2.group , data.code1, data.number INTO TEMP break1;" Select works quikly (without insert), but insert to TEMP is slow. (approx. 2000000 rows and takes 20 minuts .... Or It's normal? I'll need to insert difference data to other table and for it I am using TEMP tables. How can increase speed ? Some experiense? Thanks advance, Rein
May be it seems to you that your select works quickly? May be it's the first fetch that works quickly, but if you try to fetch all rows (like doing insert into temp table) then your select does not work quickly at all. > Hi, > > Next query: > > "SELECT data.code1, data.code2, "D" AS flow, "151/501" AS class, data2.group > AS class1, data3.t_code AS class2, Sum(mb_data.deebet) AS summa, data.number > AS number, "MB" AS source > FROM data, data4, data2, data3 WHERE data4.period = data.period AND > data3.code = data.code AND data2.c_code =data.c_code GROUP BY data.code2, > data3.t_code , data2.group , data.code1, data.number > INTO TEMP break1;" > > Select works quikly (without insert), but insert to TEMP is slow. > (approx. 2000000 rows and takes 20 minuts .... Or It's normal? > > I'll need to insert difference data to other table and for it I am using > TEMP tables. > > How can increase speed ? Some experiense? > > Thanks advance, > > Rein
Rein Puksand wrote: > Hi, > > Next query: > > "SELECT data.code1, data.code2, "D" AS flow, "151/501" AS class, data2.group > AS class1, data3.t_code AS class2, Sum(mb_data.deebet) AS summa, data.number > AS number, "MB" AS source > FROM data, data4, data2, data3 WHERE data4.period = data.period AND > data3.code = data.code AND data2.c_code =data.c_code GROUP BY data.code2, > data3.t_code , data2.group , data.code1, data.number > INTO TEMP break1;" > > Select works quikly (without insert), but insert to TEMP is slow. > (approx. 2000000 rows and takes 20 minuts .... Or It's normal? > > I'll need to insert difference data to other table and for it I am using > TEMP tables. > > How can increase speed ? Some experiense? > > Thanks advance, > > Rein > Do you have multible temp dbspaces, is DBSPACETEMP configuration parameter or environment variable defined? If your database is logged, then use 'with no log' to avoid transaction records. Do read the following articles: http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.perf.doc/perf134.htm#sii-05cnfio-24433 http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.admin.doc/admin365.htm#sii-11disk-34145