insert @tbl problem - 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 2005 Forums

Transact-SQL (2005)

insert @tbl problem

Author

Topic

ArieB
Starting Member

9 Posts

Posted - 2008-06-03 : 09:40:13

Thanks for any suggestions.
I've recently migrated to SQL2005.
Not sure if it's related.

Here it is.
First insert: storing rows returned from a function
works great. (about 5000 rows in 2 secs)

Second insert: supposed to return about 10 rows never completes.
can run for hours.
But when I remove the the "Insert @tbl" line, the whole batch executes with 10 secs.
The select returns the same datatype that are difined in the table variable.
(ps. Another database on the same server executes the whole batch is secs)

Any help with this greatly appreciated.
Thanks


Declare @StartDate smallDateTime, @EndDate smallDateTime, @DollarRate Float
Set @StartDate='20080427'
Set @EndDate='20080531'
set @DollarRate=1

Declare @PriceStart table (StyleNumber bigint, ColorID varchar(5),  RetailPrice money, Original_Retail money, ChangeDate smalldateTime primary key (StyleNumber, COlorID))
insert into @PriceStart select * from PriceUpdateToDate(@StartDate)

-- THIS Insert works

declare @tbl table
(id bigint identity(1,1) primary key,Dept Int, Units int, Retail money)

-- This INSERT DOESN'T WORK!!
insert @tbl
select im.DepartmentID , Sum(NumberOfUnits), Sum(NumberOfUnits*p.RetailPrice) 
From dbo.InventoryMovementsStyle im join @PriceStart p on 
im.StyleNumber=p.StyleNumber and im.ColorID=p.ColorID
Where TransactionDate <@StartDate and DepartmentID<>99 and im.StyleNumber not between 11432 and 11441
group by im.DepartmentID
having Sum(NumberOfUnits)<>0

select * from @tbl

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 09:42:16

Use temporary table instead.
Replace @tbl with #tbl and see if there is a great change.
It should be, if there are many records in the table to be inserted.

E 12°55'05.25"
N 56°04'39.16"

ArieB
Starting Member

9 Posts

Posted - 2008-06-03 : 09:47:43

Thanks peso,
but the second select returns only 10 rows!
while the first insert select that returns more 5000 rows does execute in 2 secs!
there must be some other problem...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-03 : 09:52:52

What indexes are there on InventoryMovementsStyle table? Also how many records in this table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 09:53:33

Try first. Talk later.
The reason is that it seems SQL Server has a limited space for table variables in memory and when that space is filled,
all content is flushed to tempdb and this is a relative heavy timeconsuming task.

I have seen examples where a small table variable of 117 bytes record size and 12000 records took over 3 minutes to insert.
Changing it to temp table reduced the time to a fraction of a second.

E 12°55'05.25"
N 56°04'39.16"

ArieB
Starting Member

9 Posts

Posted - 2008-06-03 : 10:00:39

I've never used Tmp tables.
Do I have to drop it at the end of the batch?
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 10:02:47

[code]DECLARE @StartDate SMALLDATETIME,
@EndDate SMALLDATETIME,
@DollarRate FLOAT

SELECT @StartDate = '20080427',
@EndDate = '20080531',
@DollarRate = 1

CREATE TABLE #PriceStart
(
StyleNumber BIGINT NOT NULL,
ColorID VARCHAR(5) NOT NULL,
RetailPrice MONEY,
Original_Retail MONEY,
ChangeDate SMALLDATETIME
)

INSERT #PriceStart
SELECT *
FROM PriceUpdateToDate(@StartDate)

CREATE UNIQUE CLUSTERED INDEX IX_PriceStart ON #PriceStart (StyleNumber, ColorID)

CREATE TABLE #tbl
(
ID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Dept INT,
Units INT,
Retail MONEY
)

INSERT #tbl
SELECT im.DepartmentID,
SUM(NumberOfUnits),
SUM(NumberOfUnits * p.RetailPrice)
FROM dbo.InventoryMovementsStyle AS im
INNER JOIN #PriceStart AS p on p.StyleNumber = im.StyleNumber
AND p.ColorID = im.ColorID
WHERE TransactionDate < @StartDate
AND DepartmentID <> 99
AND im.StyleNumber NOT BETWEEN 11432 AND 11441
GROUP BY im.DepartmentID
HAVING SUM(NumberOfUnits) <> 0

SELECT *
FROM #tbl[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 10:04:17

No. Local temporary tables are automatically dropped when the batch has ended.

E 12°55'05.25"
N 56°04'39.16"

ArieB
Starting Member

9 Posts

Posted - 2008-06-03 : 10:19:06

peso, worked great.
but when I run a second time i get.

There is already an object named '#PriceStart' in the database.

ArieB
Starting Member

9 Posts

Posted - 2008-06-03 : 10:26:50

Anyways peso,
appreciate the help a great deal man.
the statement executes in 3 secs.
That's really amazing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 13:51:32

quote:
Originally posted by ArieB

peso, worked great.
but when I run a second time i get.

There is already an object named '#PriceStart' in the database.

You need to explicitly drop the #table if you're running it second time in the existing connection itself. Other option is to wrap CREATE TABLE around if to check if it exists and drop if it does.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 14:01:27

Add a "DROP TABLE #PriceStart" after the last SELECT.

I've found that a table variable is only good for storing 1 or 2 pages of data in memory.
If the data gets bigger, SQL Server flushes to tempdb and this is what is taking time.

Anyway, it's great that you solver your problem.
Also test without the CLUSTERD hint when creating the index. Maybe you won't need the data clustered.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources