> -----Original Message-----
> From: sqlite-users [mailto:[email protected]] On
> Behalf Of Simon Slavin
> Sent: Wednesday, May 17, 2017 10:05 AM
> To: SQLite mailing list <[email protected]>
> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
