SQL Server Performance Forum – Threads Archive

Cost > 100%?

Im writing a "CRM"-system like program and im using sql server. I wanted to test my querys to see their performance but i ran in to trouble. IÂ´m not very familiar with query analyzer yet but lets try. I rand this query:
SELECT Reply.Reply,Reply.ReplyDate,Agent.FirstName,Agent.LastName FROM Reply,Agent
WHERE Reply.CustomerID=6624 AND
Agent.AgentID=Reply.AgentID ORDER BY Reply.ReplyDate DESC I have a clusterd index on Reply.Replydate (smalldatetime) and on Reply.CustomerID, witch is also the primary key. When i watch the execution plan it says that there are two index scans, one for the Reply table and one for the Agent table. But the cost for doing the index scan on Reply table is 600% and for the Agent table itÂ´s 700%? How is this possible? The VERY strange thing is if i run this query:
SELECT Reply.Reply,Reply.ReplyDate,Agent.FirstName,Agent.LastName FROM Reply,Agent
WHERE Reply.CustomerID=11254
AND Agent.AgentID=Reply.AgentID ORDER BY Reply.ReplyDate DESC witch differ only in the CustomerID in the WHERE clause. All costs comes out att 0%? What does the execution plan really tell me? I get the same results if i run this querys many times. And also ive tryed to update statistics, but with no change. Anyone got any ideas? Many thanks! -HÃ¥kan

Hi knoen,
First of all i think u need to change the query and then see the plan.
from
SELECT Reply.Reply,Reply.ReplyDate,Agent.FirstName,Agent.LastName FROM Reply,Agent
WHERE Reply.CustomerID=6624 AND
Agent.AgentID=Reply.AgentID ORDER BY Reply.ReplyDate DESC to [:I] SELECT r.reply,r.replydate,a.firstname,a.lastname FROM Reply r
inner join agent a on r.agentid=a.agentid
where R.CustomerID=6624
ORDER BY R.ReplyDate DESC I feel this query is more sargeble and will improve on performance.
Else u can go for ITW.[^]

]]>