Re: Performance of my algorithms & system

The best advice would be to redesign the underlying data structures. If you are being required to use subst() and thus eliminate the use of indexes, you have on of these problems, or maybe both:

• improper modeling of application data
• using the wrong technology.

I mentioned the second bullet point as you don't really give any information about the app.

Jared

On Fri, 26 May 2000 gungor_o_at_kocfinans.com.tr wrote:

> Hi all,
> I want to share your opinions/results with mine about some performance tests
> on my system. I want to compare some subset of our customers (about 3000
> daily) with whole set (currently 2000000) of them. I use 6 fields to find
> similar records. I can't utilize indexes due to column substring comparisons
> of the algorithm. You can suggest any advice about 1.comparing subset of
> two char columns and 2.full-table loops optimizations. After the following
> tests, I think of using the C version of algorithm by extracting the table
> data into flat files. Currently, this version gives me about 50 min.s of
> extract time (159 MB) and 30 min.s of computation time. I also want to hear
> about any suggestions (if possible, with actual or example codes) about
> catch algorithms. For example, just using some columns for comparison,
> computing the similarity of two personal records, such as using name,
> surname, address, father name, etc.
>
> Here are my tests. I used Oracle tkprof, Precise/SQL, and SQLPlus (timing)
> tools. I can't see any I/O wait since I rerun the following code many times,
> and created "mytbl" with 100,000 rows from the original file to enable it
> fit into the memory fully. I observed that computation time is linearly
> proportional to the rowcount. i.e. if you increase the rowcount as much as
> twice or ten times, then its duration also increases twice or ten-times
> respectively. I want to learn about similar/exact statistics about your
> systems and comments about my system and tests.
>
> Our system is: Oracle 7.3.4.5 on 8-processor, 2560 MByte mem, SUN E10000
> Solaris 2.6.
>
> Some of my tests:
>
> 1. This one tries to compute time required for a cached
> (i.e. no disk io and any other wait events except CPU wait)
>
> begin
> for i in (select name from mytbl) loop
> null;
> end loop;
> end;
>
> This returns to me after >.5sn.
> select Avg(vsize(name)) from mytbl = 6
>
> 2. Then I tried a null loop without any block read etc. Here is some exerpt
> from the trace file:
>
> begin
> for i in 1..300000 loop
> for j in 1..2000 loop
> null;
> end loop;
> end loop;
> end;
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 881.21 925.48 0 0 0
> 1
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 881.21 925.48 0 0 0
> 1
>
> 3. Finally, I tried a C version, which lasts 4 sec.s. I list it below:
>
> >>>>>>>>>>>>>>>>>>>>>>>
> #include <stdio.h>
> #include <string.h>
> #include <time.h>
>
> void ReturnTime(char *s)
> {
> time_t t;
> struct tm *tm;
>
> t = time(NULL);
> tm = localtime(&t);
> sprintf(s, "%02d:%02d:%02d",
> tm->tm_hour, tm->tm_min, tm->tm_sec);
> }
>
> void PrintTime(void)
> {
> char s[10];
>
> ReturnTime(s);
> fprintf(stderr, "%s\n", s);
> }
>
> main()
> {
> int i, j, k;
> int a;
> double m=0;
> fprintf(stderr, "Press Enter to start...");
> a=getchar();
> fprintf(stderr, "Start: ");
> PrintTime();
>
> for(i=0; i<300000; i++) {
> for(k=0; k<2000; k++) {
> m ++;
> }
> }
>
> fprintf(stderr, "Finish: ");
> PrintTime();
>
> fprintf(stderr, "m =%f i=%d j=%d k=%d", m,i, j, k);
>
> return 0;
> }
> <<<<<<<<<<<<<<<<<<<<<<<
>
>
> />cc -o myc -xO5 myc.c
> />myc
> Press Enter to start...
> Start: 11:28:19
> Finish: 11:28:23
> m =600000000.000000 i=300000 j=0 k=2000
>
>
> Thanks in advance.
>
>
> --
> Author:
> INET: gungor_o_at_kocfinans.com.tr
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Received on Sat May 27 2000 - 12:40:41 CDT