Re: sql server 2000 - slow record retrieval

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Anith Sen (anith_at_bizdatasolutions.com)
Date: 03/29/04 

Date: Sun, 28 Mar 2004 21:18:14 -0600

Is there an index on your MessageQueue table? Have you checked the execution
plans to find out the differences between index usage between #1 & #2? If
you haven't, I suggest you to check if #1 uses a scan while #2 uses an index
seek.

If that is not an issue, here is another guess: Since SQL Server can know
the outcome of the predicate 0=1 as FALSE without any parameter substitution
or further optimization, the execution plan is possibly built during the
trivial optimization endeavor & is much simpler. Thus it enables a faster
execution of #2. In case of #1, each possible permutation of the predicates
needs to be considered which may have generated a complex access path. This
causes the lousy execution of #1. 

-- 
Anith

Relevant Pages

  • Re: Experiences with mysql_pconnect?
    ... On some DBMSs the server will cache execution plans and reuse the same ... performance benefit - however MYSQL DOES NOT CURRENTLY CACHE PLANS. ... To use a prepared statement takes at least 2 round trips to the DBMS - ...
    (comp.lang.php)
  • Re: Experiences with mysql_pconnect?
    ... prepared statements". ... On some DBMSs the server will cache execution plans and reuse the same ... IME the performance benefit of using _pconnect rather than _connect is ...
    (comp.lang.php)
  • Re: left joins with nulls
    ... Unfortunally, "When SET SHOWPLAN_ALL is ON, SQL Server returns execution ... And the execution plans for both queries are equal, ... >> The problem is that I can't find any differences between the query plans. ... > As to comparing two query plans.... ...
    (microsoft.public.sqlserver.programming)
  • Re: Same query - different execution plans??
    ... these differ, the plan can differ. ... exhibits *drastically* different execution times depending on how its ... recompile of the stored procedure? ... COMPILED PLANs are being created, depending on whether the sp is run ...
    (comp.databases.ms-sqlserver)
  • Re: Clustered Vrs Non Clustered Indexes
    ... execution plans where those plans might use indexes in different ways. ... operations and shorter execution time compared to using a clustered index. ... "Peter The Spate" wrote in message ...
    (microsoft.public.sqlserver.server)