SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select Query Performance
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

tkizer
Almighty SQL Goddess

USA
36617 Posts

Posted - 06/24/2014 :  15:07:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Lamprey

quote:
Originally posted by cardgunner

Finally. So instead of (@ext_emno='' or gp.ext_emno=@ext_emno) I reversed the table (@ext_emno='' or ccom100.t_emno=@ext_emno)and it ran in less then a minute.

Why would that be?


CardGunner

I can't say for sure, but it is possible that changing the query around caused QL to create/use a different execution plan.



And likely it is indexed in that table but not in the other.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
316 Posts

Posted - 06/25/2014 :  11:19:56  Show Profile  Reply with Quote
@gbritton and @tkizer THANK YOU for all your help!

The report now runs alot faster and the users are happy.

I appreciate your time.

CardGunner
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36617 Posts

Posted - 06/25/2014 :  12:46:39  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
316 Posts

Posted - 07/08/2014 :  10:05:45  Show Profile  Reply with Quote
If I wanted to run this query with nolocks do I have to write that in for each table?

CardGunner
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36617 Posts

Posted - 07/08/2014 :  12:37:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by cardgunner

If I wanted to run this query with nolocks do I have to write that in for each table?

CardGunner



Yes, but why do you want to do that? Have you looked into RCSI?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
316 Posts

Posted - 07/08/2014 :  15:54:48  Show Profile  Reply with Quote
RCSI? The Royal College of Surgeons in Ireland? No should I? :)

I want to do that because when I run this query in VS2010 and thru SSRS it locks up our invoice tables. I thought getting the performance would have eliminated this.

CardGunner
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36617 Posts

Posted - 07/08/2014 :  16:19:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
Adding nolock is dangerous as it could mean dirty reads (bad data). I would instead fix the performance issue by analyzing the code, execution plan and table design. You might be missing indexes too.

RCSI= read committed snapshot isolation

It's a big topic, but any time I hear people using nolock, I automatically will say RCSI is "better" as it's less "wrong".

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
316 Posts

Posted - 07/09/2014 :  08:55:49  Show Profile  Reply with Quote
From the beginning we migrated our db from SQL 2005 operating on windows server 2003 to SQL 2012 operating on Windows Server 2008. Now this report operated poorly before. It took a long time to complete but it would eventully. While the report was running there was no indications of table locks. When we updated the report to the new server it would run and not complete and lock up the tables. So I fine tuned it. In the morning it will run good, and complete. But by the afternoon it will run, not complete, and lock up the tables. By the table I mean our invoicing tables. If they are locked up we can't complete an invoice. That is bad.

Now I can run the query from anytime from SQL Manager. It is when I try to run it in VS2010 or in SSRS this happens. Now we have other reorts running but none seem to affect the system like this one does.

We are in the process of creating a backup db, where it updates every 30 minutes, and run the reports against that. Freeing up our live db at the sacrifice of reporting old data.

I'm just wondering what could cause the report to do this on the new server where it didn't on the old.

CardGunner
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36617 Posts

Posted - 07/09/2014 :  12:17:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
Sounds like a bad execution plan, parameter sniffing, or out-of-date stats (or a combo).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000