Make SQL Server faster at manipulating data - turn off transaction logging?

I use SQL Server 2005 as a data store for a lot of data I do analytic work on. This is not a transactional database as I am not hitting it with updates or capturing real time data. I get a few gigs of data from my clients, load them into SQL Server and do a series of manipulations. I then grab bits of this data and pull them into R where I do most of my analysis. I then push a little data into tables in SQL Server and maybe do a join or two.

I have a heck of a time with the logs in SQL Server getting big and I assume it takes some degree of overhead to create these. How can I configure SQL Server so that it runs with little or no logging? If things get corrupt I am happy to just start from the beginning. Any ideas how to make this all faster?

BTW, no need to tell me how to shrink logs, I'm already doing that. But I wish I didn't have to make the logs in the first place. I'm only using the DB to house the data because its too big to fit into memory in R.

Should I be using a more simple DB than Sql Server? Feel free to tell me I am killing an ant with a sledgehammer. But please recommend a more appropriate sized hammer. :)

I moved to SQL Server because I was regularly banging my head on the 2GB limit in Access. I almost asked this question in the form of "how can I get SQL Server to act more like Access" but I was afraid I was going to get a lot of BS about how Access sucks, yada yada. I just need a good data store!

JD Long 2009-02-21 01:58:06

Can you split your data up into several Access database files? The linked table semantics in Access would make it very easy and logically sound to setup a main file that refers to several child Access data files.

James 2009-02-21 20:15:31

Good idea, but accommodating multiple Access tables would be a total kludge that would hamper analysis. Having this in SQL Server also lets me push the expensive queries to a more powerful server. Access would require me to do those queries on the client machine.

JD Long 2009-02-25 00:47:08

I was re-reading these responses and the mention of batches caught my eye. Can you give me more insight into what you mean by doing things in batches? If I do a long script with 30 steps is that different than running 30 scripts? Thanks for your help.

JD Long 2009-03-11 13:21:05

by batches i mean for example if you have to update/delete 50.000 rows do that in batches of 1000. and each batch in it's own transaction. you can do this with a while loop.for inserts use bulk insert capabilities.

Mladen Prajdic 2009-03-12 10:32:54

in the real world, at least in Oracle (ouch!), it's always faster to process your data as a complete set, not split it up into smaller bites. COMMITs take work, as do beginning and ending transactions. Another piece of advice is that the fastest way to update ALL (or most) of the rows in a table is to create a new table.

Neil Kodner 2009-10-19 15:57:17

ansaurus

tags:

views:

answers:

Make SQL Server faster at manipulating data - turn off transaction logging?

related questions