why do i get a pause in a SQL batch

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

why do i get a pause in a SQL batch

Author

Topic

brenchley
Starting Member

3 Posts

Posted - 2004-12-14 : 07:13:47

I have an SP which takes ages to run.
after testing it appears that the batch pauses in 2 places for approx 6 seconds.
if I comment out the select statements at these 2 points I get different behaviours.
the second pause dissapears but even if i comment out the statements above and below the first offender the pause still exists

Any ideas?

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-12-14 : 07:42:37

We'd need to see the SQL to see what could be the issue.

I suspect that it's doing some index rebuilding from an INSERT or DELETE operation, or maybe some TXLog cleanup or TempDB cleanup.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

brenchley
Starting Member

3 Posts

Posted - 2004-12-14 : 07:53:00

this is smost of it

print 'start procedure '+convert(varchar, getdate(), 114)
declare
@start_at datetime,
@event_id numeric (18,0),
@type int --null or 0 itinerary basic
--1 itinerary full
--2 event admin

set @event_id = 554
set @type = 0
set @start_at = getdate()

declare @pres_comp bit,
@pres_inv bit,
@pres_abg bit,
@time_offset int,
@locked bit

if exists (select * from event_region where er_evnt_id = @event_id)
set @time_offset = (SELECT MAX(region.reg_time_offset) AS offset
FROM region INNER JOIN
event_region ON region.reg_id = event_region.er_reg_id
WHERE (event_region.er_evnt_id = @event_id))
else
set @time_offset = 0

set nocount on

--check if locked
set @locked = (select evnt_locked from event where evnt_id = @event_id)

print 'start companies '+convert(varchar, datediff(ms, @start_at, getdate()))
/* CREATE A TEMP TABLE OF ALL COMPANIES INVOLVED IN THE EVENT */

SELECT pc_comp_id AS ct_recno INTO #comp_ids FROM presenter_company WHERE pc_event_id = @event_id
INSERT INTO #comp_ids SELECT mc_comp_id FROM meeting_company WHERE mc_evnt_id = @event_id

-- get active data from salesmaker
SELECT DISTINCT
esm_contact_light.ct_recno,
ct_name1,
-- ct_company_recno,
-- ct_activ,
ct_streetadr1,
ct_postaladr1,
ct_zipcode1,
ct_zipplace1,
-- ct_co_recno,
-- ct_de_recno,
-- ct_rg_recno,
-- vcp_telephone,
-- vcp_telefax,
-- vcp_mobile,
-- vcp_email,
vcp_web_address,
vcp_switchboard
INTO #companies
FROM esm_contact_light inner join #comp_ids
on esm_contact_light.ct_recno = #comp_ids.ct_recno
--WHERE ct_recno in ( select ct_recno from #comp_ids )

--get any deleted companies from the lock_company table
if @locked = 1
begin
insert into #companies
SELECT
ct_recno,
ct_name1,
-- ct_company_recno,
-- ct_activ,
ct_streetadr1,
ct_postaladr1,
ct_zipcode1,
ct_zipplace1,
-- ct_co_recno,
-- ct_de_recno,
-- ct_rg_recno,
-- vcp_telephone,
-- vcp_telefax,
-- vcp_mobile,
-- vcp_email,
vcp_web_address,
vcp_switchboard
FROM lock_companies
WHERE evnt_id = @event_id
and ct_recno not in (SELECT ct_recno FROM #companies)
end

-- CLEAR UP #comp_ids
DROP TABLE #comp_ids

/* create temp table of all people invloved in the event */
set nocount on
print 'start people '+convert(varchar, datediff(ms, @start_at, getdate()))

-- people
if @locked = 1
begin
insert into #lock_people2
SELECT distinct
@event_id as event_id,
ct_recno,
ct_title,
ct_name1,
ct_company_recno,
ct_activ,
vcp_telephone,
vcp_telefax,
vcp_mobile,
vcp_email
FROM lock_people
Where evnt_id = @event_id
end
else
begin

-- print 'in like flynn'
SELECT pres_contact_id as ct_recno
into #ct_ids
FROM presenters
WHERE pres_event_id = @event_id

insert into #ct_ids
SELECT oc_contact_id
FROM organiser_contact
WHERE oc_event_id = @event_id
--AND oc_contact_id not in (select ct_recno from #ct_ids)

insert into #ct_ids
SELECT mat_attendee
FROM meeting_attendees
WHERE mat_evnt_id = @event_id
--AND mat_attendee not in (select ct_recno from #ct_ids)

insert into #ct_ids
SELECT
pres_contact_id
FROM presenters
where pres_event_id = @event_id
--AND pres_contact_id not in (select ct_recno from #ct_ids)

insert into #ct_ids
SELECT oc_contact_id
FROM organiser_contact
WHERE oc_event_id = @event_id
--AND oc_contact_id not in (select ct_recno from #ct_ids)

insert into #ct_ids
SELECT mat_attendee
FROM meeting_attendees
WHERE mat_evnt_id = @event_id
--AND mat_attendee not in (select ct_recno from #ct_ids)

insert into #ct_ids
SELECT ap_contact_id
FROM abgsc_presenters
WHERE ap_event_id = @event_id
--AND ap_contact_id not in (select ct_recno from #ct_ids)

insert into #ct_ids
SELECT ac_contact_id
FROM abgsc_contact
WHERE ac_event_id = @event_id
--AND ac_contact_id not in (select ct_recno from #ct_ids)

insert into #ct_ids
SELECT mc_salesperson_id
FROM meeting_company
WHERE mc_evnt_id = @event_id
--AND mc_salesperson_id not in (select ct_recno from #ct_ids)

-- insert into #lock_people
SELECT distinct
@event_id as event_id,
esm_contact_light.ct_recno,
ct_title,
ct_name1,
ct_company_recno,
ct_activ,
vcp_telephone,
--vcp_telefax,
vcp_mobile,
vcp_email
into #lock_people
FROM esm_contact_light inner join #ct_ids
on esm_contact_light.ct_recno = #ct_ids.ct_recno

drop table #ct_ids

--select * from #lock_people
end
print 'end people '+convert(varchar, datediff(ms, @start_at, getdate()))

select '<data>'
--admin group for menu
select IS_MEMBER ('abgsc_admin') + is_member ('dbo') as admin
--for xml raw

--see if event is finished
SELECT datediff(d, getdate(), evnt_end_date) as days FROM event as finished WHERE evnt_id = @event_id
--for xml AUTO

--basic event info
SELECT
evnt_id as id,
evnt_title as title,
convert(varchar, evnt_start_date, 106) as start_date,
convert(varchar, evnt_end_date, 106) as end_date,
evnt_open_priority,
evnt_cancelled,
evnt_locked,
etyp_description as type,
etyp_pres_comp
from vw_event_base_info as event
where evnt_id = @event_id
--for xml auto

--if @@error != 0
--print @@error

print 'start presenting companies '+convert(varchar, datediff(ms, @start_at, getdate()))

SELECT
presenting_company.ct_name1 AS comp_name,
presenting_company.ct_recno AS comp_id
FROM
presenter_company INNER JOIN event
ON presenter_company.pc_event_id = event.evnt_id
INNER JOIN #companies presenting_company
ON presenter_company.pc_comp_id = presenting_company.ct_recno
WHERE event.evnt_id = @event_id
--for xml auto

--if @@error != 0
--print @@error
print 'start presenters '+convert(varchar, datediff(ms, @start_at, getdate()))

--presenters
SELECT
presenters.pres_event_id,
ct_recno as cont_id,
ct_title cont_title,
ct_name1 cont_first_name,
vcp_email cont_email,
vcp_mobile cont_mobile,
vcp_telephone cont_tel
FROM
presenters inner join #lock_people as contact
on presenters.pres_contact_id = contact.ct_recno

WHERE
presenters.pres_event_id = @event_id
--for xml auto

--if @@error != 0
--print @@error

print 'start presenter contacts '+convert(varchar, datediff(ms, @start_at, getdate()))
--presenter contacts
SELECT
organiser_contact.oc_location,
ct_title cont_title,
ct_name1 cont_first_name,
vcp_email cont_email,
vcp_mobile cont_mobile,
vcp_telephone cont_tel
FROM
#lock_people as contact,
event INNER JOIN organiser_contact
ON event.evnt_id = organiser_contact.oc_event_id
WHERE
event.evnt_id = @event_id
AND event.evnt_disabled = 0
AND contact.ct_recno = organiser_contact.oc_contact_id
-- AND contact.evnt_id = @event_id
--for xml auto

--if @@error != 0
--print @@error
print 'start abg presenters '+convert(varchar, datediff(ms, @start_at, getdate()))

--abgsc presenters
SELECT distinct
abgsc_presenters.ap_event_id,
ct_recno as cont_id,
ct_title cont_title,
ct_name1 cont_first_name,
vcp_email cont_email,
vcp_telephone cont_tel
FROM
abgsc_presenters ,
#lock_people as contact
WHERE
abgsc_presenters.ap_contact_id = contact.ct_recno
-- AND contact.evnt_id = @event_id
and abgsc_presenters.ap_event_id = @event_id
--for xml auto

--if @@error != 0
--print @@error
print 'start abg contacts '+convert(varchar, datediff(ms, @start_at, getdate()))

--abg contacts
SELECT
abgsc_contact.ac_location,
ct_title cont_title,
ct_name1 cont_first_name,
vcp_email cont_email,
vcp_mobile cont_mobile,
vcp_telephone cont_tel
FROM
#lock_people as contact,
event INNER JOIN abgsc_contact
ON event.evnt_id = abgsc_contact.ac_event_id
WHERE
event.evnt_id = @event_id
AND event.evnt_disabled = 0
AND contact.ct_recno = abgsc_contact.ac_contact_id
-- AND contact.evnt_id = @event_id
--for xml auto

--if @@error != 0
--print @@error

the section in red is where the pause happens but still happens in the next statement if it is commented out

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-12-14 : 08:34:10

What is the subtree cost of that part of the query? How many logical reads does it generate?

If you don't know how to answer those questions, please feel free to ask and I can explain those.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

brenchley
Starting Member

3 Posts

Posted - 2004-12-14 : 09:36:50

subtree cost is 1.89%, 79 reads.

is there any way to find out if the delay is something to do with a temp table. both calls that are causing the problem come soon after creation/ dropping temp tables

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-12-14 : 13:38:03

Yeah, you might be taking some hits on the Temp Table stuff.
You might want to try table variables, but from what I understand they can get stored in TempDB as well.

Can you use PerfMon to monitor Average Disk Queue length, CPU usage, etc on the machine in question when you run this query? That might tell you if the DROP TABLE's are killing you if you see the disk thrash when that part of the code happens.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources