Re: ADVICE NEEDED - Striping? SQL? Speed

Hi Jane,

    I am assuming that this query accounts for a significant percentage of your system activity and that you can spend some money in order to get the performance level you are seeking. If you can throw some more hardware at the problem Oracle can be made much faster than Access.

    First increase the amount of RAM to at least one GB and more if you can afford it. This will allow you to specify a very large SGA with a enough buffer space to keep most, if not all, of your indexes in RAM. Then Oracle can do an in memory scan instead of reading disks when running your query.

    Next, you should add a couple of drives outside of your RAID set for redo logs, archives and everything other than your data and indexes.

    You should also investigate the feasibility of reengineering your SQL. For example you could revise your tables and query by fully inverting the three fields that you are using in the where clause. This would involve parsing the fields and building your own index tables where each row would consist of a primary key or rowid and one word out of the field. These tables would be indexed on the column containing the word.

        CREATE TABLE Field3_inversion  (
            word            VARCHAR2(xx),
            key_value    VARCHAR2(xx))
        /

Your query would then do a join of the existing table with the new tables using something like:

        SELECT Field2,Field3,Field4,Field5
        FROM Table1, Field3_inversion, Field4_inversion, Field5_inversion
        WHERE  (Table1.<primary key> = Field3_inversion.<key value>
                            AND   Field3_inversion.word LIKE <string%>)
                  OR (Table1.<primary key> = Field4_inversion.<key value>
                            AND   Field4_inversion.word LIKE <string%>)
                  OR (Table1.<primary key> = Field5_inversion.<key value>
                            AND   Field5_inversion.word LIKE <string%>)
        ORDER BY Field5;

regards

Jerry Gitomer

Jane H. wrote in message <7i2691$5dc_at_dfw-ixnews8.ix.netcom.com>...
>PROBLEM:
>
>When I execute a query across the database (approx. 300mb), it takes about
>35 secs. to return the results.
>
>This is too long. I need it to be back in 4 seconds max, preferably much
>less. Any advice is appreciated.
>___________________________________
>QUESTIONS:
>
>Would using Oracle instead of Access make it CONSIDERABLY faster?
>
>
>------
>Is there a better way to write the SQL statement?
>(The strings can be in either field, but each string in the array must
>exist).
>
>
>
>-----
>Do I need more Drives in the Raid Set? Is there a general formula to
>calculate performance?
>
>
>
>-----
>What's a good configuration, (without too much more additional expense)??
>
>
>-----
>How can I get the results faster (i.e. 5 seconds or less)?
>
>
>
>
>___________________________________________
>SERVER CONFIGURATION:
>
>4 - 9MB SCSI DRIVES / 10K RPM
>Dual Processor - Intel 400mhz
>512MB Ram
>Raid Card / Raid Level 5 (3 striped)
>
>
>
>
>____________________________________________
>SQL (text fields up to 255 chars.)
>
>
> SELECT Field2,Field3,Field4,Field5 FROM Table1 WHERE
> (
> (
> (Field3 LIKE '%string[0]%') OR
> (Field4 LIKE '%string[0]%') OR
> (Field5 LIKE '%string[0]%')
> )
> AND
> (
> (Field3 LIKE '%string[1]%') OR
> (Field4 LIKE '%string[1]%') OR
> (Field5 LIKE '%string[1]%')
> )
>
> AND
> (
> (Field3 LIKE '%string[2]%') OR
> (Field4 LIKE '%string[2]%') OR
> (Field5 LIKE '%string[2]%')
> )
>
>
> ) ORDER BY Field5
>
>
>etc.
>
>
>
>Thanks in Advance :)
>
>
>
>
>
Received on Fri May 21 1999 - 08:36:43 CDT