On Sat, 24 Jan 2009 17:28:12 -0800 (PST), Derek Developer
<derekdevelo...@yahoo.com> wrote in General Discussion of
SQLite Database <sqlite-users@sqlite.org>:
>Yes this is the first SELECT Querie that returns a result set.
>- The Client app launches and opens a connection,
>
>- A TRANSACTION is begun
Do you also COMMIT when done?
>- Multiple UPSERTS are performed if data is
> available ( about 5k rows of:
>
>REPLACE INTO MyTable ( RowID, UpDtUNIX, Zip, cCode, RecID,
>jDate, Blob1, Blob2, Blob3) VALUES ( ?,?,?,?,?,?,?,?,? )
>prepare()
>bind()
>step()
>reset()
>finalize()
If the replace statement is the same every time, you only
have to prepare it once. As drh said, to be able to help you
more, we need the schema and more detailed code.
prepare()
begin()
while data_available{
bind()
step()
reset()
[ clear_bindings() ]
}
commit()
>- A querie is executed to retrieve a result set
> from the table of 40k records:
>
> SELECT RowID, Zip, RecID, Blob1, Blob2 FROM MyTable
> WHERE RecID=1 OR RecID=4 OR RecID=5
> AND jDate > MinJulianDate AND jDate < MaxJulianDat
> ORDER BY cCode
>
>RowID is the Primary Key and there is an
>Index for jDate but not RecID or cCode.
If RecID is a uniuqe integer anyway, it should be used as
the first, rowid, column, in other words, there is no need
for a separate RowID:
CREATE TABLE MyTable (
RecID INTEGER PRIMARY KEY,
UpDtUNIX ...,
...
Blob1 ...,
Blob2 ...,
Blob3 ...
);
RecID becomes an alias for ROWID, and you get the index for
free. This will speed up your SELECT above considerably.
Make sure to define the Blobs as the last columns, in order
of increasing expected size. If that doesn't help enough, it
is better to store the blobs in separate tables, using a
foreign key relationship with MyTable. If the blobs are
often queried separately, use one table per blob.
Also, don't use the default page_size. Blobs performance is
much better with large pages.
>Now I suspect your (very valid) first suggestion will
>be to add an index for these columns, then the first
>querie will not have to do all the processing which
>I assume is something like reading the entire database
>and making a temporary index for the other columns
>that has a shelf life of the connection hande?)
You can see what it does with
EXPLAIN SELECT ....
>Unfortunatly the design spec is VERy focused upon the
>UPSERT phase of the app. This has to meet some
>requirements that have intially dictated the reduction
>in the number of keys.
>
>While it would make sense to discuss ways to possibly
>improve that, the real point is that no one cares that
>the first Querie takes so long as long as there some
Do you mean the REPLACE or the SELECT ?
>progress indication (and I dont mean a endlessly
>repeating progress bar)
>So I am wondering if there is a way for SQLite to report progress?
The progress indicator of both REPLACE and SELECT can be
driven by the while { step() } loops in your program.
A progress callback is not of much use here, except for the
first step(), because the first step() returns after any
intermediate tables have been built.
To get a % progress indicator you need a more or less
accurate estimate of what 100% is. You could gather some
statistics and keep that in a table, and/or assume worst
case every time.
>The other scenario where this would make sense would be
>when there are many columns and a user is given the
>ability to design his own Querie that may include
>columns that are not indexed.
Exact progress indicators are hard. It's better to optimize
the schema and selects and not need progress indicators at
all. With proper design, 40k rows and no joins, most selects
should be really fast.
HTH
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
