question

rsboesystems avatar image
rsboesystems asked

Two different SQL accounts, same query, same PC, same results, very different speed times... why?

This one has me stumped. We have a simple view on a table which enforces some security rules so users can only see the data they're entitled to see. This is on SQL 2008 (client software and we can't force them to upgrade, alas)

We have two users, both set up identically as far as we can see.

When we run the same query, they both get the same results, but one takes less than a second, the other takes more than 5 seconds.

The execution plans (attached) look the same for both.

What should we be looking for?

The "Estates_v" is defined as below:

ELECT [AllTheFieldsFromTheTable] from [dbo].[Estates]
where (TeamCode in (Select TU.TeamCode from TeamUsers TU, StaffUser SU where 
SU.StaffCode = TU.StaffCode and SU.Username = SUSER_SNAME()) ) OR (TeamCode = 'ALL') 
OR (SUSER_SNAME() in ('sa','kpi_stats'))) WITH CHECK OPTION

Any insight greatly appreciated.

performanceexecution plan
userspeeds.png (100.8 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

And if you drop this plan from the cache, then run the queries in reverse order, do you get a different execution plan?

1 Like 1 ·

1 Answer

·
rsboesystems avatar image
rsboesystems answered

@Kev Riley

(Apologies if this is a dupe, my first reply to you seemed to disappear into the ether)

Unfortunately it looks we don't have privileges to do that.

This is a client server where we have relatively limited privileges. Anything else we can try?

Thanks again.

Richard


cachedaccess.png (8.7 KiB)
11 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Does the query

Select TU.TeamCode from TeamUsers TU, StaffUser SU where 
SU.StaffCode = TU.StaffCode and SU.Username = SUSER_SNAME()

return wildly different results for each of the users?

0 Likes 0 ·
@Kev Riley

Users don't have access to the tables directly so ran the closest equivalent using the views:

Select TU.TeamCode from TeamUsers_v TU, StaffUser_V SU where SU.StaffCode = TU.StaffCode and SU.Username = SUSER_SNAME()

Pretty much instant for both the "fast" user and the "slow" one.

We've only had speed problems reported from this 1 user, 2 other accounts we've tried seem fine.

Really appreciate your feedback and suggestions.

0 Likes 0 ·

And does it return the same-ish number of rows?

0 Likes 0 ·

Three for the "fast" user, one for the slow. The row for the slow user is included in the fast user. Originally the fast user was in more teams, but we've removed him from all of them bar one in the hope of finding the cause.

0 Likes 0 ·

Ah! Have you updated statistics on the tables since making that change?

0 Likes 0 ·
Show more comments
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.