Tag Info

Hot answers tagged

10

Lets take your problem step by step: It has been running great until one fine morning I pushed a lot of rows to a table that is heavily used and since then I was getting query timeouts. Whenever you do large updates/inserts to you tables, highly recommend to update stats and reorg/rebuild indexes. That way query optimizer does not select or produce bad ...


6

correct not necessarily - it depends on whether a suitable index exists and whether the CBO decides to use it. For example if the table is small or the the statistics lead the optimizer to believe that the filter will be true for a large percentage of the results, it may consider the cost of a FTS to be lower this is not guaranteed - it is even possible ...


5

First of all, no, you probably shouldn't clear your query plan cache. You probably having a problem with bad parameter sniffing. Here are some articles about it by various people. Greg Larson with SimpleTalk, Jes Schultz Borland with Brent Ozar Unlimited, and Thomas LaRock. You can do a quick search and you will see tons of others. It's a popular ...


5

Jack's right, so I won't repeat that. I'll just add some articles I kind of like regarding sargability of an expression: Sargability: Why %string% Is Slow - by (my all time favorite) Brent Ozar Low-Hanging Fruit of Sargable Expressions - by Dan Guzman SQL Server Transact-SQL WHERE - by Brad McGehee Sargable Predicates - by Jeff Wharton Indexing for ORs - ...


3

WITH KeepLatest AS ( SELECT *, RANK() OVER ( PARTITION BY CPT4,CPT4Mod ORDER BY Eff_Date DESC, ItemCharge) as Rnk FROM dbo.YourTable ) DELETE KeepLatest WHERE Rnk > 1 ; By using RANK() instead of ROW_NUMBER() you will avoid deleting anything if you have a row which is identical across all four columns - although I'm not sure ...


2

I'm not 100% sure about LINQ but i would say that the seperation is based on transaction control. You apply any number of updates, inserts or deletes. That are then held as a unit of work, then once you've finished your changes you simply commit them to the database as a single action or roll all of the changes back, maintaining the integrity of your ...


1

You can learn from this example : create table dbo.ProductsCount (prdid int , cnt int) go insert into dbo.ProductsCount (prdid, cnt) select 111,2 union all select 111,5 union all select 112,10 union all select 112,20 go select * from dbo.ProductsCount order by prdid; go -- delete duplicates with CTE and ROW_NUMBER() ; WITH C AS ( select ...



Only top voted, non community-wiki answers of a minimum length are eligible