locked
Retrieving large amounts of data from SQL Server Database - ASP.NET RRS feed

  • Question

  • User-322275084 posted

    Hello, I'm work on my project on ASP.NET (C#), I have Sqlserver database that could contain large number of records (Items that the users have), I need to read the appropriate records according to a key that the user entered, Then i need to put all this matched records into a list to perform some operations on them later, The problem is that this number of matched records can be very large (by the time it can be 100,000,000 and even more), and it will take alot of time to insert all of them into a list and perform some operations on all of them using a loop. What i can do to minimize the execution time?

    Any suggestions?

    Thanks for help!!

    Wednesday, October 3, 2018 7:37 AM

Answers

  • User61956409 posted

    Hi RRT955,

    The problem is that this number of matched records can be very large (by the time it can be 100,000,000 and even more), and it will take alot of time to insert all of them into a list and perform some operations on all of them using a loop.

    Can you clarify your actual scenario and requirement that requires returning about 100,000,000 records at a time? As you mentioned, the query would take long time to return large number of records, it is not a good user experience. 

    What i can do to minimize the execution time?

    You can try:

    • avoid table scans (a table scan might be caused by queries that don't properly use indexes)
    • reduce the amount of data that you transfer over the network by using queries that select just the fields you need
    • if possible, query and return the paged result/dataset instead of return all matched records at a time

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 4, 2018 8:52 AM

All replies

  • User61956409 posted

    Hi RRT955,

    The problem is that this number of matched records can be very large (by the time it can be 100,000,000 and even more), and it will take alot of time to insert all of them into a list and perform some operations on all of them using a loop.

    Can you clarify your actual scenario and requirement that requires returning about 100,000,000 records at a time? As you mentioned, the query would take long time to return large number of records, it is not a good user experience. 

    What i can do to minimize the execution time?

    You can try:

    • avoid table scans (a table scan might be caused by queries that don't properly use indexes)
    • reduce the amount of data that you transfer over the network by using queries that select just the fields you need
    • if possible, query and return the paged result/dataset instead of return all matched records at a time

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 4, 2018 8:52 AM
  • User-322275084 posted

    I explain my scenario: The user enter some words on the search bar, i need to return the items that their name is more or less consists of or equals to the text that the user entered, the number of items that their name can consist of the text that the user entered can be very high number, i retrieve from the database all the items and count them, i display on the page 10 records, then i put kind of navigation bar on the page with buttons so the user can view all the records, i explain myself: if there are 100 results, i display the first 10 results on the page and i have 10 buttons on the navigation bar, if the user click on the second button of the navigation bar the second 10 results are displayed on the page i.e: the results with index greater than 10 and lesser than 21 in the list.

    Why i need to retrieve all the matching results at a time?

    because i need to know the number of the matching results so i know how much buttons to put on the navigation bar.

    how to do that very fast?

    Thanks in advance!!

    Thursday, October 4, 2018 10:17 AM