none
Loading DB record's data on the Dictionary RRS feed

  • Question

  • I'm making codes for image backup.

    Image path's are in a DB table A of which record count is hundreds of thousands.

    I will make another DB table B of which fields consists of image_path and backup_done...

    Prior to backup, I need to scan files to check if the image file is corrupt.

    However I'm worrying about speed issue because the record count of table A and table B is big.

    I'm thinking that I could reduce time if I use dictionary class and load the table B's data on the  dictionary.

    Can anybody advice if this is good solution or is there any better way?

    Wednesday, April 25, 2018 11:08 PM

Answers

  • I would entertain the idea of using a temporary table if this is being done with SQL-Server rather than place data into any container like a dictionary. Inspect one image at a time rather than load all into a container in code.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Jeff0803 Wednesday, April 25, 2018 11:57 PM
    • Unmarked as answer by Jeff0803 Wednesday, April 25, 2018 11:57 PM
    • Marked as answer by Jeff0803 Wednesday, April 25, 2018 11:57 PM
    Wednesday, April 25, 2018 11:44 PM
    Moderator
  • MS-Access does not support temp tables. You could use INSERT INTO ... SELECT. Or create a table, use and then drop. But the main issue is nothing is going to stop the access database from bloating so compacting will be needed over time. You could copy the MS-Access table data to a temp table in SQL-Server and do what is needed there.

    Another option for MS-Access is to have a blank/new database one folder down from the app, copy it to the app folder, do your work in there then when done delete that database thus no bloating.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Jeff0803 Thursday, April 26, 2018 4:19 PM
    Thursday, April 26, 2018 9:34 AM
    Moderator

All replies

  • I would entertain the idea of using a temporary table if this is being done with SQL-Server rather than place data into any container like a dictionary. Inspect one image at a time rather than load all into a container in code.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Jeff0803 Wednesday, April 25, 2018 11:57 PM
    • Unmarked as answer by Jeff0803 Wednesday, April 25, 2018 11:57 PM
    • Marked as answer by Jeff0803 Wednesday, April 25, 2018 11:57 PM
    Wednesday, April 25, 2018 11:44 PM
    Moderator
  • My code handle 2 database(MS Access 2012 and MS SQL Server 2012)

    I'll check if MS Access supports temporary table.

    Is the temporary table faster than other regular table?

    If MS Access doesn't support it, which way would be better?

    My code handle MS Access & MS SQL in the same code like.

        if (db_type == MSACCESS)
       {
            //SQL for MS Access
       }
       else if (db_type == MSSQL)
       {
            //SQL for MS SQL
       }
    
    


    So I need to apply the same way to the Access and SQL Server.


    Thursday, April 26, 2018 12:08 AM
  • MS-Access does not support temp tables. You could use INSERT INTO ... SELECT. Or create a table, use and then drop. But the main issue is nothing is going to stop the access database from bloating so compacting will be needed over time. You could copy the MS-Access table data to a temp table in SQL-Server and do what is needed there.

    Another option for MS-Access is to have a blank/new database one folder down from the app, copy it to the app folder, do your work in there then when done delete that database thus no bloating.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Jeff0803 Thursday, April 26, 2018 4:19 PM
    Thursday, April 26, 2018 9:34 AM
    Moderator