October 4, 2008 at 4:56 am
Hi,
I have been having problems where users complain that their call centre application hangs or is too slow. The application uses a SQL2005 backend. I tried to look at various things e.g drive performance, blocks, index tuning, etc to no avail. I have come to the conclusion that there are certain queries that are performing poorly and need to be optimized. One of them is attached. Users complain that when they run the stored proc through the application, it takes a long time to come up with results. This apparently happens at certain times during the day when everyone is busy.
Can someone please help me to optimize this code so that it pulls results through fast? In the past it would take at the most 2 seconds with 120 people on the system. Now it takes 20 seconds even if you have entered a specific person's detail and disk activity appaeras to be high. The proc does a partial search based on what you supply it. You can either enter the accountid or just the idnumber or just the telephone number and it will pick up those people who contain what you supply.
Thanks
Tendayi
Tendayi
October 4, 2008 at 8:31 pm
Do you have a separate index on every column that you can search on? You ned this at a minimum to get any kind of performance out of a procedure like this one. However, you should be aware that any query that is a long list of OR clauses may potentially have performance problems.
Also, please be aware that this query is a severe injection target because you are embedding your parameter values in your dynamic SQL string instead of passing them as parameters to your sp_ExecuteSQL call.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 6, 2008 at 3:59 am
Hi Tandy,
As rbarryyoung explained, you need to make sure you have a separate index on every column that you can search on.
I have change something in your code, Please give a try. I hope I might get more faster in performance wise.
Let me know if you still got a problem.
Leo
October 6, 2008 at 6:54 am
Somehow I don't think indexes are going to help. From what I can see, none of the conditions that may be in the where clause are sargable. All of the likes have wildcards at the beginning and hence cannot use an index seek even if there is an appropriate index.
Is the app such that people will always enter a portion from the middle of what they're searching for? If it's possible to remove the preceeding wildcards, the query will be able to do index seeks and should run a lot faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2008 at 7:11 am
Adding indexes to the system would not be an option for a couple of reasons;
1) If - as is extremely likely - the app's a third party app, then you're going to be breaking your support contract
2) f you're going to build indexes on several tables some of which potentially contain tens of millions of rows you're going to kill the system while you're doing it
3) It's going to impact on the key function of the system, getting the calls in and out by impacting on insert speed
If possible you should be taking this reporting offline by putting it into a reporting system preferably on another server
October 6, 2008 at 1:10 pm
My first question on this is: Are all of the columns being run with "like" string data or numeric? I would normally expect columns like "AccountID" to be an identity (numeric) column, in which case using "like" on it requires a cast/convert. SQL will do that implicitly, but that slows it down too.
My second question is: Have you tried doing this as an insert into a temp table and then selecting from that, instead of dynamic SQL? That usually performs better than a lot of "or" statements.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2008 at 11:05 pm
Are there specific formatting requirements that will tell you if you need a partial search? For example, if you have North American phone numbers only, and you have a ten digit number, then you don't need a LIKE query. That would probably flip you from an index scan to an index seek. Similar rules may apply to other identifiers.
Your users might be making things harder by supplying more data thinking that would make the query faster when in fact, it will probably be slower.
I personally know of a call center application that has a similar approach to searching, but it usually doesn't perform too badly because, it seems, the entire telephone index is either in SQL Server memory, or OS disk cache. After the first hit of the day, it's fast... just not as fast as it could be.
I do more development these days than administration, so if you have some control / influence over the design of the application, consider adding support for EXPLICIT wildcarding. Let the user specify if and where wildcards should be - just make sure to sanitize it before handing off to your stored procedure.
Do some tracing. Check some query execution plans. See what pops up.
October 7, 2008 at 1:26 am
Hi,
I tested the modified procedure and it is better than the one i posted on the site. Thank you very much. I came across something this morning that i would like to ask. When machines hung, i ran sp_lock to see what was happening. The results are attached. I found that there are some spids with a lock TAB and others with a lock type HBT. Could this be part of the problem we are experiencing here? From what i understand, a lock type TAB means that a lock has been made on the entire table, including all data and indexes. A lock type HBT means that a lock is on a b-tree index or heap. How do i identify the table being locked or the procedures causing the lock? How do i prevent locking of that nature in the system if thats the problem?
October 7, 2008 at 1:27 am
Sorry, forgot to attach the file.
October 7, 2008 at 7:01 am
Andrew Gothard (10/6/2008)
Adding indexes to the system would not be an option for a couple of reasons;1) If - as is extremely likely - the app's a third party app, then you're going to be breaking your support contract
I have in fact had great success adding indexes to 3rd party products. About half of the time the vendors have been willing to allow customer-added indexes on a probational basis.
2) f you're going to build indexes on several tables some of which potentially contain tens of millions of rows you're going to kill the system while you're doing it
Note that they are already killing their performance. Besides, adding indexes is something that can be done during off-hours.
3) It's going to impact on the key function of the system, getting the calls in and out by impacting on insert speed
Reasonable indexes, in size and number, have a minimal impact on insert performance. And note again that their insert speed is already being impacted, adding the right indexes (if missing) would improve that situation, not worsen it.
Exporting to a Reporting DB is a good proposal, but the fact remains, wherever the reporting occurs that you cannot expect reasonable performance if you do not have indexes on the columns that you are searching.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 7, 2008 at 7:24 am
tendayit (10/7/2008)
Hi,I tested the modified procedure and it is better than the one i posted on the site. Thank you very much. I came across something this morning that i would like to ask. When machines hung, i ran sp_lock to see what was happening. The results are attached. I found that there are some spids with a lock TAB and others with a lock type HBT. Could this be part of the problem we are experiencing here? From what i understand, a lock type TAB means that a lock has been made on the entire table, including all data and indexes. A lock type HBT means that a lock is on a b-tree index or heap. How do i identify the table being locked or the procedures causing the lock? How do i prevent locking of that nature in the system if thats the problem?
Please post the modified procedure... there are other "optimizations" that might be made. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2008 at 7:35 am
Hi,
Please find attached the modified code.
Thanks
Tendayi
October 7, 2008 at 8:06 am
That query still can't use any indexes due to the leading wildcards on almost all of the filters.
Do your users really enter middle portions of account numbers, phone numbers or reference numbers?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 8:12 am
tendayit (10/7/2008)
A lock type HBT means that a lock is on a b-tree index or heap. How do i identify the table being locked or the procedures causing the lock?
The HBT locks are coming from a bulk operation. Bulk insert, bcp or something similar. Do you have data imports occurring?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 8:44 am
Hi,
Users can enter a full accountid, idnumber, etc and retieve an account but they can also do partial searches where they put in a string say on the account number field e.g '067', they get a maximum 100 entries displayed to them of info that satisfies their criteria and then they pick the person that they want.
There are no data exports or imports happening into the callcenter databases during the day. I found today one machine that apparently works off the callcentre databases and which had a trojan horse on it. How i picked that there was something wrong with this machine was that its sp_lock entry had a resource value of [BULKOP_BACKUP_DB]. There was no backup being done on the server at that time. Could the trojan horse have filtered to the server causing it to have a high disk activity? There are a number of other machines that have the same problem.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply