Bob Learned
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.ICModelI D AND
I.FirstYear<=@year AND
I.LastYear>=@year
)
order by M.longModelName
END
Indexes: unknown
Version: 2000 and 2005
Bob
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.ICModelI
I.FirstYear<=@year AND
I.LastYear>=@year
)
order by M.longModelName
END
Indexes: unknown
Version: 2000 and 2005
Bob
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Also, what affect would SET NOCOUNT ON have?
Bob
Bob
ASKER
And, there's an example with an inner join ^^^. I will have that version tested.
Bob
Bob
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-U nique, Non Clustered)
ICIndex71_1_PartCode(Non-U nique, Non Clustered)
ICIndex71_1(Clustered)
Bob
On the ICModel table
the LongModelName (Non-Unique, Non Clustered) is the only indexed column
On the ICIndex71_1 table
ICIndex71_1_FirstYear(Non-
ICIndex71_1_HollModelName(
ICIndex71_1_ICModelID(Non-
ICIndex71_1_LastYear(Non-U
ICIndex71_1_PartCode(Non-U
ICIndex71_1(Clustered)
Bob
>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
for smaller tqbles say less than 10k rows, i think exists will be better than the joibs
ASKER
I'll have the client try these things out, and I'll try to get back to you.
Thanks,
Bob
Thanks,
Bob
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
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
>> 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
constraints affects only the DML operations not the SELECT statements ... I am not sure what else made your proc run faster
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
Bob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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..
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..
ASKER
Point taken and passed on.
Bob
Bob
ASKER
Bob