MSSQL and slow HExecuteQuery() every now and then

Hi,
Im using WD14 against MSSQL2008. When opening one of my windows some queries are executed to fill listboxes. All querys are very simple, getting data from one table. Tables contain max. 50 records.

Now something strange is happening. Every 4th or 5th or so, the window is opened, it takes about 3 seconds to execute one of the queries. Where all other times this query runs within 0.1 seconds!
I used the dbgInfo() to create a wlog-file to analyze it. It's always the statement HExecuteQuery() who is causing the trouble.

I tried commenting out this query. But: then the next one behaves like this!!

Who has ever seen this behaviour??!?

btw: I cannot profile the MSSQL server right now, because I don't have access to it.

Don't use MSSQL but use queries extensively.

Are you canceling queries when you close the window??

regards

issah

Hi Issah,
I do use HCancelDeclaration most of the time, after reading all data. Is that a bad idea?

I'm using a connection through OLEDB. I also tested this window with SQL-commands (sqlexec, sqlconnect and so on) rather than using H-commands. This did not help.

Hello,
I'm working with queries quite enough. When I was starting with Windev, I had sometimes problem with some queries. I've found that sometimes Windev "cannot" execute more complicated queries(with joins or with more where)-it takes long time or the result is not correct. I think you have to step by step and find the "weak point".
Regards,Alena

Message forwarded from pcsoft.us.windev

Hi Arie
We use MSSQL & OLEDB extensively & have not experienced the problem. A couple of years ago we profiled HExecuteQuery & HExecuteSQLQuery for some fairly complex queries & found HExecuteSQLQuery was far more efficient so we always use that. Having said that though, I would have thought that SQLExecute etc would be equally as efficient. As virtually all our queries are generated in procedures we don't always use HCancelDeclaration, relying on Windev to automatically release it. My understanding is that using HCancelDeclaration forcibly releases the memory so the fact that you do that every time should not cause an issue. The only other difference is we are using MSSQL2005. At this stage we also only have 1 site running with Windev14 but they have not reported any speed issues so I don't think it would be that.

David

This issue has been solved.
One would never guess, but the cause was the WX-file. This is a file created by the WD-setup, when installing an application. It holds settings like hfcs-connection-parameters and the path to user-report-and-queries.

In my case there was a line LOCAL_DIR under de [WDREPORT] section, referring to a (existing!) network drive \\server123\map1
Yesterday this share was removed on the server. On the same moment all instances of the application ran VERY VERY slow!!

After deleting the WX-file every database-access was as fast as it should be. AND: my first problem (a glitch every other network-trip) was gone also!

Strange thing is: even before using any reports, this setting effect the applications flow!!
Apparently WD uses this setting to do something even when you don't use reports at all!!

I will inform PC-soft about this.