Subselect for reducing execution time

1

The below query retrieves 44000 rows and takes 2 mins, I cannot use any more keys, can I use subselect to reduce the execution time

thanks

SELECT
ORD.IDCode [Order Id],
ORD.name [Order Name],
ORD.OrderStatusCode [Status],
ORD.Entered [Entered DT],
ORD.RequestedDtm [Requested DT],
OUD.UserDataCode [Data Code],
OUD.Value [Value],
MedEx.RateAmount [Rate]
FROM CV3Order ORD
INNER JOIN CV3OrderUserData OUD on ord.guid=oud.OrderGUID
INNER JOIN CV3MedicationExtension MedEx
ON ORD.GUID = MedEx.GUID
WHERE OUD.UserDataCode = 'RX_Maintenance Rate'
AND ORD.Entered BETWEEN '01-Jan-2016' AND '30-Jun-2016'
ORDER BY [Entered DT] DESC

2

Nothing you do with sub-selects will help. You might be able to improve performance by creating a temp table from the CV3OrderUserData table that only contains the columns you want - and the data you are looking for...however, I suspect it won't improve the overall performance.

Removing the ORDER BY might improve the performance some - but probably not significantly enough.

The only options left are to insure you have the appropriate indexes created to support the query and joins.

1 Like
3

Try replacing:

AND ORD.Entered BETWEEN '01-Jan-2016' AND '30-Jun-2016'

with:

and ord.entered>=cast('2016-01-01' as datetime)
and ord.entered <cast('2016-01-07' as datetime)
1 Like
4
  1. If you almost always query the ORD table by specifying an Entered date range, you should change the clustering key on the table to that column. Then change the ORDER BY to "ORD.Entered" rather than "Entered Date".
  2. If the ORD table has a much larger number of rows than 44000, then try creating a nonclustered index on ORD.Entered, and see if SQL will use that index for this query.
  3. Create an index on the OUD table on ( OrderGUID ) and INCLUDE ( Value )
  4. Create an index on the MedEx table on ( GUID ) and INCLUDE ( RateAmount )
1 Like