Update README by sumitmsft · Pull Request #4 · microsoft/mssql-python

@bewithgaurav

Problem:
--------
Row creation and assignment had multiple layers of overhead:
1. Per-row allocation: py::list(numCols) creates pybind11 wrapper for each row
2. Cell assignment: row[col-1] = value uses pybind11 operator[] with bounds checking
3. Final assignment: rows[i] = row uses pybind11 list assignment with refcount overhead
4. Fragmented allocation: 1,000 separate py::list() calls instead of batch allocation

For 1,000 rows: ~30-50 CPU cycles × 1,000 = 30K-50K wasted cycles

Solution:
---------
Replace pybind11 wrappers with direct Python C API throughout:

1. Row creation: PyList_New(numCols) instead of py::list(numCols)
2. Cell assignment: PyList_SET_ITEM(row, col-1, value) instead of row[col-1] = value
3. Final assignment: PyList_SET_ITEM(rows.ptr(), i, row) instead of rows[i] = row

This completes the transition to direct Python C API started in OPT #2.

Changes:
--------
- Replaced py::list row(numCols) → PyObject* row = PyList_New(numCols)
- Updated all NULL/SQL_NO_TOTAL handlers to use PyList_SET_ITEM
- Updated all zero-length data handlers to use direct Python C API
- Updated string handlers (SQL_CHAR, SQL_WCHAR) to use PyList_SET_ITEM
- Updated complex type handlers (DECIMAL, DATETIME, DATE, TIME, TIMESTAMPOFFSET, GUID, BINARY)
- Updated final row assignment to use PyList_SET_ITEM(rows.ptr(), i, row)

All cell assignments now use direct Python C API:
- Numeric types: Already done in OPT #2 (PyLong_FromLong, PyFloat_FromDouble, etc.)
- Strings: PyUnicode_FromStringAndSize, PyUnicode_FromString
- Binary: PyBytes_FromStringAndSize
- Complex types: .release().ptr() to transfer ownership

Impact:
-------
- ✅ Eliminates pybind11 wrapper overhead for row creation
- ✅ No bounds checking in hot loop (PyList_SET_ITEM is a macro)
- ✅ Clean reference counting (objects created with refcount=1, transferred to list)
- ✅ Consistent with OPT #2 (entire row/cell management via Python C API)
- ✅ Expected 5-10% improvement (smaller than OPT #3, but completes the stack)

All type handlers now bypass pybind11 for maximum performance.