Re: View performance problem

Thanks, Sandra, for asking a fun question. Most of the SQL questions here are kind of boring, but this is interesting. I hope I understand your table schema well enough. I assume you have two tables, EMPLOYEE and APPOINTMENT.  They are joined by some common key, say 'person'.

The problem with the DISTINCT qualifier is that it forces a sort to occur. Nasty business there. You apparently want to get back the first APPOINTMENT row associated with the EMPLOYEE, so try something like:

SELECT person, etc from EMPLOYEE, APPOINTMENT

	WHERE EXISTS
	(SELECT employee, etc FROM APPOINTMENT
		WHERE APPOINTMENT.person = EMPLOYEE.person)
	AND EMPLOYEE.person = :person_number;

Or, words to that effect. Sorry, but my off-the-top-of-my-head syntax is a bit weak on a Monday morning. The main thing is the WHERE EXISTS predicate. It will return when the first row is found where there is a match on the 'person', or whatever the common, joining key is between the two tables. Presumably, your tables are normalized and you have a nice, tidy foreign key relationship between EMPLOYEE and APPOINTMENT. If you are getting one employee, though, 40 secs seems a bit high if your index is cleanly defined, even on a huge table.

Now, if you really need to get a particular APPOINTMENT row, with a MAX value somewhere, you may have to plow through all of the subquery (appointment) rows. Make sure, then, that the index is defined to include the column for the MAX function. The index for the APPOINTMENT table would include the person (or foreign key column, whatever the name) and at least the column you are selecting on (with the MAX function). Hopefully, this is not the case.

Hope this helps a bit.

Roger

Sandra M Stock <sstock_at_cs.buffalo.edu> wrote in article <5bdpe8$35e_at_prometheus.acsu.buffalo.edu>...
> We are running into a performance problem with a view, and I wonder if
> anyone has any ideas to improve response time. The problem is:
>
> We have an EMPLOYEE view that needs to be joined to an APPOINTMENT
> view. For each record in EMPLOYEE, there may be many records in
> APPOINTMENT. However, we only want one record output for each distinct
> employee (regardless of how many records exist in APPOINTMENT).
Received on Mon Jan 20 1997 - 00:00:00 CST