-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
That depends on how you write the queries, how busy the server is, how much information needs to be shuttled between the client and the server, and various other factors.
Also, an ad hoc query against an SQL server needs to be parse, planned, and executed by the server each time it is sent. A complex query may be better handled as a stored procedure, so that it can be pre-planned; this will cut down on execution time later on. If you are dealing with a large amount of information, processing it on the server may cut down on time spent transmitting info across a slower network which will later be eliminated anyway. Additionally, depending on the operations being performed, the server may be able to take advantage of indexes, etc. that the client will not have access to.
Now as far as using the SQL server to maintain lists, if you can store each entry as a separate row in a table, for example (based on PostgreSQL and untested; may or may not be the most efficient queries in some cases):
CREATE TABLE myLists
(
listID INTEGER NOT NULL,
listValue TEXT
);CREATE INDEX ndxMyLists ON myLists (listID, listValue);
Now to retrieve a list in sorted order by value:
SELECT listValue FROM myLists WHERE (listID = 1) ORDER BY listValue;
To perform an intersection operation between two lists:
SELECT DISTINCT listValue FROM myLists WHERE (listID = 1) AND (listValue IN (SELECT listValue FROM myLists WHERE (listID = 2))))
To perform a union operation between two lists:
(SELECT listValue FROM myLists WHERE (listID = 1)) UNION (SELECT listValue FROM myLists WHERE (listID = 2))
**or**
SELECT DISTINCT listValue FROM myLists WHERE ((listID = 1) OR (listID = 2))
To perform a set difference:
SELECT listValue FROM myLists WHERE (listID = 1) AND NOT (listValue IN (SELECT listValue FROM myLists WHERE (listID = 2)))
Note that this set difference is only different from my suggestion for intersection by a single NOT operator...
I missed the original post, what else did you need to be able to do?
BTW, one of the issues with using mySQL like this is its lack of support for stored procedures, or have they added this capability yet? Even pure SQL stored procedures would be fine for most of this stuff...
On Apr 4, 2005, at 4:11 PM, jbv wrote:
I like this one, Ken. In general, I'd reconsider the overall design. Specifically, I'd think about queries that do what you want. If the set has to be a single value, then I'd consider a blob that represents a bit set as the db representation.
well, I don't know... I've been heavily using Rev cgi and mySQL during the past few months, and I found out that extracting raw data from the db and processing them (sorting, comparing...) in Transcript is most of the time much faster than writing sophisticated SQL code... JB
_______________________________________________ use-revolution mailing list [email protected] http://lists.runrev.com/mailman/listinfo/use-revolution
- ----------------------------------------------------------- Frank D. Engel, Jr. <[EMAIL PROTECTED]>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)
iD8DBQFCUaYR7aqtWrR9cZoRAviWAJ9SzSDGdg3JJNPdmCoOka8RVk7otwCfdB1I 5YwzbHHISddnuNB20nd3fR4= =FSMG -----END PGP SIGNATURE-----
___________________________________________________________ $0 Web Hosting with up to 200MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
_______________________________________________ use-revolution mailing list [email protected] http://lists.runrev.com/mailman/listinfo/use-revolution
