SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Same query, two users, different performance


Same query, two users, different performance

Author
Message
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51265 Visits: 10844
Tom - I've seen so many problems caused by with views on views on views in the past that I've denounced them as heretical and will never write them myself. You definitely have your work cut out for you. You're going to have to examine the performance of each view and underlying table. You're going to have to look at every UDF and figure out what they do and how. Performance improvement opportunities will present themselves.

I'm offering this piece of advice because it sounds like a real mess, I don't know what environments they have for you to play around with and you're new to your job. I don't do any changes like this on the production database until it's thoroughly tested for both results and performance. I would follow the same advice here. I don't want your first month at your new job to be your last because you brought down a production server.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51265 Visits: 10844
Grant Fritchey (9/5/2013)
Without knowing what's happening inside those UDF's, it's hard to say whether or not it would affect performance. I'm willing to bet money that there's some type of "security" check against data or something in there that is slower for one user than for another. Or, within the views.

Nesting views and UDFs... I really should become a consultant. I can make millions doing nothing but unwinding nested views and nested UDFs.

Grant - Write more books; you're good at it. ;-)


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
sqlslacker
sqlslacker
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 209
Have you watched the I/O of the server while the queries are running? I know that, for example, we're limited to 600Mb/s for or developers, but the backups user can top out at 700Mb/s or more. Perhaps there's an artificial cap of that kind on the user where dbo doesn't have one?
Tom Bakerman
Tom Bakerman
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 931
Thanks so far.

Ed: I agree with you 1000% about the layering of views. Got truly spaghetti sql to deal with here. I'm working on convincing the powers that be that we should create a database with the "answers" pre-populated. First step towards a data warehouse.

Grant: Please continue writing books!
Haven't yet found any evidence of filtering based on username, but I'm still digging.

sqlslacker: No resource governing implemented on any resources or any users.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search