Query not really finishing- complex views

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query not really finishing- complex views

Author

Topic

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-08-24 : 10:05:44

Hi- I have a confusing query that I'm running that isn't finishing. It's party slow b/c I'm using views but I don't have a choice about that- I would have to drop and recreate tables otherwise and the dba is not allowing that.

So, the views I'm joining are mostly created by stored procedures with loop through tables and create a cross tab type view with all of the distinct values that are in one column becoming the column heads. In those procedures I have indexes on all joined columns. But it still takes 8 minutes to run one query. That 8 minute query would be fine but then I have to join the results of that query to another view that takes 2 seconds to run. That is the query that won't finish. Both of those views have one column that is unique- it's called prid and that is the column I join on, there are 14K rows. I can't create an index b/c it's a view. Neither views have null values for prid.

I'll list the views
Here is the first view:

create view testview as
select IDS.Id as prid,
a.*,b.*,d.*,e.*
from
(select LVPrID as Id from vwComplicationsLV
union
select LNPrID as Id from vwComplicationsLN
union
Select SRPrID as Id from vwComplicationsSR
union
Select IndPrID as Id from vwIndications
union
Select SI_PRID as Id from vwSIProcs) IDs
Left join vwComplicationsLV a
on IDS.Id = a.LVPrID
Left Join vwComplicationsLN b
on IDS.Id = b.LNPrID
Left Join vwComplicationsSR c
on IDS.Id = c.SRPrID
Left Join vwIndications d
on IDS.Id = d.IndPrID
Left Join vwSIProcs e
on IDS.Id = e.SI_PRID

The reason for the unions is that they may not all have entries for all the IDS that are needed to get results from.

The second view is really quick:
create view vwProcedures as
select a.date as OperationDate ,a.PID as PID, b.LastName, a.OpID as OpID, c.PrID as PrID,d.ProcName as ProcName
from tblOperations2 a, tblDoctors2 b, tblProcs2 c,tblProcNames2 d
where
a.DRID=b.DRID
and c.OpID=a.OpID
and d.PrNID=c.PrNID

The query joining them both:
select * from vwProcedures a,testview b
where a.PrID = b.PrID

When I do an execution plan, it's awful. What I'm trying to end up with is a result set which would have tons of columns all unique on PRID.

Any ideas? Are there any more details I should provide? Anything from the execution plan that would explain why 8 minutes and 2 seconds becomes hours to run?

Nicki
PS> Sorry so long- one other thing... this runs really fast:
select * from vwProcedures a
where prid in (select prid from testview)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources