Prashanth Bhanu

Ranch Hand
+ Follow
since Sep 25, 2003
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Prashanth Bhanu

Hi All,

I've a table TXN_SC have many columns in it and contains about 160,000 records. Constarint set on this table is one columns Id,gpcode,loc,le_id,ext_id.

One more column called sys_id which is not a part of key along withn this there are other several columns.

I have many duplicates records for the combiantion of Id,gpcode,loc,ext_id,sys_id,ext_id,le_id and all these duplicate rows need to be retained .

But duplicate records fro the combiantion of Id,gpcode,loc,ext_id,sys_id,ext_id and whose le_id are different such rows need to be deleted.

I tried some thing like this

delete from TXN_SC sc where (sc.loc,sc.ext_id,sc.sys_id,sc.le_id) in (select a.loc,a.ext_id,a.sys_id,a.le_id where Id=? and gpcode=? group by a.loc,a.ext_id,a.sys_id,a.le_id having count(*)=1)

This deletes even those records whose occuarance is only one in entrire set.Please sugest how to go about it.

Thanks











15 years ago
Hi Friends,

I've a table ORG_TABLE has columns CU_ID,ACC_ID,PORT_ID,SUPPORT_ID,REAS_NAME. I also have a table TEMP_TABLE having columns CU_ID,ACC_ID,PORT_ID,SUPPORT_ID. ORG_TABLE got about 45000 records and TEMP_TABLE has about 10000 records.

While all the columns for a row in TEMP_TABLE are not filled all the time.For example for one row CU_ID and PORT_ID having values.For another row CU_ID and SUPPORT_ID having values.It may also be like ACC_ID and PORT_ID having values and rest are null.So such combinations will exist in TEMP_TABLE.

I'm looking for single query which would be some thing like(considering first row values from TEMp_TABLE) UPDATE ORG_TABLE set ORG_TABLE.REAS_NAME=TEMP_TABLE.REAS_NAME where ORG_TABLE.CU_ID=TEMP_TABLE.CU_ID and ORG_TABLE.PORT_ID=TEMP_TABLE.PORT_ID.Similary the update should execute for the second combinations and so on for all the records in TEMP_TABLE.Dynamically query should be formed.

Thanks in advance



15 years ago
Hi Paul Sturrock,

Thanks,Well Excel part is taking very minimum time.Data base querying and updating part is taking time.I just queries the database during the updates each record updation is taking about 1 second.

I can't remove the index is because i dont expect duplicate records in the table.

I'll try distributing the load among multiple JVM instances

I'm not clear on database part asynchronous.Can you please elobarate.

Thanks and Regards,

Prashu
Hi Paul Sturrock,

Thanks for your reply.If i'm being very optimistic then i'lll be more than happy to have a relaistic solution for the above said problem.Ideally i shouldn't take minutes together.

Thanks and Records,

Prashu
Dear Friends


I upload a excel file which has about 30,000 records(having columns data1,data2,data3 etc..).Values in data1 column looks some thing like {XXXX-XXX-ABCD}
and so on . And my database table TXN_DATA is having about 200,000 records(data1,data2,dataK...dataZ).
While file is being uploaded based on some logic i would derive one more value like XXXX-XXX-ACDB.Now i have two values of data1(XXXX-XXX-ABCD and XXXX-XXX- ACDB) .By using these two values i must query the database table to verify for a match.

1) If match is found and the match was for XXXX-XXX-ABCD(which i obtained from excel file) then all values fecthed from excel row for this particular row is updated in TXN_DATA table
2) If match is found for XXXX-XXX-ACDB which is a derived value then data1 column will be updated with XXXX-XXX-ABCD and all other values where
TXN_DATA.data1='XXXX-XXX-ACDB'

Since i'm doing this activity row by row it takes about 1Hour 30 Minutes to process(for each row one select and one corresponding update is fired).

what did i do => 1) obtained the excel records in to a collection
2) looped through the collection
3) for each object of the collection i do the above said logic
4) query the database to see wether the record exist and fetch the record(using hibernate,fecthing Entity object)
5) update that row using plain sql(using org.springframework.jdbc.core.JdbcTemplate. and calling update method on it
6) i'm connecting to oracle 10G database


Here i'm seeking your valuable advice to do it more efficiently and it shouldn't take more than 5-10 seconds.Do you guys have any
suggestion for me to tackle the issue.Please help and Thanks in advance

Thanks and Regards,

Prashu
Hi Friends,

Problem is resoved.I'm posting solution if it may be of some use for somebody in the future.



Instead of keeping the binary content in memory(which needs huge memory incase of large data).A temperory file is created to temporarly keep the data before writting.

Hope this helps......

Thanks
Prashu
Hi Friends,

I'm fetching huge number of records say 140,000 records from the database and write it to excel.I'm using jxl for this purpose.

But i keep getting

CoordinatorCo W HMGR0152W: CPU Starvation detected. Current thread scheduling delay is 7 seconds.


and while calling



I'm facing memory issue found in the server console.

Exception data: java.lang.OutOfMemoryError
at jxl.write.biff.MemoryDataOutput.write(MemoryDataOutput.java:72)
at jxl.write.biff.File.write(File.java:149)
at jxl.write.biff.RowRecord.writeCells(RowRecord.java:324)
at jxl.write.biff.SheetWriter.write(SheetWriter.java:479)
at jxl.write.biff.WritableSheetImpl.write(WritableSheetImpl.java:1431)
at jxl.write.biff.WritableWorkbookImpl.write(WritableWorkbookImpl.java:915)

I'm getting records in batch of 30,000(inside do loop).And making sure each worksheet will have not more than 60000 records.I strongly suspect

Because all the queries are fired inside the loop and the above error occurs when write method is called.I've posted part of my code as shown below.



I appriciate your help in this regard.Thanks in advance

Prashu




Hi Ulf,

The problem is,the file is not getting downloaded to the client.

Thanks
15 years ago
Hi Ulf Dittmer,
Well i had manipulated the code to avoide unnecessary codes of ResourceBundle while posting.Infact,fullpath variable includes the file name and path of the file.

1.file which i'm trying to download is available
2.I'm not getting any exception at the server or at the client.




15 years ago
Dear All,

I'm trying to download a bad file which is being generated by the sql loader.Code depicted below of my servlet has downloaded .log file perfectly but .bad got a problem in getting download.I suspect


for unrecognized file types.

Can anybody help me in solving this issue please.



thanks in advance
15 years ago
Hi,

Below is the code i used to upload the file


Thanks
15 years ago
Hi Bear Bibeault

I aplogise for using inappropriate subject.I'm not allowed to modify as this feature is only available for "administrators".Please suggest

Thanks
[ September 09, 2008: Message edited by: Prashanth Bhanu ]
15 years ago
Hi All,

I'm sending zip file through DataStream from the client and at the servlet i'm retrieving it some thing like following code



upon recieving i would write it to the writer to reconstruct the ziped file.
Well,my question is if i were to send along some other parameters then how to go about doing so?.Can somebody clarify this please.
[ September 09, 2008: Message edited by: Prashanth Bhanu ]
15 years ago
Hi All,


I'm still having trouble in uploading the Excel file to load to the server.Do we have to do any server specific setting(like MIME etc..) for the server to accept GZIP files.....I'm not sure where the problem is...

Thanks in advance
15 years ago