April 13, 2009 at 4:36 am
Greetings!
Sir, what is the best way in searching of millions of records in the database?
Situation:
1. I have a table that registers all the table and their columns that were used in the program. So basically in my UI I have an option that will allow the user to set what columns of the table to be searchable. Searchable means columns that will only be consider in the searching. For example if Table1 has 50 columns (field1, field2,... field50) and the user set the field1 until field10 to be searchable then my query will be look like this..
SELECT field1, field2, field3, field4, field5, field6, field7, field8, field9, field10
FROM Table1
WHERE field1 LIKE '%abc%' AND
field2 LIKE '%abc%' AND
field3 LIKE '%abc%' AND
field4 LIKE '%abc%' AND
field5 LIKE '%abc%' AND
field6 LIKE '%abc%' AND
field7 LIKE '%abc%' AND
field8 LIKE '%abc%' AND
field9 LIKE '%abc%' AND
field10 LIKE '%abc%'
[\code]
Note: 'abc' comes from the textbox in my search engine UI.
: Please note that my application is a desktop application, NOT web app.
Is there other way or tricks to do to make my search even faster without changing the logic of my searching?
Hope you understand what I mean.
Thanks a lot.
Mizzi
April 13, 2009 at 7:15 am
You would need to use dynamic SQL and the best way, in my opinion, is to use sp_executesql.
Also check out this blog post about SQL Injection
One "problem" with your query is that you are using LIKE with a leading wild card. This will keep SQL Server from doing any index seeks, which means you will get at best index scans which are usually more expensive than seeks.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply