Re:x3 It works, it's fast, and it's scalable!

My main objective to this thread was that I was looking for a way to populate CLS_TMP_$$ very quickly. I didn't want to do something silly like this:
foreach (@files) { # INSERT INTO CLS_TMP_$$ values('$_') }
[download]
because each query would then do a COMMIT, making it VERY slow.

Are we still talking about DBI here? It so, why not just create a db handle with {Autocommit => 0} so that it doesn't perform a commit on each insertion. Insert the x thousand records, and do a single commit at the end. You might also want to drop the indexes before the insert, and then create the index after all records have been inserted.

Of course, I'm sure you know all of this already.

`g r i n d e r print@_{sort keys %_},$/if%_=split//,'= & *a?b:e\f/h^h!j+n,o@o;r$s-t%t#u';`

Comment on Re:x3 It works, it's fast, and it's scalable! Download Code

Replies are listed 'Best First'.
Re: Re:x3 It works, it's fast, and it's scalable! by joealba (Hermit) on Jan 25, 2002 at 20:00 UTC
I thought about that after I posted my response... But I still think that the overhead for all those transactions (even without the commit) would still be higher than a nice, fast bulk load. I'll benchmark it and post the results here -- after my coffee break. :)	[reply]
Re: Re: Re:x3 It works, it's fast, and it's scalable! by pmas (Hermit) on May 28, 2002 at 19:36 UTC
I am really curious - how were benchmarks? I am solving almost exactly same problem. Thank you for asking it. Isn't PM great? pmas To make errors is human. But to make million errors per second, you need a computer.	[reply]
Re: Re: Re: Re:x3 It works, it's fast, and it's scalable! by joealba (Hermit) on May 28, 2002 at 20:51 UTC
Well, some huge (and involuntary) changes have happened since I posted this question... So I can't run benchmarks now to give you real data anymore. But I can tell you that it was a great solution at the time! We beat the heck out of it for 2 months and never had any problems. Our biggest searches ran no more than 3 seconds, with N<1000 searches running at <1 sec. But now, I'm in a MySQL environment instead of Oracle. MySQL allows you to input multiple rows in one query as a list. Eliminating the file creation and external executable call to sqlloader can only speed things up more. Recently, a few related posts here popped up which had some other good ideas. Super Search is choking on me, so I can't find them right now. If you're still seeking answers, toss a fresh post at SoPW with some specific questions so we can beat this topic around a little again. :)	[reply]