Select Query Optimization

Not being an optimization expert, I was wondering how this stored procedure could be optimized for speed:

ALTER PROCEDURE dbo.getModel
  @YearID int,
  @Manu varchar(50)
AS
BEGIN
  DECLARE @year int
  SET @year = (SELECT TOP 1 LongYear FROM [year] WHERE id=@yearID)
  IF @year IS NOT NULL
    SELECT DISTINCT M.LongModelName FROM ICModel M
      WHERE M.ManufactuerName=@Manu AND
        EXISTS(SELECT TOP 1 1 FROM ICIndex71_1 I
                      WHERE M.LongModelName=I.ICModelID AND
                            I.FirstYear<=@year AND
                            I.LastYear>=@year
                   )
                   order by M.longModelName
END

Indexes:  unknown
Version:  2000 and 2005

Bob
LVL 96
Bob LearnedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
ALTER PROCEDURE dbo.getModel
  @YearID int,
  @Manu varchar(50)
AS
BEGIN
  DECLARE @year int
  SELECT @year =  TOP 1 LongYear FROM [year] WHERE id=@yearID
  IF @year IS NOT NULL
    SELECT DISTINCT M.LongModelName
    FROM ICModel M
    WHERE M.ManufactuerName=@Manu
    AND EXISTS(SELECT TOP 1 1 FROM ICIndex71_1 I
          WHERE M.LongModelName=I.ICModelID AND
                I.FirstYear<=@year AND
                I.LastYear>=@year
       )
    ORDER BY M.longModelName -- You can eliminate this line and handle the sorting at the front end
END


Also u can create indexes on M.ManufactuerName, ICModelID,FirstYear, LastYear
0
raopsnCommented:
Try this:

    SELECT DISTINCT M.LongModelName
    FROM ICModel M
    INNER JOIN ICIndex71_1 I ON M.LongModelName=I.ICModelID AND
                @year BETWEEN I.FirstYear AND I.LastYear
    WHERE M.ManufactuerName=@Manu
    ORDER BY M.longModelName
0
Bob LearnedAuthor Commented:
Is EXISTS an optimized query condition?  I would have thought that an INNER JOIN would be better.

Bob
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bob LearnedAuthor Commented:
Also, what affect would SET NOCOUNT ON have?

Bob
0
Bob LearnedAuthor Commented:
And, there's an example with an inner join ^^^.  I will have that version tested.

Bob
0
Bob LearnedAuthor Commented:
Indexes:

On the ICModel table
     the LongModelName (Non-Unique, Non Clustered) is the only indexed column

On the ICIndex71_1 table
     ICIndex71_1_FirstYear(Non-Unique, Non Clustered)
     ICIndex71_1_HollModelName(Non-Unique, Non Clustered)
     ICIndex71_1_ICModelID(Non-Unique, Non Clustered)
     ICIndex71_1_LastYear(Non-Unique, Non Clustered)
     ICIndex71_1_PartCode(Non-Unique, Non Clustered)
     ICIndex71_1(Clustered)

Bob
0
Aneesh RetnakaranDatabase AdministratorCommented:
>Is EXISTS an optimized query condition?  I would have thought that an INNER JOIN would be better.

for smaller tqbles say less than 10k rows, i think exists will be better than the joibs
0
Bob LearnedAuthor Commented:
I'll have the client try these things out, and I'll try to get back to you.

Thanks,
Bob
0
Bob LearnedAuthor Commented:
Actually, I discovered an even better solution.

I am working with a .NET solution, and working with a DataSet.  Disabling constraints made this stored procedure execute much faster (from 6-8 seconds down to under a second).

I don't know which SQL statement is faster, and I don't have an SQL Server instance here to check this one out.

Bob
0
raopsnCommented:
>> Disabling constraints made this stored procedure execute much faster

constraints affects only the DML operations not the SELECT statements ... I am not sure what else made your proc run faster

0
Bob LearnedAuthor Commented:
There is a significant amount of processing that .NET does when constraints are enabled, to validate the data, and disabling them should give you faster fill time for the SqlDataAdapter and the DataSet.

Bob
0
LowfatspreadCommented:
it looks reasonably efficient as is

ALTER PROCEDURE dbo.getModel
  @YearID int,
  @Manu varchar(50)
AS
BEGIN
  DECLARE @year int
  SET @year = (SELECT TOP 1 LongYear FROM [year] WHERE id=@yearID)
  IF @year IS NOT NULL
    SELECT DISTINCT M.LongModelName FROM ICModel M
      WHERE M.ManufactuerName=@Manu AND
        EXISTS(SELECT TOP 1 1 FROM ICIndex71_1 I
                      WHERE M.LongModelName=I.ICModelID AND
                            I.FirstYear<=@year AND
                            I.LastYear>=@year
                   )
                   order by M.longModelName
END


however the
  SET @year = (SELECT TOP 1 LongYear FROM [year] WHERE id=@yearID)

should include an order by clause to determine the correct Longyear value
as otherwise what you get back may not be what you anticipate particularly when
considering different sql versions..

soo  
write it as
   SET @year = (SELECT TOP 1 LongYear FROM [year] WHERE id=@yearID order by longyear)
 
try this...

ALTER PROCEDURE dbo.getModel
  @YearID int,
  @Manu varchar(50)
AS

    SELECT  M.LongModelName
      FROM ICModel M
     WHERE M.ManufactuerName=@Manu
          AND  EXISTS
                    (SELECT  1
                       FROM ICIndex71_1 I
                         Inner Join
                                 (SELECT TOP 1 LongYear FROM [year]
                                  WHERE id=@yearID
                                  order by longyear  
                                  ) as X
                             on I.FirstYear <= X.Longyear
                           and I.lastYear>= x.Longyear
                      WHERE M.LongModelName=I.ICModelID
                   )
       group by M.longModelName
       order by 1
Return
go

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob LearnedAuthor Commented:
This has now become a mental exercise, since the client got something that they are happy with (disable constraints), and so they don't want to change working SQL code, and also don't feel like experimenting.

But, thank you all for your comments.  I was looking at their stored procedure, and it didn't look like it was as optimized as it could be, but I couldn't think of anything better.

Bob
0
LowfatspreadCommented:
fair enough

however
this could bite them at some stage

>>however the
  SET @year = (SELECT TOP 1 LongYear FROM [year] WHERE id=@yearID)

should include an order by clause to determine the correct Longyear value
as otherwise what you get back may not be what you anticipate particularly when
considering different sql versions..

0
Bob LearnedAuthor Commented:
Point taken and passed on.

Bob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.