SQL query very slow

Question

I am trying to create a view for our firm to check the subdollar price.

I create a temp table in the view hoping to get the program running Faster. But it does not work. The query is still super slow... could anyone help me improve my query or do you have ant suggestion what caused the query running slow? Thanks.

This program is to implement: - If the New Order violates the sub-penny rule - If the Order Ack violates the sub-penny rule - When the New Order and Order Ack price are not equal

•Only orders with correct number of decimals/rounding should be in the system – (on Order price)

/*
    price < $ 0.01    6 decimal places
    price< $1.00     4 decimal places
    price>=  $1.00  2 decimal places
    price>= $100,000   1 decimal places
*/


WITH PRICE (newOrderPrice, NewOrderprice, newTransactionGUID, newMsgText,ackOrderPrice,AckOrderprice,ackTransactionGUID,ackMsgText)
     AS (
         SELECT  
            --remove the trailing 0 in price
            REVERSE(SUBSTRING(reverse(NewOrderprice), PATINDEX('%[1-9.]%', reverse(NewOrderprice)), len(NewOrderprice) - PATINDEX('%[1-9.]%', reverse(NewOrderprice)) + 1))  AS newOrderPrice
            , NewOrderprice
            , newTransactionGUID
            , newMsgText
            , REVERSE(SUBSTRING(reverse(AckOrderprice), PATINDEX('%[1-9.]%', reverse(AckOrderprice)), len(AckOrderprice) - PATINDEX('%[1-9.]%', reverse(AckOrderprice)) + 1))  AS ackOrderPrice
            , AckOrderprice
            , ackTransactionGUID
            , ackMsgText

            FROM
            (
                SELECT    --make sure we get the price from transactiontext tag 44 is only numbers
                          --------------------new Order----------------------------------------------------------
                          LEFT(SUBSTRING(LscDP.dbo.ParseTransactionText(44,t.MsgText), PATINDEX('%[0-9.]%', LscDP.dbo.ParseTransactionText(44,t.MsgText)), 8000),
                          PATINDEX('%[^0-9.]%', SUBSTRING(LscDP.dbo.ParseTransactionText(44,t.MsgText), PATINDEX('%[0-9.]%',
                          LscDP.dbo.ParseTransactionText(44,t.MsgText)), 8000) + 'X') -1)  
                          AS NewOrderprice
                          , t.TransactionGUID AS newTransactionGUID
                          , t.MsgText AS newMsgText
                          -----------------ack order------------------------------------------------------------
                          ,LEFT(SUBSTRING(LscDP.dbo.ParseTransactionText(44,ack.MsgText), PATINDEX('%[0-9.]%', LscDP.dbo.ParseTransactionText(44,ack.MsgText)), 8000),
                          PATINDEX('%[^0-9.]%', SUBSTRING(LscDP.dbo.ParseTransactionText(44,ack.MsgText), PATINDEX('%[0-9.]%',
                          LscDP.dbo.ParseTransactionText(44,ack.MsgText)), 8000) + 'X') -1)  
                          AS AckOrderprice
                          , ack.TransactionGUID AS ackTransactionGUID
                          , ack.MsgText    AS ackMsgText
                FROM      LscDP..DailyTransactionText t
                          INNER JOIN  LscDP..DailyTransactionText ack
                                      ON t.Sender = ack.Sender
                                         AND t.Branch = ack.Branch
                                         AND t.BranchSeq = ack.BranchSeq
                                         and ack.MsgType = '1' 
                                         and t.MsgType = '0'
                          INNER JOIN LscDP..DailyOrders o
                                      ON t.TransactionGUID = o.TransactionGUID
                                         OR ack.TransactionGUID = o.TransactionGUID
                          LEFT JOIN AccountsEX..OutQueues oq
                                      ON o.Route = oq.OutQueue


                WHERE   
                        t.Symbol not like '%-%' -- exclude foreign symbols
                        AND LscDP.dbo.ParseTransactionText(167,t.MsgText) <> 'OPT'  --Exclude options
                        AND LscDP.dbo.ParseTransactionText(167,ack.MsgText) <> 'OPT'  --Exclude options
                        AND ISNULL(oq.QueueType, '') NOT IN ('DC','CL')
                        AND LscDP.dbo.ParseTransactionText(44,t.MsgText) <> ''
                        AND LscDP.dbo.ParseTransactionText(44,ack.MsgText) <> ''
                        AND LscDP.dbo.ParseTransactionText(44,t.MsgText) like '%.%'   --Only need verify the subdollar.
                        AND LscDP.dbo.ParseTransactionText(44,ack.MsgText) like '%.%'   --Only need verify the subdollar.
            ) X
           ) 


SELECT  
       CASE  WHEN 
                  ----------------------NEW-------------------------------------
                 ( --Price < 0.01 , 6 decimal max

                    CAST(newOrderPrice AS FLOAT) < 0.01 
                    AND (LEN(newOrderPrice) - PATINDEX('%.%',newOrderPrice)) <= 6
                 )
                OR
                 ( --Price < 1 , 4 decimal max

                    CAST(newOrderPrice AS FLOAT) < 1 
                    AND (LEN(newOrderPrice) - PATINDEX('%.%',newOrderPrice)) <= 4
                 ) 
                 OR
                 (  --Price >= 1 , 2 decimal max

                   CAST(newOrderPrice AS FLOAT) >= 1
                   AND (LEN(newOrderPrice) - PATINDEX('%.%',newOrderPrice)) <= 2
                  )
                 OR
                 (  --Price >= 100000 , 1 decimal max
                   CAST(newOrderPrice AS FLOAT) >= 100000
                   AND (LEN(newOrderPrice) - PATINDEX('%.%',newOrderPrice)) <= 1
                  )
                  ----------------------ACK-------------------------------------
                 OR
                 ( --Price < 0.01 , 6 decimal max

                    CAST(ackOrderPrice AS FLOAT) < 0.01 
                    AND (LEN(ackOrderPrice) - PATINDEX('%.%',ackOrderPrice)) <= 6
                  )
                 OR
                 ( --Price < 1 , 4 decimal max

                    CAST(ackOrderPrice AS FLOAT) < 1 
                    AND (LEN(ackOrderPrice) - PATINDEX('%.%',ackOrderPrice)) <= 4
                 ) 
                 OR
                 (  --Price >= 1 , 2 decimal max

                   CAST(ackOrderPrice AS FLOAT) >= 1
                   AND (LEN(ackOrderPrice) - PATINDEX('%.%',ackOrderPrice)) <= 2
                  )
                 OR
                 (  --Price >= 100000 , 1 decimal max
                   CAST(ackOrderPrice AS FLOAT) >= 100000
                   AND (LEN(ackOrderPrice) - PATINDEX('%.%',ackOrderPrice)) <= 1
                  )
                  THEN 'Qualified'
                  WHEN  ISNULL(ackOrderPrice,'') <> ISNULL(newOrderPrice,'')
                  THEN 'NewAckPriceMismatch'
                  ELSE 'Violation'
                  END AS PriceVerify


       ,*



FROM  PRICE

What database? What's the schema? Where is the explain plan? — Mat, Jan 02 '18 at 21:26
This is MS SQL . i am not sure how to using the indexing columns in my case @Mat — Rachel, Jan 03 '18 at 18:50

score 2 · Answer 1 · answered Jan 02 '18 at 21:28

Found your problem:

AND price like '%.%'

By using a wildcard to start with you are forcing a table scan, which will tank performance. There are several ways you could get the same result without making something not a SARG. Patindex would be my reccomendation:

https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql

score 1 · Answer 2 · answered Jan 02 '18 at 23:04

In general the Like condition with % is slowing down the queries and the parsetransactionText also sound not that fast

AND price like '%.%'
    AND t.Symbol not like '%-%' -- exclude foreign symbols
                            AND LscDP.dbo.ParseTransactionText(167,MsgText) <> 'OPT'  --Exclude options

Plus please manage the trailing zero as in following example How to display two digits after decimal point in SQL Server

another big mistake is to filter later from the temp table result, move up the filters

WHERE   Price <> ''
        AND price like '%.%'   --Only need verify the subdollar.

in order to reduce the size of the temptable

plus, all the "postprocessing" if possible needs to be moved down on the temp table in order to be performed only to a smaller set of data

LEFT(SUBSTRING(LscDP.dbo.ParseTransactionText(44,MsgText), PATINDEX('%[0-9.]%', LscDP.dbo.ParseTransactionText(44,MsgText)), 8000),
                          PATINDEX('%[^0-9.]%', SUBSTRING(LscDP.dbo.ParseTransactionText(44,MsgText), PATINDEX('%[0-9.]%',
                          LscDP.dbo.ParseTransactionText(44,MsgText)), 8000) + 'X') -1)  
                          AS price

in general rearranging data as you're doing in your select and where conditions should be avoited in sql, better cast and convert data BEFORE inserting in the table in order to light up queries

SQL query very slow

2 Answers2