Our company uses a 3rd party application for numerous aspects of the business. One aspect is dealing with customer payments and when selecting a window within the app to list all the payments ever made, the list returns pretty quickly. However, if an user then performs a filter on the list for a specific client name, this operation can take in excess of 6 minutes and returns about 15 rows out of a table that only contains 120,000 rows.

The underlying table does not contain the client name, this is held in another table (Clients). The link between the 2 tables is the clients reference number. I have defrag'd the indexes on the database, but to no avail. I have come to the conclusion that there is not a suitable index in place to improve this situation.

Q - Is it possible to create an index on the payments table but references the clients name in the clients table? Is this the solution to improve this situation or should I be doing something else (apart from telling the 3rd party vendor to improve this area in a future release)

note: I am a newbie if you haven't already guessed !

  • I guess materialized view (or indexed view in Microsoft terminology) should help. Depends on SQLServer edition you may not even need to change your queries, the view can be used by the engine under the hood when you query tables involved. – a1ex07 Nov 10 '13 at 1:00

If I understand you correctly you have two tables (for this question):

(Client int, ClientName varchar(50), Lots Of Other Columns)


(PaymentID, Payment Columns, ClientID)

Obviously not actual code there to create your tables...

And your application is somehow joining those tables behind the scenes. Probably on the ClientID. So your user runs a filter on "Acme Company" and behind the scenes SQL Server is doing a JOIN statement.

Again - pseudocode

SELECT TheDataIWantFromPayments, TheCustomerDataIWant
FROM Payments 
INNER JOIN Customers
ON Payments.ClientID = Customers.CliendID
WHERE Customer.ClientName = 'Acme Company'

So here is where having more information can really help. On first blush this could be a problem with a simple index create as a solution. Perhaps there is no index on the clientID in each table. Perhaps there is no index on the ClientName.

But there could be other factors at play, too. It is possible that the vendor issues this query as a contains search using the syntax of CompanyName LIKE '%Acme Company%' and then no index on CompanyName would help. It could be that the database design requires a lot of key lookups and an index isn't the solution.

Either way - 6 minutes for 15 rows is not acceptable. I would look at those tables and at least verify that the CompanyName and ClientID (or whatever the columns are that contain those values) are indexed on each side.

I would also strike up a conversation with the vendor. Even just you adding indexes could be an action that violates your support contract. Perhaps there are considerations that they already have. And you shouldn't have to be working through someone else's bad design or poorly performing application.

You can gather some data to help you. Play with the PAL tool and look at your performance data on your system. If you have a development/test environment and you can recreate this, perhaps through playing with Extended Events or SQL Trace you can see the query that issued and then run the query showing a query plan to get a sense for what is happening. But I would definitely loop the vendor in to a question like this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.