Results 1 to 15 of 15

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

  1. #1
    I can't get no sleep
    Join Date
    Jul 2003
    Location
    123 Fake Street, London
    Posts
    811
    Thanks
    35
    Thanked
    3 times in 3 posts

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

    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.

    Code:
    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
    "Keyboard missing - press F3 to continue" Message seen on an Apricot PC.
    "To start press any key. Where's the any key?" Homer Simpson.
    Hexus Trust

  2. #2
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    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

  3. #3
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    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)

  4. #4
    I can't get no sleep
    Join Date
    Jul 2003
    Location
    123 Fake Street, London
    Posts
    811
    Thanks
    35
    Thanked
    3 times in 3 posts
    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.
    "Keyboard missing - press F3 to continue" Message seen on an Apricot PC.
    "To start press any key. Where's the any key?" Homer Simpson.
    Hexus Trust

  5. #5
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    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

  6. #6
    I can't get no sleep
    Join Date
    Jul 2003
    Location
    123 Fake Street, London
    Posts
    811
    Thanks
    35
    Thanked
    3 times in 3 posts
    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?
    "Keyboard missing - press F3 to continue" Message seen on an Apricot PC.
    "To start press any key. Where's the any key?" Homer Simpson.
    Hexus Trust

  7. #7
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    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/

  8. #8
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    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.

  9. #9
    I can't get no sleep
    Join Date
    Jul 2003
    Location
    123 Fake Street, London
    Posts
    811
    Thanks
    35
    Thanked
    3 times in 3 posts
    No, I already tried that. It says group function is not allowed here.
    "Keyboard missing - press F3 to continue" Message seen on an Apricot PC.
    "To start press any key. Where's the any key?" Homer Simpson.
    Hexus Trust

  10. #10
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    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 ))

  11. #11
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    This is what I mean:

    Code:
    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

  12. #12
    Senior Member GAteKeeper's Avatar
    Join Date
    Feb 2004
    Location
    Derbyshire, UK
    Posts
    582
    Thanks
    14
    Thanked
    28 times in 22 posts
    • GAteKeeper's system
      • Motherboard:
      • MSI P67-GD5
      • CPU:
      • Intel i7 2600k
      • Memory:
      • 8Gb Corsair DDR3 1600
      • Storage:
      • ~44TB
      • Graphics card(s):
      • 980Ti
      • PSU:
      • Seasonic S12 600W
      • Case:
      • Lian Li PC-65
      • Operating System:
      • Win10 64bit
      • Monitor(s):
      • Dell U3415W & 2405fpw
      • Internet:
      • 45Mb vDSL
    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

  13. #13
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    Quote Originally Posted by GAteKeeper

    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

  14. #14
    Senior Member GAteKeeper's Avatar
    Join Date
    Feb 2004
    Location
    Derbyshire, UK
    Posts
    582
    Thanks
    14
    Thanked
    28 times in 22 posts
    • GAteKeeper's system
      • Motherboard:
      • MSI P67-GD5
      • CPU:
      • Intel i7 2600k
      • Memory:
      • 8Gb Corsair DDR3 1600
      • Storage:
      • ~44TB
      • Graphics card(s):
      • 980Ti
      • PSU:
      • Seasonic S12 600W
      • Case:
      • Lian Li PC-65
      • Operating System:
      • Win10 64bit
      • Monitor(s):
      • Dell U3415W & 2405fpw
      • Internet:
      • 45Mb vDSL
    Quote Originally Posted by malfunction
    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

  15. #15
    I can't get no sleep
    Join Date
    Jul 2003
    Location
    123 Fake Street, London
    Posts
    811
    Thanks
    35
    Thanked
    3 times in 3 posts
    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:
    Code:
    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.
    Last edited by Anders; 27-11-2004 at 05:13 PM. Reason: Correction in code.
    "Keyboard missing - press F3 to continue" Message seen on an Apricot PC.
    "To start press any key. Where's the any key?" Homer Simpson.
    Hexus Trust

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SQL - Display function
    By Raz316 in forum Software
    Replies: 4
    Last Post: 13-08-2004, 05:23 PM
  2. Oracle SQL
    By Raz316 in forum Software
    Replies: 7
    Last Post: 08-06-2004, 02:20 PM
  3. Learning SQL
    By Lead_Head in forum Software
    Replies: 13
    Last Post: 26-05-2004, 06:54 PM
  4. SQL help
    By Agent in forum Software
    Replies: 23
    Last Post: 11-11-2003, 09:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •