How to fast search specific data in MS SQL with Python if you have three dependent variables and you have 10 million data? If prepare use pyodbc library or any else?

CodePudding user response：

Starting from the fact that 10 million rows for a table in a RDBMS is not a huge number, I personally used TurboODBC with MSSQL to migrate a table with ~2 billion rows. I was very satisfied.

You should carefully review and optimize your query/schema (adding indexes, exploiting them, etc.) regardless of the technology you will use to run it. Most likely the database query execution will be the bottleneck of your process (assuming that you will not process this data in python).

CodePudding user response：

Using a columnstore index is appropriate for high volume of data/rows in a table (many millions at least). If your criterias for the WHERE cluse of the SELECT are the 3 variables, create such index with those three columns. As an hypothetical exemple :

CREATE COLUMNSTORE INDEX XC_MyTable ON MyTable (Col1, Col2, Col3);