![]() ![]() ![]() These tables are visible and accessible within the session only. Temporary tablesĪre stored in the TempDB database with a unique object name and created on a connection These objects will be created in the TempDB system database. You need to add prefix '#' for local temporary tables and '#' for global temporary Some more information about your execution plans will be necessary to provide finer advice.SQL Server includes the two options for temporary tables: because this might allow for an Index Only Scan for the source part of your update, but you won't be certain unless you try it. I don't think this will make much of a difference. This makes sense only if the length of detail is moderate. This parameter cannot be set for TOAST tables.Ĭonsider having a covering index on your temporary table. ![]() For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage the remaining space on each page is reserved for updating rows on that page. The fillfactor for a table is a percentage between 10 and 100. So, make sure your tables have a fillfactor less than 100. PostgreSQL should be able to perform a Heap Only Tuple (HOT) update, and the indexes wouldn't have any big effect.įor HOT updates to succeed, you need some free space in your tables. The way your query is working (only updating the column detail), if detail is not part of any index, this wouldn't have much of an influence. Check the explanations about Unused Indexes on PostgreSQL wiki. Try to find out whether you really need all the indexes in your setup. It's not normally a good idea to try to disable those (and doing it is not straightforward, if possible at all). Take into account that, on top of your 14 triggers, the database might have added some more to make sure all constraints are met ( CHECK, REFERENCES, UNIQUE. My experience is that any kind of logging or auditing your inserts might make the process slower by (easily) a factor of 10. If there are a few that consume a lot of time, have someone qualified revise them, optimize them, and even if necessary, rewrite them using C. You can find a few triggers hurting (a lot). Then reenable them one by one, and see which effect each one has on timings. If you have possibility of checking with a development machine, test disabling all the triggers, and see what effect it has (time your queries!). Processing triggers is normally time-consuming, specially if they're written in any interpreted language (which means, more or less, they're not written in C). Given that your UPDATE is quite simple, my first guess is your triggers are hurting your performance. Things that can be done, although whether they help or not. ![]() Is there anything I can do to improve the efficiency of my update? I strongly suspect the bottle-neck is in one ore more of the triggers, however I'm not able to remove/modify those triggers. Larger files will take way too long.Ī few details about the products table if they matter: Normally 20 seconds for an update is tolerable in my workplace, but the 2000-row file is a small sample I'm using for testing. I'm still right where I left off - 20 seconds for 2000 rows. I took one more stab at it by using both a BTREE and HASH index hoping that the CLUSTER would use the BTREE and the UPDATE would use the HASH: CREATE INDEX tmp_products_btree_idxĬLUSTER tmp_products USING tmp_products_btree_idx Īnd again, nothing helped. So I modified the queries in the program to use a BTREE index and then CLUSTER/ANALYZE: CREATE INDEX tmp_products_idxĬLUSTER tmp_products USING tmp_products_idx Then I thought perhaps clustering the table would help, but that meant I couldn't use the HASH index. Neither index improved the update time significantly. CREATE INDEX tmp_products_idxĬreating a HASH index: CREATE INDEX tmp_products_idx To speed things up, I tried the following with very little success:Ĭreate a BTREE index on the temporary table. The temporary table looks like this: CREATE TEMPORARY TABLE tmp_products (Īnd my update query is really straightforward: UPDATE products This all works fine, except the UPDATE query takes ~20 seconds for a small file of ~2000 rows. Updates the actual table from the temporary table.COPYs the modified data into the temporary table.Creates a temporary table to hold the processed file data.Performs certain modifications and creates an in-memory file.(I'll use trimmed-down examples for the sake of brevity.) I've written a small program to import product-detail updates from file which takes much longer than expected. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |