1

Topic: Time interval

Hello. I was tangled absolutely
There is such request
SELECT dbo. Payment. PaymentID, dbo. Payment. PaymentContractKod, dbo. WaterUser. WaterUserName, dbo. Payment. PaymentDate FROM dbo. Payment
INNER JOIN dbo. WaterUser ON dbo. Payment. PaymentContractKod = dbo. WaterUser. WaterUserKod
WHERE (dbo. Payment. RecType <> 2) AND (dbo. Payment. LangID = 0) AND (dbo. Payment. PaymentDate> = CONVERT (DATETIME, ' 2017-8-30 ', 102)) AND (dbo. Payment. PaymentDate <= CONVERT (DATETIME, ' 2017-9-30 11:59:59 PM ', 102))
In this time interval the request hangs up and kol-vom records in it of the order 700. It is necessary to change an interval started to deliver for example 2017-8-29 and less works on hurrah and  in records in this case in times increases. I do not know as think.
Thanks...

2

Re: Time interval

How many records in initial tables?

3

Re: Time interval

The order 30000. Simply strange starts what to brake only in that case if initial date of an interval more than 8/29/2017.

4

Re: Time interval

In the second how many?

5

Re: Time interval

The order 10000.

6

Re: Time interval

And try between instead of comparing.
Purely . Can .
Probably something in the input data.

7

Re: Time interval

Not .

8

Re: Time interval

aseylanyan;
smile

WITH c AS
(SELECT dbo. Payment. PaymentID, dbo.Payment.PaymentContractKod,dbo.WaterUser.WaterUserName,
dbo. Payment. PaymentDate
FROM dbo. Payment INNER JOIN dbo. WaterUser ON dbo. Payment. PaymentContractKod = dbo. WaterUser. WaterUserKod
WHERE (dbo. Payment. RecType <> 2) AND (dbo. Payment. LangID = 0) AND
(dbo. Payment. PaymentDate> = CONVERT (DATETIME, ' 2017-08-29 ', 102))
AND (dbo. Payment. PaymentDate <CONVERT (DATETIME, ' 2017-10-01 ', 102))
)
SELECT * FROM c
WHERE
(PaymentDate> = CONVERT (DATETIME, ' 2017-08-30 ', 102))
AND (PaymentDate <CONVERT (DATETIME, ' 2017-10-01 ', 102))

9

Re: Time interval

Alas sad

10

Re: Time interval

aseylanyan;
(nolock)?

11

Re: Time interval

Make SELECT top
Deduce a certain amount of lines on fail dates.
Look that there not so it turned out.

12

Re: Time interval

SELECT P.PaymentID, P.PaymentContractKod, W.WaterUserName, P.PaymentDate
FROM dbo. Payment P JOIN dbo. WaterUser W ON P.PaymentContractKod=W.WaterUserKod
WHERE P.RecType <> 2 AND P.LangID=0 AND P.PaymentDate> = ' 20170830 ' AND P.PaymentDate <' 20171001 ';

That there with indexes?

13

Re: Time interval

.;
You are a writer?

14

Re: Time interval

Write dates in the correct, invariant format, enumerate statistics (it for certain became rotten). Look that there with indexes.
At you it is exact  request, in that type in which you wrote, instead of parametrized?

15

Re: Time interval

Alas. But the question and did not dare.

16

Re: Time interval

aseylanyan
Here iap :

SELECT P.PaymentID, P.PaymentContractKod, W.WaterUserName, P.PaymentDate
FROM dbo. Payment P JOIN dbo. WaterUser W ON P.PaymentContractKod=W.WaterUserKod
WHERE P.RecType <> 2 AND P.LangID=0 AND P.PaymentDate> = ' 20170830 ' AND P.PaymentDate <' 20171001 ';

That there with indexes?

17

Re: Time interval

And if to remove JOIN and communication to reflect in WHERE?