Hi Rick, This could involve quite a bit of investigation on our part, so it could take a while before we get to the bottom of it.
We're running Windows 2003 as the OS, MS SQL Server 2005, via the SQL Server Native Driver, using PYODBC, Apache 2.2.6 & mod_python 3.3.1. The app is based on Pylons, using SQLAlchemy-0.4.4dev_r3557-py2.5 & Elixir-0.5.0dev_r2495-py2.5. Now for what it's worth, we made the change I mentioned above to our db. Before the change, we were getting 360000 maximum concurrent locks in a day. after the change, that figure went down to 400. It also reduced the load on our CPUs by about 10%. So it helped. We will be doing more examination of what's happening with cursors next week...I'll keep you posted. :) On May 16, 2:20 am, "Rick Morrison" <[EMAIL PROTECTED]> wrote: > Hi Bruce, > > I'm considering a switch from pymssql to pyodbc myself in the > not-too-distance future, and this thread has me a bit curious about what's > going on. This is a subject that may affect SQL more in the future when ODBC > and JDBC drivers get more use. > > I think there's two distinct questions that need to be answered to get to > the bottom of this. The first question is "why are these queries being > issued at all, and from where"? Like Mike says, SQLA is playing no part in > constructing or issuing these queries. > > From the bit of googling that I've done so far, it seems that the FMTONLY > queries are issued behind the scenes by the data connector to fetch metadata > regarding the query. While there's a lot of reasons a data connector might > need to have metadata, there's two that seem especially likely when SQLA > comes into play: > > a) There are un-typed bind parameters in the query, and the connector > needs to know the data types for some reason. > > b) There is going to be a client-side cursor constructed, and result > metadata is needed to allocate the cursor. From the description you give, I > would bet that this is your main issue. > > If the cause is (a), a fix might be problematic, as SQLA issues all of its > queries using bind parameters, and I'm not sure if type information is used > for each. But if you're using explicit bind parameters, you may want to > specify the type on those. > > As for the more likely cause (b) I would think this could be gotten around > by making sure you specify "firehose" (read-only, forward-processing, > non-scrollable) cursors for retrieval, but I'm not sure what the pyodbc > settings for this might be. As a bonus, you'll probably see a bit of a > performance boost using these types of cursors as well. > > The second question is more of a mystery to me: "ok, so the data connector > issues a FMTONLY query........if it's just fetching metadata, why would that > cause database locks?". > > This one I can't figure out. Unless you're calling stored procedures or > UDF's that have locking side effects, It's got to be a bug in the data > connector. From what I read a FMTONLY query should be pretty fast (other > than the round-trip network time), and should lock nothing. > > Are you running on Windows, or on Unix? What's your ODBC connector? > > Please post to the list as you work through this and let us know what you > find... > > Rick --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
