CODEWITHSUNDEEP
sqlsql-server
Bereket Teketel
Bereket Teketel

Reputation: 33

Strange SQL Criteria Filter Issue

I am running an SQL server that runs a lot of queries. But all my queries perform well. But I have this strange query. Here is the query:

  SELECT  _PATIENT_ORDER_LIST.*
    FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY SRH_SearchResultOrientedOrders.SendDate DESC ) AS RowIndex ,
                        ( SELECT    COUNT(SRH_SearchResultOrientedOrders.PatientID)
                          FROM      SRH_SearchResultOrientedOrders
                          WHERE     SRH_SearchResultOrientedOrders.PatientID <> 0
                                    AND SRH_SearchResultOrientedOrders.IsReceived = 'True'
                                    AND PatientName LIKE '%%'
                                    AND Category = 'Hematology'
                                    AND ( IsSpacementToDesk = 'true' )
                                    AND ( OrderStatus = 1 )
                                    AND ( IsOrderDone = 'true' )
                                    AND ( ResultApprovedBy IS NULL )
                                    AND OrderTo = 'Laboratory'
                        ) AS NumberOfRows ,
                        SRH_SearchResultOrientedOrders.*
              FROM      SRH_SearchResultOrientedOrders
              WHERE     SRH_SearchResultOrientedOrders.PatientID <> 0
                        AND SRH_SearchResultOrientedOrders.IsReceived = 'True'
                        AND PatientName LIKE '%%'
                        AND Category = 'Hematology'
                        AND ( IsSpacementToDesk = 'true' )
                        AND ( OrderStatus = 1 )
                        AND ( IsOrderDone = 'true' )
                        AND ( ResultApprovedBy IS NULL )
                        AND OrderTo = 'Laboratory'
            ) AS _PATIENT_ORDER_LIST
    WHERE   _PATIENT_ORDER_LIST.RowIndex > 0
            AND _PATIENT_ORDER_LIST.RowIndex <= 20

If I put 'Chemistry' on the category column filtration it will take 4 second to display the records but if I put 'Hematology' it will take 1 min 30 seconds the number of records found under chemistry is 195 and where as on hematology filteration is 69. I couldn't figure out why the query is slow. Please help

Upvotes: 0

Views: 81

Answers (2)

dnoeth
dnoeth

Reputation: 60462

Your NumberOfRows calculation is the same as a COUNT(*) OVER ():

SELECT  _PATIENT_ORDER_LIST.*
FROM    ( SELECT    Row_Number() Over ( ORDER BY SRH_SearchResultOrientedOrders.SendDate DESC ) AS RowIndex ,
                    Count(*) Over () AS NumberOfRows ,
                    SRH_SearchResultOrientedOrders.*
          FROM      SRH_SearchResultOrientedOrders
          WHERE     SRH_SearchResultOrientedOrders.PatientID <> 0
                    AND PatientName LIKE '%%'
                    AND Category = 'Hematology'
                    AND ( IsSpacementToDesk = 'true' )
                    AND ( OrderStatus = 1 )
                    AND ( IsOrderDone = 't.rue' )
                    AND ( ResultApprovedBy IS NULL )
                    AND OrderTo = 'Laboratory'
        ) AS _PATIENT_ORDER_LIST
WHERE   _PATIENT_ORDER_LIST.RowIndex > 0
        AND _PATIENT_ORDER_LIST.RowIndex <= 20

If it's not improving performance at least it's improving readability

Upvotes: 2

IVNSTN
IVNSTN

Reputation: 9299

< long comment >

Subquery is not linked to the outer query. Do you realize that for every row subquery will return the same value? COUNT(SRH_SearchResultOrientedOrders.PatientID) - it's a total count of all rows that satisfy filter criteria.

_PATIENT_ORDER_LIST.RowIndex <= 20 - TOP 20 ... ORDER BY could probably be faster. Because there is no need to number all the rows (no need for row_number since you can do ORDER BY SendDate ASC).

this code:

IsReceived = 'True'
AND IsSpacementToDesk = 'true'
AND OrderStatus = 1
AND IsOrderDone = 'true'
AND ResultApprovedBy IS NULL

says that you probably need to redesign your status columns. All these criteria might mean one particular status_id. This could be a computed or persistent column.

Storing booleans as strings is definitely a bad idea. Use BIT instead.

To understand what's the problem with performance one should investigate actual execution plan and statistics counters. Although, I doubt a lot that it will show anything except "everything is very bad".

Ad-hoc queries with different filter criteria will be treated by server as two absolutely different queries with probably different execution plans - because string literals are part of the query which make them symbolically different.

Upvotes: 2

Related Questions