Factor Calculation Performance Compared: Python + File Storage vs. DolphinDB

This test compares factor calculation implementations using Python with file storage solutions and the DolphinDB integrated framwork.

• Python + File Storage Solution: Uses libraries such as NumPy, Pandas, dolphindb, and multiprocessing.
• DolphinDB Integrated Framework: Uses DolphinDB server as the computational platform, with a single-node deployment for this test.

The hardware and software environment details required for the test are as follows:

Hardware Environment

Software Environment

The test dataset consists of Level-2 snapshot data for February 1, 2021. The data includes 26,632 securities with a total of approximately 31 million records. The data is originally stored in DolphinDB. For testing purposes, we export it into various formats, including Pickle, Parquet, and others. The data in the four formats of Pickle, Parquet, Feather, and HDF5 is organized into groups based on securities, while the data in the Npz format is evenly divided into twelve groups. The selectedstorage methods are all for achieving the best computational performance. In practice, you can choose different storage methods that best suit your needs.

The snapshot table in DolphinDB contains 55 fields, with some key fields displayed as follows:

A sample of the snapshot data is as follows:

Linear Regression Slope of Top-10 Weighted Average Price

The Top-10 weighted average price is defined as the total value of the ten best buy and sell orders divided by the corresponding total volume of those orders:

The linear regression slope of the top-10 weighted average price refers to the slope of the linear regression of the top-10 weighted average price with respect to time t.

Net Buy Increase in the Best 10 Bids

The net buy increase in the best 10 bid orders measures the overall increase in bid-side capital within the effective price range, calculated as the total sum of changes in bid prices:

The effective price range refers to the valid price within the following ranges:

Linear Regression Slope of Top-10 Weighted Average Price

The calculation of the slope of the linear regression of the top-10 weighted average price requires the following four fields, all of which are array vector storing the volumes/prices in one column:

• OfferOrderQty: Volumes of ten lowest sell orders
• BidOrderQty: Volumes of ten highest buy orders
• OfferPrice: Prices of ten lowest sell orders
• BidPrice: Prices of ten highest buy orders

The rowSum built-in aggregate function can be used to calculate the sum of each row efficiently. The linearTimeTrend function computes the sliding linear regression slope with respect to time t, returning both the intercept and slope of the regression. price.ffill().linearTimeTrend(lag1-1).at(1).nullFill(0).mavg(lag2, 1).nullFill(0) calculates the slope of the linear regression of the top-10 weighted average price.

@state
def level10_InferPriceTrend(bid, ask, bidQty, askQty, lag1=60, lag2=20){
	inferPrice = (rowSum(bid*bidQty)+rowSum(ask*askQty))\(rowSum(bidQty)+rowSum(askQty))
	price = iif(bid[0] <=0 or ask[0]<=0, NULL, inferPrice)
	return price.ffill().linearTimeTrend(lag1-1).at(1).nullFill(0).mavg(lag2, 1).nullFill(0)
}

Net Buy Increase in the Best 10 Bids

The calculation of the net buy increase in the best 10 bid orders requires two array vector fields:

• BidOrderQty – Volumes of ten highest buy orders
• BidPrice – Prices of ten highest buy orders

The rowAlign function aligns the current and previous best 10 prices. The rowAt and nullFill functions are then used to align the corresponding order quantities and prices. Finally, the total change is calculated.

@state
def level10_Diff(price, qty, buy, lag=20){
        prevPrice = price.prev()
        left, right = rowAlign(price, prevPrice, how=iif(buy, "bid", "ask"))
        qtyDiff = (qty.rowAt(left).nullFill(0) - qty.prev().rowAt(right).nullFill(0)) 
        amtDiff = rowSum(nullFill(price.rowAt(left), prevPrice.rowAt(right)) * qtyDiff)
        return msum(amtDiff, lag, 1).nullFill(0)
}

Linear Regression Slope of Top-10 Weighted Average Price

def level10_InferPriceTrend(df, lag1=60, lag2=20):
    '''
    Linear Regression Slope of Top-10 Weighted Average Price
    :param df:
    :param lag1:
    :param lag2:
    :return:
    '''
    temp = df[["SecurityID","DateTime"]]
    temp["amount"] = 0.
    temp["qty"] = 0.
    for i in range(10):
        temp[f"bidAmt{i+1}"] = df[f"BidPrice{i+1}"].fillna(0.) * df[f"BidOrderQty{i+1}"].fillna(0.)
        temp[f"askAmt{i+1}"] = df[f"OfferPrice{i+1}"].fillna(0.) * df[f"OfferOrderQty{i+1}"].fillna(0.)
        temp["amount"] += temp[f"bidAmt{i+1}"] + temp[f"askAmt{i+1}"]
        temp["qty"] += df[f"BidOrderQty{i+1}"].fillna(0.) + df[f"OfferOrderQty{i+1}"].fillna(0.)
    temp["inferprice"] = temp["amount"] / temp["qty"]
    temp.loc[(temp.bidAmt1 <= 0) | (temp.askAmt1 <= 0), "inferprice"] = np.nan
    temp["inferprice"] = temp["inferprice"].fillna(method='ffill').fillna(0.)
    def f(x):
        n = len(x)
        x = np.array(x)
        y = np.array([i for i in range(1, n+1)])
        return (n*sum(x*y) - sum(x)*sum(y)) / (n*sum(y*y) - sum(y)*sum(y))
    temp["inferprice"] = temp.groupby("SecurityID")["inferprice"].apply(lambda x: x.rolling(lag1 - 1, 1).apply(f))
    temp["inferprice"] = temp["inferprice"].fillna(0)
    temp["inferprice"] = temp.groupby("SecurityID")["inferprice"].apply(lambda x: x.rolling(lag2, 1).mean())
    return temp[["SecurityID","DateTime", "inferprice"]].fillna(0)

Net Buy Increase in the Best 10 Bids

def level10_Diff(df, lag=20):
    '''
    Net Buy Increase in the Best 10 Bids
    :param df:
    :param lag:
    :return:
    '''
    temp = df[["SecurityID","DateTime"]]
    for i in range(10):
        temp[f"bid{i+1}"] = df[f"BidPrice{i+1}"].fillna(0)
        temp[f"bidAmt{i+1}"] = df[f"BidOrderQty{i+1}"].fillna(0) * df[f"BidPrice{i+1}"].fillna(0)
        temp[f"prevbid{i+1}"] = temp[f"bid{i+1}"].shift(1).fillna(0)
        temp[f"prevbidAmt{i+1}"] = temp[f"bidAmt{i+1}"].shift(1).fillna(0)
    temp["bidMin"] = temp[[f"bid{i+1}" for i in range(10)]].min(axis=1)
    temp["bidMax"] = temp[[f"bid{i+1}" for i in range(10)]].max(axis=1)
    temp["prevbidMin"] = temp[[f"prevbid{i+1}" for i in range(10)]].min(axis=1)
    temp["prevbidMax"] = temp[[f"prevbid{i+1}" for i in range(10)]].max(axis=1)
    temp["pmin"] = temp[["bidMin", "prevbidMin"]].max(axis=1)
    temp["pmax"] = temp[["bidMax", "prevbidMax"]].max(axis=1)
    temp["amtDiff"] = 0.0
    for i in range(10):
        temp["amtDiff"] += temp[f"bidAmt{i+1}"]*((temp[f"bid{i+1}"] >= temp["pmin"])&(temp[f"bid{i+1}"] <= temp["pmax"])).astype(int) - \
                        temp[f"prevbidAmt{i+1}"]*((temp[f"prevbid{i+1}"] >= temp["pmin"])&(temp[f"prevbid{i+1}"] <= temp["pmax"])).astype(int)
    temp["amtDiff"] = temp.groupby("SecurityID")["amtDiff"].apply(lambda x: x.rolling(lag, 1).sum())
    return temp[["SecurityID","DateTime", "amtDiff"]].fillna(0)

The daily volume of Level-2 market snapshot data exceeds 10 GB, making the calculation performance of high-frequency factors based on this data a key concern for practitioners. This section compares the calculation performance of the two high-frequency factors from two perspectives: We adjusted the level of parallelism by varying the number of CPU cores used and measured the time taken to compute the factors under different storage formats. The test data consists of 26,632 securities with a total of 31 million rowsfor one trading day. All tests were conducted after clearing the operating system cache. The test results are summarized in the following tables:

These tables present the performance improvement of factor calculations with different numbers of CPU cores. DolphinDB's integrated factor calculations achieve up to 200× performance improvement for the linear regression slope of the top-10 weighted average price compared to Python-based calculations using various file storage formats, with an average improvement of approximately 100×. The performance advantages of DolphinDB's integrated calculations can be attributed to the following factors:

• DolphinDB’s proprietary data storage system provides significantly higher data retrieval efficiency compared to Python reading general file formats.
• DolphinDB’s built-in functions, such as rowSum and linearTimeTrend, eliminate the need for complex for loop-based calculations, significantly reducing computational time.

Although file formats such as Pickle and Parquet can be optimized through technical methods (e.g., redundant storage), it often results in higher hardware resource costs and increased data management complexity. In contrast, DolphinDB's proprietary storage system is more efficient, user-friendly, and simpler to manage. DolphinDB’s integrated factor calculation framework demonstrates significantly faster processing speed—from data loading to factor calculation—compared to Python + file storage solutions. There are several key insights from the performance comparison:

• Implementation: DolphinDB’s SQL-based calculation framework simplifies factor calculation and parallel execution.
• Parallelism: DolphinDB automatically utilizes available CPU resources, whereas Python scripts require explicit parallel scheduling, which offers more flexibility but demands additional coding effort.
• Speed: DolphinDB’s integrated calculation is 50 to 200× faster than Python + file storage solutions.

While DolphinDB's integrated factor calculation is significantly faster than Python + file storage, we must also consider the accuracy of the results. To verify consistency, the calculation results from both solutions were respectively imported into Pandas DataFrames for comparison. The code comparing the results of the DolphinDB solution and the Python+Pickle solution is shown below, which makes it easy to see that the results of both solutions are exactly the same:

from pandas.testing import assert_frame_equal
df_ddb = df_dolphindb.sort_values(by=["SecurityID","DateTime","amtDiff"]).reset_index(drop=True)
df_py = res_combined.sort_values(by=["SecurityID","DateTime","amtDiff"]).reset_index(drop=True)
df_ddb = df_ddb.round(4)
df_py = df_py.round(4)
assert_frame_equal(df_ddb, df_py, check_exact=False, rtol=1, atol=1e-06)
print(assert_frame_equal)
"""
The assert_frame_equal function does not produce any output when the results match. 
If the results do not match, it returns an error message.
"""