On Wed, Apr 2, 2008 at 11:59 AM, Evans, Mark (Tandem) <[EMAIL PROTECTED]> wrote:
> 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. I was under the impression that the very act of sweeping the cursor forward via xNext() means that the previous row is no longer needed, therefore the VT can safely deallocate any resources associated with the previous row. I would think that a VT need not ever end up with any old/stale rows in memory, unless of course its particular implementation called for it (e.g. caching old rows). -- Mike > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
