 |
|||
Fastest way to search data from table ..
By: rekha singh | 08 May 2010 5:16 pm
Hi All
I have a table where it has 45000 Rows of data and a Column Named Description will be used to search
Just if user enters the Text like this *"Bailed out With"* Where i split the String and will build the Query String and Query will be like this Select * from CaseList where Description
Like'%bailed% ' or description like '%Out%' or description Like '%With%'
This above Query Takes 15.6 Minutes to search data
I think this can be solved by FullText Index search But i am using SQL Express...?? ?
Can any one tell me what to do to search data very fast??? please let me know as soon as possible..
--
Thanks & Regards CommentsIf I'm reading this correctly.. Your query is written thusly: SELECT *
FROM CaseList
WHERE Description Like'%bailed% '
OR description like '%Out%'
OR description Like '%With%'
This query will return ALL rows with the keyword of 'Bailed', 'out', and
'with'
is this what you are looking for? Or do you only want records where the description has the keywords in the record?
If you are looking for those 3 keywords in the description column then remove the OR statements.
so the query would be:
SELECT *
FROM CaseList
WHERE Description Like '%bailed%Out% With%'
if not, than look into how you have your table indexes set up. You can also twike the query out by removing the '*' and replacing it with all the column names that you need to return.
like:
SELECT Name, Vehicle, Description, ReasonCode
FROM CaseList
WHERE Description Like '%bailed%Out% With%'
hope this helps! :)
By: rekha singh | 08 May 2010
Hi, Thanks for reply you are correct .
I want to search the DESCRIPTION column where Either it can have bailed or Out or With Text init..
With My Query it takes 16 minutes to return data and as what you said the query
SELECT *
FROM CaseList
WHERE Description Like '%bailed%Out% With%'
Even it takes the same time and I need to Display all the Column from the Table so i used '*'..
IF any other method please let me know. I need to search the Description
which contains *Bailed *or *Out *Or *with *Texts.
Regards
By: rekha singh | 08 May 2010
Well listing out the columns won't shave that much off the query, just makes for some cleaner code and adheres to T-SQL standards. :) as for the query you'll need to go back to the other version, to make sure you are pulling the description from a record where ANY of the keywords match.
so:
SELECT Description
FROM CaseList
WHERE Description LIKE '%bailed%'
OR Description LIKE '%Out%'
OR Description LIKE '%With%'
The only other suggestion I would have would be to verify your index's and/or make sure your tables are set up in the most efficient way possible. You should only need to worry about the tables if your 'CaseList' table
is pulling data from other tables. If not, then it's all about the table Index's and the memory and speed of the computer that's running the query.
The nature of that type of search is just slow.. Worst since you are querying on 3 or more keywords. Indexing the correct column (play around until you get the fastest time) will help a lot. Also putting the database
on faster computer or moving the database to a database server can speed up the query.
By: rekha singh | 08 May 2010
But Although my PC has 1GB ram and 500GB HDD .. and Its Taking So much time.. So is it possible to use Full text index search In SQL EXPRESS if yes then how can i use it???
[?][?][?][?] [?][?]
By: rekha singh | 08 May 2010
You CAN use Full-Text indexing with the version of SQL Express that supports Full-text Indexing. You have to download the 'Advanced' version of SQL Express. There is no possible way to use 'normal' indexing to help this query. To those that have proposed creating more or 'better' indexes: Anytime the search value begins with a wildcard -a full scan is required. Even if you build a index on the search column, the index is likely to have as many rows as the table, so the query processor will most likely select a table scan. ON the chance that the index has SUBSTAINTIALLY fewer rows (a lot of non-indexed, or NULL values), maybe an INDEX scan will be used.
But the point is, ALL rows that have values will have to be examined BECAUSE of the leading wildcard.
Full-Text indexing is the only reasonable option for data such as this.
Regards,
By: rekha singh | 08 May 2010
If you are running XP 1 gig of RAM is very underpowered. I tell my friends not to even talk to me about how sloe their computers are if they are running XP with anything less than 2 gig of RAM. and HDD really doesn't have anything to do with it (unless it's full).. it's more about RAM and CPU speed.
as for the full text search.. honestly, I do not know. :( I have never had the need to use SQL Express for testing or production. I've always had a server to play with.
By: rekha singh | 08 May 2010
Hi, The key is the Query Processor making the determination that it would be cheaper to scan the index, and then also do the bookmark lookup to the table versus just scan the table. One of those places that the 'statistics' come into consideration.
But in reality, my guess is that close to 100% of the searches, table scans would be used.
Regards,
By: rekha singh | 08 May 2010
|
