Tag Info

New answers tagged

0

As Pablo Montilla mentioned in a comment on the question, I should have added a code sample to indicate that I was using Dapper. (I have since added the sample.) The solution was to pass an argument to Dapper's Query method to tell it not to buffer all of the results before letting me iterate over them. The default behavior of Dapper is to buffer the ...


0

Large, general-purpose search queries often produce an inefficient plan. This can be ameliorated by adding OPTION (RECOMPILE) to the query to invoke parameter embedding. Now your problem is that the general-purpose query is recompiled with every execution, which is itself an overhead. Avoid this by pulling out the common sets of parameters and have ...


1

I would create One stored procedure with optional parameters. Also instead of using WHERE @Var is NULL or Column = @Var use dynamic sql to build your query. This kind of query where clause results in a very unefficient execution plan. Also Sql server doesn't do any shortcircuting when evaluating OR conditions in where clause, for example even if @Var is ...


6

First, you should always have proper transaction handling in all of your procedures so that it does not matter if they are called by app code, by another procedure, individually in an ad-hoc query, by a SQL Agent job, or some other means. But single DML statements, or code that doesn't make any modifications, doesn't need an explicit Transaction. So, what I ...



Top 50 recent answers are included