Hello and good morning. I might be asking something which I don't call it as its actual name but I'll try. I know that there are statistics in SQL server which also emit data about usages of SP's , execution times and another useful info. But I've heard that all those statistiacs are gone while the service is restarted ( the SQL server service). And so I ask : is there any ready query templates which creates structure logs table and ofcourse a job which updates the statistics in those tables ?
I'm pretty sure that there has to be something ready out there
My goal : come after half a year to my SQL SERVER and see where are the bottlenecks and what should I optimize , while not being afraid if SQL service has restarted.
If you are talking about logging dmv data that is, which I think you are
The documentation for it is a bit spread over several msdn blogs but I'm sure you'll find them
And the keywords you are looking for are things like "persisting dmv" or "logging dmv data" or stuff like that, it's called dmv/dynamic management views @RoyiNamir
@McNets it prevents syntax highlighting on dba.se, and I think it helps distinguish between the statements and results. Happy to persuaded there is another way though :)
Several sites have MathJax enabled, so there is obviously some room for site-specific markdown.
Therefore please can we have markdown tables here on dba.se? There is an open feature-request for this on mSO, but whereas it is hardly a key feature for a programming Q&A site, a significant percent...
I have an Images table where that table has these basic columns —
ImageId (int)
VotesUp (int)
VotesDown (int)
Each image can get upvotes (up , down , retract vote)
The upvotes table has :
ImageId (int)
UserId (int)
Score (int ) [-1,0,+1]
So if —
A user inserts +1 score then ( if not ...
@RoyiNamir First of all, since your trigger is ON INSERT, UPDATE, you can't inner join inserted and deleted. It should be along the lines of inserted LEFT JOIN deleted. Also, if it's possible to insert or update several entries related to the same ImageId, your trigger won't work as expected. I would aggregate the upvote changes by ImageId prior to joining Images to them.
The simplification part itself is probably easy, although I don't believe you can do completely without CASEs. You are computing two results from the same column – obviously you need a conditional to choose which value contributes to which result.
@RoyiNamir Oh, sorry, I was looking at the first of the two queries, thinking it was the only one. I can see now that the one I was talking about is meant to processes only updated rows.
@RoyiNamir to me, the simplest way would be to do this in the software. Everytime a user votes, you perform an update in the images table and record the user vote
@RoyiNamir One other point I meant to mention before we had a power outage here is that having one query to process the insert part and another for the update part kind of defeats the purpose of a single insert+update trigger. You could just as well have two triggers, one for each action.
@Lamak Good point. If Royi is in full control of that part's development process, it would make more sense to introduce a stored procedure (or several, if necessary) and use it in all applications instead of direct updates and inserts.
@AndriyM yeah, that's the part about this that I don't really get
I mean, in the software, you would know exactly if it's an upvote or a downvote, and run the corresponding sp's without checking the change in score and all that
seems like too much trouble really
one sp changes the score, the other stores the vote of the user or something like that
@AndriyM Andriy , I don't see any problem with a single trigger which will handle update and insert statements. its whole purpose is to update rank on the Images table. Anyway I was after a simplified logic (if any) to my code in that trigger
An I certainly won't do it by code. ( it will hold the connection longer). So the only option left is Sp or trigger. Anyway I think that this kind of operation does fit a trigger purpose. I don't see any problem when an ran is added to votes table - to increase a value in other table.
@RoyiNamir I don't see a problem with such triggers either. I'm merely saying that the way this specific trigger is designed is hardly different from two separate triggers for each action, because you are using two distinct queries for each action anyway.
I will hve to have a logic to see if already downvoted and block this and to make that value 0
-
Also if user put -1 to an image , and now he sets it to +1
I will have to show that this image has less downvotes and more upvotes
each image is shown as [........image.......] 76 upvotes / 21 downvotes
and when a user change his mind from upvote to downvote - I will have to update two values PLUS checking if already downvoted
this is what i've made in my trigger ( more or less)
I just don't see the benefit of 2 Sp's ( it's just 2 places to deal with , with not less code ). Please correct me if i'm wrong.
@AndriyM I see the module division you're suggesting and you're right. But still that logic ( complicated and non-intelligent of mine - imho , In my current code) - well I thought it can be simplified ( and maybe it can't)
(Lamak I'm sorry , I see now that I wrote Lamark , by mistake).
WITH
DeletedAgg AS
(
SELECT
ImageId,
UpDelta = COUNT(CASE Score WHEN +1 THEN 1 END),
DownDelta = COUNT(CASE Score WHEN -1 THEN 1 END)
FROM
deleted
GROUP BY
ImageId
),
InsertedAgg AS
(
SELECT
ImageId,
UpDelta = COUNT(CASE Score WHEN +1 THEN 1 END),
DownDelta = COUNT(CASE Score WHEN -1 THEN 1 END)
FROM
inserted
GROUP BY
ImageId
)
UPDATE
img
SET
VoteUp = ISNULL(img.VoteUp, 0) - del.UpDelta + ins.UpDelta,
before that , I'm just remindering I have to know each user if he upvote / downvote
that's why in the upvotes table I have –ImageId |UserId | Score
(Just like in Stackoverflow)
Well here is the idea :
If you look at the upper section of the trigger :
UPDATE imgs
SET VotesUp = CASE
WHEN deleted.Score = 1 AND INSERTED.score =0 THEN ISNULL(VotesUp, 0) -1
WHEN deleted.Score = 1 AND INSERTED.score =-1 THEN ISNULL(VotesUp, 0) -1
WHEN deleted.Score = 0 AND INSERTED.score =1 THEN ISNULL(VotesUp, 0) +1
WHEN deleted.Score = -1 AND INSERTED.score =1 THEN ISNULL(VotesUp, 0) +1
ELSE ISNULL(VotesUp, 0)
END
,
VotesDown = CASE
> **AWS Outage** Identified - Imgur is currently impacted by the AWS S3 outage. Some browsing might still be possible, but expect poor performance and no upload ability until the issue is resolved. Feb 28, 10:11 PST
Multiple upstream servers are sending notifications to a load-balanced django app. If all the upstream servers are working correctly, the app will always receive duplicates of these notifications (since all the upstream notifiers should send the same notifications; it's just for redundancy). I wa...
@RoyiNamir You replied to the wrong message, so I'm not sure which of my responses you are referring to. The status.imgur.com page would seem to be the definitive source, and that is blaming Amazon S3.
@bluefeet How does that make it definitely not a test run?
AWS status: "We've identified the issue as high error rates with S3 in US-EAST-1, which is also impacting applications and services dependent on S3. We are actively working on remediating the issue."
@ypercubeᵀᴹ You're setting UpVotes and DownVotes to NULL for ImageId 2 & 3. It pays to always specify NULL or NOT NULL in the table def, otherwise you're depending on a default.
@ypercubeᵀᴹ Cool. Just to be clear, making the column nullable allows the trigger to complete instead of throwing an error (which dbfilddle does not pass on). The code still needs fixing. For example INSERT dbo.Upvotes (ImageId, UserId, Score) VALUES (1, 0, 1); produces the same problem.
@TomV Specializing in an ERP means you have to be pretty close to the business side, right? How often do you get called into solving purely business problems?
@RoyiNamir Why are you using a trigger at all? Seems like it should be an indexed view, then SQL Server automatically keeps everything consistent for you.
I have an Images table where that table has these basic columns —
ImageId (int)
VotesUp (int)
VotesDown (int)
Each image can get upvotes (up , down , retract vote)
The upvotes table has :
ImageId (int)
UserId (int)
Score (int ) [-1,0,+1]
So if —
A user inserts +1 score then ( if not ...
