Implicit vs. Explicit Conversion

Moderator: NorbertKrupa

Post Reply
User avatar
Jbaskin
Intermediate
Intermediate
Posts: 61
Joined: Sat Jan 28, 2012 1:21 pm

Implicit vs. Explicit Conversion

Post by Jbaskin » Mon Sep 09, 2013 9:46 pm

Hey guys,

Does anyone know if it's better in Vertica to explicitly convert all data types to the same when doing comparisons, or is okay to let Vertica do it implicitly? I know this was always a big deal in Oracle...

Say I have two tables A and B:

Code: Select all

dbadmin=> create table a (a1 int);
CREATE TABLE
dbadmin=> create table b (a1 varchar);
CREATE TABLE
dbadmin=> insert into a values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into b values ('1');
 OUTPUT
--------
      1
(1 row)
Which of the following query is a better practice?

Code: Select all

dbadmin=> select * from a join b on a.a1 = b.a1;
 a1 | a1
----+----
  1 | 1
(1 row)

dbadmin=> select * from a join b on a.a1::varchar = b.a1;
 a1 | a1
----+----
  1 | 1
(1 row)

dbadmin=> select * from a join b on a.a1 = b.a1::int;
 a1 | a1
----+----
  1 | 1
(1 row)

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Implicit vs. Explicit Conversion

Post by nnani » Tue Sep 10, 2013 9:11 am

Hello Jbaskin,

I would go with Explicit Casting option.

Advantages

1. Easy code portability, No need to change your code for different environments
2. Easy readability and understanding of code.

I think you should Let Vertica do explicit casting while doing comparison.
More over You can give it check.
Use the timing option and test both of your scenarios with big result sets, you will surely find the difference.


Hope this helps.. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Implicit vs. Explicit Conversion

Post by JimKnicely » Tue Sep 10, 2013 3:26 pm

I agree with nanni!

I believe that it's always best practice to explicitly convert our data because who knows our data better?

The following example shows we should get better performance by explicitly converting our data, and in this case, converting to INT:

Code: Select all

dbadmin=> create table a (a1 int);
CREATE TABLE

dbadmin=> create table b (a1 varchar(1));
CREATE TABLE

dbadmin=> insert into a select randomint(10) from tables cross join columns cross join tables t1 cross join tables t2;
  OUTPUT
-----------
 217512000
(1 row)

dbadmin=> insert into b select randomint(10)::varchar from tables cross join columns cross join tables t1 cross join tables t2;
  OUTPUT
-----------
 217512000
(1 row)

dbadmin=> \timing
Timing is on.
Okay performance:

Code: Select all

dbadmin=> select count(*) from (select * from a join b on a.a1 = b.a1) foo;
      count
------------------
 4731146840901419
(1 row)

Time: First fetch (1 row): 35984.389 ms. All rows formatted: 35984.433 ms
Worst performance:

Code: Select all

dbadmin=> select count(*) from (select * from a join b on a.a1::varchar = b.a1) foo;
      count
------------------
 4731146840901419
(1 row)

Time: First fetch (1 row): 66358.395 ms. All rows formatted: 66358.427 ms
Best performance:

Code: Select all

dbadmin=> select count(*) from (select * from a join b on a.a1 = b.a1::int) foo;
      count
------------------
 4731146840901419
(1 row)

Time: First fetch (1 row): 27953.255 ms. All rows formatted: 27953.287 ms
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
Jbaskin
Intermediate
Intermediate
Posts: 61
Joined: Sat Jan 28, 2012 1:21 pm

Re: Implicit vs. Explicit Conversion

Post by Jbaskin » Thu Sep 12, 2013 3:53 pm

Excellent example and explanation, Jim! Thank you!!! This will finally end an argument I've been having with a co-worker :twisted:

Post Reply

Return to “New to Vertica Database Development”