Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 64,891 through 64,905 (of 66,712 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

Jonathan AC Roberts wrote:

I think the most obvious way to rewrite that query would be:

SELECT u.UserId
  FROM UserTable u 
 WHERE EXISTS(SELECT * 
                from TableA a 
               where DateColumn > @StartDate 
                 and a.UserId = u.UserId)
    OR EXISTS(select * 
                from TableB b 
               where Success = 1 
                 and DateColumn > @StartDate 
                 and b.UserId = u.UserId)

which I'm sure would perform better.

That was about equal to the CTE.

This runs once a month, and once the devs handed me the re-write, I re-wrote it as you did. The performance difference was negligible. I gave my blessing to their code, which was pretty much of a first.

Now, I have to make sure they don't write everything as a CTE!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27055 More actions · Answer 2

jonathan.crawford wrote:

I feel like I'm missing something. An inner join is necessarily an existence check, why not:

SELECT u.UserId
  FROM UserTable u
  JOIN TableA a ON a.UserId = u.UserId
  JOIN TableB b ON b.UserId = u.UserId
 WHERE a.DateColumn > @StartDate 
       AND b.Success = 1 
       AND b.DateColumn > @StartDate

It's an either or situation for the two tables. The data can exist in one, the other, or both. This logic will eliminate rows

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

x wrote:

jonathan.crawford wrote:
I feel like I'm missing something. An inner join is necessarily an existence check, why not:
SELECT u.UserId
  FROM UserTable u
  JOIN TableA a ON a.UserId = u.UserId
  JOIN TableB b ON b.UserId = u.UserId
 WHERE a.DateColumn > @StartDate 
       AND b.Success = 1 
       AND b.DateColumn > @StartDate 
more data would be consumed on average. "if exists" makes the determination after the first row fufills the predicate whereas just joining looks to read more from tablea and tableb than needed, also I get "cartesianish" vibes somehow.
also, you're requiring a match in both tablea and tableb but I'm a bit hazy whether that matches the requirements.

Agreed on both observations, especially the latter, which does make it so it must exist in both TableA and TableB, when it should be either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael L John One Orange Chip Points: 27055 More actions · Answer 4

If this code was run many times a day, it may be worth spending more time on it. It is executed once a month.

But, it's not the code that can be improved by much. It's the implementation in the database schema and the actual business requirements that need to be looked at. That, plus teaching the developers WHY, is a far better use of my time.

The unfortunate thing is that this code, as well as a lot more like it, was developed by folks who were let go a few weeks ago. The development team lost half of their personnel. The folks left who are trying to make sense of these issues that are now appearing because the usage of the system has risen. These guys needed a "win", badly.

Is this the most efficient code? Maybe not. But their re-write is magnitudes better than the original. I beat these folks up daily. When I gave my blessing to it, it was a desperately needed positive in a sea of negatives that looks like it will not go away any time soon.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Neil Burton SSC-Insane Points: 22999 More actions · Answer 5

What's worse: a problem which starts spontaneously and for which nobody is able to track down the cause, or, the same problem spontaneously stopping over the weekend and nobody having changed anything?

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Neil Burton wrote:

What's worse: a problem which starts spontaneously and for which nobody is able to track down the cause, or, the same problem spontaneously stopping over the weekend and nobody having changed anything?

We had a hardware problem which exhibited this pattern, not too long ago. The cause was aging SSD's and a failure of the server/drive diagnostics to see it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jasona.work SSC Guru Points: 50117 More actions · Answer 7

So yesterday we got a bit of good/bad news...

Good, in that the wife has been called back to work and goes back on Tuesday.

Bad, in that the wife got called back to work and will be making LESS per week than she was getting from unemployment, with the extra $600/wk that the gov tacked on.

But frankly, I'm glad she's going back and so is she, in part because having a 40-hr/wk job is a much less stressful source of income than unemployment (especially when you add in the "will she FIND a job when the lockdowns end or when the unemployment runs out?")

Thom A SSC Guru Points: 99130 More actions · Answer 8

Any admins about to ban https://www.sqlservercentral.com/forums/user/james500 ? He's spammed over 50 topics in the last hour with advertisements for counterfeit documents.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

Thom A wrote:

Any admins about to ban https://www.sqlservercentral.com/forums/user/james500 ? He's spammed over 50 topics in the last hour with advertisements for counterfeit documents.

I was half expecting/hoping that there would at least be some sort of posting-block applied to any user with > x spam messages in a day.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

Thom A wrote:

Any admins about to ban https://www.sqlservercentral.com/forums/user/james500 ? He's spammed over 50 topics in the last hour with advertisements for counterfeit documents.

No idea how to ban someone, but I discovered I could edit their profile. Just changed the password. That'll slow 'em down for a moment.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

Grant Fritchey wrote:

Thom A wrote:
Any admins about to ban https://www.sqlservercentral.com/forums/user/james500 ? He's spammed over 50 topics in the last hour with advertisements for counterfeit documents.
No idea how to ban someone, but I discovered I could edit their profile. Just changed the password. That'll slow 'em down for a moment.

Excellent. Make the new password 45 characters long, expiring every minute, and must contain a mixture of Greek and Chinese characters.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 12

Phil Parkin wrote:

Thom A wrote:
Any admins about to ban https://www.sqlservercentral.com/forums/user/james500 ? He's spammed over 50 topics in the last hour with advertisements for counterfeit documents.
I was half expecting/hoping that there would at least be some sort of posting-block applied to any user with > x spam messages in a day.

Yeah. At least most of us here have enough reputation to nuke a topic instantly with a single report. Honestly hoped that after 3-5 reports it was at least disable them from posting. Certainly after the 40 or so it should have done.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

frederico_fonseca SSCoach Points: 16154 More actions · Answer 13

with wordpress forums I think you need to have one of the addins to manage users - may not be able to delete it, but you can block it (with or without a message)

and yes that functionality to ban user after x reports is sadly missed - going through 20 or 30 posts and reporting 1 at the time to have it hidden isn't fun

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 14

User blocked. I don't know that there's a way now to block someone with x spam messages, but filing an issue.

Thinking that 3 spam messages is enough. The false positives seem to be 1-2 for a user, so blocking someone at >=3 seems sensible.

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 15

Probably >3 within a timeframe, to be fair? not >3 ever?

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses