private void RewriteNonSargableIsNull(object sender, EventArgs e) { try { var oldDoc = GetCurrentDocumentText(); var newDoc = oldDoc; var rewriter = new NonSargableRewrites(oldDoc); var queries = ScriptDom.GetQuerySpecifications(oldDoc); foreach (var rep in rewriter.GetReplacements(queries)) { newDoc = newDoc.Replace(rep.Original, rep.Replacement); OutputPane.WriteMessage("Non-Sargable IsNull re-written from \r\n\"{0}\" \r\nto\r\n\"{1}\"\r\n", rep.Original, rep.Replacement); } if (oldDoc != newDoc) { SetCurrentDocumentText(newDoc); } } catch (Exception ex) { OutputPane.WriteMessage("Error re-writing non sargable isnulls {0}", ex.Message); } }
public override GlyphDefinition GetDefintions(string fragment, TSqlStatement statement, GlyphDefinition definition, List <QuerySpecification> queries) { var nonSargableRewriter = new NonSargableRewrites(fragment); var replacements = nonSargableRewriter.GetReplacements(queries); if (replacements.Count > 0) { definition.Menu.Add(new MenuDefinition() { Caption = "Replace non-sargable IsNull", Action = () => { }, Type = MenuItemType.Header , Glyph = definition }); var offsettedReplacments = new List <Replacements>(); foreach (var replacement in replacements) { var replacement1 = replacement; replacement1.OriginalOffset += statement.StartOffset; offsettedReplacments.Add(replacement1); } if (replacements.Count > 1) { var menu = new MenuDefinition(); menu.Operation = new ClippyReplacementOperations(offsettedReplacments); menu.Action = () => PerformAction(menu.Operation, menu.Glyph); menu.Glyph = definition; menu.Caption = GetCaptionForAll(statement); menu.Type = MenuItemType.MenuItem; definition.Menu.Add(menu); } foreach (var replacement in offsettedReplacments) { var menu = new MenuDefinition(); menu.Action = () => PerformAction(menu.Operation, menu.Glyph); menu.Glyph = definition; menu.Caption = string.Format("\t\"{0}\" into \"{1}\"", replacement.Original, replacement.Replacement); menu.Type = MenuItemType.MenuItem; menu.Operation = new ClippyReplacementOperation(replacement); definition.Menu.Add(menu); } definition.GenerateKey(); } return(definition); }
public void sargable_rewrites_isnulls_on_joins() { var script = @" select * from dbo.tableaaa join tableb on isnull(a_column, 'sss') <> 'abc' " ; var rewriter = new NonSargableRewrites(script); var replacements = rewriter.GetReplacements(ScriptDom.ScriptDom.GetQuerySpecifications(script)); Assert.AreEqual(1, replacements.Count); Assert.AreEqual("isnull(a_column, 'sss') <> 'abc'", replacements.FirstOrDefault().Original); Assert.AreEqual("(a_column is null or a_column <> 'abc')", replacements.FirstOrDefault().Replacement); }
public void sargable_rewrites_isnull_not_equals_same_literal() { var script = @" select * from dbo.tableaaa where isnull(a.a_column, 'abc') <> 'abc' " ; var rewriter = new NonSargableRewrites(script); var replacements = rewriter.GetReplacements(ScriptDom.ScriptDom.GetQuerySpecifications(script)); Assert.AreEqual(1, replacements.Count); Assert.AreEqual("isnull(a.a_column, 'abc') <> 'abc'", replacements.FirstOrDefault().Original); Assert.AreEqual("(a.a_column is not null and a.a_column <> 'abc')", replacements.FirstOrDefault().Replacement); }