asked on

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

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

raopsn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Learned

ASKER

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

Bob

Bob Learned

ASKER

Also, what affect would SET NOCOUNT ON have?

Bob

Bob Learned

ASKER

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

Bob

Bob Learned

ASKER

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

Aneesh

>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

Bob Learned

ASKER

I'll have the client try these things out, and I'll try to get back to you.

Thanks,
Bob

Bob Learned

ASKER

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

raopsn

>> 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

Bob Learned

ASKER

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

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Learned

ASKER

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

Lowfatspread

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..

Bob Learned

ASKER

Point taken and passed on.

Bob