0

I have a web page with a database lookup that contains multiple optional parameters. When I select a couple of parameters and run the query, it returns in less than two seconds. However, when another person logs in, and runs the same query with the same parameters on my machine, it takes 30+ seconds to come back. Web browser doesn't matter, it behaves the exact same way in multiple different browsers for each user. Both users have all the needed permissions on the database to run the query as they go through the same database connection using tcp/ip. At this point, the only difference is who is actively logged in, which doesn't make any sense to me. Does anyone have any ideas as to why the person would make a difference when everything else appears to be the exact same? Thanks

2

There could be different reasons - I'll speculate some of them:

  1. You could have row level security and have different rows returned for different callers
  2. You could have resource governor enabled with different resource sizings for each user based on a login trigger
  3. Perhaps the different users are on different machines and there are subtle differences in how the tests are run - for example, one user is always first and gets to compile the query not in the cache or there is a parallel plan that is parallel for one user and not the other due to load on the machine at the time

  4. Maybe the queries are slightly different in text on the two machines and you are actually getting different query plans somehow for the two cases.

It usually helps to either do a profiler or XEvent trace to look at the exact runtimes and showplans at runtime to get insight into why one is slower. On SQL 2016+, Query Store makes it easier (as is the case on SQL Azure), so if you have the option to try the repro on a later version it might help you find the answer. Good luck!

1
  • 1. No row level security, I know that as I'm both developer and dba. 2. never knew a resource governor was available (primarily developer), so never turned one on. 3. Same machine, same web browser, same settings, different users on front end, same database user on back end. 4. query is run from a custom web component which takes parameters, then runs the same query with the parameters input and processed. Point taken about the profiling, though. I'll try to see if that shows anything different.
    – Tom A
    Apr 23 '20 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.