Searching by name is VERY slow

[Vitiositas]

Hi guys having a problem with our current database, we are using the query:

SELECT [Client Name] FROM [basic details] WHERE [Client Name] like '*william*' UNION ALL SELECT [Partners Name] FROM [basic details] WHERE [Partners Name] like '*william*';

We use this to search for clients in our database when we are unsure of the client number we assign, when this is searched it can take over 30 seconds to fill the box due to access seizing up, does anyone have any suggestions, is the code doing something it shouldnt or could the problem lie elsewhere, if there is any other information that is needed just let me know.

Thanks alot!

 

[bob fitz]

Have you set an index on "Client Name" and "Partners Name" in their respective tables.

 

[Vitiositas]

I think i may have, how would i check and if i have how would i remove them from an index?

 

[bob fitz]

I think i may have, how would i check and if i have how would i remove them from an index?

I'm sorry, I should have been clearer. I was not suggesting that you remove the index. I was suggesting that an idex should be set. See: http://support.microsoft.com/kb/304272

 

[Vitiositas]

Ah okay, i have gone into the property sheet of the basic details table and it shows that nether the client name nor the partner name is indexed, as the database is currently being used, this is something i will have to do out of hours, I will respond tomorrow to tell you if it has or hasnt fixed my issue

Cheers

 

[Vitiositas]

Got lucky with timing and managed to kick everyone off, have added those two to an index and everything is flying.

Thanks alot for the help!

 

[bob fitz]

You're welcome. Glad to help.

 

[Vitiositas]

Follow up from this, everything was going well and the field was really fast, after afew days it has gone back to how it was, freezing up while entering the name and taking a very long time to even fill in the box. Everything is still indexed so i am really confused.

Any ideas?