Run SQL Server in Memory (like TimesTen)

Question

Run SQL Server in Memory (like TimesTen)

Mike Klostermeyer

SSC Enthusiast

Points: 194
More actions
September 3, 2006 at 1:35 pm

#62269

I have some long running DTS packages that I really need to trim down the processing time. They are very basic (simple file import), so not much can be done there. I was curious if SQL Server 2000 has any options to run a database in memory, ala TimesTen?
Any other suggestions you can give me on speed improvements would be very helpful. Thanks.
Mike

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I'm not the expert in mass data importation but I really don't think it's that slow either (remember importing 300K large rows of data in just a few seconds).

However I'm sure there's a way to speed up what you do after the import. Can you tell us more about the process you are doing?

Mike Klostermeyer SSC Enthusiast Points: 194 More actions · Answer 2

I have a couple files with 10 fields of data per record, and less than 200,000 records per file that takes up to 5 minutes on our underutilized production machine (Dual 1 Ghz, 2 GB RAM, SCSI disks, etc.). There is nothing special about the import process, just a straight data copy (no transformations or other processing logic involved). I could drop the indexes beforehand, but I would have to recreate them afterwards, so I don't know that the net gain would be all that great.

Any suggestions would be great. Would dropping indexes help much? How about disabling transaction logs for the import process? Any thing else I should look at?

Thanks a ton!

Mike

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

How many indexes do you have on that table??

What is the databases recovery mode?

Mike Klostermeyer SSC Enthusiast Points: 194 More actions · Answer 4

I have 3 indexes on the table. I'll have to check on the database recovery mode. Thanks.

Mike

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

How large is the clustered index (columns definition)?

Mike Klostermeyer SSC Enthusiast Points: 194 More actions · Answer 6

Interesting. I was using the SQL Server ODBC connection because I was executing the package remotely. When I changed it to OLE, the process immediately went from several minutes to 10-12 seconds. Anyone else notice this? Does this indicate any configuration problem, or is this to be expected?

Mike

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Sorry but not idea... I'll leave that one for other dbas.