Size of table created on the fly is limited

Question

Size of table created on the fly is limited

Mark Green

SSCrazy

Points: 2085
More actions
April 30, 2007 at 3:20 am

#68406

I have an application that creates many tables 'on the fly'. It is an analytical database system. The problem I have occurs when I query it with England and Wales requests (large data set with millions of rows). In this case I am limited, presumably because of limits on the table size, to a six year time period. Can I set my MS SQL Server 2000 system up to allow the creation and filling of huge tables? Were do I look in the docs?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you didn't really describe what the problem was...whether it was slow running, received a real error, or just bad performance.....so here's my guesstimate:

There's no problem creating a really big temp table; i've done it with a zip+4 database that was 13 gig before, and a godawful number of rows. the only real limits are I/O thruput and diskspace.

did you create any index(s) on the table created on the fly? the index will greatly speed up any subsequent queries against the data there;

How much space is allotted to the temp database? it defaults to something small, like 10 meg, and expands as needed. If you query calls for making a 1 gig temp table, you might get stalled for a long time as the server starts grabbing space, and then finally processes the query. setting the temp database to a larger size could help speed up the table creation.

Instead of temp tables, you could use real tables in the system, and truncate them prior to each use if the structure is the same all the time; that's might help as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark Green SSCrazy Points: 2085 More actions · Answer 2

I am not really referring to a #temp table. But it is a table that is created at the beginning of the run and then dropped at the end of the run (ordinary table from MS SQL Server's perspective). The query does not fill the table when more than six years is chosen (slightly complex method for filling the table). I don't think that it is a limit on disk space, although I don't have a huge amount of space to run the app.

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 3

There is no limit on a table created at runtime for size. I'd guess that you might exceed the disk space for the file and cause a growth, which in SQL 2000, can pause the system while the file is zero'd out.

I'd dig in further with Profiler and perfmon and see what is taking place.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Maybe it's a good idea to put your "on the fly" tables in separate (huge) filegroup(s) on fast spindles.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Mark Green SSCrazy Points: 2085 More actions · Answer 5

I'm not really sure what you mean. But if it can speed up the application I'll like to try. Please can someone expand on this idea.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

a database consists of at least one filegroup (called PRIMARY).

- a filegroup is a "container" for db-files.

- If you want to put your "on the fly" tables in the same database as your other data, but do not want to mess around with the current db-files,

you may want to ask your dba to create a new filegroup and adding separate files to it. check Books online! (filegroups) (alter database add filegroup)

If you make the new filegroup the db-default one, it has its concequences for every regular user creating tables or indexes !

e.g. : (from BOL)

ALTER DATABASE Test1

ADD FILEGROUP Test1FG1

GO

ALTER DATABASE Test1

ADD FILE

( NAME = test1dat3,

FILENAME = 'yourdrive:\MSSQL\Data\t1dat3.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB),

( NAME = test1dat4,

FILENAME = 'yourdrive:\MSSQL\Data\t1dat4.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB)

TO FILEGROUP Test1FG1

ALTER DATABASE Test1

MODIFY FILEGROUP Test1FG1 DEFAULT

Create table [on_the_fly] (columnlist) ON [Test1FG1]

Consult your dba because this may have impact on his/her DRP !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me