My SQL, circular, inefficient ... or neither?

[Anders]

Can someone have a look at this SQL and tell me if it is circular or inefficient? When I run it without restricting it one person it seems to run forever. This could just be the volume if there are no obvious errors in my SQL.

Table “Place” contains a list of locations with all their details including who is responsible for visiting that location and the last date that it was visited on. However in some instances this date is incorrect and does not match up with the data in the “Visits” table.

Table “Visits” lists all the visits to the locations, including a location reference number and the date of that visit.

SELECT s.user_num, s.place_ref
FROM
 places s
WHERE
 s.last_visit_date <> (SELECT 
                        MAX(v.visit_date)
                       FROM
                        visits v
                       WHERE
                        v.place_ref = s.place_ref)
--AND
-- s.user_num = '000743'
ORDER BY
 s.user_num, s.place_ref

[Iain]

You need to give an indication of the number of rows in visits and places and also what indexes you have applied. An index on s.place_ref is certainly essential

[malfunction]

I'm not sure I understand what you're trying to do here...

Why are you using a sub select? What data are you trying to bring back? If it's just a list of recent visits across all places why don't you just use a cut off date (e.g. "current date - interval 3 month"... Actual syntax may vary - that should work in MySQL at least)

[Anders]

Thanks for your replies.

Az:
Sigh, this is the problem with trying to work with incomplete information. I do not know which entities are indexed. Is there a command I can use to find this out? I am using SQLPlus and connected to: Oracle7 Server Release 7.3.4.3.0 - Production, PL/SQL Release 2.3.4.3.0 – Production.

When restricted to the locations I am interested in there are around 29 000, each with numerous visits so it is a lot of data.

When you say, “An index on s.place_ref is certainly essential” do you mean essential for performance or essential for the query to actually work?

I restricted it further based on the user_num. This restricts it down to around 7000 locations and I start getting rows returned within a few minutes. Where as with out this restriction I have yet to get any returns after 5 hours of waiting. Unfortunately the data linking the user_num within the database has turned out to be incomplete. I am left wondering if not restricting on the user_num some howmakes the query infinite.

Malfunction:
I am trying to pull out which site records that have an incorrect last_visit_date. So I find the maximum visit date grouping by site_ref and then check which do not match.

[Iain]

The stupid thing is I read your title of "My SQL" and read MySQL. When I came back it occurred to me that subselects dont work in MySQL so I was about to query whether you were using a beta version. Must remember to read more carefully in future

The point of my ramble is that I don't know much about Oracle so I can't help I'm afraid. Sorry

[Anders]

Oops, I had not thought of that possible confusion. I thought SQL was supposed to be similar across platforms. Can you look at the logic of the SQL to see if you think there would be the possibility of it infinite looping?

[Iain]

There's no infinite loop, it is just down to 2 full table scans and the MAX operation. I'm not a DBA though, it looks like you need one. Surely Oracle has dev support where you can get the query to tell you what indexes are applied ?

I would also recommend Toad as a useful database interrogation tool
http://www.quest.com/

[malfunction]

One thing for performance tuning is to simply use the explain command.

It will tell you which indexes (indicies if you must) the query is going to use and how many rows it will have to search in each index / table to fulfil the query. For what it's worth though...

Also... Does this work:

SELECT places.user_num, places.place_ref
FROM
places, visits
WHERE
places.last_visit_date <> MAX(visits.visit_date)

and visits.place_ref = places.place_ref

ORDER BY
s.user_num, s.place_ref

If it does it ought to be MUCH more efficient than a sub select. If you can post the table definitions ("describe places" and "describe visist") and the output from the explain command (just stick explain before the query) I might be able to help some more. If that's 'sensitive' info just PM them to me instead.

[Anders]

No, I already tried that. It says group function is not allowed here.

[malfunction]

Oh sorry... Indeed it SHOULD say that...

Reason being you need to say this before the order by bit:

group by 2

(depends on database server whether or not you'll need 2 (places.place_ref) or both (group by 1, 2)... At a guess I would say that place_ref was your primary key on the places table so group by 2 ought to do it... Try just group by 2 first... Or group by places.place_ref if you prefer (better really but '2' is the lazy way ))

[malfunction]

This is what I mean:

SELECT

places.user_num, places.place_ref

FROM

places, visits

WHERE places.last_visit_date <> MAX(visits.visit_date)
AND visits.place_ref = places.place_ref

GROUP BY 2

ORDER BY 1, 2

[GAteKeeper]

Mmm.

The reason that is sooo so is cos U are linking every row in Places to every row in visits. - Thats alot of rows to perform maths on.

Is there any relation between the 2 tables? Your description would lead me to assume that there is a link on location ID/ref. If so something like this would be more appropriate:

SELECT s.user_num, s.place_ref,s.last_visit_date, MAX(v.visit_date)
FROM
places s, visits v
WHERE
s.place_ref = v.place_ref
s.last_visit_date <> MAX(v.visit_date)
Group BY s.user_num, s.place_ref,s.last_visit_date
ORDER BY s.user_num, s.place_ref

Side note: in SQL you need to have everything that isnt an aggregate funciton(sum, count, max, min etc) included in the group by clause.

GAteKeeper

[malfunction]

Side note: in SQL you need to have everything that isnt an aggregate funciton(sum, count, max, min etc) included in the group by clause.

GAteKeeper

Is that the standard? Cuz I know from experience it varies depending on the DBMS used... I thought you only had to make sure the group by clause actually gave it something to group on if you see what I mean

[GAteKeeper]

Is that the standard? Cuz I know from experience it varies depending on the DBMS used... I thought you only had to make sure the group by clause actually gave it something to group on if you see what I mean

As far as i know this is the case. In both Access JET/ODBC oracle and native oracle PL/SQL I have had to ensure that everything that was in the select statement is in the group by.

GAteKeeper

[Anders]

A lot of the time this query needed was due to the sheer volume of data. It seemed to me that restricting the tables as much as possible before the joins produced the quickest results. I cannot be sure as I was also changing other things so I maybe completely wrong. It would really depend on the order in which the lines of the query actually get executed and also on which is more efficient, a join or a restrict. I think it would be restrict. I believe there is a specific piece of software/part of the DB that decides how the query is executed. I cannot remember off the top of my head, I am guessing it is called something like Query Optimizer, lol.

SQL wise, I do not have the code here but I did something like this:

SELECT 
  user_num
  location_ref,
  MVD_table.MVD,
  [...other entities I am interested in]
FROM
 [other tables]
 ( SELECT
    location_ref,
    MAX(visit_date) AS MVD 
   FROM
    visits
   WHERE 
    [restrict visits to only relevant visits]
   HAVING
    date < 30-SEP-04 ) MVD_table
WHERE
  [various other restricts and joins]
ORDER BY
  user_num, place_ref

What I was trying to do slightly changed. I wanted places that had not been visited since September 30th, although the code is only slightly changed. HAVING allows you to restrict an entity when it has had a function such as MAX() used on it.

I also found that an SQL statement can be put in parenthesis and given a table name and thus accessed like a table. It is necessary give MAX(visit_date) a reference name using "AS" so it can be used else where. My understanding of why this is superior is that the last_visit dates are all found once, as opposed to accessing the tables on each run through.

I am unsure if I could have got away with using MAX(visit_date) AS without the extra select statement MVD_table. At any rate I had to draw a line under it time wise as I believe my code to be alright. I am interested to hear comments on this solution?

GAteKeeper, I did find that everything in a SELECT statement had to be in a GROUP BY clause unless it was included in an aggregate function, e.g. MAX(). It was hard to get my head around exactly what the effects of including so many items in the GROUP BY clause might be.