April 23, 2002 at 4:17 pm
Hi, I use a storedprocedure which contains 3 select stmts going against 4 tables. The 1st two select stmts populate data in a #temp1 table and the last select into a #temp2 table. Then these 2 tables are combined using UNION to form #temp3. Now a cursor comes into picture to do some aggregate functions and finally the data is written / updated into #temp3. Cursor is now closed & deallocated. But whenever the procedure runs the server gets blocked and doesn;t allow any new user to log in until the process is killed.
Can anyone explain why?
April 23, 2002 at 5:36 pm
Is it because the memory is shooting high? Are those four tables really huge?
Could you please post more details.
Thanks,
NeoNash
April 23, 2002 at 7:44 pm
First why are you generating the first 2 tables if you are going to union these results together just do the 2 queries unioned instead. Also how do you create you temp tables, by using SELECT INTO or CREATE TABLE the later is prefered as causes less blocking to occurr. Finally what is the cursor doing and why does temp3 stick around? If you can post you code we may be able to help find a non-cursor solution that may also not have a temp table in it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 24, 2002 at 6:55 am
Hi, Thanks
This is the code..
CREATE PROCEDURE CFR_NES_UNIT_CAT (@ST_DT DATETIME, @END_DT DATETIME)
AS
declare
@rqtp_cd varchar(5),
@unit_id int,
@rc_tot_clsd int,
@row_cnt int,
@rc_tot_opnd int,
@rc_tot_clsd_og int,
@rc_tot_opnd_og int
/****** PICKS UP CLOSED NOTES ROW FROM CTCTRQSH & CTCTRQST TABLE ******/
/****** AND IS STORED IN TEMP TABLE #NES_CLSD ******/
SELECT
ctctunit.unit_id,
CTCTRQTP.RQTP_CD,
CTCTRQSh.RQST_RCPT_DTM,
CTCTRQSh.RQST_STAT_DTM,
CTCTRQTP.RQTP_SVC_GOAL_NBR,
CTCTRQSH.RQST_CTC_DAYS_RMAN ,
CTCTRQSH.RQST_CARR_MNE,
ctctunit.unit_desc,
CTCTRQTP.RQTP_Dsc
INTO #NES_CLSD
FROM CTCTRQSH, CTCTRQTP, CTCTUNIT, CTCTEMPL
WHERE
( CTCTRQSH.RQTP_CD = CTCTRQTP.RQTP_CD ) and CTCTUNIT.UNIT_ID > 0 AND
( CTCTUNIT.DEPT_CD = '926') and
( CTCTUNIT.UNIT_ID = CTCTEMPL.EMPL_WORK_UNIT_NBR ) and
( CTCTRQSH.RQST_RCPT_ASOC_ID = CTCTEMPL.
EMPL_LOGIN_ID ) and
( CTCTEMPL.EMPL_DEPT_CD='926' ) AND
( CTCTRQSH.RQST_RCPT_DEPT_CD = '926') and
( CTCTRQTP.RQTP_DEPT_CD = '926' ) AND
( CTCTRQSH.RQST_STAT_CD ='C') AND
( CTCTRQSH.RQST_STAT_DTM >=@st_dt AND CTCTRQSH.RQST_STAT_DTM <= @end_dt)
INSERT #NES_CLSD
SELECT ctctunit.unit_id,
CTCTRQTP.RQTP_CD,
CTCTRQST.RQST_RCPT_DTM,
CTCTRQST.RQST_STAT_DTM,
CTCTRQTP.RQTP_SVC_GOAL_NBR,
CTCTRQST.RQST_CTC_DAYS_RMAN ,
CTCTRQST.RQST_CARR_MNE,
ctctunit.unit_desc,
CTCTRQTP.RQTP_Dsc
FROM CTCTRQST, CTCTRQTP, CTCTUNIT , CTCTEMPL
WHERE
( CTCTRQST.RQTP_CD = CTCTRQTP.RQTP_CD ) and CTCTUNIT.UNIT_ID > 0 AND
( CTCTUNIT.DEPT_CD = '926') and
( CTCTUNIT.UNIT_ID = CTCTEMPL.EMPL_WORK_UNIT_NBR ) and
( CTCTRQST.RQST_RCPT_ASOC_ID = CTCTEMPL.EMPL_LOGIN_ID ) and
( CTCTEMPL.EMPL_DEPT_CD='926' ) AND
( CTCTRQST.RQST_RCPT_DEPT_CD = '926') and
( CTCTRQTP.RQTP_DEPT_CD = '926' ) AND
( CTCTRQST.RQST_STAT_CD ='C') AND
( CTCTRQST.RQST_STAT_DTM >=@st_dt AND CTCTRQST.RQST_STAT_DTM <= @end_dt )
/****** PICKS UP OPEN NOTES ROW FROM CTCTRQST TABLE *****/
/****** AND IS STORED IN TEMP TABLE #NES_OPND ******/
SELECT ctctunit.unit_id,
CTCTRQTP.RQTP_CD,
CTCTRQST.RQST_RCPT_DTM,
CTCTRQST.RQST_STAT_DTM,
CTCTRQTP.RQTP_SVC_GOAL_NBR,
CTCTRQST.RQST_CTC_DAYS_RMAN ,
CTCTRQST.RQST_CARR_MNE,
ctctunit.unit_desc,
CTCTRQTP.RQTP_Dsc
INTO #NES_OPND
FROM CTCTRQST, CTCTRQTP, CTCTUNIT , CTCTEMPL
WHERE
( CTCTRQST.RQTP_CD = CTCTRQTP.RQTP_CD ) and CTCTUNIT.UNIT_ID > 0
AND
( CTCTUNIT.DEPT_CD = '926') and
( CTCTUNIT.UNIT_ID = CTCTEMPL.EMPL_WORK_UNIT_NBR ) and
( CTCTEMPL.EMPL_DEPT_CD='926' ) AND
( CTCTRQST.RQST_RCPT_ASOC_ID = CTCTEMPL.EMPL_LOGIN_ID ) and
( CTCTRQST.RQST_RCPT_DEPT_CD = '926') and
( CTCTRQTP.RQTP_DEPT_CD = '926' ) AND
( CTCTRQST.RQST_STAT_CD <>'C') AND
( CTCTRQST.RQST_RCPT_DTM >=@st_dt AND CTCTRQST.RQST_RCPT_DTM <= @end_dt )
CREATE TABLE #TBL_RPT
(UNIT_ID INT,
unit_desc varchar(255),
RQTP_CD VARCHAR(5),
rqtp_dsc varchar(255),
TOT_OPND INT NULL,
TOT_OPND_OG INT NULL,
TOT_CLSD INT NULL ,
TOT_CLSD_OG INT NULL)
INSERT INTO #TBL_RPT
SELECT DISTINCT UNIT_ID, unit_desc, RQTP_CD,rqtp_dsc, 0,0,0,0 FROM #NES_CLSD
UNION
SELECT DISTINCT UNIT_ID, unit_desc, RQTP_CD,rqtp_dsc, 0,0,0,0 FROM #NES_OPND ORDER BY UNIT_ID, RQTP_CD
/****** A CURSOR TO DO THE SUMMARISING CALCULATIONS ON THE ROWS ACCUMULATED *****/
declare report_cur cursor for
SELECT unit_id, rqtp_cd FROM #TBL_RPT ORDER BY UNIT_ID, RQTP_CD
open report_cur
fetch report_cur into @unit_id, @rqtp_cd
while (@@fetch_status=0)
begin
select @rc_tot_opnd=0
select @rc_tot_opnd_og=0
select @rc_tot_clsd=0
select @rc_tot_clsd_og=0
select @rc_tot_opnd=count(*) from #nes_opnd where
rqtp_cd=@rqtp_cd and unit_id=@unit_id
select @rc_tot_opnd_og=count(*) from #nes_opnd
where rqtp_cd=@rqtp_cd and unit_id=@unit_id and rqst_ctc_days_rman<0
update #tbl_rpt set tot_opnd=@rc_tot_opnd, tot_opnd_og=@rc_tot_opnd_og
where rqtp_cd=@rqtp_cd and unit_id=@unit_id
select @rc_tot_clsd=count(*) from #nes_clsd where rqtp_cd=@rqtp_cd and unit_id=@unit_id
select @rc_tot_clsd_og=count(*) from #nes_clsd
where rqtp_cd=@rqtp_cd and unit_id=@unit_id and rqst_ctc_days_rman<0
update #tbl_rpt
set tot_clsd=@rc_tot_clsd , tot_clsd_og=@rc_tot_clsd_og
where rqtp_cd=@rqtp_cd and unit_id=@unit_id
fetch report_cur into @unit_id, @rqtp_cd
end
close report_cur
deallocate report_cur
select upper(unit_desc), rqtp_dsc, tot_opnd, tot_opnd_og, tot_clsd, tot_clsd_og from #tbl_rpt order by
unit_desc, rqtp_dsc
--------------------------------
Table #of rows in each table
CTCTRQSH1,728,560
CTCTRQTP6,082
CTCTUNIT190
CTCTEMPL2,656
CTCTRQST21,508
Our DBA analysed the block and said when the user closes the front end application itself, the block goes away immediately. So he suggests to disconnect the server connection from the frontend as soon the procedure gets executed.
Please advise.... I am sinking.....
April 24, 2002 at 6:55 am
Hi, Thanks
This is the code..
CREATE PROCEDURE CFR_NES_UNIT_CAT (@ST_DT DATETIME, @END_DT DATETIME)
AS
declare
@rqtp_cd varchar(5),
@unit_id int,
@rc_tot_clsd int,
@row_cnt int,
@rc_tot_opnd int,
@rc_tot_clsd_og int,
@rc_tot_opnd_og int
/****** PICKS UP CLOSED NOTES ROW FROM CTCTRQSH & CTCTRQST TABLE ******/
/****** AND IS STORED IN TEMP TABLE #NES_CLSD ******/
SELECT
ctctunit.unit_id,
CTCTRQTP.RQTP_CD,
CTCTRQSh.RQST_RCPT_DTM,
CTCTRQSh.RQST_STAT_DTM,
CTCTRQTP.RQTP_SVC_GOAL_NBR,
CTCTRQSH.RQST_CTC_DAYS_RMAN ,
CTCTRQSH.RQST_CARR_MNE,
ctctunit.unit_desc,
CTCTRQTP.RQTP_Dsc
INTO #NES_CLSD
FROM CTCTRQSH, CTCTRQTP, CTCTUNIT, CTCTEMPL
WHERE
( CTCTRQSH.RQTP_CD = CTCTRQTP.RQTP_CD ) and CTCTUNIT.UNIT_ID > 0 AND
( CTCTUNIT.DEPT_CD = '926') and
( CTCTUNIT.UNIT_ID = CTCTEMPL.EMPL_WORK_UNIT_NBR ) and
( CTCTRQSH.RQST_RCPT_ASOC_ID = CTCTEMPL.
EMPL_LOGIN_ID ) and
( CTCTEMPL.EMPL_DEPT_CD='926' ) AND
( CTCTRQSH.RQST_RCPT_DEPT_CD = '926') and
( CTCTRQTP.RQTP_DEPT_CD = '926' ) AND
( CTCTRQSH.RQST_STAT_CD ='C') AND
( CTCTRQSH.RQST_STAT_DTM >=@st_dt AND CTCTRQSH.RQST_STAT_DTM <= @end_dt)
INSERT #NES_CLSD
SELECT ctctunit.unit_id,
CTCTRQTP.RQTP_CD,
CTCTRQST.RQST_RCPT_DTM,
CTCTRQST.RQST_STAT_DTM,
CTCTRQTP.RQTP_SVC_GOAL_NBR,
CTCTRQST.RQST_CTC_DAYS_RMAN ,
CTCTRQST.RQST_CARR_MNE,
ctctunit.unit_desc,
CTCTRQTP.RQTP_Dsc
FROM CTCTRQST, CTCTRQTP, CTCTUNIT , CTCTEMPL
WHERE
( CTCTRQST.RQTP_CD = CTCTRQTP.RQTP_CD ) and CTCTUNIT.UNIT_ID > 0 AND
( CTCTUNIT.DEPT_CD = '926') and
( CTCTUNIT.UNIT_ID = CTCTEMPL.EMPL_WORK_UNIT_NBR ) and
( CTCTRQST.RQST_RCPT_ASOC_ID = CTCTEMPL.EMPL_LOGIN_ID ) and
( CTCTEMPL.EMPL_DEPT_CD='926' ) AND
( CTCTRQST.RQST_RCPT_DEPT_CD = '926') and
( CTCTRQTP.RQTP_DEPT_CD = '926' ) AND
( CTCTRQST.RQST_STAT_CD ='C') AND
( CTCTRQST.RQST_STAT_DTM >=@st_dt AND CTCTRQST.RQST_STAT_DTM <= @end_dt )
/****** PICKS UP OPEN NOTES ROW FROM CTCTRQST TABLE *****/
/****** AND IS STORED IN TEMP TABLE #NES_OPND ******/
SELECT ctctunit.unit_id,
CTCTRQTP.RQTP_CD,
CTCTRQST.RQST_RCPT_DTM,
CTCTRQST.RQST_STAT_DTM,
CTCTRQTP.RQTP_SVC_GOAL_NBR,
CTCTRQST.RQST_CTC_DAYS_RMAN ,
CTCTRQST.RQST_CARR_MNE,
ctctunit.unit_desc,
CTCTRQTP.RQTP_Dsc
INTO #NES_OPND
FROM CTCTRQST, CTCTRQTP, CTCTUNIT , CTCTEMPL
WHERE
( CTCTRQST.RQTP_CD = CTCTRQTP.RQTP_CD ) and CTCTUNIT.UNIT_ID > 0
AND
( CTCTUNIT.DEPT_CD = '926') and
( CTCTUNIT.UNIT_ID = CTCTEMPL.EMPL_WORK_UNIT_NBR ) and
( CTCTEMPL.EMPL_DEPT_CD='926' ) AND
( CTCTRQST.RQST_RCPT_ASOC_ID = CTCTEMPL.EMPL_LOGIN_ID ) and
( CTCTRQST.RQST_RCPT_DEPT_CD = '926') and
( CTCTRQTP.RQTP_DEPT_CD = '926' ) AND
( CTCTRQST.RQST_STAT_CD <>'C') AND
( CTCTRQST.RQST_RCPT_DTM >=@st_dt AND CTCTRQST.RQST_RCPT_DTM <= @end_dt )
CREATE TABLE #TBL_RPT
(UNIT_ID INT,
unit_desc varchar(255),
RQTP_CD VARCHAR(5),
rqtp_dsc varchar(255),
TOT_OPND INT NULL,
TOT_OPND_OG INT NULL,
TOT_CLSD INT NULL ,
TOT_CLSD_OG INT NULL)
INSERT INTO #TBL_RPT
SELECT DISTINCT UNIT_ID, unit_desc, RQTP_CD,rqtp_dsc, 0,0,0,0 FROM #NES_CLSD
UNION
SELECT DISTINCT UNIT_ID, unit_desc, RQTP_CD,rqtp_dsc, 0,0,0,0 FROM #NES_OPND ORDER BY UNIT_ID, RQTP_CD
/****** A CURSOR TO DO THE SUMMARISING CALCULATIONS ON THE ROWS ACCUMULATED *****/
declare report_cur cursor for
SELECT unit_id, rqtp_cd FROM #TBL_RPT ORDER BY UNIT_ID, RQTP_CD
open report_cur
fetch report_cur into @unit_id, @rqtp_cd
while (@@fetch_status=0)
begin
select @rc_tot_opnd=0
select @rc_tot_opnd_og=0
select @rc_tot_clsd=0
select @rc_tot_clsd_og=0
select @rc_tot_opnd=count(*) from #nes_opnd where
rqtp_cd=@rqtp_cd and unit_id=@unit_id
select @rc_tot_opnd_og=count(*) from #nes_opnd
where rqtp_cd=@rqtp_cd and unit_id=@unit_id and rqst_ctc_days_rman<0
update #tbl_rpt set tot_opnd=@rc_tot_opnd, tot_opnd_og=@rc_tot_opnd_og
where rqtp_cd=@rqtp_cd and unit_id=@unit_id
select @rc_tot_clsd=count(*) from #nes_clsd where rqtp_cd=@rqtp_cd and unit_id=@unit_id
select @rc_tot_clsd_og=count(*) from #nes_clsd
where rqtp_cd=@rqtp_cd and unit_id=@unit_id and rqst_ctc_days_rman<0
update #tbl_rpt
set tot_clsd=@rc_tot_clsd , tot_clsd_og=@rc_tot_clsd_og
where rqtp_cd=@rqtp_cd and unit_id=@unit_id
fetch report_cur into @unit_id, @rqtp_cd
end
close report_cur
deallocate report_cur
select upper(unit_desc), rqtp_dsc, tot_opnd, tot_opnd_og, tot_clsd, tot_clsd_og from #tbl_rpt order by
unit_desc, rqtp_dsc
--------------------------------
Table #of rows in each table
CTCTRQSH1,728,560
CTCTRQTP6,082
CTCTUNIT190
CTCTEMPL2,656
CTCTRQST21,508
Our DBA analysed the block and said when the user closes the front end application itself, the block goes away immediately. So he suggests to disconnect the server connection from the frontend as soon the procedure gets executed.
Please advise.... I am sinking.....
April 24, 2002 at 12:58 pm
How is the front-end application configured? Are you using MTS or COM+?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 24, 2002 at 2:29 pm
Hi Kelly,
We use PB for the front end application. Connection is done thru the Transaction Object. Please let me know if u have any suggestions.
Thanks
shobha
April 24, 2002 at 3:16 pm
We've seen issues where our developers didn't set transaction state in COM+ properly. As a result, even though there weren't explicit BEGIN TRAN and COMMIT TRAN within SQL Server, we were still seeing blocking and deadlocking because COM+ was holding transactions open. I'm wondering if the same thing is occurring here.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 24, 2002 at 6:41 pm
First I read in an article either on SQLMag or some site or on the MS KB site that using a SELECT INTO to a #temp table does cause blocking unless you do a create table then insert into. Cannot find off hand and that was the reason for my question.
Now looking over you item here I believe this is the way around all temp tables and the cursor. Try this and see how it does.
---OutsideQ::---
SELECT
UPPER(unit_desc) AS unit_desc,
rqtp_dsc,
SUM(SubCount) AS tot_opnd,
SUM(RMCHK) AS tot_opnd_og,
SUM(CLSDCHK) AS tot_clsd,
SUM(CLSDRMNCHK) AS tot_clsd_og
FROM
(
---Q1::---
SELECT
ctctunit.unit_id,
ctctunit.unit_desc,
CTCTRQTP.RQTP_CD,
/*
These are not used.
CTCTRQSh.RQST_RCPT_DTM,
CTCTRQSh.RQST_STAT_DTM,
CTCTRQTP.RQTP_SVC_GOAL_NBR,
CTCTRQSH.RQST_CTC_DAYS_RMAN ,
CTCTRQSH.RQST_CARR_MNE,
*/
CTCTRQTP.RQTP_Dsc,
SUM( 1 ) AS SubCount, --This is so I can produce the totals when done.--
SUM( CASE WHEN CTCTRQST.RQST_CTC_DAYS_RMAN > 0 THEN 1 ELSE 0 END ) AS RMNCHK, --Remaining Check.--
SUM( 0 ) AS CLSDCHK, --Sum done here to avoid need in group by.--
SUM( 0 ) AS CLSDRMNCHK --Sum done here to avoid need in group by.--
FROM
CTCTRQSH
INNER JOIN
CTCTRQTP
ON
CTCTRQSH.RQTP_CD = CTCTRQTP.RQTP_CD
INNER JOIN
CTCTEMPL
ON
CTCTRQSH.RQST_RCPT_ASOC_ID = CTCTEMPL.EMPL_LOGIN_ID
INNER JOIN
CTCTUNIT
ON
CTCTUNIT.UNIT_ID = CTCTEMPL.EMPL_WORK_UNIT_NBR
WHERE
CTCTUNIT.UNIT_ID > 0 AND
CTCTUNIT.DEPT_CD = '926' and
CTCTEMPL.EMPL_DEPT_CD='926' AND
CTCTRQSH.RQST_RCPT_DEPT_CD = '926' and
CTCTRQTP.RQTP_DEPT_CD = '926' AND
CTCTRQSH.RQST_STAT_CD ='C' AND
( CTCTRQSH.RQST_STAT_DTM BETWEEN @st_dt AND @end_dt )
GROUP BY
ctctunit.unit_id,
ctctunit.unit_desc,
CTCTRQTP.RQTP_CD,
CTCTRQTP.RQTP_Dsc
UNION ALL --Keep these duplicates on this subquery.--
---Q2&3::---
SELECT
ctctunit.unit_id,
ctctunit.unit_desc,
CTCTRQTP.RQTP_CD,
/*
These are not used.
CTCTRQST.RQST_RCPT_DTM,
CTCTRQST.RQST_STAT_DTM,
CTCTRQTP.RQTP_SVC_GOAL_NBR,
CTCTRQST.RQST_CTC_DAYS_RMAN ,
CTCTRQST.RQST_CARR_MNE,
*/
CTCTRQTP.RQTP_Dsc,
SUM( 1 ) AS SubCount, --This is so I can produce the totals when done.--
SUM( CASE WHEN CTCTRQST.RQST_CTC_DAYS_RMAN > 0 THEN 1 ELSE 0 END ) AS RMNCHK, --Remaining Check.--
SUM( CASE WHEN CTCTRQST.RQST_STAT_CD = 'C' THEN 0 ELSE 1 END ) AS CLSDCHK,
SUM( CASE WHEN CTCTRQST.RQST_STAT_CD != 'C' AND CTCTRQST.RQST_CTC_DAYS_RMAN > 0 THEN 1 ELSE 0 END ) AS CLSDRMNCHK
FROM
CTCTRQST
INNER JOIN
CTCTRQTP
ON
CTCTRQST.RQTP_CD = CTCTRQTP.RQTP_CD
INNER JOIN
CTCTEMPL
ON
CTCTUNIT.UNIT_ID = CTCTEMPL.EMPL_WORK_UNIT_NBR
INNER JOIN
CTCTUNIT
ON
CTCTRQST.RQST_RCPT_ASOC_ID = CTCTEMPL.EMPL_LOGIN_ID
WHERE
CTCTUNIT.UNIT_ID > 0 AND
CTCTUNIT.DEPT_CD = '926' and
CTCTEMPL.EMPL_DEPT_CD='926' AND
CTCTRQST.RQST_RCPT_DEPT_CD = '926' and
CTCTRQTP.RQTP_DEPT_CD = '926' AND
/*
Both 2nd and 3rd query the same except
2nd has -- CTCTRQST.RQST_STAT_CD ='C' AND
3rd has -- CTCTRQST.RQST_STAT_CD <>'C') AND
You didn't do anything special for either than this.
*/
( CTCTRQST.RQST_STAT_DTM BETWEEN @st_dt AND @end_dt )
GROUP BY
ctctunit.unit_id,
ctctunit.unit_desc,
CTCTRQTP.RQTP_CD,
CTCTRQTP.RQTP_Dsc
ORDER BY
UNIT_ID, RQTP_CD
) AS tblBase
GROUP BY
UNIT_ID, --May not need this as I did not know if you have dup unit_desc's.--
unit_desc,
RQTP_CD, --May not need this as I did not know if you have dup rqtp_dsc's.--
rqtp_dsc
ORDER BY
unit_desc,
rqtp_dsc
If this is not dead on then it isn't far off based on all you do. Also if unit_desc and rqtp_dsc are unique you can make some adjustments inside the subquery that may help even more. Basically if the are then remove the unit_id protions from all select and group by items, and if rqtp_dsc is unique do the same with rqtp_cd
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 25, 2002 at 9:28 am
Thanks ur code seems to work perfectly with the suggested modifications.
Thanks a lot....
April 25, 2002 at 9:57 am
I enjoyed the challenge and glad to hear.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply