ResultSet to Table
Hi..
I'm connecting to a SQL Server 2000 database using a type 4 JDBC driver.
I have a table containing over 1.6 million rows(8 fields).
One of the tasks of my application is to sort this huge table on the basis of two fields and group the rows in ascending order...the query takes abt 2.5 minutes to execute and i have the ResultSet to work with.I'm using cursors and a bit of virtual memory to execute this.
If i need to store this ResultSet back into the database as a new table(i don't want to update the original table),how do I go about getting it done quickly?..
Will i have to loop through the entire ResultSet and perform an INSERT over the network?Are there any better approaches?
I'll really appreciate any help...
Thanks a lot..
Jay.
[818 byte] By [
jayd23] at [2007-9-26 4:40:29]
Hi Jay,
You could try disabling autocommit then doing a batch update - this will cut down on the number of trips to the database so should speed things up. But, you will still need to loop through your records to add each row to the batch list.
Having said that, if all you're doing is sorting the rows into a new table can't you just do it all in the database using a stored procedure - let the db do the hard work for you?
Richard
Rijaos at 2007-6-29 18:02:04 >
Hi Richard,
Thanks for the reply...i did contemplate using stored procedures but didnt have much info on them to start with.
The prototype i'm working on aims to be database independent in the later revisions.
Can stored procedures be made database independent?
Right now,i'm using SQL Server 2000 for starters,but plan to be db-independent.
Thanks again!!
Jayadeep.
jayd23 at 2007-6-29 18:02:04 >
you can use Java Stored Procedures and hope to achieve independence, but then you are using MSSQL!
mchan0 at 2007-6-29 18:02:04 >
If possible, can you create a new table using SQL? Use the create table command with a subquery to copy an existing table in full or in part. I'm not sure if this is a proprietary(Oracle) command or universal SQL but give it a try
Statement s = con.createStatement();
s.executeUpdate("CREATE TABLE new_table AS select column1,(column2*1.15) FROM original_table where column2 < 50555");
Jamie
and you could also tag an "order by" clause into the statement to order the rows in your new table. Just wondering why you need to duplicate the data? It seems like a view may do the trick?Jamie
At the very least this can be done using a stored procedure even in MS SQL.And it would probably be orders of magnitude faster.
Hi Jamie..
Thanks for the suggestion..i tried the querystring out but it doesn't work in SQL Server..i guess its ORACLE specific..
There is a need for the original table to be kept intact...further...i need to alter this new sorted table and add 4 more fields to it and later fill them with values calculated on the basis of the existing fields..
I also found out that SQL Server won't allow ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword in creating views...so can't use this to sort..
I believe that the syntax for stored procedures varies with each DBMS..
i hope i'm right in assuming that the stored procedure for the same functionality for each target dbms will differ a bit ..
Thanks again!!
Jay.
jayd23 at 2007-6-29 18:02:04 >
> I also found out that SQL Server won't allow ORDER
> BY, COMPUTE, or COMPUTE BY clauses or the INTO
> keyword in creating views...so can't use this to sort..
And you found this out how?
The documentation for create view has a 'order by' clause as one of the examples.
Try this SQL statement:
SELECT source_table.* ,source_table.column1*source_table.column2 as new_column_name
INTO new_table
FROM source_table
ORDER BY some_columns_from_source_table
It works with MS SQL SERVER 2000 (like create table table_name as select ... in ORACLE). You can use SELECT INTO to combine data from several tables or views into one table. Keep in mind that when a computed column like new_column_name is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are just the values that were computed by the select statement. You can use the last for creating some new columns from existing data.
johnkv at 2007-6-29 18:02:04 >
Hi John..The statement works fine in SQL Server 2000...but it does give a different arrangement of ordered groupings after each call to the same table when i use 2 fields for the order by clause...anyway that's fine by me right now..Thanx.
jayd23 at 2007-6-29 18:02:04 >
