Opening/Closing Connections - Efficiency
-
Hello :) I have a program which must read a directory of files and update a database. I can open my connection at the program start and run the process but it may take a couple of minutes or so. Is is worth opening and closing the database connection for each record I update or can I just hold the connection open for minutes at a time and close it at the end of the process? Thanks :)
-
Hello :) I have a program which must read a directory of files and update a database. I can open my connection at the program start and run the process but it may take a couple of minutes or so. Is is worth opening and closing the database connection for each record I update or can I just hold the connection open for minutes at a time and close it at the end of the process? Thanks :)
You should use connection pooling when you create the connection (creating is the slow bit) and close the connection after each write. This will put the connection into the pool and the next write operation will retrieve and use the same connection. You could also put all the filenames into a table and bulkcopy them into the database.
Never underestimate the power of human stupidity RAH
-
You should use connection pooling when you create the connection (creating is the slow bit) and close the connection after each write. This will put the connection into the pool and the next write operation will retrieve and use the same connection. You could also put all the filenames into a table and bulkcopy them into the database.
Never underestimate the power of human stupidity RAH
-
Hello :) I have a program which must read a directory of files and update a database. I can open my connection at the program start and run the process but it may take a couple of minutes or so. Is is worth opening and closing the database connection for each record I update or can I just hold the connection open for minutes at a time and close it at the end of the process? Thanks :)
The answer Mycroft Holmes gave is a good and correct way to go. The only reason I see to keep the connection open in the program is if you have transactional needs over separate writes. For example: if you need a logic where every record is written succesfully to the database or no records at all (commit point is after all separate writes).
The need to optimize rises from a bad design. My articles[^]
-
Hello :) I have a program which must read a directory of files and update a database. I can open my connection at the program start and run the process but it may take a couple of minutes or so. Is is worth opening and closing the database connection for each record I update or can I just hold the connection open for minutes at a time and close it at the end of the process? Thanks :)
Yeah, what they said. I definitely wouldn't open the connection any sooner than necessary, but I also wouldn't close it any sooner either. I prefer to get all the data collected and ready to go, then open, process all the stuff, and close. If holding all the data in memory at once isn't an option, then opening and closing for each may be a good idea.