temp tables or virtual tables, which is faster?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

temp tables or virtual tables, which is faster?

Sam Carleton-2
I am working on a web based kiosk system that displays images to 1 to
50, maybe 100 viewing stations.

Currently I am using the file system (Windows) to store the images and
to do lookups on the images.  At one point I need to do a join between
the images and a table (containing details about some of the images,
such as which ones are selected).  Currently the code opens a
read/write connection to the database, creates a temp table, populates
the temp table with all the files (upwards of 1000), and than uses the
temp table to do a join against the table of selected images.

I have now learned about the concept of virtual tables.  Am I better
of with my current approach because I can index the files in the temp
table, or would I be better off using a virtual table to scan the hard
drive for the images?

Also, since the only writing I am doing is to a temp table, can I
connect to the SQLite database with read only?

Sam
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: temp tables or virtual tables, which is faster?

Bugzilla from gnurou@gmail.com
> I have now learned about the concept of virtual tables.  Am I better
> of with my current approach because I can index the files in the temp
> table, or would I be better off using a virtual table to scan the hard
> drive for the images?

Depends on how often you must reindex and how critical it is for you
to be up-to-date. A virtual table will ensure you do not have any
synchronization problem. On the other hand, if your temp table is
indexed, you may expect it to be faster. You do not tell much about
your join conditions.

> Also, since the only writing I am doing is to a temp table, can I
> connect to the SQLite database with read only?

When I need to be such things, I just set my database file to be
read-only at OS level. :)

Alex.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: temp tables or virtual tables, which is faster?

Sam Carleton
On Tue, Jun 9, 2009 at 10:17 PM, Alexandre Courbot <[hidden email]> wrote:

>
> > I have now learned about the concept of virtual tables.  Am I better
> > of with my current approach because I can index the files in the temp
> > table, or would I be better off using a virtual table to scan the hard
> > drive for the images?
>
> Depends on how often you must reindex and how critical it is for you
> to be up-to-date. A virtual table will ensure you do not have any
> synchronization problem. On the other hand, if your temp table is
> indexed, you may expect it to be faster. You do not tell much about
> your join conditions.

Ok, here is what I am doing right now.  The idea is to return all the
images in the folder to the frontend and indicate which ones the
current customer has in his/her favorite's.

CREATE TEMP TABLE Files( ImageId CHAR(260) NOT NULL, DisplayName
CHAR(260) NOT NULL, UNIQUE (ImageId))

INSERT INTO Files (ImageId, DisplayName) VALUES (?1, ?2)

SELECT Files.ImageId, Favorite.Selected
  FROM Files LEFT OUTER JOIN Favorite
       ON Files.ImageId = Favorite.ImageId AND
             Favorite.FolderId = ?1 AND Favorite.CustomerId = ?2
ORDER BY Files.DisplayName, Files.ImageId

The DisplayName is simply a matter of trimming off the front and/or
end of the full filename.

> > Also, since the only writing I am doing is to a temp table, can I
> > connect to the SQLite database with read only?
>
> When I need to be such things, I just set my database file to be
> read-only at OS level. :)

Well, the database in general is read/write, such as when a customer
logs in and out and when customers select images.

Sam
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: temp tables or virtual tables, which is faster?

Bugzilla from gnurou@gmail.com
> Ok, here is what I am doing right now.  The idea is to return all the
> images in the folder to the frontend and indicate which ones the
> current customer has in his/her favorite's.

If your images do not change, I guess you would get good performances
by indexing ImageId and using the temp table. As far as I understand,
the virtual table would work as well, and would save you some memory,
but you have to program it. So in my opinion both options are correct.

Alex.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: temp tables or virtual tables, which is faster?

LincolnBurrows
This post has NOT been accepted by the mailing list yet.
In reply to this post by Sam Carleton-2