Effective in the end, but HO because the problems the Soviets faced were created by Stalin a decade earlier. Had he instead invested in military infrastructure and helped train a modern officer corps, the Germans would have probably never made it to Stalingrad. While I do believe the Order was effective in galvanizing the military to slow its withdrawal (and realize that Russia could not continue to constantly withdrawl), the use of penal battalions and the encouragement of soldiers to shoot their own men who fled the battle was another example of Stalinist policy working against the average soldier who had already sacrificed so much.
Ultimately, the Soviet army lacked discipline and an understanding of how to counter mobile tactics (defense in depth proved to be a genius solution later on), but I feel that much of the army's struggles were self-inflicted and while Stalin may have 'patched' the hole, he was the one who created it in the first place.....
Moreover, a practice of holding indefensible positions amounted to strategic suicide in many cases, especially where a tactical withdrawal would have allowed unites to resupply, reorganize, and reposition themselves in better defended areas. Needlessly forcing hundreds of thousands of men to hold defensive points without reason cost lives and achieved little militarily.
For reference:
Excerpted from Ivan’s War: Life and Death in the Red Army, 1939-1945 by Catherine Merridale
Poll
