Trying to summarize data in a very large(over 12 million records) table that also contains an XML stored in a text column. I need to extract data from the XML and add to the summarized data. The summarized data is inserted into a new summary table. My query takes over 3 hours to complete execution. I tried using temp tables but no improvement in execution time. I tried breaking it into chunks and only shaved off 2 minutes. Can someone please help?
INSERT INTO newDataTable(creat_mth,creat_yr,sys_cd,sys_nm,avgTime,avg_cnt,row_dt)
AVG(usedTime) as avgTime,
count(*) as avg_cnt,
GETDATE() as row_dt
SELECT DATEPART(mm,created_dt) as mth, DATEPART(yyyy,created_dt) as yr, sys_cd, sys_nm, ISNULL(CONVERT(XML,data_txt).value('(/request/exeTime)', 'float'), 0)as usedTime FROM baseTable WHERE created_dt < @datePrev) as x GROUP BY sys_cd, sys_nm, mth, yr