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
Medium Priority
231 Views
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
Question by:Bob Learned
• 9
• 2
• 2
• +1

LVL 75

Assisted Solution

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

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

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

Bob
0

LVL 96

Author Comment

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

Bob
0

LVL 96

Author Comment

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

Bob
0

LVL 96

Author Comment

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

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

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

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

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

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

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

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

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

ID: 16430315
Point taken and passed on.

Bob
0

## Featured Post

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
Course of the Month10 days, 14 hours left to enroll