View: DJT_MECH_REPORT

SELECT     machine, start, shift, mech1, mech2, verb1, prod_date
FROM         dbo.DJT_MECH_PAGED
UNION ALL
SELECT     machine, start, shift, mech1, mech2, verb1, prod_date
FROM         dbo.DJT_SHOW_MECH_ARRIVE
UNION ALL
SELECT     TOP (100) PERCENT machine, start, shift, mech1, mech2, verb1, prod_date
FROM         dbo.DJT_Mech_show_logout
ORDER BY machine, start, shift

===================================================================== View: DJT_MECH_PAGED

SELECT     TOP (100) PERCENT dbo.Prodtrack.machine, dbo.Prodtrack.shift, dbo.Prodtrack.start, dbo.Prodtrack.mechpg, dbo.Prodtrack.mech1, 
                      dbo.Prodtrack.mech2, CASE WHEN dbo.Prodtrack.mechpg = 0 THEN 'pageinit' END AS verb1, dbo.Prodtrack.prod_date
FROM         dbo.DJT_SHOW_MECH_PAGE_RESP INNER JOIN
                      dbo.Prodtrack ON dbo.DJT_SHOW_MECH_PAGE_RESP.machine = dbo.Prodtrack.machine AND 
                      dbo.DJT_SHOW_MECH_PAGE_RESP.shift = dbo.Prodtrack.shift AND dbo.DJT_SHOW_MECH_PAGE_RESP.start = dbo.Prodtrack.stop
WHERE     (dbo.Prodtrack.mechpg = 0) AND (dbo.Prodtrack.mech1 = 0) AND (dbo.Prodtrack.status <> 'lgin')
ORDER BY dbo.Prodtrack.machine, dbo.DJT_SHOW_MECH_PAGE_RESP.start

==================================================================View: DJT_SHOW_MECH_ARRIVE

SELECT     TOP (100) PERCENT dbo.Prodtrack.machine, dbo.Prodtrack.start, dbo.Prodtrack.mech1, dbo.Prodtrack.shift, dbo.Prodtrack.mechpg, 
                      dbo.Prodtrack.mech2, CASE WHEN dbo.Prodtrack.operator != 0 AND 
                      dbo.Prodtrack.mech1 != 0 THEN 'Respond' WHEN dbo.prodtrack.jobtask = 9999999999 AND dbo.prodtrack.operator = 0 AND 
                      dbo.prodtrack.mech1 <> 0 THEN 'Maint.' ELSE 'Setup' END AS verb1, dbo.Prodtrack.operator, dbo.Prodtrack.prod_date
FROM         dbo.Prodtrack INNER JOIN
                      dbo.Prodtrack AS Prodtrack_1 ON dbo.Prodtrack.machine = Prodtrack_1.machine AND dbo.Prodtrack.prod_date = Prodtrack_1.prod_date AND 
                      dbo.Prodtrack.start = Prodtrack_1.stop
WHERE     (dbo.Prodtrack.mech1 <> 0) AND (Prodtrack_1.mech1 = 0)
ORDER BY dbo.Prodtrack.machine, dbo.Prodtrack.start

==================================================================View: DJT_SHOW_MECH_LOGOUT

SELECT     TOP (100) PERCENT dbo.Prodtrack.machine, dbo.Prodtrack.shift, dbo.Prodtrack.start, dbo.Prodtrack.mechpg, CASE WHEN dbo.Prodtrack.mechpg = 0 AND
                       dbo.Prodtrack.mech1 = 0 THEN 'Logout' ELSE 'END' END AS verb1, dbo.Prodtrack.mech1, dbo.Prodtrack.mech2, dbo.Prodtrack.prod_date
FROM         dbo.Prodtrack INNER JOIN
                      dbo.DJT_SHOW_MECH_PAGE_RESP ON dbo.Prodtrack.machine = dbo.DJT_SHOW_MECH_PAGE_RESP.machine AND 
                      dbo.Prodtrack.shift = dbo.DJT_SHOW_MECH_PAGE_RESP.shift AND dbo.Prodtrack.start = dbo.DJT_SHOW_MECH_PAGE_RESP.stop
WHERE     (dbo.Prodtrack.mechpg <> 1) AND (dbo.Prodtrack.mech1 = 0) OR
                      (dbo.Prodtrack.status = 'lgof') AND (dbo.Prodtrack.logged <> N' qc ')
ORDER BY dbo.Prodtrack.machine, dbo.Prodtrack.start





                       View: DJT_SHOW_MECH_PAGE_RESP 

SELECT     TOP (100) PERCENT machine, shift, start, stop, mechpg, mech1, mech2, prod_date, CASE WHEN t1.mech1 = 0 AND 
                      t1.mechpg = 1 THEN 'LiteOn' WHEN t1.mech1 > 0 AND t1.mechpg = 1 THEN 'MechResp' WHEN t1.mech1 > 0 AND t1.mechpg = 0 AND 
                      status = 'byps' THEN 'Bypass' ELSE t1.status END AS verb1
FROM         dbo.Prodtrack AS t1
WHERE     (mechpg = 1) OR
                      (mechpg = 0) AND (mech1 <> 0)
ORDER BY machine, start, stop, shift



Prodtrack is an actual table all these other items are views.

quote:

---

Originally posted by robvolk

Might help to take the TOP and ORDER BY clauses out of the view definitions, it's certainly not speeding up the query.

---

quote:

---

Originally posted by tkizer

What indexes do you have on the underlying tables? Hopefully you have indexes on all of the join columns.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

---

quote:

---

Originally posted by whitefang

Why do you need a TOP 100 PERCENT in the DJT_MECH_REPORT query?

---

quote:

---

I do all my programming what limited amount I know thru the Studio Manager GUI.

---

quote:

---

Originally posted by visakh16

do you have an index on i.prod_date field? also what determines value of manually entered date?

---

quote:

---

Originally posted by visakh16

then replace

where datediff(d,i.prod_date,'{manual entered date}')=0

by

where i.prod_date>=dateadd(d,datediff(d,0,'{manual entered date}'),0)
and i.prod_date< dateadd(d,datediff(d,0,'{manual entered date}')+1,0)


and try query

---