DECLARE @MY_TABLE TABLE - SQL Server Forums

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

SQL Server Development (2000)

DECLARE @MY_TABLE TABLE

Author

Topic

nod
Starting Member

19 Posts

Posted - 2004-11-16 : 15:43:33

The problem is that we have a procedure that is being called simulaneously multiple times (over 100). The procedure is used to build a report. So of course I am running into some severe locks and my initial thought was to simple use Dynamic SQL to create these tables with day + mi + sec + ms or something there of. I have tried instead of using #temp tables using @temp tables. Not sure of what my results are telling yet!

HELP!!!

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-16 : 15:51:45

Use the table variable

Brett

8-)

nod
Starting Member

19 Posts

Posted - 2004-11-16 : 15:58:50

To be honest I'm not sure how the table variable (declare @temp) works in comparison to (declare #temp). So by using the variable will I gain the use of creating this table multiple times as if it is created with a different name each time it was created?

This maybe asking a lot but can you give me some insight if possible?

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-16 : 16:07:49

quote:
will I gain the use of creating this table multiple times as if it is created with a different name each time it was created?

think so

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-16 : 16:10:44

I guess my question becomes.... why do you experience locks on a #temp table? It is unique to the session (or connection i suppose)...?

Why don't you give us a sample (just enough to get the idea across) of what you are doing (what the procedure does, how its called, can a user call it more than one time from a connection...etc) and maybe we can give you better advice and/or direction

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-16 : 16:15:26

Are you sure you don't mean blocking?

If it is locks, it's with something else the temp table is around only for, and only lives in, the spid that creates it.

The table variable will be better because it'll be in memory

And where's winkin and blinkin?

Brett

8-)

nod
Starting Member

19 Posts

Posted - 2004-11-16 : 16:29:10

And where's winkin and blinkin? LOL Who is that?

The temp table is created and then updated several times based on other queries in the procedure. I haven't spent much time looking over it at all in terms of optimizing it. Definitely aquiring multiple locks not blocks... Users don't access the procedure. The proc is kicked off by a Crystal Reports Scheduler so it kicks off the reports to run all at basically the same time. From the way it appears on SQL there are multiple connections.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-11-16 : 16:37:40

I think the first thing to do is use table variables instead and let us know the results. Thanks

nod
Starting Member

19 Posts

Posted - 2004-11-16 : 18:15:41

Prelimanary results look good with using the table variable though I won't have the actual results until tomorrow morning. Once I'm able to capture the results and usages I will inform.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-16 : 18:45:46

quote:
Originally posted by nod

The problem is that we have a procedure that is being called simulaneously multiple times (over 100). The procedure is used to build a report. So of course I am running into some severe locks and my initial thought was to simple use Dynamic SQL to create these tables with day + mi + sec + ms or something there of. I have tried instead of using #temp tables using @temp tables. Not sure of what my results are telling yet!

HELP!!!

you should re-write your procedure so that it is only called once and works in a set-based manner instead of being called over and over.

Give us an example of what this procedure does and why you need to call it 100 times, and also some code on how you are doing this.

let me guess -- lots of cursors!

- Jeff

nod
Starting Member

19 Posts

Posted - 2004-11-17 : 10:22:12

No cursors... Though I am a fan of them for some reporting needs. The proc is being called so often because it is given different values as parameters. The values are being fed by Crystal Reports. So you may produce one report for California, another for Florida and yet another for Fort Lauderdale and so on. On these reports data is collected on a regional basis the data is not related outside of how is defined to fit on the reports.

I'm not familiar with set-based, can you explain or point me somewhere?

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-17 : 11:27:54

quote:
Originally posted by nod

I'm not familiar with set-based, can you explain or point me somewhere?

[thud]
What's that sound?
[/thud]

Dr! Are you alright?

http://www.librarysupport.net/mothergoosesociety/goosemessages/messages/1446.html

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-17 : 12:03:26

I think we need to see some examples of the code you are running ... the question is: why do you need temp tables or table variables?

- Jeff

nod
Starting Member

19 Posts

Posted - 2004-11-17 : 13:49:37

CREATE PROCEDURE sp_build_monthly_rep
@LOB_NM VARCHAR(50),
@ENT_NM VARCHAR(50),
@RPT_MONTH INT,
@RPT_YEAR INT
AS

SET NOCOUNT ON

DECLARE @MTD_ABBR VARCHAR(5),
@DIR_ABBR VARCHAR(5),
@TRR_ABBR VARCHAR(5),
@RT_ABBR VARCHAR(5),
@APP_ABBR VARCHAR(5),
@EAST_IND CHAR(1),
@WEST_IND CHAR(1),
@BOTH_IND CHAR(1),
@STAND_IND CHAR(1),
@COR_IND CHAR(1),
@UNCOR_IND CHAR(1),
@LOB_ID INT,
@ENT_ID INT,
@DATE VARCHAR(30),
@TABLE SYSNAME

--EXEC sp_executesql @CMD, @ParmDefinition,
--@IMP_OBJ_ID = @IMP_OBJ_ID

SELECT @LOB_ID = LOB_ID
FROM appLOB
WHERE LOB_NM = @LOB_NM;

SELECT @ENT_ID = RPT_ENT_ID
FROM appRPT_ENT
WHERE ENT_NM = @ENT_NM;

SET @MTD_ABBR = 'MTD'
SET @DIR_ABBR = 'DIR'
SET @TRR_ABBR = 'TRR'
SET @RT_ABBR = 'RT'
SET @APP_ABBR = 'APP'
SET @EAST_IND = 'E'
SET @WEST_IND = 'W'
SET @BOTH_IND = 'B'
SET @STAND_IND = 'S'
SET @COR_IND = 'N'
SET @UNCOR_IND = 'U'

-- Create temp table that will contain the data to be returned to the report

DECLARE @MTD_TMP TABLE (
RPT_GRID_NUM NUMERIC,
ITEM_DESC VARCHAR(100),
LOG_DATE DATETIME,
NCO NUMERIC,
NCH NUMERIC,
OFRD_LOAD NUMERIC,
NICM NUMERIC,
ABAN NUMERIC,
BUSY NUMERIC,
NOC NUMERIC,
DELAYS NUMERIC,
AUC NUMERIC,
AU20 NUMERIC,
AU30 NUMERIC,
AOP DECIMAL,
SAPM NUMERIC,
ATTWV NUMERIC,
AWTWV NUMERIC,
AOTWV NUMERIC,
HOLD_WV NUMERIC,
HOLD_NUM NUMERIC,
CWS NUMERIC,
SIGNIN NUMERIC,
AVAIL NUMERIC,
SOL NUMERIC,
WV NUMERIC,
AHT NUMERIC,
RTD NUMERIC,
IVRU NUMERIC,
ZERO_TOUCH NUMERIC,
APP_IND VARCHAR(5),
REG_IND VARCHAR(5),
TRR_NCH NUMERIC,
TRR_SIGNIN NUMERIC,
TRR_AVAIL NUMERIC,
RT_NCH NUMERIC,
RT_AOP NUMERIC,
RT_ATTWV NUMERIC,
RT_AWTWV NUMERIC
)

-- Insert main report data from multi_ttl_day to #MTD_TMP
INSERT @MTD_TMP (
RPT_GRID_NUM,
ITEM_DESC,
LOG_DATE,
NCO,
NCH,
OFRD_LOAD,
NICM,
ABAN,
BUSY,
NOC,
DELAYS,
AUC,
AOP,
SAPM,
ATTWV,
AWTWV,
AOTWV,
HOLD_WV,
HOLD_NUM,
CWS,
SIGNIN,
AVAIL,
SOL,
WV,
AHT,
RTD,
IVRU,
ZERO_TOUCH
)
SELECT RG.RPT_GRID_NUM,
RG.ITEM_DESC,
M.LOG_DATE,
sum(NCO) AS NCO,
sum(NCH) AS NCH,
sum(OFRD_LOAD) AS OFRD_LOAD,
sum(NICM) AS NICM,
sum(ABAN) AS ABAN,
sum(BUSY) AS BUSY,
sum(NOC) AS NOC,
sum(DELAYS) AS DELAYS,
sum(AUC) AS AUC,
sum(AOP) AS AOP,
sum(SAPM) AS SAPM,
sum(ATTWV) AS ATTWV,
sum(AWTWV) AS AWTWV,
sum(AOTWV) AS AOTWV,
sum(HOLD_WV) AS HOLD_WV,
sum(HOLD_NUM) AS HOLD_NUM,
sum(CWS) AS CWS,
sum(SIGNIN) AS SIGNIN,
sum(AVAIL) AS AVAIL,
sum(SOL) AS SOL,
sum(WV) AS WV,
sum(AHT) AS AHT,
sum(RTD) AS RTD,
sum(IVRU) AS IVRU,
sum(ZERO_TOUCH) AS ZERO_TOUCH
FROM appRPT_TYPE RT,
appREPORT R,
rptCOMP_GRID CG,
rptRPT_GRID RG,
rptMULTI_TTL_DAY M
WHERE
rtrim(RT.RPT_TYPE_ABBR) in (@MTD_ABBR, @DIR_ABBR)
AND RT.RPT_TYPE_ID = R.FK_RPT_TYPE_ID
AND R.FK_LOB_ID = @LOB_ID
AND R.FK_RPT_ENT_ID = @ENT_ID
AND DATEPART(M,LOG_DATE) = @RPT_MONTH
AND DATEPART(YYYY,LOG_DATE) = @RPT_YEAR
AND R.REPORT_ID = RG.FK_REPORT_ID
AND RG.RPT_GRID_ID = CG.FK_RPT_GRID_ID
AND CG.FK_COMP_ID = M.FK_COMP_ID
AND CG.ACT_FLAG = '1'
AND RG.ACT_FLAG = '1'
AND M.NORM_IND in (@STAND_IND,@COR_IND)
GROUP BY RG.RPT_GRID_NUM, RG.ITEM_DESC, LOG_DATE

--select * from #mtd_tmp

-- Update the APP_IND column in #MTD_TMP for Grids that include Applications
UPDATE @MTD_TMP
SET APP_IND = @APP_ABBR
WHERE RPT_GRID_NUM IN (
SELECT RG.RPT_GRID_NUM
FROM appRPT_TYPE RT,
appREPORT R,
rptCOMP_GRID CG,
rptCOMP C,
rptRPT_GRID RG
WHERE
rtrim(RT.RPT_TYPE_ABBR) in (@MTD_ABBR, @DIR_ABBR)
AND RT.RPT_TYPE_ID = R.FK_RPT_TYPE_ID
AND R.FK_LOB_ID = @LOB_ID
AND R.FK_RPT_ENT_ID = @ENT_ID
AND R.REPORT_ID = RG.FK_REPORT_ID
AND RG.RPT_GRID_ID = CG.FK_RPT_GRID_ID
AND C.COMP_ID = CG.FK_COMP_ID
AND CG.ACT_FLAG = '1'
AND RG.ACT_FLAG = '1'
GROUP BY RG.RPT_GRID_NUM, C.FK_TYPE_CD
HAVING C.FK_TYPE_CD = @APP_ABBR
)

-- Create the @MTD_TMP_REG table with data sources to help determine grids that are East, West or Both

DECLARE @MTD_TMP_REG TABLE (RPT_GRID_NUM INT, DATA_SRC_CD VARCHAR(30))

INSERT @MTD_TMP_REG
SELECT DISTINCT RG.RPT_GRID_NUM,
DS.DATA_SRC_CD

FROM appRPT_TYPE RT,
appREPORT R,
rptCOMP_GRID CG,
rptCOMP C,
rptRPT_GRID RG,
rptDATA_SRC DS
WHERE
rtrim(RT.RPT_TYPE_ABBR) in (@MTD_ABBR, @DIR_ABBR)
AND RT.RPT_TYPE_ID = R.FK_RPT_TYPE_ID
AND R.FK_LOB_ID = @LOB_ID
AND R.FK_RPT_ENT_ID = @ENT_ID
AND R.REPORT_ID = RG.FK_REPORT_ID
AND RG.RPT_GRID_ID = CG.FK_RPT_GRID_ID
AND C.COMP_ID = CG.FK_COMP_ID
AND DS.DATA_SRC_ID = C.FK_DATA_SRC_ID
AND CG.ACT_FLAG = '1'
AND RG.ACT_FLAG = '1'

--SELECT * FROM @MTD_TMP_REG

-- Update any grid in #MTD_TMP that has both RT and TRR components to B for Both
UPDATE @MTD_TMP
SET REG_IND = @BOTH_IND
WHERE RPT_GRID_NUM IN (
SELECT R1.RPT_GRID_NUM
FROM @MTD_TMP_REG R1,
@MTD_TMP_REG R2
WHERE R1.DATA_SRC_CD = @RT_ABBR
AND R2.DATA_SRC_CD = @TRR_ABBR
AND R1.RPT_GRID_NUM = R2.RPT_GRID_NUM)

-- Update any grid in #MTD_TMP that has RT but no TRR components to E for East
UPDATE @MTD_TMP
SET REG_IND = @EAST_IND
WHERE RPT_GRID_NUM IN (
SELECT R1.RPT_GRID_NUM
FROM @MTD_TMP_REG R1
WHERE R1.DATA_SRC_CD = @RT_ABBR
AND RPT_GRID_NUM NOT IN (
SELECT R2.RPT_GRID_NUM
FROM @MTD_TMP_REG R2
WHERE R2.DATA_SRC_CD = @TRR_ABBR))

-- Update any grid in #MTD_TMP that has TRR but no RT components to W for West
UPDATE @MTD_TMP
SET REG_IND = @WEST_IND
WHERE RPT_GRID_NUM IN (
SELECT R1.RPT_GRID_NUM
FROM @MTD_TMP_REG R1
WHERE R1.DATA_SRC_CD = @TRR_ABBR
AND RPT_GRID_NUM NOT IN (
SELECT R2.RPT_GRID_NUM
FROM @MTD_TMP_REG R2
WHERE R2.DATA_SRC_CD = @RT_ABBR))

--SELECT distinct RPT_GRID_NUM, APP_IND, REG_IND FROM #MTD_TMP

-- Create temp table to hold the values for the TRR specific Occupancy calculations

DECLARE @YTD_TRR_OCC TABLE (RPT_GRID_NUM INT, LOG_DATE DATETIME, TRR_NCH NUMERIC, TRR_SIGNIN NUMERIC, TRR_AVAIL NUMERIC)

INSERT @YTD_TRR_OCC
SELECT
RG.RPT_GRID_NUM,
M.LOG_DATE,
sum(M.NCH) as TRR_NCH,
sum(M.SIGNIN) AS TRR_SIGNIN,
sum(M.AVAIL) AS TRR_AVAIL

FROM appRPT_TYPE RT,
appREPORT R,
rptCOMP_GRID CG,
rptRPT_GRID RG,
rptMULTI_TTL_DAY M,
rptDATA_SRC DS,
rptCOMP C
WHERE
rtrim(RT.RPT_TYPE_ABBR) in (@MTD_ABBR, @DIR_ABBR)
AND RT.RPT_TYPE_ID = R.FK_RPT_TYPE_ID
AND R.FK_LOB_ID = @LOB_ID
AND R.FK_RPT_ENT_ID = @ENT_ID
AND DATEPART(M,LOG_DATE) = @RPT_MONTH
AND DATEPART(YYYY,LOG_DATE) = @RPT_YEAR
AND R.REPORT_ID = RG.FK_REPORT_ID
AND RG.RPT_GRID_ID = CG.FK_RPT_GRID_ID
AND CG.FK_COMP_ID = M.FK_COMP_ID
AND C.COMP_ID = CG.FK_COMP_ID
AND DS.DATA_SRC_ID = C.FK_DATA_SRC_ID
AND DS.DATA_SRC_CD = @TRR_ABBR
AND CG.ACT_FLAG = '1'
AND RG.ACT_FLAG = '1'
AND M.NORM_IND in (@STAND_IND,@COR_IND)
GROUP BY RG.RPT_GRID_NUM, LOG_DATE

-- Update the main Temp table with values for the TRR Occupancy calculations
UPDATE @MTD_TMP
SET TRR_NCH = occ.TRR_NCH,
TRR_SIGNIN = occ.TRR_SIGNIN,
TRR_AVAIL = occ.TRR_AVAIL
FROM @MTD_TMP tmp JOIN @YTD_TRR_OCC occ
ON tmp.LOG_DATE = occ.LOG_DATE
AND tmp.RPT_GRID_NUM = occ.RPT_GRID_NUM

-- Create temp table to hold the values for the RT-specific Occupancy calculations

DECLARE @MTD_RT_OCC TABLE (RPT_GRID_NUM INT, LOG_DATE DATETIME, RT_NCH NUMERIC, RT_AOP NUMERIC, RT_AWTWV NUMERIC, RT_ATTWV NUMERIC)

INSERT @MTD_RT_OCC
SELECT
RG.RPT_GRID_NUM,
M.LOG_DATE,
sum(M.NCH) AS RT_NCH,
sum(M.AOP) as RT_AOP,
sum(M.AWTWV) AS RT_AWTWV,
sum(M.ATTWV) AS RT_ATTWV

FROM appRPT_TYPE RT,
appREPORT R,
rptCOMP_GRID CG,
rptRPT_GRID RG,
rptMULTI_TTL_DAY M,
rptDATA_SRC DS,
rptCOMP C
WHERE
rtrim(RT.RPT_TYPE_ABBR) in (@MTD_ABBR, @DIR_ABBR)
AND RT.RPT_TYPE_ID = R.FK_RPT_TYPE_ID
AND R.FK_LOB_ID = @LOB_ID
AND R.FK_RPT_ENT_ID = @ENT_ID
AND DATEPART(M,LOG_DATE) = @RPT_MONTH
AND DATEPART(YYYY,LOG_DATE) = @RPT_YEAR
AND R.REPORT_ID = RG.FK_REPORT_ID
AND RG.RPT_GRID_ID = CG.FK_RPT_GRID_ID
AND CG.FK_COMP_ID = M.FK_COMP_ID
AND C.COMP_ID = CG.FK_COMP_ID
AND DS.DATA_SRC_ID = C.FK_DATA_SRC_ID
AND DS.DATA_SRC_CD = @RT_ABBR
AND CG.ACT_FLAG = '1'
AND RG.ACT_FLAG = '1'
AND M.NORM_IND in (@STAND_IND,@COR_IND)
AND M.LOG_DATE BETWEEN isnull(CG.BEGIN_DATE, '01-01-1900') and isnull(CG.END_DATE, '01-01-2100')

GROUP BY RG.RPT_GRID_NUM, LOG_DATE

-- Update the main Temp table with values for the RT Occupancy calculations
UPDATE @MTD_TMP
SET RT_NCH = occ.RT_NCH,
RT_AOP = occ.RT_AOP,
RT_ATTWV = occ.RT_ATTWV,
RT_AWTWV = occ.RT_AWTWV
FROM @MTD_TMP tmp JOIN @MTD_RT_OCC occ
ON tmp.LOG_DATE = occ.LOG_DATE
AND tmp.RPT_GRID_NUM = occ.RPT_GRID_NUM

select * from @MTD_TMP

SET NOCOUNT OFF

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-17 : 14:21:55

Have you ever used JOIN's before? I didn't see any -- keep those out of the WHERE clause for better readability and performace. Also, do you know you can join to derived tables (other SELECT statements) like this:

SELECT ..
FROM Tbl
INNER JOIN (SELECT ....) tmp
ON tbl.ID = tmp.ID

?

You often do things in steps, including an UPDATE to set values in your temp tables, where you could just do a join to the foreign table or another SELECT statement (as shown above) to get the data you need. If there isn't always a match in the foreign tables, you can use LEFT OUTER JOINs which are not always possible when doing your joins in the WHERE clause.

also -- avoid WHERE IN ( SELECT WHERE IN ( ... )) -- those are painfully slow, and again, you are much better off doing joins there and using derived tables.

How long does this take to run for a given report?

Finally, in your WHERE clause, avoid conditions like this:

AND DATEPART(M,LOG_DATE) = @RPT_MONTH
AND DATEPART(YYYY,LOG_DATE) = @RPT_YEAR

those will be very inefficient. Calculate a StartDate and an EndDate based on the month/year passed to the report, and replace all of those with:

WHERE LOG_DATE BETWEEN @StartDate AND @EndDate

That will increase performance by a lot. If you need help wiht a formula for this, let us know.

I strongly recommend stepping back, looking at the tables involved and the results you'd like to return, and rewriting this as a single SELECT statement with no temp tables or table variables or anything. Using proper indexes, proper WHERE clauses and correct use of JOINS and derived tables, I guarantee you it can be made to run much, much faster. It will be a bit of work, and will require re-thinking things as you know it (I think it was Yoda who said "you must unlearn what you have learned") -- but I promise you in the long run it will be well worth it.

Feel free to ask us for specific help, but break it down into parts and slowly build up your SELECT as you go, doing it one part of time while testing, and eventually joining it all together for the end result. VIEWS can be extremely helpful here as well.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources