need help with db-query on sql-server 2005.
Posted
by Avinash
on Stack Overflow
See other posts from Stack Overflow
or by Avinash
Published on 2010-04-27T12:00:31Z
Indexed on
2010/04/27
12:03 UTC
Read the original article
Hit count: 225
We're seeing strange behavior when running two versions of a query on SQL Server 2005:
version A:
SELECT otherattributes.* FROM listcontacts JOIN otherattributes ON listcontacts.contactId = otherattributes.contactId WHERE listcontacts.listid = 1234 ORDER BY name ASC
version B:
DECLARE @Id AS INT;
SET @Id = 1234;
SELECT otherattributes.* FROM listcontacts JOIN otherattributes
ON listcontacts.contactId = otherattributes.contactId
WHERE listcontacts.listid = @Id
ORDER BY name ASC
Both queries return 1000 rows; version A takes on average 15s; version B on average takes 4s. Could anyone help us understand the difference in execution times of these two versions of SQL?
If we invoke this query via named parameters using NHibernate, we see the following query via SQL Server profiler:
EXEC sp_executesql N'SELECT otherattributes.* FROM listcontacts JOIN otherattributes ON listcontacts.contactId = otherattributes.contactId WHERE listcontacts.listid = @id ORDER BY name ASC', N'@id INT', @id=1234;
...and this tends to perform as badly as version A.
Thanks in advance,
© Stack Overflow or respective owner
