> -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Simon Slavin > Sent: Wednesday, May 17, 2017 10:05 AM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Bulk load strategy
> I’m questioning the point of AdAttribute_idx_0 and AdAttribute_idx_1. It’s > rare to usefully index values without types, for instance. Do you actually > have a SELECT which uses that one ? Wouldn’t it be more efficient to do > > CREATE INDEX AdAttribute_idx_tv ON AdAttribute ( > Type, Value > ); > > ? That’s assuming that even that one gets used at all, since it seems far > more > likely that you’d use (AdObjectId,Type). There were some instances where I need to search all values regardless of the type. However, I see that approach could make changes I don't intend. The use case was a moddn, however you may be right and I should constrain that to types of "member" and "memberOf". Otherwise I could modify a free form text field for which I have no authority over. So I have one query which if I expect if I encounter will be painful: UPDATE AdAttribute SET Value = @NewValue WHERE Type = @Type AND Value = @Value; I may pass member or memberOf to @type, without the indexes this will be abysmal. I don't expect to see this often and I don't have data that requires it in my large data set. However good catch. > The sub-select is killing you. Since it’s identical for all the INSERT > commands I > suggest that you do that first, and keep the results in memory as a lookup > table, or a hashed table, or a dictionary, or whatever your preferred language > does. You can look up those values in RAM far more quickly than SQLite can > do the required file handling. It seems I provided some bad numbers, I passed -w instead of -l to `wc` when providing figures, I have ~160k records. The application processed at roughly constant speed and finished quickly. Brilliant Simon and thank you everyone for the guidance. jlc _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users