Creating a view from a table w/L outer Join

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

General SQL Server Forums

New to SQL Server Programming

Creating a view from a table w/L outer Join

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-02-28 : 13:27:19

I dont know all the rules and such when using SQL2005 and when I try this it seems to work but it takes such a long time to run. And it sometimes also times out. So here is the query I am trying please let me know if it is the query that is messed up as to why it either takes so long or sometimes times out. THanks for looking:

--------------------------------------------------------------
SELECT TOP (100) PERCENT t1.machine, t1.start, t1.shift, t1.mech1, t1.mech2, t1.mechpg, t1.row, t1.t_stamp, CASE WHEN t1.mech1 = 0 AND
t1.mechpg = 1 THEN 'PageInit' WHEN t1.mech1 > 0 AND t1.mechpg = 1 THEN 'MechResp' WHEN t1.mech1 > 0 AND
t1.mechpg = 0 THEN 'Bypass' ELSE 'logout' END AS verb1
FROM dbo.prodtracklive AS t1 LEFT OUTER JOIN
dbo.prodtracklive AS t2 ON t1.row = t2.row + 1
WHERE (t1.mechpg <> t2.mechpg) OR
(t1.mech1 <> t2.mech1)
ORDER BY t1.machine, t1.t_stamp, t1.shift

nr
SQLTeam MVY

12543 Posts

Posted - 2009-03-01 : 21:28:23

ON t1.row = t2.row + 1
That's not going to use an index on t2.
Try creating a computed column = row + 1 and index it (include mechpg and mech1 so it's covering)
so that it gets materialised (you could also maitain it by other means).

FROM dbo.prodtracklive AS t1 LEFT OUTER JOIN
dbo.prodtracklive AS t2 ON t1.row = t2.rowplusone

But before you do that try creating an index on row - include mechpg and mech1.
Also create an index on row and include all the other columns accessed (unless there's a clustered index on it and not many other columns).

select ...
FROM dbo.prodtracklive AS t3
LEFT OUTER JOIN dbo.prodtracklive AS t2
ON t3.row = t2.rowplusone
and (t3.mechpg <> t2.mechpg OR t3.mech1 <> t2.mech1)
join prodtracklive t1
on t3.row = t1.row
order by ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources