[yunqa.de] Re: Subscription confirmation for 'yunqa'
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Fri, 10 May 2013 12:50:20 +0200
On 09.05.2013 21:39, Jacque Lafitte wrote:
> Here is the link to download the sample database demonstrating the
> problem described in my earlier e-mails
> (http://www.erosoftware.com/Download/TestDatabase.zip). Please keep this
> sample database confidential.
>
> Attached you can also find a sample project demonstarting the issue.
Your query is slow because your table contains BLOB columns intermixed with the
other columns. Access to columns following BLOB columns is slow, hence your
query is slow.
Try these 2 queries:
SELECT "RatingID" FROM Film;
This executes fast. There is no BLOB column before the "RatingID" column.
SELECT "FilmStatusSeen" FROM Film;
This is slower because the "FilmPicture" BLOB column is just before the
"FilmStatusSeen" column.
SQLite has to read the whole "FilmPicture" BLOB before it can get to the
"FilmStatusSeen" integer.
Solution 1:
Store your blobs in a separate table. This is the preferred solution. It is
recommended that large BLOBs be stored in a separate table with only an INTEGER
PRIMARY KEY.
Proof: Move all non-blob columns into a separate table and rerun your queries:
CREATE TABLE Film2 AS SELECT
"FilmID", "FilmIDGUI" , "FilmBarcode" , "FilmUniqueKey" , "FilmSynchronised",
"FilmName", "FilmDescription", "FilmRanking", "FilmProductionYear",
"FilmProductionDate", "FilmReleaseDate", "FilmPrice", "FilmRunningTime",
"FilmMediaCount", "FilmMinimumAge", "FilmIMDbNumber", "FilmTrailer",
"FilmFile", "GenreID", "CountryID", "FormatID", "TVSystemID", "RatingID",
"FilmStatusSeen", "FilmFavorite", "FilmPublished", "FilmMasterFilm",
"FilmStatusError", "FilmStatusFixed", "FilmStatusIMDb", "FilmStatusComment",
"FilmPictureBiDiMode", "FilmDateSeen", "FilmDateCreated", "FilmDateChanged"
FROM Film;
For me, the DISTINCT query from your demo executes almost instantaneously.
Solution 2:
Make sure the BLOB fields are listed last in CREATE TABLE. This is not as good
as Solution 1, but also speeds up your query.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
