Table Variables - 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

Transact-SQL (2000)

Table Variables

Author

Topic

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2002-09-18 : 09:01:23

I have a stored proc that I was running, it currently uses # temp tables to store the data it needs to use. This process loops through various records and I was getting about 500 records in 15 minutes, not using a cursor.

I changed the procedure to use table variables, and then for the same 500 records, the time went up to about 30 minutes.

Any thoughts on why I would get an increase in the time for the same records? Here is the code, and you can see where i changed the process from the # to use table variables

CREATE PROC dbo.LOAD_DRGFACILITYFACTS

@BEGINID INT,
@ENDID INT

AS

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

/*----------------------------------------------------------------------*/
DECLARE @KEYID BIGINT,@YEAR VARCHAR(4),@DRGCODE VARCHAR(4),@TEACHINGIND CHAR(1),
@REGION VARCHAR(3),@MSA VARCHAR(5),@STATE VARCHAR(2),@BEDBAND VARCHAR(10),
@ALOS FLOAT,@MEDIANLOS FLOAT,@GEOMLOS FLOAT,@CMI FLOAT,@ISS FLOAT,@MEDIANIDX INT,
@Q1MEDIAN FLOAT,@Q3MEDIAN FLOAT,@IQR FLOAT,@LOSPRODUCT FLOAT(53),@GMAVG FLOAT(53),
@CASECOUNT INT,@SEQNO INT,@SRRSEQNO INT,@COUNTER INT,@CASEFACTKEY INT,
@AVGCHG MONEY,@mycount INT

SELECT @KEYID = 0,@YEAR = '',@DRGCODE = '',@TEACHINGIND = '',
@REGION = '',@MSA = '',@STATE = '',@BEDBAND = '',
@ALOS = 0,@MEDIANLOS = 0,@GEOMLOS = 0,@CMI = 0,@ISS = 0,@MEDIANIDX = 0,
@Q1MEDIAN = 0,@Q3MEDIAN = 0,@IQR = 0,@LOSPRODUCT = 0,@GMAVG = 0,
@CASECOUNT = 0,@SEQNO = 0,@SRRSEQNO = 0,@COUNTER = 0,@CASEFACTKEY = 0,
@AVGCHG = 0,@mycount = 0

/*----------------------------------------------------------------------*/
-- IF EXISTS (SELECT * FROM tempdb..sysindexes Where id = object_id('tempdb.dbo.@TEMP_DRGFACILITY'))
-- DROP TABLE @TEMP_DRGFACILITY
--
-- IF EXISTS (SELECT * FROM tempdb..sysindexes Where id = object_id('tempdb.dbo.@TEMP_SRR'))
-- DROP TABLE @TEMP_SRR

-- CREATE TEMP TABLES AS LOCAL VARIABLES
DECLARE @TEMP_DRGFACILITY TABLE
(SEQNO int IDENTITY (1,1) NOT NULL ,
CASEFACTKEY int NOT NULL ,
LOS smallint NULL ,
RW real NULL ,
CHARGES money NULL
PRIMARY KEY (SEQNO))

DECLARE @TEMP_DRGFACILITY_2 TABLE
(SEQNO int IDENTITY (1,1) NOT NULL ,
CASEFACTKEY int NOT NULL ,
LOS smallint NULL ,
RW real NULL ,
CHARGES money NULL
PRIMARY KEY (SEQNO))

DECLARE @TEMP_SRR TABLE
(SEQNO int IDENTITY (1,1) NOT NULL,
SRR real NULL
PRIMARY KEY (SEQNO))

/*----------------------------------------------------------------------*/
SELECT @KEYID = MIN(KEYID) FROM TBLKEYS
WHERE KEYID > @KEYID AND
KEYID BETWEEN @BEGINID AND @ENDID
--AND Processed = 0

WHILE @KEYID IS NOT NULL AND @KEYID BETWEEN @BEGINID AND @ENDID
BEGIN --BEGIN OUTER LOOP THROUGH KEYS

SELECT @YEAR = CAST(K.YEARID AS VARCHAR(4)),
@DRGCODE = CASE CAST(D.DRGCODE AS VARCHAR(4))
WHEN 'ALL' THEN '%' ELSE CAST(D.DRGCODE AS VARCHAR(4)) END,
@TEACHINGIND = CASE CAST(K.TEACHINGID AS CHAR(1))
WHEN '0' THEN '%' ELSE CAST(K.TEACHINGID AS CHAR(1)) END,
@REGION = CASE R.GEOGRAPHICREGIONCODE
WHEN 'ALL' THEN '%' ELSE R.GEOGRAPHICREGIONCODE END,
@MSA = CASE M.MSA
WHEN 'ALL' THEN'%' ELSE M.MSA END,
@STATE = CASE S.USSTATECODE
WHEN 'XX' THEN '%' ELSE S.USSTATECODE END,
@BEDBAND = CASE B.BEDSIZEDESC
WHEN 'ALL' THEN '%' ELSE B.BEDSIZEDESC END
FROM TBLKEYS AS K
JOIN DRGDim AS D ON K.DRGKey = D.DRGKey
JOIN TBLBedSize AS B ON K.BEDSIZEID = B.BEDSIZEID
JOIN TBLState AS S ON K.STATEID = S.STATEID
JOIN TBLRegions AS R ON K.REGIONCODE = R.REGIONID
JOIN TBLMSA AS M ON K.MSACODE = M.MSAID
WHERE K.KEYID = @KEYID
ORDER BY K.KEYID

/*----------------------------------------------------------------------*/
-- INSERT THE SET OF CASES MATCHING THE CRITERIA INTO TEMP TABLE

-- IF EXISTS (SELECT * FROM tempdb..sysindexes Where id = object_id('tempdb.dbo.@TEMP_DRGFACILITY') AND name = 'IDX_DRGFACILITY')
-- DROP INDEX @TEMP_DRGFACILITY.IDX_DRGFACILITY

-- MUST USE DELETE TO CLEAN OUT TABLE VARIABLE
DELETE FROM @TEMP_DRGFACILITY

-- CANT SELECT INTO, BUT NO LOGGING ANYWAY
INSERT INTO @TEMP_DRGFACILITY
(CASEFACTKEY,LOS,RW,CHARGES)
SELECT --IDENTITY(INT,1,1) AS SEQNO,
CASEFACTKEY, LENGTHOFSTAY AS LOS,
RELATIVEWEIGHT AS RW, TOTALCHARGES AS CHARGES
FROM MEDPARCASEFACT AS M
JOIN DRGDim AS D ON M.DRGKEY = D.DRGKEY
JOIN FacilityDIM AS F ON M.FACILITYKEY = F.FACILITYKEY
WHERE M.[YEAR] = @YEAR AND DRGCODE LIKE @DRGCODE
AND TEACHINGIND LIKE @TEACHINGIND AND FACILITYREGIONCODE LIKE @REGION
AND FACILITYMSA LIKE @MSA AND FACILITYSTATE LIKE @STATE
AND NUMBERBEDSBAND LIKE @BEDBAND
ORDER BY LENGTHOFSTAY

Select @SEQNO = @@IDENTITY

-- CREATE CLUSTERED INDEX IDX_DRGFACILITY ON @TEMP_DRGFACILITY(SEQNO)

-- ONLY CREATE FACT ROWS WHERE KEY VALUES HAVE MATCHING ROWS IN CASE FACT
IF @SEQNO > 0
BEGIN --IF THERE IS DATA, THEN GO THROUGH THE REST OF THE PROCESSING

/*----------------------------------------------------------------------*/
-- CASECOUNT, AVERAGE LOS, AVGERAGE CHARGES
-- CASE MIX INDEX - SUM OF RELATIVE WEIGHTS / TOTAL CASES
SELECT @CASECOUNT = COUNT(*),
@ALOS = AVG(CAST(LOS AS FLOAT)),
@AVGCHG = AVG(CHARGES),
@CMI = SUM(CAST(RW AS FLOAT)) / CAST(COUNT(*) AS FLOAT)
FROM @TEMP_DRGFACILITY

-- MEDIAN LOS
-- MIDPOINT VALUE OR AVERAGE OF 2 MIDPOINT VALUES
SELECT @MEDIANLOS = AVG(CAST(LOS AS FLOAT))
FROM @TEMP_DRGFACILITY
WHERE SEQNO BETWEEN @SEQNO / 2 AND (@SEQNO / 2) + SIGN(@SEQNO + 1 % 2)

-- GEOMETRIC MEAN LOS
-- PRODUCT OF ALL LOS FOR ALL CASES IN SET
SELECT @COUNTER = 1,@LOSPRODUCT = 1.0,@GMAVG = 0.0

WHILE @COUNTER <= @SEQNO
BEGIN
SELECT @GMAVG = @GMAVG + LOG(LOS)
FROM @TEMP_DRGFACILITY
WHERE SEQNO = @COUNTER

SELECT @COUNTER = @COUNTER + 1
END

-- THIS CODE ADDED TO HANDLE ARITHMETIC OVERFLOW FOR SOME SETS
SELECT @GEOMLOS = EXP(@GMAVG / CAST(@SEQNO AS FLOAT))

-- ILLNESS SEVERITY SCORE
-- PRODUCT OF ALL SRR FOR ALL DIAGNOSIS AND PROCEDURES

-- IF EXISTS (SELECT * FROM tempdb..sysindexes Where id = object_id('tempdb.dbo.@TEMP_SRR'))
-- DROP TABLE @TEMP_SRR
--
-- IF EXISTS (SELECT * FROM tempdb..sysindexes Where id = object_id('tempdb.dbo.@TEMP_SRR') AND name = 'idx_x')
-- DROP INDEX @TEMP_SRR.idx_x

SET @SRRSEQNO = 0

/*----------------------------------------------------------------------*/

-- MUST USE DELETE TO CLEAN OUT TABLE VARIABLE
DELETE FROM @TEMP_SRR

INSERT INTO @TEMP_SRR(SRR)
SELECT --IDENTITY(INT,1,1) AS SEQNO,
MEDPARSRR AS SRR
FROM @TEMP_DRGFACILITY AS TD
JOIN MedParCaseFact AS M ON TD.CASEFACTKEY = M.CASEFACTKEY
JOIN DiagnosisGroup AS DG ON M.DIAGNOSISGROUPKEY = DG.DIAGNOSISGROUPKEY
JOIN DiagnosisDim AS DD ON DG.DIAGNOSISKEY = DD.DIAGNOSISKEY
WHERE M.DIAGNOSISGROUPKEY > 0

INSERT INTO @TEMP_SRR(SRR)
SELECT MEDPARSRR
FROM @TEMP_DRGFACILITY AS TD
JOIN MedParCaseFact AS M ON TD.CASEFACTKEY = M.CASEFACTKEY
JOIN ProcedureGroup AS PG ON M.PROCEDUREGROUPKEY = PG.PROCEDUREGROUPKEY
JOIN ProcedureDim AS PD ON PG.PROCEDUREKEY = PD.PROCEDUREKEY
WHERE M.PROCEDUREGROUPKEY > 0

Select @SRRSEQNO = @@IDENTITY

-- CREATE CLUSTERED INDEX idx_x ON @TEMP_SRR(SEQNO)

SELECT @COUNTER = 1,@ISS = 1.0
WHILE @COUNTER <= @SRRSEQNO
BEGIN
SELECT @ISS = @ISS * SRR
FROM @TEMP_SRR
WHERE SEQNO = @COUNTER

SELECT @COUNTER = @COUNTER + 1
END

-- INTERQUARTILE RANGE (IQR)
-- Q1 = MEDIAN LOS FOR BOTTOM HALF
-- Q3 = MEDIAN LOS FOR TOP HALF
-- IQR = Q3 - Q1
IF @SEQNO > 1
BEGIN
SELECT @MEDIANIDX = AVG(CAST(SEQNO AS FLOAT))
FROM @TEMP_DRGFACILITY

SELECT @Q1MEDIAN = AVG(CAST(LOS AS FLOAT))
FROM @TEMP_DRGFACILITY
WHERE SEQNO BETWEEN @MEDIANIDX / 2 AND (@MEDIANIDX / 2) + SIGN(@MEDIANIDX + 1 % 2)

--THIS WILL GET THE DATA FOR THE TOP HALF OF THE TABLE INTO A NEW
-- TABLE INSTEAD OF DOING A DELETE AND REPOPULATE

-- MUST USE DELETE TO CLEAN OUT TABLE VARIABLE
DELETE FROM @TEMP_DRGFACILITY_2

INSERT INTO @TEMP_DRGFACILITY_2
(CASEFACTKEY,LOS,RW,CHARGES)
SELECT --IDENTITY(int,1,1) AS SEQNO,
A.CASEFACTKEY,A.LOS,A.RW,A.CHARGES
FROM @TEMP_DRGFACILITY AS A
WHERE A.SEQNO > @MEDIANIDX
ORDER BY A.LOS

SELECT @SEQNO = @@IDENTITY

-- CREATE CLUSTERED INDEX IDX_SEQ ON @TEMP_DRGFACILITY_2(SEQNO)

SELECT @Q3MEDIAN = AVG(CAST(LOS AS FLOAT))
FROM @TEMP_DRGFACILITY_2
WHERE SEQNO BETWEEN @SEQNO / 2 AND (@SEQNO / 2) + SIGN(@SEQNO + 1 % 2)

SELECT @IQR = @Q3MEDIAN - @Q1MEDIAN

-- DROP TABLE @TEMP_DRGFACILITY_2
END
ELSE
SELECT @IQR = 0,
@Q3MEDIAN = @MEDIANLOS,
@Q1MEDIAN = @MEDIANLOS

/*----------------------------------------------------------------------*/
INSERT INTO DRGFACILITYFACTS_three(FACTKEY,CASECOUNT,ALOS,MEDIANLOS,GEOMLOS,ISS,CMI,Q1,Q3,IQR,AVGCHARGES)
VALUES(@KEYID,@CASECOUNT,@ALOS,@MEDIANLOS,@GEOMLOS,@ISS,@CMI,@Q1MEDIAN,@Q3MEDIAN,@IQR,@AVGCHG)

--UPDATE THE TBLKEYS WITH A PROCESSED = 1
UPDATE TBLKEYS SET PROCESSED = 1 WHERE KEYID = @KEYID
END

-- IF EXISTS (SELECT * FROM tempdb..sysindexes Where id = object_id('tempdb.dbo.@TEMP_DRGFACILITY'))
-- DROP TABLE @TEMP_DRGFACILITY

-- IF EXISTS (SELECT * FROM tempdb..sysindexes Where id = object_id('tempdb.dbo.@TEMP_SRR'))
-- DROP TABLE @TEMP_SRR

--RESET THE VARS HERE
SELECT @YEAR = '',@DRGCODE = '',@TEACHINGIND = '',@REGION = '',@MSA = '',
@STATE = '',@BEDBAND = '',@ALOS = 0,@MEDIANLOS = 0,@GEOMLOS = 0,
@CMI = 0,@ISS = 0,@MEDIANIDX = 0,@Q1MEDIAN = 0,@Q3MEDIAN = 0,@IQR = 0,
@LOSPRODUCT = 0,@GMAVG = 0,@CASECOUNT = 0,@SEQNO = 0,@SRRSEQNO = 0,
@COUNTER = 0,@CASEFACTKEY = 0,@AVGCHG = 0

SELECT @KEYID = MIN(KEYID) FROM TBLKEYS WHERE KEYID > @KEYID

Select @mycount = @mycount + 1

END

/*-----------------------------------------------------------------------*/
--IF EXISTS (SELECT * FROM tempdb..sysindexes Where id = object_id('tempdb.dbo.@TEMP_DRGFACILITY'))
-- DROP TABLE @TEMP_DRGFACILITY

--IF EXISTS (SELECT * FROM tempdb..sysindexes Where id = object_id('tempdb.dbo.@TEMP_SRR'))
-- DROP TABLE @TEMP_SRR

Thanks,
Scooter McFly

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-09-18 : 19:31:28

I don't feel awake enough to read all your code

but at a guess....
Temp tables are basically normal tables, built on the disk. Table variables are in memory. If your server didn't have a lot of memory, but had quick disks then you might see better performance out of temp tables.

Damian

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2002-09-19 : 07:24:51

I have SQL 2K Advanced running on Win2K Advanced Server, a QUAD 1.4GHz with 6GB RAM, and the drives are 15Krpm SCSI in RAID 0(only a development box).

Is there anywhere that documents that the table variables are actually created in memory and not on the disk? I am just having trouble locating anything that documents that.

Thanks,
Scooter McFly

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-19 : 07:57:17

A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q305977[/url]

HTH
Jasper Smith

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-09-19 : 08:51:27

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2002-09-19 : 09:13:40

Hi Scot,

The best approach of doing this is, make a sql function which retuns a table type variable. U can do this by :-

CREATE function function_name()
Returns @table_var table
(
Field1 varchar(10)
Field2 varchar(10)
.....
)
as
Begin
insert into @table_var
Select field1, field2,..... from student
return
end

This is a bit faster than the method u r using. After creating this function u can call this function in ur store procedure. U can even pass parameters to the function.

The performance can also be improved if u try to make the queries little bit simpler in ur Store procedure or else denormalize the data base. If ur store procedure is taking 15 min, then it will kill the user. I hope this will help. Let me know.

Thanx

Sachin
sachin.samuel@fqsltd.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources