very long queries

[nirs]

hi
i have a very big problem this query takes a very long time
i found that the only problem is the "(R_Batch_Test_V.tDate BETWEEN ShamirOp.dbo.CeramicLine.proc_date AND '040404')" part of the where condintion,
when i take off this part the query run fast.

need help!!!
thank you


SELECT R_Batch_Test_V.CNC, R_Batch_Test_V.ceramicId, R_Batch_Test_V.client, Count(R_Batch_Test_V.passB) AS Total,
ShamirOp.dbo.CeramicHead.product_no
FROM ((R_Batch_Test_V INNER JOIN ShamirOp.dbo.CeramicHead ON R_Batch_Test_V.CNC = ShamirOp.dbo.CeramicHead.ceramic_no) INNER JOIN ShamirOp.dbo.CeramicLine ON
(ShamirOp.dbo.CeramicHead.ceramic_no = ShamirOp.dbo.CeramicLine.ceramic_no) AND
(R_Batch_Test_V.ceramicId = ShamirOp.dbo.CeramicLine.serial_no)) INNER JOIN ShamirOp.dbo.Batch ON
R_Batch_Test_V.batch_no = ShamirOp.dbo.Batch.batch_no
WHERE (R_Batch_Test_V.tDate BETWEEN ShamirOp.dbo.CeramicLine.proc_date AND '040404') AND (ShamirOp.dbo.Batch.work_order_no Not Like 'pi%') AND ([client]= 'CD2') AND ShamirOp.dbo.CeramicHead.product_no=3
GROUP BY R_Batch_Test_V.CNC, R_Batch_Test_V.ceramicId, R_Batch_Test_V.client
, ShamirOp.dbo.CeramicHead.product_no

 

[markosimic]

Do you use msSQL server 7? i had the same problem mwith mssql 7 and, to be more misterious, it did not happen always, but just in some cases. Anyway, what I did was that I have replaced "between" with "greater-then and less-then" logic

(R_Batch_Test_V.tDate BETWEEN ShamirOp.dbo.CeramicLine.proc_date AND '040404')
->
(R_Batch_Test_V.tDate >= ShamirOp.dbo.CeramicLine.proc_date AND R_Batch_Test_V.tDate <= '040404')

I hope it helps. And if anyone know what could be the &quot;real&quot; problem (what is behind) - please share with us.

 

[nirs]

Yes i use msSql 7