So I have this idea of scheduling sql server agent to create some #temptables in parallel before mass updating my large table (roughly 30Gb) to save some time. Currently, it took about 2 hours for the whole process. I wonder if this the 'right' thing to do or should I be aware of other approaches.
-
How would temp tables help? Pre-calculating the values needed? So that you would then just update your main table, without actually calculating anything?Yano_of_Queenscastle– Yano_of_Queenscastle2024-04-11 20:31:04 +00:00Commented Apr 11, 2024 at 20:31
-
It will then be use for some inner join sequences with the main table for updateD H– D H2024-04-11 20:33:10 +00:00Commented Apr 11, 2024 at 20:33
-
You say "#temptables" - but I'm wondering if you understand the difference between "#" temp tables (which exist only within individual user sessions) and "##" temp tables (which exist globally), because if you have lots of different SQL Agent jobs that create "#" temp tables, then they won't be accessible by a subsequent separate query sessions. "##" global temp tables will - however, there are often particular considerations when using global temp tables.Craig– Craig2024-04-12 03:10:06 +00:00Commented Apr 12, 2024 at 3:10
-
Other considerations in having a bunch of, essentially, "disconnected" SQL Agents jobs that are all running in parallel (to improve your overall processing time) would be handling errors, and also knowing that all of the necessary separate processes have completed successfully before you do your "large table update"Craig– Craig2024-04-12 03:12:08 +00:00Commented Apr 12, 2024 at 3:12
-
Also to add on to @Craig 's point, even global temp tables would probably not work so well since they're destroyed when no session references them anymore. Depending on the timing of when your SQL Job runs vs when another session of your main process tries to access them, they may not longer exist at that point. Finally, I'd say creating a temp table is not the reason your process takes 2 hours. It takes an immeasurably short amount of time (nanoseconds?...maybe less) for SQL Server to create a temp table. Unless you're planning to include the loading of data into it as part of your SQL Job.J.D.– J.D.2024-04-12 14:20:54 +00:00Commented Apr 12, 2024 at 14:20
|
Show 2 more comments
1 Answer
Yes you can pre-populate tables already created in your update process but I suggest you drop/create permanent staging tables rather than temp tables. You can then create indexes on these tables that should speed up the main update process later on.
Please see the MSSQL Tips site "SQL Server Staging Table vs Temp Table" for more info : https://www.mssqltips.com/sqlservertip/6041/sql-server-staging-table-vs-temp-table/#:~:text=Permanent%20tables%20used%20to%20store,a%20suite%20of%20staging%20tables.
