0

I have one query for the report.

Query

Use RIS

select CİHAZ,sum(sayi) as [Çekim Sayısı], count(*) as [Hasta Sayısı] from
(select CİHAZ,[KABUL NO],[TC NO], count(*) as Sayi from 

(SELECT 
a.GeneralReceptionNo as [KABUL NO], pa.TRIDNO as [TC NO],
m.EtiketAdi as [CİHAZ]
FROM   Appointments a 
INNER JOIN Examinations e    ON a.ObjectID=e.AppointmentID   --aobject 1392334
INNER JOIN Patients pa       ON a.PatientID=pa.ObjectID
INNER JOIN Procedures pr     ON a.ProcedureID=pr.ObjectID
LEFT OUTER JOIN Machines m   ON a.MachineID=m.ObjectID
LEFT OUTER JOIN ExaminationReportAssistanceAssociation ra ON e.ObjectID =ra.ExaminationID  
INNER JOIN Hospital..Receptions r       ON r.GeneralReceptionNo =a.GeneralReceptionNo 
WHERE 
CONVERT (DATE,e.CompletedDate)>='2018-12-01'
AND CONVERT (DATE,e.CompletedDate)<'2019-12-30' AND e.IsCompleted =1) sorgu
GROUP BY CİHAZ,[KABUL NO],[TC NO] ) sorgu2
GROUP BY CİHAZ ORDER BY CİHAZ

My problem is ,when i wrote condition is CONVERT (DATE,e.CompletedDate)>='2018-12-01' AND CONVERT (DATE,e.CompletedDate)<'2019-11-30' , query not ended in 50 min but

if i wrote condition is CONVERT (DATE,e.CompletedDate)>='2018-11-01' AND CONVERT (DATE,e.CompletedDate)<'2019-11-30' I can get the result in 12 sec.

First example time range wider than the second example. I can't find a solution. Thanks for the help.

  • 1
    What is the type of the column e.CompletedDate ? How many rows have "IsCompleted = 1" vs 0 ? – Danielle Paquette-Harvey Jan 3 at 14:49
  • 1. You should include Actual execution plan for both queries in your question. 2.Are you sure your first condition is correct? date >= '2018-12-01' and date < '2018-11-30' returns NOTHING – sepupic Jan 3 at 15:27
  • data type is smalldatetime - @DaniellePaquette-Harvey SELECT COUNT(*) FROM RIS..Examinations e WHERE e.CompletedDate>='2018-12-01' and e.CompletedDate<'2019-11-30' and e.IsCompleted =1 *75134-- result – Burak Üner Jan 4 at 8:11
  • SELECT COUNT() FROM RIS..Examinations e WHERE e.CompletedDate>='2018-11-01' and e.CompletedDate<'2019-11-30' and e.IsCompleted =1 *142383 -- result – Burak Üner Jan 4 at 8:13
  • SELECT COUNT(*) FROM RIS..Examinations e WHERE e.CompletedDate>='2018-12-01' and e.CompletedDate<'2019-11-30' and e.IsCompleted =0 ***1480 -- result *** – Burak Üner Jan 4 at 8:13
1

Why are you using the condition?

CONVERT (DATE,e.CompletedDate) >= '2018-11-01' 
AND CONVERT (DATE,e.CompletedDate) < '2018-11-30'

This query is not SARGable, just rewrite your query as

e.CompletedDate >= '2018-11-01' 
AND e.CompletedDate < '2018-11-30'

And ensure you have an index with CompletedData field as the first field. As far as I see the condition, it should work in the same way but much faster and will use minimal resources.

As per your comment, apart from the proposed change in your WHERE condition you need to create a new index as:

CREATE INDEX IX_Examinations_CompletedDate ON Examinations(CompletedDate)
  • 2018-11-01 -- 2018-11-30 --- 2018-12-01--2018-11-30-- Convert using or not using dosent change anything. Still i try something .Thanks for help. – Burak Üner Jan 3 at 7:01
  • And i try to create index but /* USE [RIS] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Examinations] ([IsCompleted],[CompletedDate]) INCLUDE ([ObjectID],[AppointmentID]) GO */ time is too long. – Burak Üner Jan 3 at 7:07
  • I think you are accepting the index that the system proposed but not running the Index I recommended. Not sure wich one is better but please, try to run the command I posted to CREATE INDEX. – Angel M. Jan 3 at 7:15
  • @Angel - the recommended one is better here. Otherwise there would be lookups needed. And an oddity about converting to date is that the QO understands this and handles it as if it’s sargable. – Rob Farley Jan 3 at 20:24
  • sorry for mistake . I add wrong date on query . I updated. e.CompletedDate>='2018-12-01' and e.CompletedDate<'2019-11-30' --example 1 -- Not ended in 50 min e.CompletedDate>='2018-11-01' and e.CompletedDate<'2019-11-30' -- example 2 - Ended 12 sec – Burak Üner Jan 4 at 8:23
0

This sounds like statistics to me. It knows how many rows there are in November but incorrectly thinks there are very few rows in December. I imagine if you look at the query plans you’ll see they’re quite different.

Try updating your statistics and seeing if things improve.

Also, do look for the missing index recommendations here, because indexes will be critical to performance.

  • Recommended index is /* USE [RIS] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Examinations] ([IsCompleted],[CompletedDate]) INCLUDE ([ObjectID],[AppointmentID]) GO */ but it does not reduce the time. Thanks for message. – Burak Üner Jan 4 at 8:24
  • But did you update the stats?? – Rob Farley Jan 4 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.