Are the posted questions getting worse?
-
Why can a person not understand that when you write a query against dev that has 6 rows per table, and the query does 3 table scans, it will not run as fast in production, that has 60 million rows in each table?
And, I can't get him to understand that he needs to consider the time portion of a datetime2 column in order for the query to produce the correct results.
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 wrote:
Why can a person not understand that when you write a query against dev that has 6 rows per table, and the query does 3 table scans, it will not run as fast in production, that has 60 million rows in each table?
And, I can't get him to understand that he needs to consider the time portion of a datetime2 column in order for the query to produce the correct results.
What the hell do you expect, Michael? Everyone knows that SQL Server is just a place to store data, right? That "SQL" stands for "Scarcely Qualifies as a Language", right? And that we don' need no stinkin' DBA to tell us how to do it, RIGHT? 😀 😀 😀
Give it up and learn NoSQL, RedShift, and Hadoop with a Java kicker in the cloud... that'll fix it. RIGHT??? 😀 😀 😀
--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 wrote:
Why can a person not understand that when you write a query against dev that has 6 rows per table, and the query does 3 table scans, it will not run as fast in production, that has 60 million rows in each table?
Is it reasonable to assume from the way this comment is phrased that production is properly indexed and that it isn't doing the table scans that dev is doing?
If so, is there a reason why those indexes can't be moved to dev so that this person stops complaining?
-
It sounds to me like it's all the fault of the query and the person that wrote it... not an absence of indexes.
--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) -
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.userIDThe 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.
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/ -
Obviously, it's time for you to move this all to the cloud where you can easily add a bazillion CPU's to scale out for this horribly complex problem. After all, hardware is cheaper than Developer time, RIGHT? 😛
--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) - Jeff Moden wrote:
Obviously, it's time for you to move this all to the cloud where you can easily add a bazillion CPU's to scale out for this horribly complex problem. After all, hardware is cheaper than Developer time, RIGHT? 😛
It is in the cloud. This is running against an Azure database. And, no, we won't add CPU's to make this crappy code run better.
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 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.userIDThe 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.
- 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.
-
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.
-
I stick to my trusted brand of Brain Bleach
😎
- 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.userIDThe 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 !
- 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.userIDThe 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 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.userIDThe 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!
- 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.userIDThe 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/
Viewing 15 posts - 64,861 through 64,875 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply