I just started trying pymssql with freetds as I had issues with pyodbc with freetds. The first thing I noticed is that pymssql doesn't support bind parameters. I can't use "?", but instead have to use what looks like python string formatting. I captured the queries sent over profiler and noticed that the values are inlined, meaning they are not really bound. Since this could be a huge performance issue for many, why is this not mentioned as one of limitations? Or am I not understanding this correctly?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
DB-Library for C doesn't have the concept of parameters binding similar to ODBC or .NET. That's why they are imitated using string formatting. I don't see this as huge performance penalty until I see numbers which prove that. In my opinion time spent on concatenating strings is insignificant in comparison to time needed to (i) send the query over the network, (ii) prepare the query plan on the SQL Server, (iii) actually execute the query, possibly doing joins, aggregations, physical i/o and many other stuff, (iv) send the results over the network, (v) parse the results.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I wasn't referring to the penalty of concatenating the strings, but the penalty due to the inability to prepare the query in advance. Each query sent to the db would seem to be different from the other, so wouldn't it also end up thrashing the query caches? Also, won't this logic break when inserting strings with newlines or binary/varbinary data?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You're right. The query would have been prepared each time, avoiding plan caching. But still, the library has no option to improve that. There are two workarounds: you can have an UDF, which IS cached, and the second, not very elegant, to explicitly call sp_prepare or sp_prepexec followed by bunch of sp_execute. Varbinary may be broken, however nobody ever reported this.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
To be clear, I am not sure if binary/varbinary is broken, but I was just suspecting that it wouldn't work. If I get a chance to try it out, I will report if it worked or not.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I just started trying pymssql with freetds as I had issues with pyodbc with freetds. The first thing I noticed is that pymssql doesn't support bind parameters. I can't use "?", but instead have to use what looks like python string formatting. I captured the queries sent over profiler and noticed that the values are inlined, meaning they are not really bound. Since this could be a huge performance issue for many, why is this not mentioned as one of limitations? Or am I not understanding this correctly?
DB-Library for C doesn't have the concept of parameters binding similar to ODBC or .NET. That's why they are imitated using string formatting. I don't see this as huge performance penalty until I see numbers which prove that. In my opinion time spent on concatenating strings is insignificant in comparison to time needed to (i) send the query over the network, (ii) prepare the query plan on the SQL Server, (iii) actually execute the query, possibly doing joins, aggregations, physical i/o and many other stuff, (iv) send the results over the network, (v) parse the results.
I wasn't referring to the penalty of concatenating the strings, but the penalty due to the inability to prepare the query in advance. Each query sent to the db would seem to be different from the other, so wouldn't it also end up thrashing the query caches? Also, won't this logic break when inserting strings with newlines or binary/varbinary data?
You're right. The query would have been prepared each time, avoiding plan caching. But still, the library has no option to improve that. There are two workarounds: you can have an UDF, which IS cached, and the second, not very elegant, to explicitly call sp_prepare or sp_prepexec followed by bunch of sp_execute. Varbinary may be broken, however nobody ever reported this.
To be clear, I am not sure if binary/varbinary is broken, but I was just suspecting that it wouldn't work. If I get a chance to try it out, I will report if it worked or not.