Re: Freetds slow on large inserts

You're correct it's not a freetds issue  -  there's no good answer to
your question without a great deal more information, and even so someone
really needs to be there.  But consider these:

1. Easy things first: does the insert trigger an autogrow of the log or
db?  Then change the size of the log and/or db to handle the volume
without triggering an autogrow.  "Autogrow Is Slow" - it's a nice
failsafe but don't rely on it.

2. Can you use bcp instead?  There's a reason it exists - this is it!

3. Do they need to be a single transaction?  There's a lot more rollback
needs to be saved for that.

4. Indices (indexes) - are the enemy of insert.  
	If you have 3 non-clustered indexes - each insert is actually 4
inserts (the original and each non-clustered)
	Every insert runs the risk of page split - making 2 pages from 1
to make room in the middle. 
		With 3 non-clustereds, the chance of a page split is 4x
higher	
	Do you have a clustered index?  Many pages might move in SQL2000
to keep the indexes right.

5. Is the system in use for heavy retrieve at the same time?  You'll get
in each others way to some extent.  That's what its designed to handle,
but depending on the situation, it may not be fast.

What to do depends on what you can live with.  Large inserts go fastest
if you take all the indices off, blow them in, and recreate.  But that
won't be acceptable if folks are doing retrieves at the same time.

Honestly, SQL2005 or 2008 would help - depending.  They do MOST things
much faster and more efficiently, and with a whole lot less contention.
(I'm confused at the moment about which system doesn't even really have
a "clustered" index anymore...I think actually it's Sybase 15 when its
doing datapages locking).

The bottleneck can be in a lot of places.  You may just need faster
systems - you do reach a point where you run out of horsepower and
tuning isn't the answer.  But you can mitigate the effects in various
ways, depending on what you can get away with.   

By the way - Apache has no business telling you how long your query can
run.  But that's web services for you - can't live with them, can't get
away with pretending they don't exist.  Can you tell Apache to mind its
own business - or better yet, do your inserts directly instead of going
through whatever horrors all those layers designed by non-database
people do it?

But seriously - a lot of things are a lot better for 2005 than 2000, and
there's a bunch of performance problems that no longer need to be fixed
in that case.


> -----Original Message-----
> From: Marco Baurdoux [mailto:[email protected]]
> Sent: Tuesday, September 09, 2008 8:10 AM
> To: 'FreeTDS Development Group'
> Subject: [freetds] Freetds slow on large inserts
> 
> We are connecting to a SQL2000 DB via freetds 0.82 with tds-ver=8 via
PHP
> 4.4.9 (Yes I know we should upgrade :-)). That works fine. Selects
work
> fine.
> 
> However when we try to perform a "large" insert query we mostly reach
the
> apache Time-out. By large insert I mean creating around 3'000 records
in one
> transaction.
> 
> I've been trying to find some other posts about this issue but haven't
been
> able to find a satisfying solution. The only thing I've found is that
it is
> not related to Freetds. OK fine, however in this configuration this is
the
> only point where I believe someone can provide me with some help and
believe
> me I need some help.
> 
> So anyone?
> 
> Thanks in advance
> 
> Marco Baurdoux
> 
> 
> 
> 
> 
>