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)
SELECT
x.mth,
x.yr,
sys_cd,
sys_nm,
AVG(usedTime) as avgTime,
count(*) as avg_cnt,
GETDATE() as row_dt
FROM(
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)[1]', 'float'), 0)as usedTime
FROM baseTable
WHERE created_dt < @datePrev) as x
GROUP BY sys_cd, sys_nm, mth, yr