A single slow view friezes the whole server

[Matthew51]

I have a browse when sorted by the description field the SQL server takes a very long time to return the results of the view.  Now I'm already take steps to make the view faster, that's not my big concern.  My big concern is that while waiting the the view results the net talk server won't respond to any other requests.  Or at least not any where the p_web object is ask to access the database.  The requests just keep piling up until the SQL server finally returns it's results.

Net talk; 8.68 with SQL Server 2012 Express

Thanks

Matthew

[kevin plummer]

are you using the MARS and Busyhandling driver strings on your SQL connection?

[Matthew51]

MARS yes, Busyhandling no.

The only busy option I see in the driver options dialog is loop on busy error, which is set to 20.

[kevin plummer]

search busyhandling on this NG for more info.

[Bruce]

see
http://www.nettalkcentral.com/index.php?option=com_smf&Itemid=36&topic=403.0
for busyHandling and MARS setup.

But also note that if you have p_web.sqlsync = true (ie in your embed code, probably in webhandler) then this is also asking the server to serialize access to the Sql server, and thus would only allow one thread to access the sql server at a time.

cheers
Bruce

[Matthew51]

Adding /BUSYHANDLING=2 to the connect string fix it.  The slow view is still slow, but everyone else doesn't suffer for it.

[kevin plummer]

check the filter on the slow view and make upper. make sure it's not doing the filtering locally.