SELECT Statement against view takes 3 seconds on one machine and 1:10 on others

Question

SELECT Statement against view takes 3 seconds on one machine and 1:10 on others

Welsh Corgi

SSC Guru

Points: 116520
More actions
October 11, 2013 at 10:10 am

#299637

I was approached by a Developer who created a view.
It takes 3 seconds to run from the developers PC.
It tasked 1:10 seconds when a BA or I execute the query.
If the BA logs into the sever and executes the query it still takes 1:10 seconds.
If the developer logs into the server it takes 3 seconds.
This is very odd.
Any ideas?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 9 (of 9 total)

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Welsh Corgi (10/11/2013)
I was approached by a Developer who created a view.
It takes 3 seconds to run from the developers PC.
It tasked 1:10 seconds when a BA or I execute the query.
If the BA logs into the sever and executes the query it still takes 1:10 seconds.
If the developer logs into the server it takes 3 seconds.
This is very odd.
Any ideas?

I have lots of ideas but with no more details than it is slow for some people it is impossible to hazard any real guess.

What does the query look like? How about posting the actual execution plan at the bare minimum?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I'm sorry but Proprietary information would be disclosed if I posted the execution plan.

So consider this post closed.

btw, it is slow for EVERYONE except the developer that created the view. Very odd.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Welsh Corgi (10/11/2013)
I'm sorry but Proprietary information would be disclosed if I posted the execution plan.
So consider this post closed.
btw, it is slow for EVERYONE except the developer that created the view. Very odd.

There could be a zillion reasons for that. Maybe the where clause prevents some rows because of a userid or some other value. Maybe there are functions, maybe you have stale statistics somewhere, maybe you have...

Seeing the query could very likely shed some light on what it might be. Without some kind of information it is just guess work.

I hope you can figure it out.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

Welsh Corgi (10/11/2013)
I'm sorry but Proprietary information would be disclosed if I posted the execution plan.
So consider this post closed.
btw, it is slow for EVERYONE except the developer that created the view. Very odd.

you can download SQL Sentry Plan Explorer...there is a free edition....it has the ability to anonymize the execution plan....which you could post

http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

J Livingston SQL (10/11/2013)
Welsh Corgi (10/11/2013)
I'm sorry but Proprietary information would be disclosed if I posted the execution plan.
So consider this post closed.
btw, it is slow for EVERYONE except the developer that created the view. Very odd.
you can download SQL Sentry Plan Explorer...there is a free edition....it has the ability to anonymize the execution plan....which you could post
http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Dumb question but how do you post an execution plan from start to finish?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Save it as .sqlplan (right click to save) and below the post editor, there's an attachments option.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2