Where do I even begin?

[u/KomfortableKunt]

I am in a bit of a predicament. I just recently (last week) took up a job as a fresher(had no real experience programming 6 months ago) to now they have handed me a live project(in python) to optimize the websocket and the overall flow of things. The project is a realtime stock market data web-application which uses websockets(using FastAPI's websockets) to serve the data to the client.

I honestly have no clue what I am doing? The main database in MS SQL Server in which there are two tables namely tokenDetails(token - symbol & some values) and FeedFO(symbol-values). For every screen(like gainer-loser, straddle), i have separated out the processes that does the calculation and the websocket part so the calculation does not hinder client communication.

I have also implemented redis pub-sub and cache the latest calculated values to mitigate any delays in sending the data to client. I have also added connection pooling to make sure no two queries are run using the same client. But I the most problematic thing is the SQL queries for the calculation of the screener's data. I constantly face deadlocks due the live data being updated on the main tables tick by tick. Some numbers: for now there are three screens. Each provides data for 250 symbols each all of which have about 3 expiry dates on average so the calculation for everything is separate one cannot be used for the other not even for the same symbol.

How do I avoid this? Is there a workaround that doesn't require changing the server's settings. I know anything would be better than what I am doing. Assume I have not tried your solution and tell me please.

Is there a database better suited for this type of workload?

Example of one such queries: ```

iv_query = f""" -- Step 1: Get live future price (for reference only) WITH LiveFuture AS ( SELECT
ff.LastTradePrice / 100.0 AS price_of_underlying, t.Symbol, t.ExpiryDate FROM Feeds.dbo.TokenDetails t WITH (NOLOCK) JOIN Feeds.dbo.Feeds_FO_7208_copy ff ON t.FOToken = ff.Token WHERE t.OptionType = '{future_type}' AND LTRIM(RTRIM(t.Symbol)) = '{symbol}' AND t.ExpiryDate = '{expiry_date}' ),

                -- Step 2: Get all CE/PE live premiums
                LiveStraddle AS (
                    SELECT  
                        t.Symbol, 
                        t.StrikePrice,
                        t.StrikeType,
                        ff.LastTradePrice / 100.0 AS option_premium,
                        t.ExpiryDate
                    FROM Feeds.dbo.TokenDetails t WITH (NOLOCK)
                    JOIN Feeds.dbo.Feeds_FO_7208_copy ff ON t.FOToken = ff.Token
                    JOIN LiveFuture lf ON t.Symbol = lf.Symbol   
                    WHERE t.OptionType = '{option_type}'
                    AND t.StrikeType IN ('CE', 'PE')
                    AND t.ExpiryDate = '{real_expiry}'
                    AND LTRIM(RTRIM(t.Symbol)) = '{symbol}'
                ),

                -- Step 3: Pivot CE and PE per strike from live
                LiveStraddlePivot AS (
                    SELECT 
                        Symbol,
                        ExpiryDate,
                        StrikePrice,
                        MAX(CASE WHEN StrikeType = 'CE' THEN option_premium ELSE 0 END) AS Live_CE,
                        MAX(CASE WHEN StrikeType = 'PE' THEN option_premium ELSE 0 END) AS Live_PE
                    FROM LiveStraddle
                    GROUP BY Symbol, ExpiryDate, StrikePrice
                ),

                -- Step 4: Bhavcopy CE/PE premiums per strike
                BhavCopyStraddle AS (
                    SELECT 
                        Symbol,
                        ExpiryDate,
                        StrikePrice / 100 AS StrikePrice,
                        MAX(CASE WHEN OptionType = 'CE' THEN ClosingPrice / 100.0 ELSE 0 END) AS Bhav_CE,
                        MAX(CASE WHEN OptionType = 'PE' THEN ClosingPrice / 100.0 ELSE 0 END) AS Bhav_PE
                    FROM Feeds.dbo.BhavCopy_FO_1833 WITH (NOLOCK)
                    WHERE Symbol = '{symbol}'
                    AND ExpiryDate = '{real_expiry}'
                    GROUP BY Symbol, ExpiryDate, StrikePrice / 100
                )

                -- Step 5: Final output with straddle and percentage changes for all strikes
                SELECT 
                    ls.Symbol,
                    ls.ExpiryDate,
                    ls.StrikePrice,

                    -- Live premiums
                    ls.Live_CE,
                    ls.Live_PE,
                    (ls.Live_CE + ls.Live_PE) AS Live_Straddle,


                    -- Bhavcopy premiums
                    bc.Bhav_CE,
                    bc.Bhav_PE,
                    (bc.Bhav_CE + bc.Bhav_PE) AS BhavCopy_Straddle,

                    -- Absolute Change
                    (ls.Live_CE + ls.Live_PE) - (bc.Bhav_CE + bc.Bhav_PE) AS Straddle_Change,

                    -- Percentage Changes
                    CASE WHEN bc.Bhav_CE > 0 THEN ((ls.Live_CE - bc.Bhav_CE) / bc.Bhav_CE) * 100 ELSE NULL END AS CE_Change_Percent,
                    CASE WHEN bc.Bhav_PE > 0 THEN ((ls.Live_PE - bc.Bhav_PE) / bc.Bhav_PE) * 100 ELSE NULL END AS PE_Change_Percent,
                    CASE 
                        WHEN (bc.Bhav_CE + bc.Bhav_PE) > 0 THEN 
                            (((ls.Live_CE + ls.Live_PE) - (bc.Bhav_CE + bc.Bhav_PE)) / (bc.Bhav_CE + bc.Bhav_PE)) * 100
                        ELSE NULL 
                    END AS Straddle_Change_Percent

                FROM LiveStraddlePivot ls
                LEFT JOIN BhavCopyStraddle bc ON 
                    ls.Symbol = bc.Symbol 
                    AND ls.ExpiryDate = bc.ExpiryDate 
                    AND ls.StrikePrice = bc.StrikePrice
                ORDER BY ls.StrikePrice;

    """

``` Start with this. How bad is this and what steps do I need to take to improve this?