[SQL] Join performance

[Koen Antonissen]

Hi there,

I'm having some problems with the performance on queries including a join.

for example:
SELECT members_data.pwd FROM emails,members_data WHERE emails.email =
'[EMAIL PROTECTED]' AND emails.uin = members_data.uin;

is a lot slower then extracting the join into 2 seperate queries like:

SELECT emails.uin FROM emails WHERE lower(emails.email) = '[EMAIL PROTECTED]';

--and use the result in the next query ($db_result)

SELECT members_data.pwd FROM members_data WHERE members_data.uin =
$db_result;

Now the login routine is MUCH faster ;-) , but performance problems remain
when the the result is more than 1 record (Unlike a unique emailaddress...)

Is there anyone out there who has ideas how to write faster queries
including tablejoins?

I already tried Inner Join, Natural Join and Join On, wich didn't seem
affect the performance in any way...

Kind regards,
Koen Antonissen
[EMAIL PROTECTED]
www.trilab.com

Re: [SQL] reinitialize a sequence?

[Ross J. Reedstrom]

On Mon, Dec 04, 2000 at 03:51:35PM -0800, Dan Lyke wrote:
> Bruno Boettcher writes:
> > is there a simple way to tell all sequences to take the max value +1 of
> > their respective tables? (a bit like the vacuum command?)
> 
> This is completely gross, but what I've done:
> 

Hmm, what I usually do is something like:

SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;


To do that in one fell swoop is not trivial, since there's no easy way to
extract the automatically generated sequence name from the system
tables.

Well, I couldn't resist the challenge, so here's a crufty example, but better
than the perl that was here (I think ;-)

The following psql will generate psql statements to reset all your sequences
to the maximum value. This is for 7.0.2, and depends critically on exactly
how the default value for the 'serial' type is constructed. 


select 'SELECT setval(\'"' || 
substr(adsrc,10,(length(adsrc) - 17)) || 
'"\', max("' || attname || '")) FROM "' || 
relname || '";' 
from pg_class c, 
 pg_attribute a, 
 pg_attrdef d 
where c.oid=d.adrelid and 
  a.attrelid=c.oid and 
  d.adnum=a.attnum and 
  d.adsrc ~ ('nextval\\(\''||relname);

Use this by redirecting output to a file, then reading in that file, as so:

me@mycomputer:~$ psql mydb

mydb=# \t
Showing only tuples.
mydb=# \o sequence_reset.sql
mydb=# select 'SELECT setval(\'"' || substr(adsrc,10,(length(adsrc) - 17)) || '"\', 
max("' || attname || '")) FROM "' || relname || '";' from pg_class c, pg_attribute a, 
pg_attrdef d where c.oid=d.adrelid and a.attrelid=c.oid and d.adnum=a.attnum and 
d.adsrc ~ ('nextval\\(\''||relname);
mydb=# \o
mydb=# \i sequence_reset.sql



You might get some errors for empty tables, since '0' is out of bounds
for sequences.

Ross
-- 
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Using SELECT as DDL/DML statement is wrong (was RE: [SQL] reinitialize a sequence?)

[Edmar Wiggers]

If and when stored procedures are supported, there should be some way to
prevent functions called in a SELECT statement to modify the database
(create, insert, etc.).

It is confusing (and wrong IMHO) to use statements like

SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;
(which is used to reset a sequence)

That should be done with

EXECUTE procedure(tablename_name,sequence_name);
(not sure if execute is the right keyword)

Yours sincerely,

Edmar Wiggers
BRASMAP Information Systems
+55 48 9960 2752

Re: [SQL] reinitialize a sequence?

[Ross J. Reedstrom]

Following up to myself:

There is in fact a bug in 7.0.2 that's been fixed in 7.0.3 which causes
my select below not to work. The name of the sequence is now stored
with double quotes around it, to handle the mixed case names problem,
so the query needs to look like (untested: note added double quote to
last where clause):

select 'SELECT setval(\'"' || 
substr(adsrc,10,(length(adsrc) - 17)) || 
'"\', max("' || attname || '")) FROM "' || 
relname || '";' 
from pg_class c, 
 pg_attribute a, 
 pg_attrdef d 
where c.oid=d.adrelid and 
  a.attrelid=c.oid and 
  d.adnum=a.attnum and 
  d.adsrc ~ ('nextval\\(\'"'||relname);


On Tue, Dec 05, 2000 at 10:10:12AM -0600, Ross J. Reedstrom wrote:
> On Mon, Dec 04, 2000 at 03:51:35PM -0800, Dan Lyke wrote:
> > Bruno Boettcher writes:
> > > is there a simple way to tell all sequences to take the max value +1 of
> > > their respective tables? (a bit like the vacuum command?)
> > 
> > This is completely gross, but what I've done:
> > 
> 
> Hmm, what I usually do is something like:
> 
> SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;
> 
> 

Ross
-- 
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005