Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Odd performance caused by Where Clause Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 9:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 5:55 AM
Points: 4, Visits: 14
So I have this odd issue that I can't figure out how to solve. I have a poorly written view which runs slowly but works (returns data in ~ 8 sec). Select * from view1 returns:

Action Name Server
add item1 server1
add item2 server1
update item3 server2


I know i am violating a common rule but have a good reason where I am nesting this view inside of another view which filters the results to an action. In this case my view is called view2 with code Select * from view1 where action = 'update'. This runs in ~ 7 secs.

Now here is the problem. When I run the following: Select * From View2, it returns data in roughly ~7 sec. When I run Select * from View2 Where Server = 'Server2', returns data in roughly ~ 7sec, but when I run Select * from View2 where Server = 'Server1', this returns no rows (which is correct) but take over 1 min to return. Any idea why this would happen or how to troubleshoot this?
Post #1419811
Posted Thursday, February 14, 2013 1:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 2,372, Visits: 7,561
JblakTSql (2/13/2013)
So I have this odd issue that I can't figure out how to solve. I have a poorly written view which runs slowly but works (returns data in ~ 8 sec). Select * from view1 returns:

Action Name Server
add item1 server1
add item2 server1
update item3 server2


I know i am violating a common rule but have a good reason where I am nesting this view inside of another view which filters the results to an action. In this case my view is called view2 with code Select * from view1 where action = 'update'. This runs in ~ 7 secs.

Now here is the problem. When I run the following: Select * From View2, it returns data in roughly ~7 sec. When I run Select * from View2 Where Server = 'Server2', returns data in roughly ~ 7sec, but when I run Select * from View2 where Server = 'Server1', this returns no rows (which is correct) but take over 1 min to return. Any idea why this would happen or how to troubleshoot this?


It's unlikely that I'll have time to help today, but if you want others to help then it might be a good idea to follow the advise in this link --> http://www.sqlservercentral.com/articles/SQLServerCentral/66909/.



Not a DBA, just trying to learn

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

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



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1419868
Posted Thursday, February 14, 2013 9:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 5,078, Visits: 11,862
Do you have an index on the 'server' column?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1420166
Posted Thursday, February 14, 2013 3:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
Any idea why this would happen or how to troubleshoot this?

No way to tell from here. Please post the Actual Execution Plans for all three queries.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1420328
Posted Thursday, February 14, 2013 10:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 5:55 AM
Points: 4, Visits: 14
no, I am not able to create indexes for this data. It has duplicates in it.
Post #1420376
Posted Thursday, February 14, 2013 10:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 5:55 AM
Points: 4, Visits: 14
opc.three (2/14/2013)
Any idea why this would happen or how to troubleshoot this?

No way to tell from here. Please post the Actual Execution Plans for all three queries.


I'll pull that and post it. Regardless, i was trying to understand why this would happen. I can tell you that the execution plan for the filter that works looks the same as the filter that doesn't work. The difference is in cost for the filter and nested loops
Post #1420378
Posted Friday, February 15, 2013 1:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 5,078, Visits: 11,862
JblakTSql (2/14/2013)
no, I am not able to create indexes for this data. It has duplicates in it.


Yes you are. Indexes do not have to contain unique values.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1420417
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse