The Limits and Potentials of Local SGD for Distributed Heterogeneous Learning with Intermittent Communication