Tag Info

New answers tagged

1

I missed that you are already adjusting for maintenance_work_mem in my first read. I'll leave quote and advice in my answer for reference. Per documentation: maintenance_work_mem (integer) Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 ...


0

Since your situation is product is commercial you cannot change the query you cannot change table layout Your best shot (really your only shot) would be ALTER TABLE table2 ADD INDEX status_table1_ndx (status,table1); You may or may not see a change since the AND table2.table1 = CONCAT( 'constant prefix', table1.attrib1 ) is really a JOIN clause ...


-1

Thanks for all the helpers. I've created a base line from the below link, to check my instance before and after the parameter change: http://www.sqlservercentral.com/scripts/baselines/96797/


3

In order to avoid overloading my server, the requests are queued and handled one at a time. That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details ...


0

I think you know about performance of using UNION instead of OR, but it's not in this case vs. using CASE that just add an inline filter. In your second query you will have two Sort actions as a side effect of having two GORUP BY statements and also two Filter actions with adding a Concatenation by using UNION. Note: The count of actions is not a ...


0

Don't use a TRIGGER. Instead, gather a minute's worth (say) of data in a staging table. Then do the desired updates en masse. If you need the user to get up-to-the-second information (instead of just up-to-the-minute), have the user's query do that final update also. To phrase it differently, summarization is much more efficiently done in bulk. Your ...


0

Eventually I realized that it was some king of caching and probably on Linux level. After applying # sync && echo 3 | tee /proc/sys/vm/drop_caches followed by mysql> RESET QUERY CACHE; the query time became linear. Note though, that before this the effect was stable on different queries.



Top 50 recent answers are included