It's generally the sign of an overcomplex query on a schema not designed to service it. Normally, any sign of WITH in a query has my hackles up. Instead, we need a technique called a "Modifying Common Table Expression", or "Modifying CTE". You could use a trigger here - that will absolutely work, but I find triggers that are beyond really trivial cases to be astonishingly difficult to test and debug. You can put multiple rows into a single table ( INSERT. Sadly, there's no such thing as INSERT MULTIPLE. What we're going to do is make the round-trips go away - and maybe make things a bit faster. There's other posts that will take you through the delights of query optimisation - I hope, and if not, then nag me and I'll write one. If the round-trip time to the database is 5ms, then that code above will take a minimum of 10ms, no matter how fast the database and app service is. The latter, though, we have little control over. The former we have some control over we can write things more efficiently, add database indexing, boost instance sizes and whatever else needs doing. Inserting records one statement at a time is more time consuming and less efficient than inserting multiple rows at once. Also it simplifies and speed up the deletion of the old data. If you will create partitioned table, in that case you will have smaller indexes per partition, and because of that insertion speed will be high. There's "How long something takes to execute", and "How far is it away". You don't have to drop the primary key, as there is another solution: partitions. All you have to do is, provide multiple rows after VALUES keywork in the INSERT. The moment we introduce a network connection, we end up with two key parameters for performance. We can also insert multiple rows into PostgreSQL table using a single query. So why am I going to suggest changing it? Maybe it's more complicated, maybe it has SELECT statements and all sorts.īut it works. Enter fullscreen mode Exit fullscreen mode
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |