SQLTeam.com | Weblogs | Forums

Select query taking too long how to make sure second select only starts after first select finished


#1

I have the following query, in the first select i am loading data to temp table variable based. if i run just that it is showing result in 1 second.

But when i use first temp load and then use that temp tables data in the second final query, then taking almost 30 minutes for 600 rows.

Thanks a lot for the helpful info.

set @startdate = '04/01/2016'
set @enddate = '04/11/2016'

set @progid = 71

Declare @tmpRM as table
(ID int identity, rmid int, ProgID int, ProjID int, ContractID int, Departamento varchar(50),
Proceso varchar(50), contractno varchar(50), ContractorRefNo varchar(50),sequenceno decimal(10,2), barcode varchar(50),
destdepartment varchar(50), origsender varchar(50), documentdate datetime, ReceivedDate datetime, rmloggeddate datetime, acknowledgeddate datetime,
documenttypeid int, LogTypeID int, documentsourceid int, SubjectID int, origorganization varchar(50))

insert into @tmpRM(rmid , ProgID , ProjID , ContractID,Departamento ,
Proceso , contractno , ContractorRefNo ,sequenceno , barcode ,
destdepartment , origsender , documentdate , ReceivedDate , rmloggeddate , acknowledgeddate ,
documenttypeid , LogTypeID , documentsourceid , SubjectID , origorganization )
(SELECT rm.rmid,
rm.ProgID,
rm.ProjID,
rm.ContractID,
pg.progno,
pj.projno,
ctr.contractno,
rm.ContractorRefNo,
rm.sequenceno,
rm.barcode,
rm.destdepartment,
rm.origsender,
rm.documentdate,
rm.ReceivedDate,
rm.rmloggeddate,
rm.acknowledgeddate,
rm.documenttypeid ,
rm.LogTypeID,
rm.documentsourceid,
rm.SubjectID,
rm.origorganization
FROM Tab_ccsnetrm rm
inner join TAB_ccsNetPrograms pg on(rm.ProgID = pg.ProgID)
inner join TAB_ccsNetProjects pj on(rm.ProjID = pj.ProjID)
inner join TAB_ccsNetContracts ctr on(rm.ContractID=ctr.ContractID)
where rm.ProgID <> 70
and (rm.progid = @progid)

and datediff(d, @startdate, rm.rmloggeddate) >=0
and datediff(d, rm.rmloggeddate,@enddate) >=0

--and rm.rmloggeddate >= CONVERT(varchar(10), @startdate, 1)
--and rm.rmloggeddate <= CONVERT(varchar(10), @enddate, 1)
--and rm.rmloggeddate < CONVERT(varchar(10), dateadd(day, 1, @enddate), 1)
)

----Second process using above temptable data as join.

SELECT rm.rmid,
rm.Departamento,
rm.Proceso,
rm.contractno as [Tipo de ID],
rm.ContractorRefNo as "No de ID",
rm.sequenceno as "No de Radicacion",
rm.barcode as Expedient,
rm.origsender as Nombre_del_cliente,
rm.destdepartment as "Correo Electronico",
dbo.f_GetModuleTitle(MR.ModuleName,MR.ModuleRecordID) as Titulo,
CONVERT(varchar(12), rm.documentdate, 103) as "Fecha Documento",
CONVERT(varchar(12), rm.ReceivedDate, 103) as "Fecha Recibido",
CONVERT(varchar(12), rm.rmloggeddate, 103) as "Fecha De Ragistro",
rm.rmloggeddate as rmlogeddate,
CONVERT(varchar(12), rm.acknowledgeddate, 103) as "Acuse de Recibo",
rm.origorganization as Tarjeta_prof,
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'lblDocumentType' and PickID= rm.documenttypeid) as Concepto,
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'lbllogtype' and PickID= rm.LogTypeID) as "Tipo(Canal)",
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'lblDocumentSource' and PickID= rm.documentsourceid) as "Comite",
(Select SpanishDescription from TAB_ccsNetPickLists where fieldlabelkey = 'lblsubject' and PickID= rm.SubjectID) as "Tema De Solicitud",
MR.step,
(Select username from tab_ccsnetusers where userid = MR.AssignedTo) as Asignado,
MR.Description as descripcion,
CONVERT(varchar(12), MR.duedate, 103) as Vencimento,
CONVERT(varchar(12), MR.donedate, 103) as Realizada,
(Select SpanishText from TAB_ccsNetPicklistValue where fieldlabelkey = 'ddldisposition' and PickID= mr.disposition) as DisposiciĆ³n,
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'ActivityType' and PickID= MR.Type) as Type,
[days] = CASE WHEN MR.duedate < GetDate() THEN -1 * DATEDIFF(Day, MR.duedate, GetDate())
ELSE CONVERT(varchar(20), DATEDIFF(Day, GetDate(), MR.duedate))
END
FROM @tmpRM rm
inner join TAB_ccsNetPrograms pg on(rm.ProgID = pg.ProgID)
inner join TAB_ccsNetProjects pj on(rm.ProjID = pj.ProjID)
inner join TAB_ccsNetContracts ctr on(rm.ContractID=ctr.ContractID)
left outer join Tab_ccsNetWorkflowActivity MR on(rm.RMID=MR.ModuleRecordID and MR.modulename='rm' and dbo.f_GetModuleTitle(MR.ModuleName,MR.ModuleRecordID) <> '')
;


#2

I suggest using a #TEMP table rather than an @TEMP table as they are often much faster, and also putting a suitable index on the #TEMP table.

I would expect that using a function as part of the JOIN COndition:

left outer join Tab_ccsNetWorkflowActivity MR 
 on rm.RMID=MR.ModuleRecordID and MR.modulename='rm' 
and dbo.f_GetModuleTitle(MR.ModuleName,MR.ModuleRecordID) <> ''

would slow the query by as much as 100-times. Obviously it depends what the function does, but those type of functions are notoriously slow. I would try putting the code from the function "inline" as a test, and seeing if that is faster