Re: Re: Re: many to many join on text files

assuming SQLite supports it, your inserts could be greatly sped up by either using extended insert syntax like "...VALUES (a,b,c),(d,e,f),(g,h,i)" or using some kind of raw data/csv import utility. the more data you can fit per query the faster it will run.

perl -e'$_="nwdd\x7F^n\x7Flm{{llql0}qs\x14";s/./chr(ord$&^30)/ge;print'

Comment on Re: Re: Re: many to many join on text files

Replies are listed 'Best First'.
Re: Re: Re: Re: many to many join on text files by davido (Cardinal) on Apr 15, 2004 at 18:44 UTC
As Castaway pointed out, there is a COPY function incorporated within the SQL that SQLite understands. That should also be considerably faster, and if I were planning on converting a text file again, especially one of greater size than what I dealt with last time, I'd use that. The biggest problem with COPY (that I see) is that in order to get the autoincrementing first column, you have to preprocess the flat file by prepending a \N character to the beginning of each record. Of course that can be accomplished by a one-liner, but it would be nice if the COPY feature of SQLite's SQL provided for automatic padding and such. Dave	[reply]

Replies are listed 'Best First'.

Re: Re: Re: Re: many to many join on text files
by davido (Cardinal) on Apr 15, 2004 at 18:44 UTC

Castaway

COPY

The biggest problem with COPY (that I see) is that in order to get the autoincrementing first column, you have to preprocess the flat file by prepending a \N character to the beginning of each record. Of course that can be accomplished by a one-liner, but it would be nice if the COPY feature of SQLite's SQL provided for automatic padding and such.

Dave

[reply]