Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 64,861 through 64,875 (of 66,815 total)

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

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 1

Michael L John wrote:

Here's the query. There's not any understanding on how the the language works, index usage (or lack of usage), and what these things do.
It works in dev, why is is slow in production???
select   distinct    U.ClientID from (select userID from TableA where DateColumn > @StartDate 
                    union select userID from TableB where Success = 1 and DateColumn> @StartDate) l 
                    inner join UserTable u on u.userID = l.userID
The aggravating part is that I worked very closely with the original development team on the architecture and code. The WORST proc/orm code that runs against the database was averaging 4 MILLISECONDS.
Now, with this new crew, we have queries taking many minutes.

Brain bleach! WHERE IS THE BRAIN BLEACH???

I could totally make that code worse, but I could also write better code in my sleep. Good grief.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

jasona.work SSC Guru Points: 50216 More actions · Answer 2

Ed Wagner wrote:

jasona.work wrote:
Lynn Pettis wrote:
My problem is that I start forgetting what day it is. The only reason I know this is Sunday is that it is Mothers Day.
Been running into that problem too...
I'll wake up for work on Wednesday or Thursday and spend a couple minutes trying to remember which day of the week it is...
I think that's more because, working from home, your workplace never really changes, you don't get that 30-45-60+ minute drive in to work to get your brain really started...
Yup - I have the same problem. I think a lot of people do for the exact reason you specify. I've been WFH almost exclusively since late last year and I find I'm far more productive. I've also become accustomed to knowing what day it is, but with the whole family home all the time now, the "what day is it again?" syndrome has returned. This too shall pass.
One thing I definitely DO NOT miss is that rotten commute. Yours was undoubtedly worse than mine, as I take Telegraph and I think you'd take I-696 :(. I get back over an hour a day without losing anything. I've also gotten 2 months out of a full tank of gas and haven't put on the miles that I usually do. The coffee's better at home, as is the food. 🙂

Leaving for work at around 6:30 every day, 696 wasn't too bad most times. Although, the occasional accident at the Van Dyke or Mound exits would make it an ungodly pain...

Makes me glad those times that I listened to WWJ in the mornings, especially the traffic reports.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 3

Does Brain Bleach do anything if no brain exists?

Or is it strong enough? Any viable grey matter at this point might be like the .01 surviving virus when using a disinfectant.

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 4

Eirikur Eiriksson

SSC Guru

Points: 182948

May 12, 2020 at 4:18 pm

#3750863

I stick to my trusted brand of Brain Bleach

😎

x SSC-Insane Points: 23660 More actions · Answer 5

Brandie Tarvin wrote:

Michael L John wrote:
Here's the query. There's not any understanding on how the the language works, index usage (or lack of usage), and what these things do.
It works in dev, why is is slow in production???
select   distinct    U.ClientID from (select userID from TableA where DateColumn > @StartDate 
                    union select userID from TableB where Success = 1 and DateColumn> @StartDate) l 
                    inner join UserTable u on u.userID = l.userID
The aggravating part is that I worked very closely with the original development team on the architecture and code. The WORST proc/orm code that runs against the database was averaging 4 MILLISECONDS.
Now, with this new crew, we have queries taking many minutes.
Brain bleach! WHERE IS THE BRAIN BLEACH???
I could totally make that code worse, but I could also write better code in my sleep. Good grief.

Well lets see the query then! Please include an explanation why yours will use indexes while the original one won't. I'm going to admit that I'm probably like the poor 6 row guy and don't understand why indexes won't help that query!

Also, would a query that targets a 6 row table EVER use an index? I would hazard a guess that it wouldn't but I'm n0t a wiz like you guys !

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Michael L John wrote:

Here's the query. There's not any understanding on how the the language works, index usage (or lack of usage), and what these things do.
It works in dev, why is is slow in production???
select   distinct    U.ClientID from (select userID from TableA where DateColumn > @StartDate 
                    union select userID from TableB where Success = 1 and DateColumn> @StartDate) l 
                    inner join UserTable u on u.userID = l.userID
The aggravating part is that I worked very closely with the original development team on the architecture and code. The WORST proc/orm code that runs against the database was averaging 4 MILLISECONDS.
Now, with this new crew, we have queries taking many minutes.

Just curious, but what is the purpose of the code (and yes I could come up with what I think it is, but would rather hear from someone involved).

x SSC-Insane Points: 23660 More actions · Answer 7

x wrote:

Brandie Tarvin wrote:
Michael L John wrote:
Here's the query. There's not any understanding on how the the language works, index usage (or lack of usage), and what these things do.
It works in dev, why is is slow in production???
select   distinct    U.ClientID from (select userID from TableA where DateColumn > @StartDate 
                    union select userID from TableB where Success = 1 and DateColumn> @StartDate) l 
                    inner join UserTable u on u.userID = l.userID
The aggravating part is that I worked very closely with the original development team on the architecture and code. The WORST proc/orm code that runs against the database was averaging 4 MILLISECONDS.
Now, with this new crew, we have queries taking many minutes.
Brain bleach! WHERE IS THE BRAIN BLEACH???
I could totally make that code worse, but I could also write better code in my sleep. Good grief.
Well lets see the query then! Please include an explanation why yours will use indexes while the original one won't. I'm going to admit that I'm probably like the poor 6 row guy and don't understand why indexes won't help that query!
Also, would a query that targets a 6 row table EVER use an index? I would hazard a guess that it wouldn't but I'm n0t a wiz like you guys !

And another thing, 6 rows for development in the first place, well that is a really low effort setup. Who made the decision for this developer to write efficient queries then toss him 6 rows?

I'm sure I'm the dumb one here but that's why I'm here, to learn!

Michael L John One Orange Chip Points: 27079 More actions · Answer 8

x wrote:

x wrote:
Brandie Tarvin wrote:
Michael L John wrote:
Here's the query. There's not any understanding on how the the language works, index usage (or lack of usage), and what these things do.
It works in dev, why is is slow in production???
select   distinct    U.ClientID from (select userID from TableA where DateColumn > @StartDate 
                    union select userID from TableB where Success = 1 and DateColumn> @StartDate) l 
                    inner join UserTable u on u.userID = l.userID
The aggravating part is that I worked very closely with the original development team on the architecture and code. The WORST proc/orm code that runs against the database was averaging 4 MILLISECONDS.
Now, with this new crew, we have queries taking many minutes.
Brain bleach! WHERE IS THE BRAIN BLEACH???
I could totally make that code worse, but I could also write better code in my sleep. Good grief.
Well lets see the query then! Please include an explanation why yours will use indexes while the original one won't. I'm going to admit that I'm probably like the poor 6 row guy and don't understand why indexes won't help that query!
Also, would a query that targets a 6 row table EVER use an index? I would hazard a guess that it wouldn't but I'm n0t a wiz like you guys !
And another thing, 6 rows for development in the first place, well that is a really low effort setup. Who made the decision for this developer to write efficient queries then toss him 6 rows?
I'm sure I'm the dumb one here but that's why I'm here, to learn!

Six rows was an exaggeration. There are a 200-300k rows in the dev environment for each of the tables. In prod, there are ~60 million in each of the tables.

Lynn, as for what this is trying to do, it's attempting to get a list of clients ID's who have had people log in over the past 12 months. Where the design goes haywire is that the last login date may be in two places, one in the "user token" table, the second in a "login attempt" table. A record may exist in one or both tables, so they need to check both places. Figuring out why there are two places is something I am working on.

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Michael L John wrote:

x wrote:
x wrote:
Brandie Tarvin wrote:
Michael L John wrote:
Here's the query. There's not any understanding on how the the language works, index usage (or lack of usage), and what these things do.
It works in dev, why is is slow in production???
select   distinct    U.ClientID from (select userID from TableA where DateColumn > @StartDate 
                    union select userID from TableB where Success = 1 and DateColumn> @StartDate) l 
                    inner join UserTable u on u.userID = l.userID
The aggravating part is that I worked very closely with the original development team on the architecture and code. The WORST proc/orm code that runs against the database was averaging 4 MILLISECONDS.
Now, with this new crew, we have queries taking many minutes.
Brain bleach! WHERE IS THE BRAIN BLEACH???
I could totally make that code worse, but I could also write better code in my sleep. Good grief.
Well lets see the query then! Please include an explanation why yours will use indexes while the original one won't. I'm going to admit that I'm probably like the poor 6 row guy and don't understand why indexes won't help that query!
Also, would a query that targets a 6 row table EVER use an index? I would hazard a guess that it wouldn't but I'm n0t a wiz like you guys !
And another thing, 6 rows for development in the first place, well that is a really low effort setup. Who made the decision for this developer to write efficient queries then toss him 6 rows?
I'm sure I'm the dumb one here but that's why I'm here, to learn!
Six rows was an exaggeration. There are a 200-300k rows in the dev environment for each of the tables. In prod, there are ~60 million in each of the tables.
Lynn, as for what this is trying to do, it's attempting to get a list of clients ID's who have had people log in over the past 12 months. Where the design goes haywire is that the last login date may be in two places, one in the "user token" table, the second in a "login attempt" table. A record may exist in one or both tables, so they need to check both places. Figuring out why there are two places is something I am working on.

Okay, so TableA and TableB in the derived table are those two source tables where the login info may exist. So why the distinct in the outer query when there are no duplicates because of the UNION in the derived table? I won't bother to throw out any ideas on the this as I am sure you may have some good ideas of your own.

x SSC-Insane Points: 23660 More actions · Answer 10

Six rows was an exaggeration.

I suspect that's not the only exaggeration LOL

Now is someone going to rewrite that query or not?

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 11

What percentage of the rows of each table are less than 12 months ago?

--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)

x SSC-Insane Points: 23660 More actions · Answer 12

Okay, so TableA and TableB in the derived table are those two source tables where the login info may exist. So why the distinct in the outer query when there are no duplicates because of the UNION in the derived table? I won't bother to throw out any ideas on the this as I am sure you may have some good ideas of your own.

Why would this disallow using indexes though? I would go in the entire other direction, and expect that SQL might detect and drop the superfulous "distinct" but that's of course dependent on whether the query optimizer would have code to detect this.

Michael L John One Orange Chip Points: 27079 More actions · Answer 13

Jeff Moden wrote:

What percentage of the rows of each table are less than 12 months ago?

All of them. The system is only 9 months old.

Lynn, there are a lot of index changes for this database that are in various states of being deployed. There are 3 that will directly help this query, as well as quite a few other queries. These are in QA.

X, the re-write goes like this:

WITH CTE (Pull all the user ids who have logged in for last 12 months)

SELECT Client ID's WHERE Exists (select From CTE)

You're not getting the actual code, because I do not feel like obfuscating it, and the original discussion was not about how to tune a query, but more a vent about developers who don't think past their fingertips.

With the indexes, and the rewrite, there are 3 index seeks as opposed to scans, and in dev the execution time improvement went from ~40 seconds to less than 1 second.

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/

jonathan.crawford SSCertifiable Points: 6615 More actions · Answer 14

Why even use a CTE? Why not just join to those tables?

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

x SSC-Insane Points: 23660 More actions · Answer 15

Michael L John wrote:

Jeff Moden wrote:
What percentage of the rows of each table are less than 12 months ago?
All of them. The system is only 9 months old.
Lynn, there are a lot of index changes for this database that are in various states of being deployed. There are 3 that will directly help this query, as well as quite a few other queries. These are in QA.
X, the re-write goes like this:
WITH CTE (Pull all the user ids who have logged in for last 12 months)
SELECT Client ID's WHERE Exists (select From CTE)
You're not getting the actual code, because I do not feel like obfuscating it, and the original discussion was not about how to tune a query, but more a vent about developers who don't think past their fingertips.
With the indexes, and the rewrite, there are 3 index seeks as opposed to scans, and in dev the execution time improvement went from ~40 seconds to less than 1 second.

Hey thats fine, but being accurate doesn't really need the exact code, I try to tell folks to act as if they're logging a service call and try to duplicate the issue. You'll notice that I'm not asking "what the code is for," because all I was interested in was why this snippet won't use indexes.