Thnk you for your thoughts. I have learned a few things as always.
There is a tenency these days to question the question these days
as most developers could benefit from a better design. It tends
to divert the question in directions that were not intended
and requires defending the underlying structure that gave rise
to the question.
In this case a GREAT deal of effort went into this particular schema
and the tip of the iceberg is the SQLite portion. Many things
have been dictated by the submerged layers and layers of server
database structure.
I have given a fair amount of detail about the table structure, its basically
a few BIG INTs and some Blobs. I gave this detail so that you can see that
ten seconds for the Prepare() is significant compared to the
size and structure of the database. This is not a criticism
of SQLite. With the proper keys this step would be instant.
But here is a real world case where progress indication would
help.
>Do you also COMMIT when done?
Oh yes
>If the replace statement is the same every time, you only
have to prepare it once.
Yes we use the reset()
>If RecID is a uniuqe integer anyway...
yes
>RecID becomes an alias for ROWID, and you get the index for
free. This will speed up your SELECT above considerably.
yup
>Make sure to define the Blobs as the last columns,
done that
in order of increasing expected size.
hmmm they are all around the same size but one is slightly larger.
I will have to play with that thanks
>it is better to store the blobs in separate tables,
no can do
>Blobs performance is much better with large pages.
forget to check that one thx
>You can see what it does with EXPLAIN SELECT ....
Yes but that doesn't really get me where I want to go
>Do you mean the REPLACE or the SELECT ?
the first Querie after the UPSERT phase is a SELECT which takes a while
>The progress indicator of both REPLACE and SELECT can be
driven by the while { step() } loops in your program.
As explained that zips along no prob. I need progress for
Prepare() phase
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...
>Exact progress indicators are hard.
Hence the post
> It's better to optimize the schema
Well I really just need a progress indicator
but I am allways interested to hear ways to increase performance
>With proper design, 40k rows and no joins, most selects
should be really fast.
With all the relevant indexs, you are right, but this happens to be one
case where that is not possible as I explained, there exists a general
case that would benefit from some kind of progress indication.
Now it doesnt have to be a smooth progress bar, it could just be a notification
from the completion of each step in the process, perhaps a code anything
that could be reported.
Users hav NO PATIENCE. They want to see SOMETHING... anything real will do.
They hate cute animations that are eye candy disconeected from
the underlying process... at least thats what I am told by people
that make a LOT more money than I do at this stuff...
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
