| Index would be your solution, but looks like you on v <11. You are right, I’ve seen a dramatic performance change in SQL Server when I used temporary table and then dynamically build an index on required columns and use it in the following queries. I think it’s because in some cases SQL Server does a poor job on query optimization.
In case if you are using v <11 you can try to build index with three columns Create index <index name> on table2(x, field2, field1);
Make sure the column which you are not using in where clause conditions comes last.
Then play with execution plan to check if it uses your index or not. Also try to not use a subquery but table straight forward. Haven’t worked with v<11 a lot, wouldn’t comment on it.
Could you show me the modified query using with and join??
Hi Jose,
If table2 is really big and you run this update on regular base just make sure you have a covering index on pair x and field2 with field1 in include option, then put both x=z and a.field2=b.field2 to where clause. You don't have to use subquery, just table straight forward, index will do the trick for you.
Create index <index name> on table2(x, field2) include(field1);
But include is available starting from v11.
If version of your PostgreSQL < 11, use with to express your subquery, then use it in join.
Best, Igor
Sent from my Verizon, Samsung Galaxy smartphone
-------- Original message -------- Date: 8/14/20 16:27 (GMT-08:00) Subject: Temp tables
Hi all , i have a doubt wich query performs betterUpdate table a set field1=b.field 1 From (select field1,field2
From table2 where x=z ) b Where a.field2=b.field2
Or
Create temp table y as select field1,field2 From table2 where x=z;
Update table a set field1=b.field1
From y as b Where a.field2=b.field2
I ve been told not to use subquerys, but i think this refers to
Select * from table where field1 in (select field1 from table2)
Any thoughts? Thanks people y'all
|
