> the issue of doing the string/list compare/search is that i can get
> everything from the db with one call... i can then iterate through memory
> for each of my row information that i'm searching to see if it exists in the
> db...
>
> memory searches should be faster than the network overhead, and the
> associated multiple db calls...
[trimming extra stuff, and converting from top-post reply to
in-line reply format]
Yes, memory searches are better/faster than the network overhead
of searching. However, letting the DB do the work and only
return the rows of interest is even better/faster.
I presume you're shipping off a SQL statement something like
SELECT * FROM tblFoo
However, SQL allows you to modify your query with a variety of
things (like Python, SQL is a very powerful and flexible language
that most folks don't tap to its fullest extent). Thus, you can
do something like
SELECT *
FROM tblFoo
WHERE
(fieldFoo = 'foovalue1' AND
fieldBar = 'barvalue1') OR
(fieldFoo = 'foovalue2' AND
fieldBar = 'barvalue2')
By crafting a SQL statement that returns just the records you
want, you can solve the whole problem--on top of which, the
entire table isn't transmitted to your application--just the
pertinent records.
Do be sure to escape items correctly when dynamically building
SQL. Whichever DB toolkit you're using should have an
appropriate escape() sort of function that allows you to pass it
in a string to escape. Thus, you could do something like
foobarpairs = [('foovalue1', 'barvalue1'), ('foovalue2',
'barvalue2')]
sql_stmt = "SELECT * FROM tblFoo"
clauses = " OR ".join(["fieldFoo = '%s' AND fieldBar = '%s'" % (
escape(foo), escape(bar)) for foo,bar in foobarpairs])
if clauses:
sql_stmt += ' WHERE %s' % clauses
Modify accordingly.
Once run, it should just return the rows of interest...easy on
the DB (it should be designed to do such crazy stuff quickly with
hashes and indexes and other good stuff), easy on the network
(you're only sending a handful of records, not the whole table),
and easier on your Python app (no need to post-process the
result-set).
-tkc
--
http://mail.python.org/mailman/listinfo/python-list
