Alpha Video Training
Results 1 to 5 of 5

Thread: Caching or speeding up SQL

  1. #1
    Member
    Real Name
    Roelof
    Join Date
    Jul 2008
    Location
    Seattle, WA
    Posts
    763

    Default Caching or speeding up SQL

    This is my SQL statement, but even to find 1 machine it takes to long. So people are complaining about it. This is the statement I use in my grid. Is there a way to speed this up???

    I am displaying all of the records.


    SELECT FIRST 50 MachineWithDeleted.MachineID, MachineWithDeleted.Name, MachineWithDeleted.AssetNumber, MachineWithDeleted.Manufacturer, MachineWithDeleted.Model, MachineWithDeleted.TotalRAM, MachineWithDeleted.Status, MachineWithDeleted.Type, MachineWithDeleted.BuildingId, MachineWithDeleted.RoomId, MachineWithDeleted.RackId, MachineWithDeleted.Deleted, MachineWithDeleted.LastHeartbeat, Building.BuildingName, Room.RoomName, Rack.RackName, MachineWithDeleted.Deleted AS Deleted1, Pool.PoolName, Pool.PoolPath, MachineProperty.PropertyName, MachineProperty.PropertyValue
    FROM (MachineWithDeleted MachineWithDeleted
    LEFT OUTER JOIN Building Building
    ON (Building.BuildingId = MachineWithDeleted.BuildingId)
    LEFT OUTER JOIN Room Room
    ON (Room.RoomId = MachineWithDeleted.RoomId)
    LEFT OUTER JOIN Rack Rack
    ON (Rack.RackId = MachineWithDeleted.RackId)
    LEFT OUTER JOIN Pool Pool
    ON (MachineWithDeleted.PoolId = Pool.PoolId)
    LEFT OUTER JOIN MachineProperty MachineProperty
    ON ((MachineProperty.MachineID = MachineWithDeleted.MachineID) AND (propertyname = 'VMHost')))
    ORDER BY MachineWithDeleted.Name, MachineWithDeleted.AssetNumber

  2. #2
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,594

    Default Re: Caching or speeding up SQL

    Since you are joining 6 different tables, it's no wonder it takes a long time. This particular select statement has no where clause, so every record is being joined with every other record. That's a very large result!

    First, I'd check that you have indexes set up for all the join and where fields.

    In MySQL, you can use the "explain select" to check what indexes are being used, and test the use of alternate indexes.

    Pat

  3. #3
    "Certified" Alphaholic NoeticCC's Avatar
    Real Name
    Andrea Gill
    Join Date
    Nov 2007
    Location
    Golcar, West Yorkshire, UK
    Posts
    2,069

    Default Re: Caching or speeding up SQL

    Quote Originally Posted by Pat Bremkamp View Post
    Since you are joining 6 different tables, it's no wonder it takes a long time. This particular select statement has no where clause, so every record is being joined with every other record. That's a very large result!

    First, I'd check that you have indexes set up for all the join and where fields.

    In MySQL, you can use the "explain select" to check what indexes are being used, and test the use of alternate indexes.

    Pat
    The joins are the where statement, this is ANSI join syntax or something like that (which sadly Oracle 8 does not support)...

    It would be much faster if you put this in a view though...

  4. #4
    Member
    Real Name
    Roelof
    Join Date
    Jul 2008
    Location
    Seattle, WA
    Posts
    763

    Default Re: Caching or speeding up SQL

    What do you mean "... in a view"?

  5. #5
    "Certified" Alphaholic NoeticCC's Avatar
    Real Name
    Andrea Gill
    Join Date
    Nov 2007
    Location
    Golcar, West Yorkshire, UK
    Posts
    2,069

    Default Re: Caching or speeding up SQL

    Quote Originally Posted by Atta View Post
    What do you mean "... in a view"?
    If you are just displaying data, a view created in your SQL database is always faster than filtering things at WAS level: http://www.packtpub.com/article/crea...-query-browser

Similar Threads

  1. Speeding Up an Append
    By programsplus in forum Alpha Five Version 9 - Desktop Applications
    Replies: 5
    Last Post: 08-18-2008, 07:49 AM
  2. Speeding up duplicate hunting
    By Ted Giles in forum Alpha Five Version 7
    Replies: 4
    Last Post: 08-20-2007, 12:32 PM
  3. Improving on a beast - Speeding up the process
    By KeithW in forum Alpha Five Version 7
    Replies: 14
    Last Post: 06-14-2006, 10:29 PM
  4. M/S SQL 2000 and "Attach SQL Table"
    By Kevin Benett in forum Alpha Five Version 5
    Replies: 1
    Last Post: 12-11-2003, 04:39 AM
  5. ODBC Access - SQL 6.5 or SQL 2000
    By Dale Kromminga in forum Alpha Five Version 5
    Replies: 8
    Last Post: 10-20-2003, 12:42 PM

Bookmarks

Posting Permissions

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