How to improve performance of fanning quries

Help Procedural SQL
1

I am working on migration from SQL server to Memsql and as memsql force us to make shard key on pk column so we are not able to make shard key in different mediums cardinality column and 80% queries has where clause on column which is not a pk and due to that some procedure is taking more than 1 mins to execute.(tried index as well but not improving performance much)

I proposed fix to create temporary table with shard key of medium cardinality column within the proc and storing records in this table using select insert and will use this temp table instead of main table in procedure but I have some queries regrading memory management:-

1- what would happen if leaf node goes down?
2:- when garbage collector will flush data from temp table and free the memory??

Please suggest…
Thanks in advance

2

Hi,

Another option to consider instead of temporary tables is to try reference tables. Those work well for smaller rarely updated tables (perfect for dimension tables in a star schema for example)

As for your questions about temporary tables

  1. Temporary tables are not durable (data in them doesn’t get replicated), so if a leaf goes down you will get an error saying the temporary table is no longer valid. You’ll need to drop + recreate it.
  2. Temporary table memory is freed as soon the table is dropped (which happens when the connection that created the temp table dies or when DROP TABLE is run).

-Adam