A client called and they had a slow system. Their complain was very simple.
They are a garment production company. Those garment items are flowing in a belt and there are workers who have a task of swiping the picked item to the bar code reader. Their complaint was that it takes more than 5 secs to read one production item. Their experience is that at the start of the season, this was around 1-2 seconds. When this is taking more than 5 seconds, they archived the data to solve the issue. However, they are looking at a permanent solution as this has been troubling them for a while.
Well, by the looks of it is very obvious that issue is an index. However, it was difficult to convince the customer, mainly client was not able to identify what is the index he should apply. Well, then it is decided to make the physical appearance by driving 40 kilometres.
At the client site, it took only ten minutes to find the troublesome query. SQL Profiler was initiated while asking the users to continue with the normal operations. The query was identified from the profiler and verified it by running it in the SQL Server Management Studio as in the query plan CX_PACKET was identified.
Then the index was applied to cover the where clause condition as it had only one column in the where clause. Well, 5 seconds was reduced to almost zero seconds making users very happy as they can earn more as an extra bonus.
The index is the most common problem in the database systems. However, the art of the index is identifying and creating them. It is something that needs a bit of experience.
If you need more details on Index read this article. Further, if you need more details on CXPACKET, this is the article.
No comments:
Post a Comment