Re: Could Perl's DBI (DBD::Oracle) be faster than SQL*Plus?

Tim Cross wrote:
> Adam Monsen <adamm_at_wazamatta.com> writes:
>
>
>>SELECT id||','||email
>> FROM users
>> WHERE UPPER(email) like UPPER('%_at_hotmail.com')
>>/
>>
>>>I then timed how long it took for Perl (via dbish) vs. the time it
>>
>>took for SQL*Plus to write one megabyte of data to disk. Perl took
>>about twenty seconds to write a meg of data, SQL*Plus took about one
>>minute.
>>
>>Yikes! Why the discrepancy? Is this test even valid? Is DBI::Shell
>>really faster at dumping data than SQL*Plus?
>>
>Well, there are a few things here which may need to be taken into
 > account.
 >
> 1. Putting a function on the email column (e.g. UPPER(email) will
> cause Oracle to not use any indexes and I don't think you did this
> with the DBI query (sorry, I've already cut it).
Not that it matters, but there is a function-based index on UPPER(email). This doesn't matter because the query execution time should be the same between Perl (dbish) and sqlplus (I ran the dump a few times to make sure results were consistent, and caching wasn't an issue). The index doesn't make much difference anyway, since I'm selecting about 1/3 the rows of the entire table, as I said.

>
> 2. I notice you set the line length to 9000 and then you trim the
> extra blanks. Do you expect any of your lines to be longer than 80
> characters? I think its unlikely, so probably remove that line as
> ell.
I lowered this to 90 characters. This actually seemed to improve the speed... sqlplus took about 12 seconds to write a meg, while Perl took only 8.

I also fixed the query run in SQL*Plus to read as follows: SELECT ''''||id||''','''||email||''''

   FROM users
  WHERE UPPER(email) like UPPER('%_at_hotmail.com')

This was to make sure dbish wasn't cheating by getting to 1M faster by writing more data. This didn't seem to affect timing.

>
> Try running the test again and se what you get. My experience has been
> that native oracle sql and plsql applications tend to run faster than
> using perl to do the same thing. Note I'm not an Oracle devotee - much
> prefer Perl, but I'm not allowed to here 'cause we are an Oracle
> shop' and I'm the only one who knows perl! I can code the same job
> faster in perl, but they run faster in Oracle sql or plsql.
Received on Fri May 17 2002 - 02:21:26 CEST