Small table is really slow...

Question

Post reply

Small table is really slow...

Christian Guevara-324511

Grasshopper

Points: 10
More actions
August 21, 2006 at 4:53 pm

#173692

I'm having a hard time optimizing this procedure. An identicaly version of this sproc takes less than one sec on our dev box (which is just a workstation). On both our staging and Prod environments (which are blades using a SAN) it is taking between 4-30 seconds. The table has just under 1000 rows.
ALTER PROCEDURE [dbo].[Music_ProducerAlbumGetList]
@producerId int = 1,
@searchText varchar(100) = ''
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON;
BEGIN
IF Len(@searchText) > 2
  SELECT @searchText = '%' + @searchText + '%'
ELSE
  SELECT @searchText = '%'
SELECT AlbumId, ProducerId, [Name], Review, LabelId AS RecordLabelId
FROM Album
WHERE producerId = @producerId AND ([Name] LIKE @searchText)
ORDER BY [Name]
END
The table def is :
CREATE TABLE [dbo].[Album](
[AlbumId] [int] IDENTITY(1000,1) NOT NULL,
[ProducerId] [int] NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Review] [varchar](6000) NULL,
[LabelId] [int] NULL,
    CONSTRAINT [PK_Album] PRIMARY KEY CLUSTERED([AlbumId]) ON [PRIMARY]
) ON [PRIMARY]
Anybody out there have any ideas!!!!
Thanks in advance for your help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

you are always performing a full table scan because you did not provide an index for [ProducerId]

This way (b)locking and waits will occur !!

Jus create an index on column [ProducerId] and it will be smooth again.

Keep in mind that for this table each row may need one (1) page !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Christian Guevara-324511 Grasshopper Points: 10 More actions · Answer 2

Ok...I added the index .

But the server doesn't use it .

As it turns out, there is only one value stored in the table for all records (its producerID = 1).

I removed the Review column (varchar (6000)) and performance shot up dramatically. So it definately has to do with the size of every record. How can I force the use of the index...will it matter?

Thanks in advance

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 3

You could create an index on the name column too. And if it still doesnt help you can force SQL to use the index on the name.

The following example shows two ways to use the INDEX optimizer hint. The first example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table, and the second example forces a table scan by using an index of 0.

-------------------------

FROM BOL:

-------------------------

-- Use the specifically named INDEX.

USE AdventureWorks ;

GO

SELECT c.FirstName, c.LastName, e.Title

FROM HumanResources.Employee e WITH (INDEX(IX_Employee_ManagerID))

JOIN Person.Contact c on e.ContactID = c.ContactID

WHERE ManagerID = 2 ;

GO

-- Force a table scan by using INDEX = 0.

USE AdventureWorks ;

GO

SELECT c.LastName, c.FirstName, e.Title

FROM HumanResources.Employee e WITH (INDEX = 0) JOIN Person.Contact c

ON e.ContactID = c.ContactID

WHERE LastName = 'Johnson' ;

GO

-------------------------------

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

- it's useless to create the index if there is only one value. The chance of it being used is fairly equal to 0 because it would only be an overhead.

- an index for [Name] as Dinakar Nethi suggest, may do the job. Let's hope this one has a big enough filter capacity