Hey,
I'm going to give some background on my situation in case anyone can
point out a way around my problem altogether... for the problem
itself, please skip to the bottom of the post. thanks.
I've been having some problems with database performance... Several
threads are constantly attempting INSERTs of new records into a large
table - that is hundreds of thousands of records -large by my account
:-) The table has a VARCHAR field tagged as UNIQUE and inserts are
kind of slow. i'm relying on the INSERT to fail on duplicates, i'm
not performing any checking before i attempt the INSERT (i figured an
additional SELECT would slow things even more - but any advice to the
contrary would be appreciated).
While these back end threads are constantly banging away with their
INSERTS, front end users are hitting the DB with searches. so i end
up with several threads simultaneously attempting very expensive DB
operations. the result is that the front end that users see is
DREADFULLY slow.
My proposed speedup is to modify the behavior of the backend threads
so that they don't constantly attempt INSERTS of single entries, but
build up a hashtable of local entries and at some interval, attempt a
multiple insert something like:
INSERT INTO table (col1,col2,col3 VALUES (x1,y1,z1), (x2,y2,z2)....
While i do expect to take a big hit when these multiple inserts
launch, in the time between, i expect front end performance to be
significantly improved.
The problem with this situation is that the multiple insert may
contain one or more new entries which will cause duplicates on my
table's UNIQUE VARCHAR field. I would like to have duplicates ignored
on insert, but have all newly unique entries be added. Sadly, from
what i've seen, once a duplicate violation for any of the new entries
is found, the call fails, leaving my newly UNIQUE entries NOT ADDED.
Is there any way to get the call to work as i want? Doing many single
row inserts sounds like it'll be much slower (judging from what i've
read) so i'd rather not resort to that.
I don't have a ton of DB experience (which may be obvious) so if i've
overlooked something simple please let me know. If there's a better
way to improve performance other than these multiple inserts, that
would be nice to know about too. Thanks for any insight,suggestions,
etc...
joe.
