Re: How to specify NOLOCK queries in SA (mssql)

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" <rickmorri...@gmail.com> 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 sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy-unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---