get data from Oracle Database !
-
Hi all ! I am building a form in VB.net. The function of this form is get all data from one table in Oracle Database and export or write into Fox format. The table in Oracle Database is very large. When I using the following : With cm .... .Parameters.Add("bg", OracleType.Cursor).Direction = ParameterDirection.Output end with adp.SelectCommand = cm adp.Fill(dataset, "bill_detail") but this command execute very very low. I need all data from the table (about 20 million rows), how can I do ?? Pls, help me !
-
Hi all ! I am building a form in VB.net. The function of this form is get all data from one table in Oracle Database and export or write into Fox format. The table in Oracle Database is very large. When I using the following : With cm .... .Parameters.Add("bg", OracleType.Cursor).Direction = ParameterDirection.Output end with adp.SelectCommand = cm adp.Fill(dataset, "bill_detail") but this command execute very very low. I need all data from the table (about 20 million rows), how can I do ?? Pls, help me !
Hi, A few things, are you only querying the database? Is this application an intranet/exrtranet or internet application? If intranet why not use a string to build your sql instead of a stored procedure? Is this a one time export, how about doing the intial export within Oracle create a flat to export to Foxpro(I believe) and then create a a procedure that will only replace or add new records going forward. I don't know your server threshold but 20 million rows seems like an awful lot and multiple this times the number of columns could be a monster. Hope this helps!! ;)
-
Hi all ! I am building a form in VB.net. The function of this form is get all data from one table in Oracle Database and export or write into Fox format. The table in Oracle Database is very large. When I using the following : With cm .... .Parameters.Add("bg", OracleType.Cursor).Direction = ParameterDirection.Output end with adp.SelectCommand = cm adp.Fill(dataset, "bill_detail") but this command execute very very low. I need all data from the table (about 20 million rows), how can I do ?? Pls, help me !
Hi, I have nearly the same situtation. Here are some things you can try out: 1. Use ODP.Net (coming with any 9 or 10 oracle db) instead of the in-built Oracle provider of the .net framework. Its by far faster. 2. If you switched to OPD.Net consider changing numeric fields to the new binary field types (coming with oracle 10) like 'BINARY_FLOAT'. They reduce the datasize and thus the time needed to transfer the data. 3. Rethink if you really need all the data in one big DataTable. I dont think any user will look into all your data. Consider using a DataReader instead.
-
Hi, A few things, are you only querying the database? Is this application an intranet/exrtranet or internet application? If intranet why not use a string to build your sql instead of a stored procedure? Is this a one time export, how about doing the intial export within Oracle create a flat to export to Foxpro(I believe) and then create a a procedure that will only replace or add new records going forward. I don't know your server threshold but 20 million rows seems like an awful lot and multiple this times the number of columns could be a monster. Hope this helps!! ;)
Joe Contreras wrote: why not use a string to build your sql instead of a stored procedure? :omg: :doh: SMACK! :mad: Being on an Intranet is no excuse for being lazy and cutting this corner! SQL Injection attacks CAN and DO originate from inside corporate networks, not just the Internet. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
Joe Contreras wrote: why not use a string to build your sql instead of a stored procedure? :omg: :doh: SMACK! :mad: Being on an Intranet is no excuse for being lazy and cutting this corner! SQL Injection attacks CAN and DO originate from inside corporate networks, not just the Internet. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
My mistake for not being clear or making that statement. Using the query would be use for troubleshooting, i.e. to determine if the issue is in the stored procedure. That's all I meant when I said to use a SQL query. You need to look at all avenues when you run into issues like this and I don't know how the sproc code base looks like. I'm just wondering why millions of records are being returned. BTW I've been in some shops where they don't allow writing any objects (sprocs,views) to the Oracle database, SQL is the only option. Thanks,