defaria.com/blogs/Status/2006/12/gpdb-database-p.html

   1 <!DOCTYPE html>
   2 <html lang="en-us" itemscope itemtype="http://schema.org/Article">
   3   <head>
   4     <meta charset="utf-8">
   5     <meta name="description" content=" Moved convertdb and gpdb_add_vob into Clearcase Attempting to standardize which Perl to use, which Oracle.pm to pickup and how to insure that other sites have the proper prerequisites for GPDB Discovered that Oracle is not supported on Linux here...">
   6     <meta name="generator" content="Movable Type 5.2.3">
   7     <title>GPDB Database performance - Status</title>
   8     <link rel="alternate" type="application/atom+xml" title="Recent Entries" href="http://defaria.com/blogs/Status/atom.xml">
   9     <link rel="canonical" href="http://defaria.com/blogs/Status/2006/12/gpdb-database-p.html">
  10     <meta name="viewport" content="width=device-width,initial-scale=1">
  11     <link rel="stylesheet" href="http://defaria.com/blogs/Status/styles.css">
  12     <!--[if lt IE 9]>
  13     <link rel="stylesheet" href="http://defaria.com/blogs/Status/styles_ie.css">
  14     <script src="/mt/mt-static/support/theme_static/rainier/js/html5shiv.js"></script>
  15     <![endif]-->
  16
  17     <link rel="start" href="http://defaria.com/blogs/Status/">
  18
  19     <link rel="prev" href="http://defaria.com/blogs/Status/2006/12/userssites-and.html" title="Users/Sites and Projects">
  20     <link rel="next" href="http://defaria.com/blogs/Status/2006/12/reworking-gpdb.html" title="Reworking GPDB tables, mkview">
  21     <!-- Open Graph Protocol -->
  22     <meta property="og:type" content="article">
  23     <meta property="og:locale" content="en-us">
  24     <meta property="og:title" content="GPDB Database performance">
  25     <meta property="og:url" content="http://defaria.com/blogs/Status/2006/12/gpdb-database-p.html">
  26     <meta property="og:description" content=" Moved convertdb and gpdb_add_vob into Clearcase Attempting to standardize which Perl to use, which Oracle.pm to pickup and how to insure that other sites have the proper prerequisites for GPDB Discovered that Oracle is not supported on Linux here...">
  27     <meta property="og:site_name" content="Status">
  28     <meta property="og:image" content="/mt/mt-static/support/theme_static/rainier/img/siteicon-sample.png">
  29     <!-- Metadata -->
  30     <meta itemprop="description" content=" Moved convertdb and gpdb_add_vob into Clearcase Attempting to standardize which Perl to use, which Oracle.pm to pickup and how to insure that other sites have the proper prerequisites for GPDB Discovered that Oracle is not supported on Linux here...">
  31     <link itemprop="url" href="http://defaria.com/blogs/Status/2006/12/gpdb-database-p.html">
  32     <link itemprop="image" href="/mt/mt-static/support/theme_static/rainier/img/siteicon-sample.png">
  33
  34   </head>
  35   <body>
  36     <div id="container">
  37       <div id="container-inner">
  38         <header id="header" role="banner">
  39           <div id="header-inner">
  40             <div id="header-content">
  41               <h1>
  42                 <a href="http://defaria.com/blogs/Status/">
  43
  44                   Status
  45
  46                 </a>
  47               </h1>
  48
  49             </div>
  50
  51             <nav role="navigation">
  52           <ul>
  53             <li><a href="http://defaria.com/blogs/Status/">Home</a></li>
  54
  55
  56           </ul>
  57         </nav>
  58
  59           </div>
  60         </header>
  61         <div id="content">
  62           <div id="content-inner">
  63             <ul class="breadcrumb breadcrumb-list">
  64               <li class="breadcrumb-list-item"><a href="http://defaria.com/blogs/Status/">Home</a></li>
  65               <li class="breadcrumb-list-item">GPDB Database performance</li>
  66             </ul>
  67             <div id="individual-main" class="main" role="main">
  68               <article id="entry-1858" class="entry entry-asset asset hentry">
  69                 <div class="asset-header">
  70                   <h2 itemprop="name" class="asset-name entry-title">GPDB Database performance</h2>
  71                   <footer class="asset-meta">
  72                     <ul class="asset-meta-list">
  73                       <li class="asset-meta-list-item">Posted on <time datetime="2006-12-19T17:32:21-08:00" itemprop="datePublished">December 19, 2006</time></li>
  74                       <li class="asset-meta-list-item">by <span class="author entry-author vcard"></span></li>
  75
  76
  77                       <li class="asset-meta-list-item">in <a itemprop="articleSection" rel="tag" href="http://defaria.com/blogs/Status/gpdb/">GPDB</a></li>
  78
  79
  80                    </ul>
  81                 </footer>
  82                 </div>
  83                 <div class="entry-content asset-content" itemprop="articleBody">
  84                   <ul>
  85   <li>Moved convertdb and gpdb_add_vob into Clearcase</li>
  86
  87   <li>Attempting to standardize which Perl to use, which Oracle.pm to pickup and how to insure that other sites have the proper prerequisites for GPDB</li>
  88
  89   <li>Discovered that Oracle is not supported on Linux here at TI. This will be a problem for GPDB</li>
  90
  91   <li>Still working on issues of the new GPDB design and attempting to get gpdb_add_project.pl to work with it</li>
  92
  93   <li>Got definition of performance problem that Donna is experiencing. She is attempting to populate a pull down with just the project names for a site. Doing so causes lots of transfer of data as the current GPDB API gpdb_getProject effectively transfers all kinds of project information where Donna needs only the project names.</li>
  94
  95   <li>Developed a new API, gpdb_getProjectsAtSite that returns only the project names in a more efficient manner</li>
  96 </ul>
  97                   <h3>gpdb_getProjectsAtSite</h3>
  98
  99 <p>Donna may be right and we may need to enlist the help of Ajay here.</p>
 100
 101 <p>I coded up a gpdb_getProjectsAtSite function:</p>
 102
 103 <div class=code><pre>
 104 sub gpdb_getProjectsAtSite ($$) {
 105   my ($site_name, $resource) = @_;
 106
 107   resetErr ();
 108
 109   unless (lc $resource eq "clearcase" or
 110           lc $resource eq "designsync") {
 111     setError (-1, "gpdb_getProjectsAtSite: Resource must be one of 'clearcase' or 'designsync'");
 112     return ();
 113   } # unless
 114
 115   my $siteID    = siteID $site_name;
 116   my $condition    = "site_id = $siteID and $resource = 'Y'";
 117
 118   my @projects = @{GPDB::primitive::searchData ("projects", $condition)};
 119   my @project_names;
 120
 121   foreach (@projects) {
 122     my %project    = %{$_};
 123     my $name    = projectName $project {PARENT_PROJ_ID};
 124
 125     next if !$name;
 126
 127     push @project_names, $name;
 128   } # foreach
 129
 130   return @project_names;
 131 } # gpdb_getProjectsAtSite
 132 </pre></div>
 133
 134 <p>Basically you call it with a site name and a resource (being clearcase or designsync). It does some housekeeping (resetting the error variables and checking that resource is one of clearcase or designsync). Next it translates the site name to an ID. We need an ID
 135 and we shouldn't burden the users with having to supply that. The siteID function is a new internal function for gpdb.pm because I often find the need to translate a site name to an ID. Next we compose a condition which is the part after "where" that says find things that have "site_id = $siteID and $resource = 'Y'". Remember resource is either "clearcase" or "designsync" and we wish to find project records where the site matches and the resource is toggled on (i.e. = 'Y').</p>
 136
 137 <p>There's a new primitive, searchData because getData only finds single records by "ID" only and findData will return multiple records based on a fieldname = value specific condition. Here we want two different fieldname/value pairs and an "and" condition. Therefore the searchData primitive takes two parameters, the table name and the condition, and composes a "select * from $tableName where $condition" and returns an array of hashes like findData does.</p>
 138
 139 <p>At this point we have an array of projects whose site IDs match our passed in Site Name and whose $resource is toggled on as 'Y'. But we want to return project names to be nice for the user and that's what the foreach loop does. Note it calls another new internal routine called projectName which returns the project's name for the product ID (the parent project ID that is). Note also that projectName will return undef if the project is retired. That's what the "next if !$name" statement is for. All non-retired project names therefore are pushed onto @project_names which are returned from the subroutine.</p>
 140
 141 <p>I do not see how I could make this any faster.</p>
 142
 143 <p>Well how did it perform? Selecting on Dallas and Clearcase projects (because gpdb_add_project.pl that does DesignSync additions is still not working well) there are 194 Clearcase projects at the Dallas site. Running a small test script here and at Manchester yields:</p>
 144
 145 <div class=code><pre>
 146 <b>Dallas:</b><u>time testproj_names.pl</u>
 147 real    0m7.156s
 148 user    0m1.260s
 149 sys     0m0.310s
 150
 151 <b>Manchester:</b><u>time testproj_names.pl</u>
 152 real    0m23.708s
 153 user    0m0.490s
 154 sys     0m0.170s
 155 </pre></div>
 156
 157 <p>That's 24 seconds from Manchester or roughly 3.5 times as slow.</p>
 158
 159 <p>Switching over to selecting designsync records, there are 9 of them at Dallas. Timings for this are:</p>
 160
 161 <div class=code><pre>
 162 <b>Dallas:</b><u>time testproj_names.pl</u>
 163 real    0m1.402s
 164 user    0m0.810s
 165 sys     0m0.120s
 166
 167 <b>Manchester:</b><u>time testproj_names.pl</u>
 168 real    0m3.646s
 169 user    0m0.300s
 170 sys     0m0.080s
 171 </pre></div>
 172
 173 <p>Again in the order of 3 times as slow</p>
 174                 </div>
 175                 <nav class="page-navigation entry-navigation pagination content-nav">
 176                   <ul class="page-navigation-list">
 177
 178                     <li class="page-navigation-list-item page-navigation-prev"><a rel="prev" href="http://defaria.com/blogs/Status/2006/12/userssites-and.html" title="Users/Sites and Projects">Previous entry</a></li>
 179
 180
 181                     <li class="page-navigation-list-item page-navigation-next"><a rel="next" href="http://defaria.com/blogs/Status/2006/12/reworking-gpdb.html" title="Reworking GPDB tables, mkview">Next entry</a></li>
 182
 183                   </ul>
 184                 </nav>
 185                 <!--
 186 <aside id="zenback" class="zenback feedback">
 187   Please paste Zenback script code here.
 188 </aside>
 189 -->
 190
 191
 192               </article>
 193             </div>
 194             <aside class="widgets related" role="complementary">
 195               <nav class="widget-search widget">
 196   <div class="widget-content">
 197     <form method="get" id="search" action="http://defaria.com/mt/mt-search.cgi">
 198       <div>
 199         <input type="text" name="search" value="" placeholder="Search...">
 200
 201         <input type="hidden" name="IncludeBlogs" value="8">
 202
 203         <input type="hidden" name="limit" value="20">
 204         <button type="submit" name="button">
 205           <img alt="Search" src="/mt/mt-static/support/theme_static/rainier/img/search-icon.png">
 206         </button>
 207       </div>
 208     </form>
 209   </div>
 210 </nav>
 211 <nav class="widget-archive-category widget">
 212   <h3 class="widget-header">Categories</h3>
 213   <div class="widget-content">
 214
 215
 216     <ul class="widget-list">
 217
 218
 219       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/ameriquest/">Ameriquest (99)</a>
 220
 221
 222       </li>
 223
 224
 225
 226
 227       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/audience/">Audience (3)</a>
 228
 229
 230       </li>
 231
 232
 233
 234
 235       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/broadcom/">Broadcom (76)</a>
 236
 237
 238       </li>
 239
 240
 241
 242
 243       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/gpdb/">GPDB (35)</a>
 244
 245
 246       </li>
 247
 248
 249
 250
 251       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/general-dynamics/">General Dynamics (61)</a>
 252
 253
 254       </li>
 255
 256
 257
 258
 259       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/general-electric/">General Electric (13)</a>
 260
 261
 262       </li>
 263
 264
 265
 266
 267       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/hewlett-packard/">Hewlett Packard (13)</a>
 268
 269
 270       </li>
 271
 272
 273
 274
 275       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/lynuxworks/">LynuxWorks (162)</a>
 276
 277
 278       </li>
 279
 280
 281
 282
 283       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/pqa/">PQA (35)</a>
 284
 285
 286       </li>
 287
 288
 289
 290
 291       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/salira/">Salira (79)</a>
 292
 293
 294       </li>
 295
 296
 297
 298
 299       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/tellabs/">Tellabs (2)</a>
 300
 301
 302       </li>
 303
 304
 305
 306
 307       <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/texas-instruments/">Texas Instruments (31)</a>
 308
 309
 310       </li>
 311
 312     </ul>
 313
 314
 315   </div>
 316 </nav>
 317
 318
 319 <nav class="widget-archive-dropdown widget">
 320   <h3 class="widget-header">Archives</h3>
 321   <div class="widget-content">
 322     <select>
 323       <option>Select a Month...</option>
 324
 325       <option value="http://defaria.com/blogs/Status/2016/02/">February 2016</option>
 326
 327
 328
 329       <option value="http://defaria.com/blogs/Status/2014/09/">September 2014</option>
 330
 331
 332
 333       <option value="http://defaria.com/blogs/Status/2014/04/">April 2014</option>
 334
 335
 336
 337       <option value="http://defaria.com/blogs/Status/2014/03/">March 2014</option>
 338
 339
 340
 341       <option value="http://defaria.com/blogs/Status/2013/02/">February 2013</option>
 342
 343
 344
 345       <option value="http://defaria.com/blogs/Status/2012/09/">September 2012</option>
 346
 347
 348
 349       <option value="http://defaria.com/blogs/Status/2012/08/">August 2012</option>
 350
 351
 352
 353       <option value="http://defaria.com/blogs/Status/2012/05/">May 2012</option>
 354
 355
 356
 357       <option value="http://defaria.com/blogs/Status/2012/04/">April 2012</option>
 358
 359
 360
 361       <option value="http://defaria.com/blogs/Status/2012/02/">February 2012</option>
 362
 363
 364
 365       <option value="http://defaria.com/blogs/Status/2012/01/">January 2012</option>
 366
 367
 368
 369       <option value="http://defaria.com/blogs/Status/2011/10/">October 2011</option>
 370
 371
 372
 373       <option value="http://defaria.com/blogs/Status/2011/07/">July 2011</option>
 374
 375
 376
 377       <option value="http://defaria.com/blogs/Status/2010/09/">September 2010</option>
 378
 379
 380
 381       <option value="http://defaria.com/blogs/Status/2010/08/">August 2010</option>
 382
 383
 384
 385       <option value="http://defaria.com/blogs/Status/2010/04/">April 2010</option>
 386
 387
 388
 389       <option value="http://defaria.com/blogs/Status/2010/03/">March 2010</option>
 390
 391
 392
 393       <option value="http://defaria.com/blogs/Status/2010/02/">February 2010</option>
 394
 395
 396
 397       <option value="http://defaria.com/blogs/Status/2009/05/">May 2009</option>
 398
 399
 400
 401       <option value="http://defaria.com/blogs/Status/2009/04/">April 2009</option>
 402
 403
 404
 405       <option value="http://defaria.com/blogs/Status/2008/07/">July 2008</option>
 406
 407
 408
 409       <option value="http://defaria.com/blogs/Status/2008/05/">May 2008</option>
 410
 411
 412
 413       <option value="http://defaria.com/blogs/Status/2008/04/">April 2008</option>
 414
 415
 416
 417       <option value="http://defaria.com/blogs/Status/2008/03/">March 2008</option>
 418
 419
 420
 421       <option value="http://defaria.com/blogs/Status/2008/02/">February 2008</option>
 422
 423
 424
 425       <option value="http://defaria.com/blogs/Status/2008/01/">January 2008</option>
 426
 427
 428
 429       <option value="http://defaria.com/blogs/Status/2007/12/">December 2007</option>
 430
 431
 432
 433       <option value="http://defaria.com/blogs/Status/2007/11/">November 2007</option>
 434
 435
 436
 437       <option value="http://defaria.com/blogs/Status/2007/10/">October 2007</option>
 438
 439
 440
 441       <option value="http://defaria.com/blogs/Status/2007/09/">September 2007</option>
 442
 443
 444
 445       <option value="http://defaria.com/blogs/Status/2007/08/">August 2007</option>
 446
 447
 448
 449       <option value="http://defaria.com/blogs/Status/2007/07/">July 2007</option>
 450
 451
 452
 453       <option value="http://defaria.com/blogs/Status/2007/06/">June 2007</option>
 454
 455
 456
 457       <option value="http://defaria.com/blogs/Status/2007/05/">May 2007</option>
 458
 459
 460
 461       <option value="http://defaria.com/blogs/Status/2007/04/">April 2007</option>
 462
 463
 464
 465       <option value="http://defaria.com/blogs/Status/2007/03/">March 2007</option>
 466
 467
 468
 469       <option value="http://defaria.com/blogs/Status/2007/01/">January 2007</option>
 470
 471
 472
 473       <option value="http://defaria.com/blogs/Status/2006/12/">December 2006</option>
 474
 475
 476
 477       <option value="http://defaria.com/blogs/Status/2006/11/">November 2006</option>
 478
 479
 480
 481       <option value="http://defaria.com/blogs/Status/2006/10/">October 2006</option>
 482
 483
 484
 485       <option value="http://defaria.com/blogs/Status/2006/09/">September 2006</option>
 486
 487
 488
 489       <option value="http://defaria.com/blogs/Status/2006/07/">July 2006</option>
 490
 491
 492
 493       <option value="http://defaria.com/blogs/Status/2006/06/">June 2006</option>
 494
 495
 496
 497       <option value="http://defaria.com/blogs/Status/2006/05/">May 2006</option>
 498
 499
 500
 501       <option value="http://defaria.com/blogs/Status/2006/04/">April 2006</option>
 502
 503
 504
 505       <option value="http://defaria.com/blogs/Status/2006/03/">March 2006</option>
 506
 507
 508
 509       <option value="http://defaria.com/blogs/Status/2006/02/">February 2006</option>
 510
 511
 512
 513       <option value="http://defaria.com/blogs/Status/2006/01/">January 2006</option>
 514
 515
 516
 517       <option value="http://defaria.com/blogs/Status/2005/12/">December 2005</option>
 518
 519
 520
 521       <option value="http://defaria.com/blogs/Status/2005/11/">November 2005</option>
 522
 523
 524
 525       <option value="http://defaria.com/blogs/Status/2005/10/">October 2005</option>
 526
 527
 528
 529       <option value="http://defaria.com/blogs/Status/2005/09/">September 2005</option>
 530
 531
 532
 533       <option value="http://defaria.com/blogs/Status/2005/08/">August 2005</option>
 534
 535
 536
 537       <option value="http://defaria.com/blogs/Status/2005/07/">July 2005</option>
 538
 539
 540
 541       <option value="http://defaria.com/blogs/Status/2005/06/">June 2005</option>
 542
 543
 544
 545       <option value="http://defaria.com/blogs/Status/2005/05/">May 2005</option>
 546
 547
 548
 549       <option value="http://defaria.com/blogs/Status/2005/04/">April 2005</option>
 550
 551
 552
 553       <option value="http://defaria.com/blogs/Status/2005/03/">March 2005</option>
 554
 555
 556
 557       <option value="http://defaria.com/blogs/Status/2005/02/">February 2005</option>
 558
 559
 560
 561       <option value="http://defaria.com/blogs/Status/2005/01/">January 2005</option>
 562
 563
 564
 565       <option value="http://defaria.com/blogs/Status/2004/12/">December 2004</option>
 566
 567
 568
 569       <option value="http://defaria.com/blogs/Status/2004/09/">September 2004</option>
 570
 571
 572
 573       <option value="http://defaria.com/blogs/Status/2004/08/">August 2004</option>
 574
 575
 576
 577       <option value="http://defaria.com/blogs/Status/2004/07/">July 2004</option>
 578
 579
 580
 581       <option value="http://defaria.com/blogs/Status/2004/06/">June 2004</option>
 582
 583
 584
 585       <option value="http://defaria.com/blogs/Status/2004/05/">May 2004</option>
 586
 587
 588
 589       <option value="http://defaria.com/blogs/Status/2004/04/">April 2004</option>
 590
 591
 592
 593       <option value="http://defaria.com/blogs/Status/2004/03/">March 2004</option>
 594
 595
 596
 597       <option value="http://defaria.com/blogs/Status/2004/02/">February 2004</option>
 598
 599
 600
 601       <option value="http://defaria.com/blogs/Status/2004/01/">January 2004</option>
 602
 603
 604
 605       <option value="http://defaria.com/blogs/Status/2003/12/">December 2003</option>
 606
 607
 608
 609       <option value="http://defaria.com/blogs/Status/2003/11/">November 2003</option>
 610
 611     </select>
 612   </div>
 613 </nav>
 614
 615
 616
 617 <div class="widget-syndication widget section">
 618   <div class="widget-content">
 619     <p><img src="http://defaria.com/mt/mt-static/images/status_icons/feed.gif" alt="Subscribe to feed" width="9" height="9" /> <a href="http://defaria.com/blogs/Status/atom.xml">Subscribe to this blog's feed</a></p>
 620
 621   </div>
 622 </div>
 623
 624             </aside>
 625           </div>
 626         </div>
 627         <footer id="footer" role="contentinfo">
 628           <div id="footer-inner">
 629             <div id="footer-content">
 630   <nav role="navigation">
 631           <ul>
 632             <li><a href="http://defaria.com/blogs/Status/">Home</a></li>
 633
 634
 635           </ul>
 636         </nav>
 637
 638   <p class="license">&copy; Copyright 2016.</p>
 639   <p class="poweredby">Powered by <a href="http://www.movabletype.org/">Movable Type</a></p>
 640 </div>
 641           </div>
 642         </footer>
 643       </div>
 644     </div>
 645     <script src="http://defaria.com/mt/mt-static/jquery/jquery.min.js"></script>
 646     <script src="http://defaria.com/blogs/Status/mt-theme-scale2.js"></script>
 647   </body>
 648 </html>