Long SQLSelect freezes window

HI all,

I have a search screen that is searching a postal address database.

Using a number of tricks I can reduce the search amounts for most types of search (eg check to see if the search string is more than 4 numbers and if true search on telephone fields etc).

But when basic text is entered in the search field the recordset can take a couple of seconds to run as the query is doing “like” searches on a number of fields whilst also running an SQL function (to remove non-alphanumeric characters from the source data) etc. This is a delay on the SQL server as it doesn’t make a great deal of difference if it returns 2 or 2000 records (which are then displayed in a listbox).

Is there a way to indicate to the user that they should wait (they can’t do anything anyway) and to stop windows displaying the “Program not responding” message?

I assume a thread is the way to go? If yes should I do all the search bits in the thread and pass back an array of data or just the recordset creation/loading in the thread? Will this allow me to use a progress wheel on my window (Can’t use a progress bar as it is one action that is slow and I don’t know in adavance how long it will take)?

Any pointers would be great.



Paul -

I’ve had great success using threads for this type of operation.

You need to be aware of the caveat that threads cannot touch the UI, so yes, you’ll need to load your results into something like an array.

A normal pattern is to have the thread set a flag (window property perhaps) when it completes, and check that with a timer. Once the flag is set, timer loads contents into the UI.

So basically:

  1. set “complete” flag to false
  2. start timer
  3. start thread
  4. run query in thread
  5. if query succeeds set complete flag to true
  6. have timer load UI if complete flag is true then stop itself.


Hi Anthony,

So with the process you describe does that mean that whilst the thread is running the query, the main window is still responsive i.e. the progress wheel is animated etc?

Also what is the best way to pass variables to the thread SQL (I have the search term entered by the user that needs to be in the SQL statement)?



What version of Xojo are you using ?

A thread may not make any difference

Hi Norman,

Using 2014 r2.

I saw in other posts that one might get this problem (windows thinking the application was stuck) but they were looping through a process. I am waiting for the server to respond to a long (few seconds) query so wasn’t sure if the UI could update while waiting.



Are you able to post the query? We may be able to optimise it…

How many rows are being parsed by the query?

What database engine ?

Fancy a bet Norman - I’ll put a pint on it at XDC 2015 (if I can get there!)

My guess is MS SQL when he talks about SQL Server :wink:


Yup MS SQL 2008 server with a twist.

Background -

Tables are the database behind SAP Business ONE and cannot be changed neither can the data so I am using a view (in a separate database) to combine 3 tables (UNION) with various bits of postal address, contact information and account information.

This works well and is fast, as expected. It looks at about 20,000 customer entries each with at least 2 addresses (in a separate table) and multiple contact records (in a third table).

All good so far but I am trying to make it very easy for my users to search for a customer with a bit of any part of the address, name, account name, telephone number or email etc.

If I can specify the type of data they are searching for I don’t have a problem (if the search term has 4 or more numbers then it is a telephone number, if it has an ‘@’ then it is an email address) as I can narrow the search BUT if they just put in some text (street name, post code etc) I have to search on a number of key fields.

To make matters worse the original data is from 20 years f customer transactions so in the past some users put dashes in telephone numbers, some added commas in address fields etc. as SAP does not restrict the data entry too much.

This means I have to remove any non-alphanumeric characters whilst searching which is adding the delay because of a “simple” function that removes the unwanted strings.

So here is the messy SQL -

sql = "SELECT dbo.SHLSearchAllSource.CardCode, dbo.SHLSearchAllSource.BPName, dbo.SHLSearchAllSource.Block, dbo.SHLSearchAllSource.Street, dbo.SHLSearchAllSource.City" _ + ", dbo.SHLSearchAllSource.County, dbo.SHLSearchAllSource.ZipCode, dbo.SHLSearchAllSource.Phone1, dbo.SHLSearchAllSource.Phone2" _ + ", dbo.SHLSearchAllSource.Cellular, dbo.SHLSearchAllSource.E_Mail, dbo.SHLSearchAllSource.Type" _ + ", (SELECT Count(*) FROM dbo.SHLSearchAllSource WHERE ((dbo.SHLSearchAllSource.BPName LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.Block LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.Street LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.City LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.County LIKE '%" _ + strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.ZipCode) LIKE '%" _ + strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Phone1) LIKE '%" _ + strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Phone2) LIKE '%" _ + strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Cellular) LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.E_Mail LIKE '%" _ + strSourceText + "%'))) As Record_Count " _ + "FROM dbo.SHLSearchAllSource WHERE ((dbo.SHLSearchAllSource.BPName LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.Block LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.Street LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.City LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.County LIKE '%" _ +strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.ZipCode) LIKE '%" _ + strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Phone1) LIKE '%" _ + strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Phone2) LIKE '%" _ + strSourceTextExtra + "%') OR (dbo.fnStripNonAlpha(dbo.SHLSearchAllSource.Cellular) LIKE '%" _ + strSourceText + "%') OR (dbo.SHLSearchAllSource.E_Mail LIKE '%" _ + strSourceText + "%'))"

And here is the function -

[code]CREATE FUNCTION dbo.fnStripNonAlpha (@temp nvarchar(1000))
RETURNS nvarchar(1000)
Declare @KeepValues as varchar(50)
Set @KeepValues = ‘%[^a-z0-9]%’
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, ‘’)

Return @Temp


I have dropped the function from the address fields already and removed the name fields during further testing but would like to put them all back in place to make sure nothing slips by.

I was hoping to find an “elegant” solution rather than limiting the effectiveness of the search.

As an aside I don’t expect this search to be used very often as the users can use CLI from our soft phones to search on the incoming telephone number (about 70% + calls give us their CLI as mostly B2C) and/or a lot of customers have their reference numbers to hand when they call us.

Wow, brain fart over :slight_smile:



I think the first thing I’d do is get rid of that subquery to get the record_count field. Every row that gets processed by the outer query will execute that subquery - so if your outer query returns 2000 rows then that subquery also gets executed 2000 times!

Unfortunately the recordset class doesn’t support the recordcount property for SQL Server or this would be easy!

One option is to look to get the record_count when I finish processing the recordset in Xojo

[code]Dim RecordCount As Integer = 0

While Not rs.EOF
RecordCount = RecordCount + 1

(Little tip - don’t do Count(*) as it deals with every column in every row - better to do Count(1) as your dealing with a value)

Your query can’t do an Index Seek, it can only make an Index Scan to get at the data - this is because of the leading wildcard search your performing

dbo.SHLSearchAllSource.BPName LIKE '% + strSourceText + %'

Leading wildcard searches are bad news and your doing a lot of them!

You may want to experiment/play with CHARINDEX - as in

CHARINDEX(strSourceText, dbo.SHLSearchAllSource.BPName) > 0 

I doubt this will have a significant saving though as it will have similar problems.

As mentioned in my last post - getting rid of the subquery will substantially reduce the wildcard searches your doing and should improve performance!

Also, use aliases to reference the table - it’ll improve code readability.

Another thing to look at is maybe removing all those function calls to remove all the nonnumeric characters - something like

Stuff(Column, 1, PatIndex('%[^a-z0-9]%', Column) - 1, '')

So your statement would look like

Stuff(dbo.SHLSearchAllSource.ZipCode, 1, PatIndex('%[^a-z0-9]%', dbo.SHLSearchAllSource.ZipCode) - 1, '') LIKE '% + strSourceTextExtra + %')
(haven’t tested the above, may be syntactically wrong but you get the idea!)

If none of the above helps out you may need to look at Full Text Search…

[quote=111422:@Patrick Delaney]Fancy a bet Norman - I’ll put a pint on it at XDC 2015 (if I can get there!)

My guess is MS SQL when he talks about SQL Server ;)[/quote]
Missed that but I’d bet you’re right


Oooh, nice, never thought of getting the count after the query which should be pretty easy as generally unless they do a stupid search it returns a handful of records and I can have the record count stop when it gets to more than an arbitrary number so it doesn’t lock everything up whilst it counts.

It was taking about a second before adding the function but “every bit helps”.

I will do a some research/testing on the CHARINDEX and replacing the function.

Dumb question - how do you implement/use aliases -



Instead of spelling the full table name out each time you can do something like

select <alias>.<column name> from <full table name> as <alias>
insert whatever you think is an appropriate alias name for each table

Also form what you are focusing on, I guess that threads will not allow the UI to be a little responsive whilst running the SQL?

In addition to optimising the SQL.

You lose - you owe me a beer!

Select a.Column1, a.Column2 b.Column1 From Table1 As a INNER JOIN Table2 As b On a.PK = b.RefColumn Where a.Col1 = 'Test'
Should get you started

Hopefully getting rid of that subquery will speed things up nicely anyway.

Cheers Norman, thought aliases was something special in Xojo :slight_smile:

Xojo Threads will allow the UI to be responsive, just don’t try updating the UI from within the Thread - NO, DON’T DO IT :slight_smile:

If you want to update the UI you’ll need to set a property and do it from within a Timer