Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Avoid cursors, that query had no need for them. SQL is <strong>not</strong> an imperative language (<em>which is why it gets a bad name because everyone uses it as one</em>) - it's a set language.</p> <p>First thing you can do is speed up the basic execution of your SQL, less time parsing/executing the query means less chance of a deadlock:</p> <ul> <li>Prefix all your tables with <code>[dbo]</code> - this cuts up to 30% off the parse stage.</li> <li>Alias your tables - it cuts off a small amount off the planning stage.</li> <li>Quoting identifiers <em>may</em> speed things up.</li> <li><em>These are tips from an ex-SQL-PM before anyone decides to dispute it.</em></li> </ul> <p>You can use a CTE to get the data to update and then use a <code>UPDATE ... FROM ... SELECT</code> statement to do the actual updates. This will be faster than a cursor, because cursors are <strong>dog slow</strong> when compared to clean set operations (even the fastest 'fire hose' cursor like yours). Less time spent updating means less of a chance of a deadlock. <em>Note: I don't have your original tables, I can't validate this - so check it against a development DB.</em></p> <pre><code>DECLARE @nowTime datetime = convert(datetime, @now, 21); WITH [DailyAggregates] AS ( SELECT [D].[dailyId] AS [dailyId], [D].[spentDaily] AS [spentDaily], [D].[impressionsCountCache] AS [impressionsCountCache], SUM([I].[amountCharged]) as [sumCharged], COUNT([I].[impressionId]) as [countImpressions] FROM [dbo].[Daily] AS [D] INNER JOIN [dbo].[Impressions] AS [I] ON [I].[dailyId] = [D].[dailyId] WHERE [I].[isCharged] = 0 AND [I].[showTime] &lt; @nowTime AND [D].[isActive] = 1 GROUP BY [D].[dailyId], [D].[spentDaily], [D].[impressionsCountCache] ) UPDATE [dbo].[Daily] SET [spentDaily] = [A].[spentDaily] + [A].[sumCharged], [impressionsCountCache] = [A].[impressonsCountCache] + [A].[countImpressions] FROM [Daily] AS [D] INNER JOIN [DailyAggregates] AS [A] ON [D].[dailyId] = [A].[dailyId]; UPDATE [dbo].[Impressions] SET [isCharged] = 1 WHERE [showTime] &lt; @nowTime AND [isCharged] = 0; </code></pre> <p>Furthermore you could disallow PAGE locks on your index, this will decrease the chances of a few rows locking a whole page (because of locking escalation, only a certain percentage of rows need to be locked before the entire page is just locked).</p> <pre><code>CREATE NONCLUSTERED INDEX [IDX_Impressions_isCharged_showTime] ON [dbo].[Impressions] ( [showTime] ASC, -- I have a hunch that switching these around might have an effect. [isCharged] ASC ) WITH (ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] GO </code></pre> <p>This will just mitigate the chances of a deadlock. You might try restricting @now a date in the past (i.e. <code>today - 1 day</code>) to make sure that the inserted row does not fall into the update predicate; chances are it will prevent the deadlock entirely.</p>
    singulars
    1. This table or related slice is empty.
    1. POupdate and insert queries creating a deadlock
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJonathan Dickinson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 My next step was going to be to look at the overall process but seems you beat me to it!
      singulars
      1. PO
      1. USMartin Smith
    2. COI will try this out now, but i really don't know how much of an improvement this will be. The impressions table has about 400 000 records constantly, more or less, and i am experiencing 20 or so exceptions daily. I will also try and move back The DateTime.Now 1 second just in case
      singulars
      1. PO
      1. USAtzoya
    3. CO@Atzoya, any reason you get the date from the server? Why not use `GETDATE()` in SQL? If your servers' clocks are out of sync this could be causing issues.
      singulars
      1. PO
      1. USJonathan Dickinson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload