Slow Select Queries

[seano]

hi all, I'm having trouble with the performance of my select queries. The database has around 121,000 records and data retrial is very slow is there way around this? maybe if i store the whole table in RAM(Data set)? would that be more efficient? and if so any pro's or con's?

thanks for your time.

 

[jmcilhinney]

What exactly are you doing, what exactly are you expecting/wanting to happen and what exactly is happening?

 

[seano]

What exactly are you doing, what exactly are you expecting/wanting to happen and what exactly is happening?

i want to be able to select the records i want without the massive delay. the select queries are just simple statements like "Select * From Table Where [Feild1]Like 'ten%'".

 

[jmcilhinney]

Details please. I want something to run faster doesn't cut it. I didn't use the word "exactly" three times in my previous post for nothing.

 

[seano]

I am running a select query ("Select * From Table Where [Feild1] Like 'txtsearch.text %'" for example) each time the user types in a search box (Key down event) that retrieves data from a very large table (~121,000 records), its probably not the best way to do it on the key down event but this is what the client wants, there is a longer delay to load the records with less text in the search box (which makes sense because it would return more records).I was hoping there would be a way to get rid of the long delay. So when the user types in the text box the records show up faster on key press. I hope thats enough details for you now thanks for your time.

 

[IanRyder]

Hi,

There could be a multitude of reasons why your queries are slow, albeit searching 121,000 records on a KeyDown event is a bit of a give away, but we will be able to help you a lot more if you help yourself by giving us a complete description of your whole setup. i.e:-

1) What is your current database setup? SQL Sever, Access etc. Is this accessed across a network or is this a local database?
2) What is the current structure of the table you are querying? More importantly, what is the structure of the field you are basing your where clause on. i.e Data type, indexes etc.
3) What is the structure of the code that you are using to access the data? Post what you have so far so that we can see what you currently have.

(As jmcilhinney has already mentioned, exact details please)

Hope that helps.

Cheers,

Ian

 

[jmcilhinney]

We can't just magically make a query faster without knowing anything about it or the context it's used in. Post #5 is much better but there are still more details you could provide, as IanRyder has indicated. Quite possibly there's no way to make your queries faster, but there are ways to make your application perform better. I'll make a few suggestions straight off the bat though.

1. The KeyDown event is very wrong. TextChanged would be the place to start, but even then you shouldn't actually execute the query from there. What if the user quickly types three letters in? There will be three events but you don't want to perform three queries. You only need the results of the last one and it's going to be the smallest result set too. On each TextChanged event you should restart a Timer. Only when that Timer Ticks should perform a query. You would settle on a reasonable delay, e.g. 250 milliseconds, so that you only perform a query if the user stops typing for that long.

2. After you've performed a query, if the user keeps typing then you know for a fact that the next query result set will be a subset of the first. As such, you can simply filter the data you already have instead querying the database again. If the current value starts with the previous value then you know that you can simply filter the data you already have. Only if the user deletes or modifies part of the value used for the first query do you actually need to go back to the database.

For instance, if the user types "ab" then you'll search for all values that start with "ab". If the user then adds "c" you can then just filter the data you have for all values that start with "abc" without going back to the database. If the user then replaces the "c" with a "d" you can still just filter the data you already have because all values that start with "abd" also start with "ab" and you already have all of those. If the user changes the text to "az", then you will need to requery the database. Even then though, you may not discard the data you already had. You might keep it in case it could be used later, saving you another query.

3. As an alternative to 2, you might query every time but limit the number of results to, say, 20. That means that you'll never have thousands of records to pull back. It may mean that the user will have to type more characters to get a match but finding a match manually in a long list is unlikely to happen anyway so they'd probably type more characters to narrow down the options anyway.

 

[seano]

thanks all for your input, i managed to get a satisfactory performance speed but selecting the top 100 rows in the select query which seems to be working great, thanks again!

 

[cjard]

I am running a select query ("Select * From Table Where [Feild1] Like 'txtsearch.text %'" for example) each time the user types in a search box (Key down event) that retrieves data from a very large table (~121,000 records), its probably not the best way to do it

It's definitely not the best way to do it. Set up your events such that the time between every keypress is logged. After 3 keypresses, start taking averages and set a timer to three times the average, max 3 seconds. In the timer's Tick event code, disable the timer and do the search.. This way your users can quickly type out a full word, not have to wait too long for the query to be launched and providing it is suitably indexed, not have to wait too long for the results..

Launchign a query on every keypress for the word hello, will execute these:

SELECT * FROM table WHERE field LIKE 'h%'
SELECT * FROM table WHERE field LIKE 'he%'
SELECT * FROM table WHERE field LIKE 'hel%'
SELECT * FROM table WHERE field LIKE 'hell%'
SELECT * FROM table WHERE field LIKE 'hello%'


I think I'd probably fire a developer who wrote code like that

 

[cjard]

Hmm.. I wonder if it would be worth subclassing TextBox to have just such a lazy event fire mechanism and banging the project on codeproject..

 

[cjard]

Oh.. this is nice. after a bit of googling I found a really neat way to do jmc's suggestion (a fixed timer after the user finishes typing rather than taking the average) using the relatively new Reactive framework

Download this: View attachment Reactive.zip
There are 2 DLLs in it: reference them
Reference also these standard assemblies:
WindowsBase
System.Xaml

Drop a textbox on a form, paste this code into the form load:

Observable.FromEvent(Of EventArgs)(textBox1, "TextChanged") _
  .Throttle(TimeSpan.FromSeconds(0.5)) _
  .ObserveOnDispatcher() _
  .Subscribe(AddressOf OnLazyTextChanged)

it's all one line of code.. i've broken it up into lines for readability and because I'm a c# guy.. If _ isnt a line continuation in VB, put it all back on one line and it'll work

Now write some code that will get called when the observable fires:

		Private Sub OnLazyTextChanged(args As IEvent(Of EventArgs))
			Dim tb = TryCast(args.Sender, TextBox)

			If tb Is Nothing Then
				Return
			End If
			'put your code to handle the event here
			MsgBox(tb.Text)
		End Sub

Amazingly simple way to coalesce a load of textchanged events into one event that fires 0.5 seconds after the user finishes typing.
Props go to this blog: Huy's Blog: Rx Lazy Loading Text Box

 

[jmcilhinney]

@cjard, Rx looks good. There's also a JavaScript version that I may look to use in our MVC apps at work.