Actually everything is under the hood implemented using threads in a fashion that should allow things to NOT block but there can be points at which this has no impact
I’m up to my eyeballs with other things at the moment but I’ll have a peek when I can
It may not if control is actually IN the client library to access the server.
Basically we have to call out to the respective client library to do the query and IF that completely blocks there may be nothing we can do until the client library returns.
Control is IN the client library (MS SQL Native client etc)
However this is supposed to all be running in a manner we can avoid that but this suggests its not
One way to tell is in the debugger catch the SQL right before its sent
Copy & paste that into a SQL server admin / terminal app outside of Xojo and see how fast it runs
IF that is quick then it may be the loading of data thats slow
IF that is slow then it may be that the client lib has blocked things in a way we can’t do anything other than wait til it returns control
Ah - ok - point taken…
From reading Paul’s post, I don’t think he’s using threads yet? Paul?
I’d start with lets see how fast the query actually runs
One way to tell is in the debugger catch the SQL right before its sent
Copy & paste that into a SQL server admin / terminal app outside of Xojo and see how fast it runs
IF that is quick then it may be the loading of data thats slow
IF that is slow then it may be that the client lib has blocked things in a way we can’t do anything other than wait til it returns control
But starting here is a lot simpler than right off implementing threads timers etc
Yup, no threads yet (this will be my first attempt).
So, optimise/test the SQL with your suggestions (in SQL admin and Xojo) THEN if still causing a noticable delay (or think it might in the future) AND try anyway as a learning experience 
Sounds like a plan to me, I will come back with some results for anyone else who might benefit from this in the future etc.
Thanks guys.
Thats interesting - in this case we’re talking about a Select statement so we should only get a shared lock at the database end. A shared lock gets removed as soon as the data is read. Looking at Paul’s statement there’s no locking hint and unless the Isolation Level is set to repeatable read surely the client lib shouldn’t be locking anything.
Paul - I always write my SQL statements in the Database Editor first to test before bringing them into Xojo.
This has 0 to do with shared lock etc
dim rs as recordset = db.sqlselect( query )calls into some client library, usually in C/C++, for every db to do the select.
IF that blocks for some reason or our code to make it run in a way that doesn’t block is faulty then it can / will block until the call returns.
Regardless of what the back end server does.
Xojo threads won’t matter because its cooperative and the native client library isn’t yielding time to Xojo for it’s threads to do anything.
This is also why I want to see how long the query in a non-Xojo environment takes.
That may point to different issues and possible solutions
So what your saying is that the blocking could all be within the API and nothing to do with the database?
If so, how would running the query outside of Xojo help? If the block isn’t at the database level then it won’t run slowly.
I want to see how long the query in a non-Xojo environment takes so I have an idea where to look.
But until I know its hard to say what remedies may work.
Threads might - and might not - depending
I think where I’m struggling/confused here is this statement
[quote=111460:@Norman Palardy]Copy & paste that into a SQL server admin / terminal app outside of Xojo and see how fast it runs
IF that is quick then it may be the loading of data thats slow
IF that is slow then it may be that the client lib has blocked things in a way we can’t do anything other than wait til it returns control[/quote]
Database Blocking is a byproduct of Database Locking - all handled by the Database Engine not an API. Where one SPID holds a lock that another process wants = block. You can’t have a block without a lock so am really confused how the API can block a resource without putting a lock on it.
Surely you’d be better off getting off Paul to run the slow query from within Xojo and get him to run sp_lock2 in the database editor to see the state of the locks over the resources to see whats going on. That’ll tell you if the API is appending some weird query hint or doing something with isolation levels.
In fact, if Paul can isolate the SPID Xojo creates when the query is running then sp_who2 will show what statement the Xojo API has actually fired against the database.
Very aware of how things work in MS SQL server as a former DBAdmin for Sybase which is still architecturally very much what MS does.
The MS SQL Server plugin uses the native client library API.
That API sends the query to the db and may actually sit & block waiting for a response.
That’s my concern.
If that is happening and the engine is taking some time to do the query then that’s one issue.
If that query in the native DB is fast then we have some other issue to look into.
Right now all we know is “it blocks in xojo” with no idea where or why.
I’m just trying to get a sense of where to look and being able to copy the query form the Xojo app and run it in a native client is a pretty simple first step.
He’s not talking about Database Blocking. He’s talking about the query taking time and the lib not yielding time back to the process - hence, just hanging the whole app. If the lib is yielding time and if Xojo is handling that yield correctly, then running the query in a thread will help. If either of those goes wrong, it won’t.
Edit; Norman beat me to it.
Since when did this become a pissing contest
There’s no sybase code left now, SQL 2005 got rid of the last remnants - I’d be very interested to see how architecturally similar they still are. But that’s a whole other subject.
Everything was reading like you were talking about database locking and that’s where I was coming from - trying to offer some constructive help.
[quote=111528:@Patrick Delaney]Since when did this become a pissing contest
There’s no sybase code left now, SQL 2005 got rid of the last remnants - I’d be very interested to see how architecturally similar they still are. But that’s a whole other subject.
Everything was reading like you were talking about database locking and that’s where I was coming from - trying to offer some constructive help.[/quote]
Never mentioned database locks so I’m puzzled where that came from but its really not relevant
http://blogs.msdn.com/b/ssma/archive/2011/06/09/migrating-sybase-ase-to-sql-server-similarities-and-differences-to-help-in-planning-a-migration-project.aspx
http://www.sybase.com/content/1024298/mssql_ase_migration_guide.pdf
They’re still very similar in major respects
Differences in terms of exact details but they’re still similar enough
i use sql server for these sorts of things, and my users wanted a really responsive UI for data entry, they really hate to wait at all.
so…
at the start of the entry session, get the whole recordset down
it depends if you need X-Plat. if you are just windows then use an adorecorsdet.
set it to use adstatic so you can move around it with movefrst move last etc.
then you can use the filter property to filter it down as much as you like.
OR.
copy the whole lot into an in memory sqlitedatabase table. then query that.
i find i can query on keystrokes very quickly with around 10k addresses.
i also tried concatenating all the address and postcode/telephone fields into a new field in the (in memory) db
and putting an index on it. not sure if it made any real-world difference though
all of this removes the need for network round trips and you then only need to deal with updates to the back end.
we have a field with an update time on it, so i query that in a timer and then do a refresh.
i have found it makes the data entry UI have a similar response time on most machines now, regardless of what else is going on on the network, like if they have streaming video in another window , or are downloading huge email attachments etc.
i encapsulated it all into a container control so i can drop it on a window and tell it , theres your DB, be a customerlookup, or a productlookup etc
dunno if its nay use to you, but its worked for me.
OK, limited test results time (very rough guides as I am no expert and don’t know the correct methods so went old school and used a stopwatch) :
Time to execute SQL as above (no optimisation) with 61 records returned -
In SQL Admin 6.9 seconds
In Xojo 6.5 seconds
Difference will be my reaction times and different software displaying the results but gives a bit more info. The times are roughly the same (in SQL admin ADN Xojo app) even with a search that returns 9000+ rows.
Time to Execute SQL without the Record_Count subquery (9000+ rows returned) -
In SQL Admin 3 seconds
Haven’t tried in Xojo as expect it to be similar and will have to change an amount of code to do without the Record_count so instead will move onto playing with threads (as an experiment and learning experience).
Cheers guys.
PS Russ what sort of memory usage does your method create? Do you get the whole recordset again after an update? How do you handle possible conflicts with 2 users updating the same record?
OK, Update 2 for today.
Took all the code doing the SQL (that was originally putting the results into a listbox) into a thread which populates a multi-dimensional array and stays well away from any UI stuff.
Then in the push button code I have it display a progress wheel and a little message on the window, run the thread (to run the SQL) then show a message box when complete (just for testing, when finished the listbox will be populated by the contents of the array).
AND …
during the 6 or 7 seconds the SQL is running, the window does not lock up and the progress wheel animates just as I was hoping would happen (even without a timer).
I can now go back and optimise the SQL, count the records after they have been returned (rather than in the SQL, thanks Patrick) and populate the listbox etc. Happy that my users will not moan about the program freezing etc.
And I have learnt a number of new techniques.
Win win. Thanks Patrick and Norman, Russ and Anthony, great advice as always.
hi,
well the memory usage is what it is, but most of my stuff runs at about 200mb on average. mind you if you look at sage accounts it can run at 500mb easily
i have last update fields on the tables and i store a last update on the control then query where last update > that
which stops me getting everything, then sqllite has an upsert function, which makes it easy to update the in memory database
(i really wish sql server had an upset)
i dont tend to find too many people updating customer records at the same time. i sometimes get people trying to update a product record but then its only really used to get the product code/description and they don’t tend to change too often.
i find real deadlocks are not common, however valid “user1 updates”, then “user2 updates” half a second later are more frequent.
we planned to have an indicator to show if a product was frequently updated, but nobody was ever bothered about that too much,
having an audit trail to follow seems more popular with the decision makers to be honest. i think its because they know that the data entry folks don’t really care whats happened after it saves ok. or even IF it saves ok 
2nd line support can look at the audit and fix it afterwards.
but thats just my industry, I’m sure every company has other ideas and problems.
if you need to search on many many fields, what i did is create a query with a new field which basically appended all the fields you want to search on into the new field separate with * (i usually called my Keyword) and then use LIKE to look for the word you want to search on.
Hi Richard,
Yes thats what i do too. i also let the user prepend a modifier into the search box :-
that tells me what it is they are typing in
a: address
t: telephone
e: email
p: postcode
then i just search on those fields which seems quick, but I’m not sure most users use them to be honest, bit geeky