Re: Time out

"Le Hung" <Le Hung@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:144D22B4-924F-4D0F-AF2F-48243BFB7A10@xxxxxxxxxxxxxxxx
[...]
The above procedure will return a data set in one second.
I can not see the different between 2 procedures any one run into the same
problem before please help.

The only difference that I see between both querys is that the "slow" one is parameterized while the second one is not. The difference in speed could be in Sql Server: The parameterized query is optimized once and then stored in the procedure cache, so that every time you send the same query, even though the values of the parameters are different, it follows the same strategy. On the other hand, the non-parameterized version is optimized every time, which will be a little less efficient, but will always choose the best index according to the current index statistics.
If your database statistics are not up-to-date, or you first executed the parameterized query with a set of parameters that happened to provide optimal performance with a full scan (according to the existing statistics at that time), the parameterized query might have been optimized for a full clustered index scan.
I recommend that you UPDATE STATISTICS and then clear the procedure cache (DBCC FREEPROCCACHE) so that your query will be recompiled.

By the way, your indexing strategy is probably suboptimal, at least for this query: You have a very wide clustered index, which needs to be appended to every other index, making those querys slower. You would probably benefit from changing the primary key to non-clustered.

.

Relevant Pages

  • Re: Leftmost column in an index
    ... That's one example where the optimizer creates these _WA% statistics to ... which is the cost of a table scan. ... But this query: ... >> would likely change to a table scan or clustered index scan. ...
    (microsoft.public.sqlserver.programming)
  • Re: Strange Cost Based Optimizer Decision Making
    ... The statistics on this table are 100% completely up to ... > I run an identical SELECT clause to the last query against this table. ... > the stats are fully up to date for LARGE_TABLE. ... compare that number with clustering factor and number ...
    (comp.databases.oracle.server)
  • RE: why this wont work!!??
    ... is used when evaluating the WHERE clause in the query to ... eliminate records that aren't included in the requested data set. ... > That's way I have used a subquery to avoid converting data like 'c*' ...
    (microsoft.public.access.queries)
  • Re: Coincidences with Numbers and Names....Then and Now...Anyone?
    ... I can't work out how to quote a link to my original query. ... "Again I'm unclear which statistics you are referring to when you ... This isn't borne out by the FreeBMD statistics which show it has ...
    (soc.genealogy.britain)
  • Re: Index with strange statistics?
    ... It is true that the index has no appropriate statistics to make any ... because for this query the index cannot be seeked or partially scanned ... > contain the first column of the index which SQL Server can use for a index ... Therefore SQL Server can rely on the ...
    (microsoft.public.sqlserver.server)