Hi Ben, You said: "You will notice that for certain queries, xBestIndex/xFilter does not get used. That means that the SQLite engine is going to have to walk through your entire table, evaluating the conditions on each field."
I am not aware of this behavior. Could you cite an example? I thought xOpen and xFilter always kick off a walk over a range of virtual table rows. Prerequisite of xOpen is index id returned by xBestIndex. So you can't just walk a virtual table without xBestIndex having been called, unless I missed a boat somewhere It is true that xOpen/xFilter/xClose can be called multiple times for an index returned by xBestIndex. So if you allocate any objects in xBestIndex, you can't let go of them in xClose. Memory leak is unavoidable unless you modify the virtual table interface as I had to do to pass additional information in the calls (prepared statement handle). When I have more time, I'd like to suggest changes to the virtual table call interface to facilitate resource management in the VT module. This doesn't come up with the FTS VT modules because they seem to always return a small index to a static structure. One other consideration: If the query or update has to walk a large range of rows, there's no way for the core to tell the VTM that it's done accessing a given row as it sweeps the cursor forward. You can end up with a huge number of virtual table rows in memory. Regards, Mark > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Ben Harper > Sent: Tuesday, April 01, 2008 4:53 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Virtual table used to query big > external database > > The only limitation imposed by SQL that I can think of would > be inefficiencies in its query plan builder. That is the part > that figures out how to use the indexes available in the > database in order to execute the SQL query most efficiently. > So it really depends on what type of SQL queries you are > going to be running against this huge DB. > The dangers are easy to evaluate: > Create a quick-and-dirty dummy virtual table mechanism, and > respond to the xBestIndex/xFilter functions. You will notice > that for certain queries, xBestIndex/xFilter does not get > used. That means that the SQLite engine is going to have to > walk through your entire table, evaluating the conditions on > each field. This is obviously what you wish to avoid. As an > example, I noticed briefly (I did not investigate > thoroughly) that having an OR condition in a query would > prevent the indexes from being used. That was some time ago, > and it was before the rewrite of the SQL VM, so I don't know > if that still applies. You'll have to investigate your > potential queries yourself. A simple query such as "WHERE > myvalue > 100" should definitely invoke the use of your own indexes. > > Ben > > > On Tue, Apr 1, 2008 at 10:12 PM, Aladdin Lampé > <[EMAIL PROTECTED]> wrote: > > > > Hi all! > > > > Very often, when people ask this list why they have trouble > managing in sqlite a "big table" (50 million lines or more > than 10 Go), they are told that sqlite is an embedded > database and is not meant to be used for very big databases/tables. > > > > I'm currently in the process of designing a specific, > read-only, sqlite "virtual table" in order to enable sqlite > to access data stored in an external database which is > specially designed to handle very big tables. > > > > My final objective is to be able to easily query a big > external table (stored in another database) through the - > excellent - sqlite interface. > > > > Now I have this terrible doubt: will the existing sqlite > "limitations" for big sqlite tables also apply to my > read-only virtual tables? > > > > Thus... am I currently losing my time developing such a > "virtual table" with this objective in mind? Or is there a > better way to achieve my objective? > > > > Thank you for your help! > > > > _________________________________________________________________ > > Votre contact a choisi Hotmail, l'e-mail ultra sécurisé. > Créez un compte gratuitement ! > > http://www.windowslive.fr/hotmail/default.asp > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
