Slowwww Query - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Slowwww Query

Author

Topic

FreshDutch
Starting Member

4 Posts

Posted - 2005-12-19 : 20:25:36

I have a query that seems to be taking an very long time to run, hope this is the right spot to post this, so here it go's:

Firstly a little background on the db. A case can have many caseservices, a caseservice can have many (or no) appointments and a caseservice can have a service provider (or not).

The purpose of the query is to obtain some details about the current service for a case. This is defined by the where clause in green. The query also returns the latest appointment (not by date, but by the primary key apt_id)

SELECT Appointments.apt_Date, (a few other fields from case and case service tables)
FROM dbo.Cases INNER JOIN
dbo.CaseServices ON dbo.Cases.ca_id = dbo.CaseServices.cs_ca_id LEFT OUTER JOIN
dbo.ServiceProviders ON dbo.CaseServices.cs_sp_id_Delivery = dbo.ServiceProviders.sp_id LEFT OUTER JOIN dbo.Appointments ON dbo.CaseServices.cs_id = dbo.Appointments.apt_cs_id
WHERE
(dbo.CaseServices.cs_id =
(SELECT TOP 1 cs_id
FROM dbo.CaseServices
WHERE cs_ca_id = ca_id
ORDER BY (CASE WHEN cs_svs_code IN ('COM', 'DNA', 'CAN') THEN 0 ELSE 1 END) DESC, cs_id DESC))

AND

(Apt_id IS NULL OR apt_id =
(SELECT MAX(apt_id)
FROM Appointments
WHERE CaseServices.cs_id = Appointments.apt_cs_id))

From my own preliminary testing the green part seems to be the slowest part of the query. Any advice on how to speed this up would be greatly appreciated!

thiyait
Yak Posting Veteran

70 Posts

Posted - 2005-12-21 : 04:49:48

Can u pls post what are the indexes avail for these tables
and each table having how many records???

FreshDutch
Starting Member

4 Posts

Posted - 2005-12-21 : 17:20:09

Here is the info
Table: Index - No. Rows
Appointmennts: apt_id - 1,262,386
Cases: ca_id - 1,203,980
CaseServices: cs_id - 1,538,598
ServiceProviders: sp_id - 4,975

FreshDutch
Starting Member

4 Posts

Posted - 2005-12-21 : 17:21:24

Sorry, in case that post wasn't clear, there aren't any indexes apart from the primary keys.

cfederl
Starting Member

26 Posts

Posted - 2005-12-21 : 18:34:17

To start, try:
Table Appointments index on apt_cs_id
Table CaseServices index on cs_ca_id

Also, revising this section of the "where" may help ( adding "dbo.Appointments.apt_id is NOT null"

AND (dbo.Appointments.Apt_id IS NULL
OR ( dbo.Appointments.apt_id is NOT null
AND dbo.Appointments.apt_id
= (SELECT MAX(apt_id)
FROM Appointments
WHERE CaseServices.cs_id = Appointments.apt_cs_id)
)
)

Carl Federl

FreshDutch
Starting Member

4 Posts

Posted - 2005-12-21 : 22:54:45

Tried each change seperately and together. Ran a test query which uses the view originally submitted.

No indexes
Orginal: 19m 27s
IS NULL changes: 19m 38s

Indexes
Original: 47s!!!
IS NULL changes: 47s!!!

Thanks very much for your help, didn't think the indexes would make such a massive difference!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources