CODEWITHSUNDEEP
sql-serversql-server-2008
Ignas
Ignas

Reputation: 315

Sql view very slow executing

I have this sql view

SELECT     
    dbo.CustomFilterDetails.CustomFilterID, 
    dbo.ItemCustomParameters.CustomFilterDetailsID, 
    dbo.Item.ItemID
FROM         
    dbo.ItemCustomParameters 
INNER JOIN
    dbo.CustomFilterDetails ON dbo.ItemCustomParameters.CustomFilterDetailsID = dbo.CustomFilterDetails.CustomFilterDetailsID 
INNER JOIN
    dbo.Item ON dbo.Item.ItemName LIKE dbo.ItemCustomParameters.Value 
INNER JOIN
    dbo.ItemParameter ON dbo.ItemParameter.ItemID IS NULL 
                      OR dbo.ItemParameter.Value LIKE dbo.ItemCustomParameters.Value 
                      OR dbo.ItemParameter.Name LIKE dbo.ItemCustomParameters.Name

Why it is so slow working?

Upvotes: 0

Views: 1892

Answers (2)

Peter
Peter

Reputation: 1065

Your problem is the LIKE clauses in this query. LIKE is used for pattern matching, and unless you have full text indexes defined on all of the columns you're comparing, any indexes created against those columns will not be used. You should use "=" instead, and make sure that the following indexes exist:

Item.ItemName
ItemParameter.Value
ItemParameter.Name
ItemCustomParameters.Value
ItemCustomParameters.Name

I'm assuming you already have indexes on 

ItemCustomParameters.CustomFilterDetailsID
CustomFilterDetails.CustomFilterDetailsID

I assume that you're LIKE to including matches that have different cases in their values? If this is not the case, then replacing LIKE with = will already have fixed your problem. Otherwise, use a case-insensitive collation for the relevant fields. Then you can compare strings with varying cases using =, without losing the benefit of your indexes:

ALTER TABLE Item ALTER COLUMN ItemName NVARCHAR(200) COLLATE LATIN1_GENERAL_CI_AS
ALTER TABLE ItemParameter ALTER COLUMN Name NVARCHAR(200) COLLATE LATIN1_GENERAL_CI_AS
ALTER TABLE ItemParameter ALTER COLUMN Value NVARCHAR(200) COLLATE LATIN1_GENERAL_CI_AS

... Etc.

Then replace the LIKEs with = as described above and create your indexes, and it should work fine, although still not ideal. If you're still suffering performance issues after this, consider linking the Item, ItemParameter and ItemCustomParaeter tables using integer keys. Not knowing your database structure, I can't give you much more information on that, but effectively it means that the task of matching items to parameters is done at the time that the parameters are created or assigned, and not every time the view is called.

Upvotes: 1

Virus
Virus

Reputation: 3415

You need not call indexes from view. Indexes are used to sort the data in the tables and store it that will be readily available. This helps a lot in improving the performance. Just create the indexes and run this view and see if it makes difference

Upvotes: 0

Related Questions