• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Performance

 
Arjunkumar Shastry
Ranch Hand
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This question related to DB rather than JDBC
We have one table where datatype of column is string.(varchar).
We are pulling a data from this table and pushing it to a target table where column type is INT.There are two options of processing data in this column.
option1: Source table is A.

exeute the above SQL,get the data and then push it.
Second option is to fetch the data and then write the above logic in a program and then push it.
Which option do you think will have better performance?
[Two tables(or databases) are on two different machines and the program is on third machine.]Prgram will be written either in Java or PERL.

[ March 31, 2005: Message edited by: Arjunkumar Shastry ]
 
Sripathi Krishnamurthy
Ranch Hand
Posts: 232
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Which option do you think will have better performance?


if the query syntax is fine, SQL should yield better performance as compared to java.
java would involve select into resultset->get data from resultset->write logic to compare the fetched data->then assign the value to a variable->use that variable to construct a new insert statement->then insert into new table.
[ March 31, 2005: Message edited by: Sripathi Krishnamurthy ]
 
Arjunkumar Shastry
Ranch Hand
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I have not written that with clarity.
In the first option,After SQL execution,we are going to use java/perl to push the data.So question is "Should this comparison and assignment be done at SQL level as done in option 1 OR should this comparison and assignment be done in a program?"
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I will prefer to do it without java using DB link(specially case of Oracle)

but In case of JDBC I will go for your first approach.

You can also make a query like



then In my program I can calll resultset.getInt(PRIORITY) it will do the conversion for me provided there is no non numeric character in column.

even if a non numeric character is there, you will have to handle that in every case.

thanks
[ March 31, 2005: Message edited by: Shailesh Chandra ]
 
Arjunkumar Shastry
Ranch Hand
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks.In PERL we can do it by simple function int(arg) which will convert string to int.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic