Query estimate cost and real run time different

I have a query, before I do any change, the estimate execute plan show me th
e cost is 1400, the real run time is about 1 minute 33 seconds.
After I eliminated some concatinated column, the estimate execute plan shows
me cost reduce to 66, I expect the query will run quite faster than the ori
ginal one, but real run time still keep in 1 minute 27 seconds.
I know estimate execute cost is not accurate, but should not be such a big d
ifference.
Any body can tell me why, what is the good way to tunning my query?
Thanks in advance,
HGHG,
The best way to tune your query is to actually run it and see what it does.
Examine the execution plan and focus on the parts of the plan that are most
expensive. If one step takes 75% of the effort and five other steps each
take 5%, then examine the 75% and see what you can do about it. (Better
join criteria, another index, recasting the code to use a different
approach, etc.)
Estimated plan is sometimes mildly helpful, but (as you note) cannot be
relied upon. Also, even the actual execution plan has blind spots. For
example, it will not measure how much time is spent in a UDF, the IS_MEMBER
function, and so forth, viewing them as nearly free.
Therefore, in addition to the execution plan, test with timing statements
that show how much clock time the steps take. If you run several tests you
will get a good measure of the execution time.
Russell Fields
"HG" <anonymous@.discussions.microsoft.com> wrote in message
news:C01707AF-3B18-45AC-B188-52D313EBE860@.microsoft.com...
> I have a query, before I do any change, the estimate execute plan show me
the cost is 1400, the real run time is about 1 minute 33 seconds.
> After I eliminated some concatinated column, the estimate execute plan
shows me cost reduce to 66, I expect the query will run quite faster than
the original one, but real run time still keep in 1 minute 27 seconds.
> I know estimate execute cost is not accurate, but should not be such a big
difference.
> Any body can tell me why, what is the good way to tunning my query?
> Thanks in advance,
> HG|||I am new to sql. Can you provide an example of a timing statement that will
show me how long a querry took?|||Larry,
DECLARE @.StartTime DATETIME
SET @.StartTime = GETDATE()
Execute your code here
SELECT DATEDIFF(ms,@.StartTime, GETDATE()) AS ElapsedMilliseconds
Because there are variable, run this a few times to get a best time.
Compare best times of two different strategies to determine how they
compare.
Russell Fields
"Larrry Pensil" <anonymous@.discussions.microsoft.com> wrote in message
news:56CC0769-A7B1-4E97-ADE6-FEF9AF056A77@.microsoft.com...
> I am new to sql. Can you provide an example of a timing statement that
will show me how long a querry took?