Start a new topic

SQL Timeout

Hi

Is there a way to change (increase) the SQL Timeout duration in QW? We've moved our back end to Azure and find that some searchs (anything with CONTAINS) timesout, yet we don't have this issue with other desktop apps with SQL backends in Azure. 


This happens with searches, reports etc. 


Olly



Hi Olly,


I don't believe such an option exists.

I have a couple of clients (even with on-prem SQL) that get timeouts when running big reports, particularly (but not exclusively) when looking at the DocumentItems table.


It would be very handy to have a setting that would allow (maybe not in the interface, but a sort of 'secret' setting we could apply) us to increase this timeout.


People, generally, are understanding if we're asking it to churn through a lot of data and will simply answer a quick email or something whilst it's running. But returning the (rather daunting) error message is a bit of a frustration, I agree.

Hello Oliver, if you go to .....


Tools -> Options -> Misc


then there is a ....


DDE timeout value


setting which you may try adjusting.


You could also try going to ......


Utilities -> User Maintenance ->  (username) -> Preferences -> Misc -> General (More)


and adjust the Document Search Defaults to maybe My Active Documents or My Documents.


Additionally, if you log in to SQL Maintenance you can rebuild or re-organise your indexes on the Document Headers and DocumentItems table. Or, you can examine the report(s) and add indexes to any fields contained in the report, which don't already have indexes.


Regards


Thanks David, DDE not relevant in this case.


Document Search defaults already set, but that only really relates to the initial loading of the 'open' screen, any subsequent searching can still be pretty intensive.


SQL indexes are already reviewed/created.


Our issue is that some of the reports are looking through, in some cases, millions of lines.

If we know it'll take 3 minutes to run, that's fine - but it seems QW has a hardcoded limit of 60 seconds to do any such queries.

+1 vote for this. Just today have a client with 100k+ quotes and it times out when hitting Open. Can run the query in SSMS and it takes about 30-45 seconds there. QW it times out about 8 out of 10 times.

We are purging documents to get around the issue since they do not like the "my quotes" and "recent quotes" suggestions.

Just tested this on one of my client installs where I have direct access to their server (this is a very high spec. server, so no issue with specification)...


If my default is to load all documents, I time out.

I then restarted the machine.


If I load AllRecentDocuments (about 5k) it takes about 5-6 seconds

If I then click All, it takes 1:45 to load all the results (no timeout)


Is the most common thing people do with 'Open' not to do a search of some kind?

I'm not quite following the issue the client has.. why not set it to load <none> by default?


I did find that running a couple of SQL Queries, at say 6am each day, gave a slight performance boost; but this was only negligible.

To add to Matt's point about running SQL queries at 6am each day: we have a client that reboots their QW SQL Server server each night that previously experienced very slow document load times, timeouts and hanging for the first couple of hours each day. We now have a SQL Job running each morning with the following SQL queries which has resolved their problem.

 

SELECT * FROM QuoteWerks.dbo.DocumentHeaders AS DH
SELECT * FROM QuoteWerks.dbo.DocumentHeaders AS DH WHERE DH.DocType = 'QUOTE'
SELECT * FROM QuoteWerks.dbo.DocumentHeaders AS DH WHERE DH.DocType = 'ORDER'
SELECT * FROM QuoteWerks.dbo.DocumentHeaders AS DH WHERE DH.DocType = 'INVOICE'
SELECT * FROM QuoteWerks.dbo.DocumentHeaders AS DH WHERE DH.SoldToCompany LIKE 'A%'
SELECT * FROM QuoteWerks.dbo.DocumentHeaders AS DH WHERE DH.DocNo = 'AAAQ1000' 



Login or Signup to post a comment