SQLTeam.com | Weblogs | Forums

Improve query and extracting data from XML

sql2012

#1

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

#2

Try avoiding subselects and make sure data type is the same (so convertion is avoided). In this query, I'm assuming row_dt is of type date:

insert into newdatatable(creat_mth,creat_yr,sys_cd,sys_nm,avgtime,avg_cnt,row_dt)
(select month(created_dt)
       ,year(created_dt)
       ,sys_cd
       ,sys_nm
       ,avg(isnull(convert(xml,data_txt).value('(/request/exeTime)[1]','float'),0))
       ,count(*)
       ,cast(current_timestamp as date)
  from basetable
 where created_dt<@dateprev
 group by month(created_dt)
         ,year(created_dt)
         ,sys_cd
         ,sys_nm
);