search

CollaboratingPlatypus / PetaPoco

Official PetaPoco, A tiny ORM-ish thing for your POCO's
Other
2.07k stars 600 forks source link

cursor_close_on_commit compilation context issue #584

Closed mockneypiers closed 4 years ago

mockneypiers commented 4 years ago

I've a strange issue, which is causing problems for me. I have a .net API that uses petapoco to call some sql server stored procedures and serialise the data. var stuff= db.Fetch<model>(@";Exec Getstuff @@Username = @0, @@date", UserName, fromDate) All very vanilla. The problem was we were getting timeouts on the website that consumed it. I'd run it in SSMS, 20ms. But running it in postman could be 40000+ ms, consistently.

In the end I compared the connection context settings from SSMS against those I recorded using SQL Profiler, and then applied each of the connection settings to the SMMS query in isolation. When i got to set cursor_close_on_commit off, bang, slow proc in SSMS. My solution was to recompile the stored procedure thus SET cursor_close_on_commit ON GO ALTER PROCEDURE blah

The problem is every time sql server does its periodic recompilations that context is gone and we're back to timeouts. I guess my question is is there anything i can set specifically on the petapoco connection string or alter the connection object in the petapoco class to avoid this, or is deep within the gubbins of the System.Data class?

mockneypiers commented 4 years ago

I should add that the procedures themselves are pretty simple select statements. They mostly follow the pattern of CTEs and a union

with a AS (SELECT snip FROM stuff JOIN otherstuff on snip), b AS (SELECT snip FROM stuff JOIN otherstuff on snip) SELECT * FROM a UNION ALL SELECT * FROM b

And that's it. No cursors or anything.

asherber commented 4 years ago

I'm not familiar with this setting but have just been reading a little. I don't think there's anything you can do via PetaPoco, but there are steps you could take in SQL Server.

  1. The setting can be turned on at the server level
  2. The setting can be turned on specifically for your database
  3. You can include SET CURSOR_CLOSE_ON_COMMIT ON as a statement in your stored proc
mockneypiers commented 4 years ago

Thanks. I'll have a look into 1 and 2. I've already tried 3, but for some reason I still get the issue post recompilation, even with it explicitly set in the sproc, so it must have something to do with the context itself.

In the meantime I'll eliminate possibilities like parameter sniffing just in case I've ended up barking up the wrong tree, although the SSMS vs API performance is a constant, so I don't think it is.