4 Replies - 812 Views - Last Post: 30 June 2015 - 01:14 PM Rate Topic: -----

#1 Mattjp213   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 26-February 15

Best Practices for Pulling Huge Amount of Data From Database

Posted 24 June 2015 - 07:37 AM

I have more of a theory question here so I won't have any code in the post. I need to do a one time data pull from our server at work to get around half a million records. The query needs some user input and then the user will push a button that will process the query and store the results in a DataTable for the program to do its work from. The query I have created is fairly complex and lengthy and it does work, but it takes 5 plus minutes to complete execution. I am using Data.SqlClient methods to open a connection and execute the query, but for this to work, I have to set my timeout to over 5 minutes in my connection string and I feel like that is not good practice.. Maybe I am wrong. I have been doing some research and haven't found any solutions that stand out as definitively correct so I was wondering if anyone might be able to point me in the right direction.

Is This A Good Question/Topic? 0
  • +

Replies To: Best Practices for Pulling Huge Amount of Data From Database

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15331
  • View blog
  • Posts: 61,450
  • Joined: 12-June 08

Re: Best Practices for Pulling Huge Amount of Data From Database

Posted 24 June 2015 - 07:43 AM

I doubt there is much labeled "definitively correct".. if that's what your app needs to do then that's what your app needs to do. The timeout can be changed for a reason, and this sounds like one of those reasons. Query optimization maybe something to look into, but if this is a one time throw away project then what ever.. let it roll.
Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7494
  • View blog
  • Posts: 15,529
  • Joined: 16-October 07

Re: Best Practices for Pulling Huge Amount of Data From Database

Posted 24 June 2015 - 07:44 AM

If you're storing half a million rows in a DataTable in memory, it's probably more of a client problem than a database problem. Pulling the data is pretty simple. You can do it one row at a time. When the database gives you the first row, it's work is mostly done and it's just waiting to complete it's transaction.

Fundamentally, the best practice for dealing with a huge amount of data in a database it to leave it in the database. You don't load huge data sets into memory and process them there, you make the database do that processing and query the result.
Was This Post Helpful? 0
  • +
  • -

#4 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Best Practices for Pulling Huge Amount of Data From Database

Posted 30 June 2015 - 12:46 PM

FYI, .5m isn't a "huge" amount of data. It seems like it at first, but that's not an unreasonable dataset to have to work with. Loading it all directly into memory might not be the best idea, of course, depending on the size of the rows themselves and the specifics of the app itself. If, as modi mentioned, the app is a single-use tool, then I would forget about the five-minute load time and just make it work. If this is something to be re-used every day/week/month...maybe it's worth investing the time to speed it up.

As previously mentioned, is this an operation that can actually be pushed to the database itself? As in, do you have to query, load, process, push? Or could you actually write the query in such a way that it does the operation on the server instead of pulling the data to the client? Databases are designed to do "the heavy lifting".

Second, is your query as optimized as it can be? Nested selects, views joining views that are made from tables joining tables, stuff like that? Are you indexing your table in such a way that your query is using your index (just because you've applied an index doesn't mean that the query is using it, check the execution plan).
Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7494
  • View blog
  • Posts: 15,529
  • Joined: 16-October 07

Re: Best Practices for Pulling Huge Amount of Data From Database

Posted 30 June 2015 - 01:14 PM

View PostCurtis Rutland, on 30 June 2015 - 03:46 PM, said:

FYI, .5m isn't a "huge" amount of data. It seems like it at first, but that's not an unreasonable dataset to have to work with.


This entirely depends on the rows, actually. :P

Processing on the client should not be more efficient than processing on the sever, in any case. If it is, then you do have a server problem.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1