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

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).

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.

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.

Tim

-- 
Tim Cross					E-Mail: tcross_at_pobox.une.edu.au

---
find / -iname microsoft -exec rm -rf {} \;