C# and Oracle DB RRS feed

  • Question

  • Hey everybody,
      I'm working with an Oracle DB. I have about 10 queries that retrieve data from the DB and each query returns about 30K records. I want to ask you what is the fast way to retrieve those data (I have to export the data to an excel file and then to save all the data in one DB table).
      My other question is what is the fast way to save all the queries results to a one table? Shall I run each and each row and insert it to the table? Is there another fast way to do this task? (My DB is placed in another country so it took a lot of time to retrieve the data and save it).
     I hope you can help me solving this issue.. Looking forward to hear from you all.
    Best regards,
    Wednesday, July 25, 2007 10:10 AM

All replies

  • You want to put all the results in one table so I would guess all queries are returning the same number of columns with the same headings etc.


    If so it might be nicer to write a stored proc that creates a temp talbe, inserts all your 300,000 rows into it and finally returns all the rows so you only have one large result set returned, use a reader to read into a datatable.


    If you can't create a stored proc and your queries are instead in your code, then I guess you would be best off to get 10 result sets back and have ten datatables. If they do have the same structure then I imagine there is a function already in .Net that allows you to merge tables or copy one to the other.


    Now you have all the data in one large datatable you can write it out to excel or straight to your new DB.


    If performance is an issue then you could load each datatable in separately and advise the user how many datatables are left but just show it as a count down from 10 to 1 so a user can see progress. Also an animated gif that makes it look like an app is busy goes a long way Smile

    Wednesday, July 25, 2007 10:40 AM
  • I can't build a stored procedure. I just have to do everything is the code. I got one DataSet and 10 OleDbDataAdapters for each query. After I retrieve the results of all the queries, I should add to each OleDbDataAdapter another two columns (with the different data for each query - for example, query id that returned the current row). If I understood your idea well, you think it will be much easy and faster to merge all the DataTables into one DataTable and then to transfer the rows of this DataTable one by one to the DB, is that right?
    About your last tip, about the UI, I tried to do it as you suggested, but when the program starts to rerieve the data from the DB (which is in a far country) the program stuck and I won't be able to see anything until the data will return to the SELECT query.
    Wednesday, July 25, 2007 11:23 AM
  • If you want to put the amalgamated results into a single table in the database then you want this done server-side rather than getting the data to the client, amalgamating it and sending it back to the database. This is particularly important if the database is remote.

    For the best performance you could create your target table and insert the result rows in a single operation by using "Select into". This will ensure that the inserts to the table are minimally logged, which for 300,000 inserts will save a reasonable amount of time. Alternatively you can do the insert into an existing table, it'll work but it'll be slower.

    The entire set of records you're after can be retrieved in a single query by using the "Union" command to combine the results of the separate select statements. In addition each select statement can include a "QueryId" column, e.g. "Select blah1, blah2, QueryId = 1 From MyTable". For the second query you use "QueryId = 2" and so on.

    All of this can be done without writing a stored proc. Then you can just select the contents of that table and do what you need to do to get it into Excel.

    I know this reply's a little late; I only just saw your post, but hopefully it'll still be of use.



    Edit - I forgot to mention, my expertise is MS Sql Server so I can't guarantee that everything I've mentioned will work for Oracle, although there's a good chance it will.

    Monday, July 30, 2007 5:23 PM
  • Thanks Sean for you advice, but i think I wasn't too clear.. The table I read the data from it is in other DB, so I need to retrieve the data from one DB and to save it in other DB and to generate an excel file with those data. There is no DB Link between those two DBs.. What is the must efficient and fast way to do this?
    Thanks alot..
    Tuesday, July 31, 2007 7:51 AM
  • Ahh yes, I hadn't realised the source and target were on different, unconnected servers.

    It's still not going to be efficient to have your app act as the middle-man between these two servers. Ideally you want the target server to query the source server and put the results into its own table, preferably using the union and select into method I mentioned earlier.

    So basically your app runs some sql on the target server which queries the source server, populates its own table from the results and sends the contents of the table back to your app.

    I'm not familiar with Oracle and so don't know how to get the target server to query the source server, but this is functionality that I think is standard in all enterprise-level database servers.



    Tuesday, July 31, 2007 9:01 PM