October 26, 2012 at 11:14 am
Dear all,
My client has 3 tables.
Orders(OrderID int PRIMARY KEY,TotalAmount money, Quantity int,BillingAddressID int,CustomerID int)
Address(AddressID int PRIMARY KEY,FirstName nvarchar(50),LastName nvarchar(50),Company nvarchar(50),PhoneNumber varchar(20))
CustomerAccount(CustomerID int PRIMARY KEY,Email nvarchar(100),BillingAddressID int)
Now, they have a web page and can enter a string to search all orders which FirstName,LastName,Company,PhoneNumber and OrderID are matched with the string
So they tried to do that with a store procedure:
CREATE PROCEDURE spa_GetOrders
(
@Keyword nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT O.OrderID,O.TotalAmount,A.FirstName,A.LastName
FROM Orders O
INNER JOIN Address A ON O.BillingAddressID = ad.AddressID
INNER JOIN CustomerAccount C ON O.CustomerID = C.CustomerID
WHERE (A.FirstName LIKE '%'+@Keyword+'%'
OR A.LastName LIKE '%'+@Keyword+'%'
OR A.PhoneNumber LIKE '%'+@Keyword+'%'
OR A.Company LIKE '%'+@Keyword+'%'
OR C.Email LIKE '%'+@Keyword+'%'
OR O.OrderID LIKE '%'+@Keyword+'%')
SET NOCOUNT OFF;
END
Beside the clustered indexes on these tables, they also created 2 non-clustered indexes
Address table:
- IX_Addess_Searching_Order index on FirstName,LastName,Company and PhoneNumber
CustomerAccount:
- IX_CustomerAccount_Email index on Email
The Orders table has about 33.000 records. And the spa runs quite slowly to return data. They are expecting it faster and ask me to optimize it.
As I knew, SQL Engine will do a scan table if there is OR operator in WHERE clause . I tried to force SQL engine to use above non-clustered indexes and the time is decreased about 50%.
INNER JOIN Address A WITH(INDEX=IX_Addess_Searching_Order ) ON O.BillingAddressID = ad.AddressID
INNER JOIN CustomerAccount C WITH(INDEX = IX_CustomerAccount_Email ) ON O.CustomerID = C.CustomerID
However, the client still want it faster, they also suggest me to use full text search feature in SQL but I'm not sure about that it can do that. Please help me any solutions which can improve it?
Thanks,
October 26, 2012 at 11:37 am
It would be better to have the query do a search for the specific item they are searching for, instead of doing a catch-all search on all columns they might be searching on.
If they put in a name, search the name columns, if they put in a phone number, search the phone number column, etc. Add a parameter to the proc to indicate the column to be searched, and then do a search for that particular item. It may be easier to do the query as dynamic SQL to simplify the coding. You might also want to add individual parameters for the items to be searched, and then dynamically build a query based on the parameters passed to allow for greater flexibility to search on multiple items together.
If you use dynamic SQL, make sure to use sp_executesql with parameters, instead of using user input to build a dynamic query, to avoid SQL injection.
Individual indexes on the columns to be searched would probably give the best performance.
October 26, 2012 at 11:39 am
There's probably a much better way to do this, but I'll just spitball this idea as something different...what if you created a computed column that contained a concatenation of the searchable fields, index that, and then just do one LIKE statement?
October 26, 2012 at 11:52 am
Michael Valentine Jones (10/26/2012)
It would be better to have the query do a search for the specific item they are searching for, instead of doing a catch-all search on all columns they might be searching on.If they put in a name, search the name columns, if they put in a phone number, search the phone number column, etc. Add a parameter to the proc to indicate the column to be searched, and then do a search for that particular item. It may be easier to do the query as dynamic SQL to simplify the coding. You might also want to add individual parameters for the items to be searched, and then dynamically build a query based on the parameters passed to allow for greater flexibility to search on multiple items together.
If you use dynamic SQL, make sure to use sp_executesql with parameters, instead of using user input to build a dynamic query, to avoid SQL injection.
Individual indexes on the columns to be searched would probably give the best performance.
The client dont want to sepreate to specific items. They only want to enter a string and search probaly everything. I suggested them but they did not agree 🙁 However, I'll try to create individual indexes.
October 26, 2012 at 11:57 am
I would agree with Michael's recommendation here. It's poor design to be using a LIKE operator in a query that does a comparison against every colum in the table. And the full-text search recommendation from the client is really not what you're looking for either.
In truth, I'd recommend checking out my idea. I think that, given the context, it's probably the most feasible.
October 26, 2012 at 11:59 am
TheGreenShepherd (10/26/2012)
There's probably a much better way to do this, but I'll just spitball this idea as something different...what if you created a computed column that contained a concatenation of the searchable fields, index that, and then just do one LIKE statement?
From your idea, I'm thinking about using view technique.
October 26, 2012 at 12:04 pm
You should take a look at Gail's post about catch all queries here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
I think once you read that you will get a pretty good idea of a good way to implement this search for your specific requirements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 26, 2012 at 12:09 pm
Forgive my pessimism and, with all due respect to Gail, I don't think it's really applicable here. OP said that it's the client's requirement that the input string be attempted to match against all columns in the table. It's nonsensical, but hey, if the client's paying...
October 26, 2012 at 12:17 pm
TheGreenShepherd (10/26/2012)
Forgive my pessimism and, with all due respect to Gail, I don't think it's really applicable here. OP said that it's the client's requirement that the input string be attempted to match against all columns in the table. It's nonsensical, but hey, if the client's paying...
That's correct. Only one input string
@sean Lange: thank you for the post
October 26, 2012 at 12:22 pm
TheGreenShepherd (10/26/2012)
There's probably a much better way to do this, but I'll just spitball this idea as something different...what if you created a computed column that contained a concatenation of the searchable fields, index that, and then just do one LIKE statement?
Given the strict requirements of a single input string this does seem like a decent approach. The upside here is that if the table changes you only need to modify the computed column, your code can remain unchanged.
I don't think you need to bother with indexing the computed column though because the like is always starting with a wildcard. It will still have to do a full scan everytime.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 26, 2012 at 12:54 pm
I had a similar requirement, and FTS was the way to go. Only way the performance would get close to being reasonable. A LIKE with % on both sides of the criteria is non sargeable no matter what indexes are in play, so thats a table scan no matter what. FTS in this case, probably makes the most sense.
October 26, 2012 at 1:00 pm
Dung Dinh (10/26/2012)
Michael Valentine Jones (10/26/2012)
It would be better to have the query do a search for the specific item they are searching for, instead of doing a catch-all search on all columns they might be searching on.If they put in a name, search the name columns, if they put in a phone number, search the phone number column, etc. Add a parameter to the proc to indicate the column to be searched, and then do a search for that particular item. It may be easier to do the query as dynamic SQL to simplify the coding. You might also want to add individual parameters for the items to be searched, and then dynamically build a query based on the parameters passed to allow for greater flexibility to search on multiple items together.
If you use dynamic SQL, make sure to use sp_executesql with parameters, instead of using user input to build a dynamic query, to avoid SQL injection.
Individual indexes on the columns to be searched would probably give the best performance.
The client dont want to sepreate to specific items. They only want to enter a string and search probaly everything. I suggested them but they did not agree 🙁 However, I'll try to create individual indexes.
With that requirement, then they will probably need to accept that performance is never going to be very good.
The requirement doesn't really make any sense. If someone puts in a phone number, then there is no good reason to search first name, last name, company name, or email.
This doesn't actually sound like a business requirement, just a "requirement" from a lazy front end developer.
October 26, 2012 at 1:12 pm
DiverKas (10/26/2012)
I had a similar requirement, and FTS was the way to go. Only way the performance would get close to being reasonable. A LIKE with % on both sides of the criteria is non sargeable no matter what indexes are in play, so thats a table scan no matter what. FTS in this case, probably makes the most sense.
Actually, SQL can use an index on an individual column with LIKE with % on both sides of the criteria. However, it will likely be an index scan, followed by a bookmark lookup, or maybe a merge between multiple index scans followed by a bookmark lookup.
A full text index might be useful, but not on an integer column, which OrderID probably is.
October 26, 2012 at 6:40 pm
Michael Valentine Jones (10/26/2012)
DiverKas (10/26/2012)
I had a similar requirement, and FTS was the way to go. Only way the performance would get close to being reasonable. A LIKE with % on both sides of the criteria is non sargeable no matter what indexes are in play, so thats a table scan no matter what. FTS in this case, probably makes the most sense.Actually, SQL can use an index on an individual column with LIKE with % on both sides of the criteria. However, it will likely be an index scan, followed by a bookmark lookup, or maybe a merge between multiple index scans followed by a bookmark lookup.
A full text index might be useful, but not on an integer column, which OrderID probably is.
I agree with you but it will be scan indexes all most of time actually and it is unexpected. Maybe, this causes bad performance.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply