It could also be a flawed DB Schema. Has it been normalized effectively? -----Original Message----- From: Dave Phipps [mailto:[EMAIL PROTECTED]] Sent: 16 January 2003 12:42 To: ColdFusion User group Subject: [ cf-dev ] Confusing SQL
Hi, I have three queries that build a navigation system which is running really slowly. This is because I haven't been able to combine the three queries into one and therefore have to do nested loops which results in about 50 queries running for some sections of the site. The three queries are below: ListCircuits: SELECT circuits.circuitid, circuits.circuitname, circuits.contentid, circuits.displayname, content.contentid, content.pagetitle, content.contentdate, content.contentdescription, content.lastupdate, content.app_status, content.lastupdate, groups.groupid, groups.groupname FROM groups, content_group, circuits LEFT JOIN content ON circuits.contentid = content.contentid WHERE content.contentid = content_group.contentid AND groups.groupid = content_group.groupid ORDER BY circuits.circuitid ListMenus SELECT circuits.circuitid, circuits.circuitname, circuits.displayname, menus.menuid, menus.menuname, menus.contentid, submenus.submenuid, content.pagetitle, content.contentdate, content.contentdescription, content.lastupdate, content.app_status, groups.groupid, groups.groupname FROM groups, circuits LEFT JOIN menus ON circuits.circuitid = menus.circuitid LEFT JOIN submenus ON menus.menuid = submenus.menuid LEFT JOIN content ON menus.contentid = content.contentid LEFT JOIN content_group ON content.contentid = content_group.contentid WHERE content_group.groupid = groups.groupid AND circuits.circuitid = #listCircuits.circuitid# AND content.pagetitle != 'The Week Ahead' GROUP BY content.contentid ORDER BY menus.menuid ListContent SELECT submenus.submenuid, submenus.menuid, submenus.submenuname, content.contentid, content.pagetitle, content.contentdate, content.contentdescription, content.lastupdate, content.app_status, content.isDeleted, groups.groupname, groups.groupid FROM submenus LEFT JOIN content ON submenus.submenuid = content.submenuid LEFT JOIN content_group ON content.contentid = content_group.contentid LEFT JOIN groups ON content_group.groupid = groups.groupid WHERE (submenus.menuid = #list_menus.menuid#) ORDER BY submenus.submenuid, content.contentid Can anyone see a way to pool these three queries into one query? The problem I have found is that each table (circuits, menus, submenus) has a contentid (which is the default page for that section). Any help on this? Cheers Dave -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
