I'm assuming you're actually using Microsoft SQL Server, based on the syntax of your SELECT statements. I'm also assuming these three queries are executed in different parts of your program, and not one right after the other. If that's the case, please provide a little more context like what's going on in your program when each of these queries are used. Is the database stored on a single hard drive or a RAID array? What data types are these fields (varchar, nvarchar)? Please update your question or add a comment, and I will update this answer as appropriate.
Using Com As New SqlCommand("SELECT [db_Analytics].[dbo].[Companies].[Company Name],[db_Analytics].[dbo].[Companies].[Company] FROM [db_Analytics].[dbo].[Companies] WHERE [db_Analytics].[dbo].[Companies].[Company Name] LIKE '" & atempstr.Substring(0, 3) & "%'", Con)
Without knowing details, I suggest adding an index to the [company name] field. Try getting an execution plan for your queries to see how they operate in more detail; this can be done from SQL Server Management Studio. Check out this post for details: How do I obtain a Query Execution Plan? If you have one, try this on a development server first because you really should never "experiment" on a production box!
Using Com As New SqlCommand("SELECT [db_Analytics].[dbo].[Companies].[Company Name],[db_Analytics].[dbo].[Companies].[Company] FROM [db_Analytics].[dbo].[Companies] WHERE Replace([db_Analytics].[dbo].[Companies].[Company Name], ' ', '') = '" & atempstr.Replace(" ", "") & "' OR Replace([db_Analytics].[dbo].[Companies].[Company Name], ' ', '') LIKE '" & antempstr.Replace(" ", "") & "'", Con)
Using Com As New SqlCommand("SELECT [db_Analytics].[dbo].[Companies].[Company Name],[db_Analytics].[dbo].[Companies].[Company] FROM [db_Analytics].[dbo].[Companies] WHERE Replace([db_Analytics].[dbo].[Companies].[Abbreviation], ' ', '') = '" & atempstr.Replace(" ", "") & "'", Con)
You may want to index the abbreviation field as well.
If you need to often search the abbreviation and [company name] fields for data and remove the spaces, it will be more efficient to add a new field to the table with the company name and abbreviation each without spaces, something like [company name nospace] and [abbreviation nospace]. Why? If you have to run these queries often, the overhead involved in visiting each row, removing the spaces from the strings, and comparing them will add up quickly. Having the data in the table in a different field without spaces is much more efficient in that case.
If you don't know how to create an index in MSSQL Server, check this MSDN article out: https://msdn.microsoft.com/en-us/library/ms188783.aspx At a bare minimum, you can say
CREATE INDEX idxCompanyName ON [db_Analytics].[dbo].[Companies]([Company Name]);
to create an index on the [company name] field, but you must research the best index configuration for your table.
Update per OP's comments:
Things are going to be a little sluggish if you're running the database off of a single hard drive. I'm guessing this is a very basic server, a workstation being used as a server, or a development box. In that case, all you'd be able to do is try to compensate for the storage setup. If you're using MSSQL Server 2014, you can try an in-memory table, but you must have enough RAM for the operating system, SQL Server, and the data. If not, you'll probably make things worse because Windows will swap it out to disk. Check out http://robtiffany.com/create-in-memory-database-tables-sql-server-2014/ and https://msdn.microsoft.com/en-us/library/dn133079.aspx If possible, try combining the last two queries into one so MSSQL's query optimizer can have a go at it. Also, for querying against the company name and abbreviation without spaces, you can do one of several things:
- Have varchar columns containing the data and an application you write puts it in there
- Have varchar columns containing the data which is put in the fields using a trigger
- Have computed fields, based on the [abbreviation] and [company name] columns. This may cause more overhead so you'd get an even bigger performance hit, so do research first.
