Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select Query Optimization

Posted on 2006-04-10
15
Medium Priority
?
231 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Bob Learned
  • 9
  • 2
  • 2
  • +1
15 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 16419820
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
 
LVL 9

Assisted Solution

by:raopsn
raopsn earned 400 total points
ID: 16419869
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
 
LVL 96

Author Comment

by:Bob Learned
ID: 16419878
Is EXISTS an optimized query condition?  I would have thought that an INNER JOIN would be better.

Bob
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 96

Author Comment

by:Bob Learned
ID: 16419884
Also, what affect would SET NOCOUNT ON have?

Bob
0
 
LVL 96

Author Comment

by:Bob Learned
ID: 16419891
And, there's an example with an inner join ^^^.  I will have that version tested.

Bob
0
 
LVL 96

Author Comment

by:Bob Learned
ID: 16419907
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16419923
>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
 
LVL 96

Author Comment

by:Bob Learned
ID: 16419991
I'll have the client try these things out, and I'll try to get back to you.

Thanks,
Bob
0
 
LVL 96

Author Comment

by:Bob Learned
ID: 16420437
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
 
LVL 9

Expert Comment

by:raopsn
ID: 16420612
>> 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
 
LVL 96

Author Comment

by:Bob Learned
ID: 16420785
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 400 total points
ID: 16425996
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
 
LVL 96

Author Comment

by:Bob Learned
ID: 16426026
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16430244
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
 
LVL 96

Author Comment

by:Bob Learned
ID: 16430315
Point taken and passed on.

Bob
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question