optimization for ginourmous numbers of packages for intergate, whew

4 files changed, 18 insertions, 13 deletions

diff --git a/FS/bin/freeside-setup b/FS/bin/freeside-setup
index 78a03385c..74164a066 100755
--- a/FS/bin/freeside-setup
+++ b/FS/bin/freeside-setup
@@ -675,7 +675,7 @@ sub tables_hash_hack {
       ],
       'primary_key' => 'pkgpart',
       'unique' => [],
-      'index' => [],
+      'index' => [ [ disabled ], ],
     },
 
 #    'part_title' => {
@@ -718,7 +718,7 @@ sub tables_hash_hack {
       ],
       'primary_key' => 'svcpart',
       'unique' => [],
-      'index' => [],
+      'index' => [ [ 'disabled' ] ],
     },
 
     'part_svc_column' => {
diff --git a/bin/fs-setup b/bin/fs-setup
index 8e000c4ba..c60181b92 100755
--- a/bin/fs-setup
+++ b/bin/fs-setup
@@ -1,6 +1,6 @@
 #!/usr/bin/perl -Tw
 #
-# $Id: fs-setup,v 1.96.4.5 2002-12-14 12:19:19 steve Exp $
+# $Id: fs-setup,v 1.96.4.6 2002-12-24 22:41:21 ivan Exp $
 
 #to delay loading dbdef until we're ready
 BEGIN { $FS::Record::setup_hack = 1; }
@@ -673,7 +673,7 @@ sub tables_hash_hack {
       ],
       'primary_key' => 'pkgpart',
       'unique' => [],
-      'index' => [],
+      'index' => [ [ 'disabled' ] ],
     },
 
 #    'part_title' => {
@@ -716,7 +716,7 @@ sub tables_hash_hack {
       ],
       'primary_key' => 'svcpart',
       'unique' => [],
-      'index' => [],
+      'index' => [ [ 'disabled' ] ],
     },
 
     'part_svc_column' => {
diff --git a/httemplate/docs/upgrade9.html b/httemplate/docs/upgrade9.html
index da5b643fb..c6afe2b0f 100644
--- a/httemplate/docs/upgrade9.html
+++ b/httemplate/docs/upgrade9.html
@@ -15,6 +15,11 @@
 INSERT INTO msgcat ( msgnum, msgcode, locale, msg ) VALUES ( 18, 'daytime', 'en_US', 'Day Phone' );
 INSERT INTO msgcat ( msgnum, msgcode, locale, msg ) VALUES ( 19, 'night', 'en_US', 'Night Phone' );
 </pre>
+  <li>Optionally, apply the following changes to your database (performance improvement for large numbers of services or packages):
+<pre>
+CREATE INDEX part_pkg1 ON part_pkg ( disabled );
+CREATE INDEX part_svc1 ON part_svc ( disabled );
+</pre>
   <li>If you want to use ACH (electronic checks), you will need to make changes to your database.  The easiest way to make these changes is to dump your database (with pg_dump), change the payinfo field in the cust_pay, cust_refund, h_cust_pay and h_cust_refund tables from varchar(16) to varchar(80), reload the database from the dump, and run dbdef-create
   <li>Restart Apache and freeside-queued.
 </body>
diff --git a/httemplate/view/cust_main.cgi b/httemplate/view/cust_main.cgi
index 62e961dfa..6fe759bd9 100755
--- a/httemplate/view/cust_main.cgi
+++ b/httemplate/view/cust_main.cgi
@@ -280,14 +280,14 @@ print '<BR>'.
   qq!<INPUT TYPE="hidden" NAME="custnum" VALUE="$custnum">!.
   '<SELECT NAME="pkgpart"><OPTION> ';
 
-foreach my $type_pkgs ( qsearch('type_pkgs',{'typenum'=> $agent->typenum }) ) {
-  my $pkgpart = $type_pkgs->pkgpart;
-#  my $part_pkg = qsearchs('part_pkg', { 'pkgpart' => $pkgpart } )
-#    or do { warn "unknown type_pkgs.pkgpart $pkgpart"; next; };
-  my $part_pkg =
-    qsearchs('part_pkg', { 'pkgpart' => $pkgpart, 'disabled' => '' } )
-    or next;
-  print qq!<OPTION VALUE="$pkgpart">!. $part_pkg->pkg. ' - '.
+foreach my $part_pkg (
+  qsearch( 'part_pkg', { 'disabled' => '' }, '',
+           ' AND 0 < ( SELECT COUNT(*) FROM type_pkgs '.
+           '             WHERE typenum = '. $agent->typenum.
+           '             AND type_pkgs.pkgpart = part_pkg.pkgpart )'
+         )
+) {
+  print '<OPTION VALUE="'. $part_pkg->pkgpart. '">'. $part_pkg->pkg. ' - '.
         $part_pkg->comment;
 }