February 13, 2013 at 9:47 pm
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?
February 14, 2013 at 1:45 am
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/%5B/url%5D.
February 14, 2013 at 9:55 am
Do you have an index on the 'server' column?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 14, 2013 at 3:35 pm
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
February 14, 2013 at 10:01 pm
no, I am not able to create indexes for this data. It has duplicates in it.
February 14, 2013 at 10:04 pm
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
February 15, 2013 at 1:16 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply