Query runs faster in MASTER but slow in my own database.

Question

Query runs faster in MASTER but slow in my own database.

lammpat

SSC Veteran

Points: 237
More actions
January 22, 2017 at 11:27 pm

#401459

I am not DBA, we have small team working on a small project and I need to take care of database, having little or no DBA experiance.
I have created a view using 4 table with joins, When I run that query in SSMS using following method, its takes different time to excute the query .
Use Master
Go
select * from [emp].[dbo].[uvw_employees] where <condition>.
** its takes around 0 seconds to give the 500 rows.
when I excute this same query using my own Database.
Use EMP
Go
select * from [emp].[dbo].[uvw_employees] where <condition>.
or
select * from [uvw_employees] where <condition>.
:crazy:
it takes around 8-10 seconds. I am unable to understand why this is happening and how to improve the performance, because from application we are connecting to EMP database and its taking 8-10 seconds. Any help would be greatly appreciated. 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

lammpat - Sunday, January 22, 2017 11:27 PM
I am not DBA, we have small team working on a small project and I need to take care of database, having little or no DBA experiance.
I have created a view using 4 table with joins, When I run that query in SSMS using following method, its takes different time to excute the query .
Use Master
Go
select * from [emp].[dbo].[uvw_employees] where <condition>.
** its takes around 0 seconds to give the 500 rows.
when I excute this same query using my own Database.
Use EMP
Go
select * from [emp].[dbo].[uvw_employees] where <condition>.
or
select * from [uvw_employees] where <condition>.
:crazy:
it takes around 8-10 seconds. I am unable to understand why this is happening and how to improve the performance, because from application we are connecting to EMP database and its taking 8-10 seconds. Any help would be greatly appreciated. 🙂

Possibly parameter sniffing and two different execution plans.
Can you post both plans please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

lammpat SSC Veteran Points: 237 More actions · Answer 2

ChrisM@Work - Monday, January 23, 2017 2:17 AM
lammpat - Sunday, January 22, 2017 11:27 PM
I am not DBA, we have small team working on a small project and I need to take care of database, having little or no DBA experiance.
I have created a view using 4 table with joins, When I run that query in SSMS using following method, its takes different time to excute the query .
Use Master
Go
select * from [emp].[dbo].[uvw_employees] where <condition>.
** its takes around 0 seconds to give the 500 rows.
when I excute this same query using my own Database.
Use EMP
Go
select * from [emp].[dbo].[uvw_employees] where <condition>.
or
select * from [uvw_employees] where <condition>.
:crazy:
it takes around 8-10 seconds. I am unable to understand why this is happening and how to improve the performance, because from application we are connecting to EMP database and its taking 8-10 seconds. Any help would be greatly appreciated. 🙂
Possibly parameter sniffing and two different execution plans.
Can you post both plans please? At the mean time I am googling and reading about the above two.

Thanks for the reply, as I said i have o knowledge about the administration part...

EDIT: OK I think I have found the solution...Database compatibility was set to 2008 I changed it to 2014...BOOM...Fastest execution.

:laugh:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Thanks.

The two plans are completely different.

Many of the tables have no indexes at all. In addition toaccelerating queries, indexes provide SQL Server with information and withinsufficient information the optimiser may not be able to guarantee a good plan,which is the case here. At the very least, some or all of those tables shouldhave a clustered index.

Just curious – the cardinality estimator differs, it’s 120for the fast “master” query and 70 for the slow “sword” query…

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden