Large Inserts, TempDB Growing

22 Sep 2005 10:44 PM

Mark

I have a query that's joining a messload of tables to populate a single
table used later for OLAP reporting.

The source tables, and the OLAP table are in different databases.

Basic Form:
INSERT INTO OLAPDB.dbo.SomeTable
SELECT lots_of_columns
FROM atables
INNER JOIN lots_of_tables....

No ORDER BYs... no GROUP BYs

The query dies because there isn't enough disk space for TempDB.
When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
is tiny.

Is there a way to insert the data straight into the destination?
without it using tempdb as an intermediate?

any ideas?

thanks
-Mark

22 Sep 2005 10:50 PM

Jerry Spivey

Mark,

Maybe add a WHERE clause and perform the INSERT in multiple parts.

HTH

Jerry
Show quote

"Mark" <AnonymousPerson12***@gmail.com> wrote in message
news:1127429078.936480.53900@o13g2000cwo.googlegroups.com...
>I have a query that's joining a messload of tables to populate a single
> table used later for OLAP reporting.
>
> The source tables, and the OLAP table are in different databases.
>
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables....
>
> No ORDER BYs... no GROUP BYs
>
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
>
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
>
> any ideas?
>
> thanks
> -Mark
>

22 Sep 2005 10:55 PM

David Gugick

Mark wrote:
Show quote

> I have a query that's joining a messload of tables to populate a
> single table used later for OLAP reporting.
>
> The source tables, and the OLAP table are in different databases.
>
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables....
>
> No ORDER BYs... no GROUP BYs
>
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
>
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
>
> any ideas?
>
> thanks
> -Mark

An INSERT INTO is a fully logged operation. A SELECT INTO OTOH is a bulk
logged operation. Instead of using tempdb, you could use a regular table
in a database of your choosing. However, if you are running out of
space in tempdb, you could make sure tempdb is adequately sized to begin
with and can auto-grow if needed. You could also try using SELECT INTO
which will keep transaction logging to a minimum, but does require SQL
Server create the table for you based on the columns in the query. You
could also try speeding up the query by using a stored procedure that
pulls data from the tables in a more efficient manner - if that's
possible.

--
David Gugick
Quest Software
www.imceda.com
www.quest.com