From 10bd4045596bc1daac97ce9d9ad706f696f8e4ab Mon Sep 17 00:00:00 2001
From: ivan <ivan>
Date: Sun, 29 Mar 2009 11:52:22 +0000
Subject: [PATCH] seems to benchmark faster, RT#5083

---
 FS/FS/cust_pkg.pm | 43 ++++++++++++++++++++++++++++++-------------
 1 file changed, 30 insertions(+), 13 deletions(-)

diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index 8d4e0bf70..f2e0005d7 100644
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -1580,20 +1580,37 @@ sub extra_part_svc {
   my $pkgnum  = $self->pkgnum;
   my $pkgpart = $self->pkgpart;
 
+#  qsearch( {
+#    'table'     => 'part_svc',
+#    'hashref'   => {},
+#    'extra_sql' =>
+#      "WHERE 0 = ( SELECT COUNT(*) FROM pkg_svc 
+#                     WHERE pkg_svc.svcpart = part_svc.svcpart 
+#                       AND pkg_svc.pkgpart = ?
+#                       AND quantity > 0 
+#                 )
+#	 AND 0 < ( SELECT COUNT(*) FROM cust_svc
+#                       LEFT JOIN cust_pkg USING ( pkgnum )
+#                     WHERE cust_svc.svcpart = part_svc.svcpart
+#                       AND pkgnum = ?
+#                 )",
+#    'extra_param' => [ [$self->pkgpart=>'int'], [$self->pkgnum=>'int'] ],
+#  } );
+
+#seems to benchmark slightly faster...
   qsearch( {
-    'table'     => 'part_svc',
-    'hashref'   => {},
-    'extra_sql' =>
-      "WHERE 0 = ( SELECT COUNT(*) FROM pkg_svc 
-                     WHERE pkg_svc.svcpart = part_svc.svcpart 
-                       AND pkg_svc.pkgpart = ?
-                       AND quantity > 0 
-                 )
-	 AND 0 < ( SELECT COUNT(*) FROM cust_svc
-                       LEFT JOIN cust_pkg using ( pkgnum )
-                     WHERE cust_svc.svcpart = part_svc.svcpart
-                       AND pkgnum = ?
-                 )",
+    'select'      => 'DISTINCT ON (svcpart) part_svc.*',
+    'table'       => 'part_svc',
+    'addl_from'   =>
+      'LEFT JOIN pkg_svc  ON (     pkg_svc.svcpart   = part_svc.svcpart 
+                               AND pkg_svc.pkgpart   = ?
+                               AND quantity > 0
+                             )
+       LEFT JOIN cust_svc ON (     cust_svc.svcpart = part_svc.svcpart )
+       LEFT JOIN cust_pkg USING ( pkgnum )
+      ',
+    'hashref'     => {},
+    'extra_sql'   => "WHERE pkgsvcnum IS NULL AND cust_pkg.pkgnum = ? ",
     'extra_param' => [ [$self->pkgpart=>'int'], [$self->pkgnum=>'int'] ],
   } );
 }
-- 
2.11.0