Re: Query tuning

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Victor Kozel (victor_kozel_at_tut.by)
Date: 04/01/04 

Date: Thu, 1 Apr 2004 17:05:02 +0300

thanks All,

This is the solution

select *
from
CDO_RXLINK T_CDO_RXLINK, CDO_RXLINK T_CDO_RXLINK1
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
 T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK1.RxSource = 23921 or T_CDO_RXLINK1.RxTarget = 23921)
and T_CDO_RXLINK1.OID = T_CDO_RXLINK.OID /*primary key*/
  "Victor Kozel" <victor_kozel@tut.by> wrote in message news:uJRJ2xvFEHA.3912@TK2MSFTNGP10.phx.gbl...
  Hi Karl

  If I remove the "Between" and "IN" conditions then the plan is OK.
  The fields "CID" and "CREATED" have own indexes too, but if I disable their for optimizer It does not help.
  select *
  from
  CDO_RXLINK T_CDO_RXLINK
  where
  '03/29/2004' BETWEEN ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
  ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.CID IN (100041, 100043) and
  ( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)

  Also "*" I inserted for readability. This is a list of fields really

  I divided the Query to two different queries and it works speedly.
  I think the poor controllability of the sql server is a serious problem.

  Thanks

  "Karl Gram" <NOSPAMkarl@gramonline.nl> wrote in message news:OwSrV4kFEHA.2408@TK2MSFTNGP10.phx.gbl...
    Hi Victor,

    This query is too complex for the optimizer for the following reasons:

    - It can't use any index on T_CDO_RXLINK.CID because of the IN clause.
    - It won't use the index on T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget
    because of the or clause

    Because of this, the optimizer will estimate that a cluster index seek or a
    table scan is probably the best execution plan.

    You best bet (I think) is a combined index on T_CDO_RXLINK.Created and
    T_CDO_RXLINK.Deleted .

    Also replace the 'SELECT *' with only the column names you really need. If
    this is a limited set, add them together with the T_CDO_RXLINK.CID,
    T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget columns to the to make it a
    covering index.

    --
    Karl Gram, BSc, MBA
    http://www.gramonline.com

    "Victor Kozel" <victor_kozel@tut.by> wrote in message
    news:egxlDkjFEHA.2976@TK2MSFTNGP10.phx.gbl...
    I have the query below:
    select *
    from
      CDO_RXLINK T_CDO_RXLINK
    where
      '03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
      T_CDO_RXLINK.CID IN (100041, 100043) and
      ( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
    The fields RxSource and RxTarget have nonclustered indexes.
    Density for the indexes is near E-5.
    Moreover there are another indexes for the table.
    The optimizer does not want to use the indexes and it uses the primary key.
    If I force to use the indexes by hints, then it raise the indexes, but it
    does not select the needed rows.

    select *
    from
      CDO_RXLINK T_CDO_RXLINK (index(rdb$foreign411, rdb$foreign412))
    where
      '03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
      T_CDO_RXLINK.CID IN (100041, 100043) and
      ( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)

    Plan:
    |--Compute
    Scalar(DEFINE:([T_CDO_RXLINK].[TEXTBLOB]=[T_CDO_RXLINK].TEXTBLOB]))

       |--Filter(WHERE:((('Mar 29 2004 12:00AM'>=[T_CDO_RXLINK].[CREATED] AND
    'Mar 29 2004 12:00AM'<=[T_CDO_RXLINK].[DELETED]) AND
    ([T_CDO_RXLINK].[CID]=100043 OR [T_CDO_RXLINK].[CID]=100041)) AND
    ([T_CDO_RXLINK].[RXSOURCE]=23921 OR [T_CDO_RXLINK].[RXTARGE

         |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
    OBJECT:([Vlad43].[dbo].[CDO_RXLINK] AS [T_CDO_RXLINK]))

            |--Hash Match(Inner Join,
    HASH:([T_CDO_RXLINK].[OID])=([T_CDO_RXLINK].[OID]))

               |--Index
    Scan(OBJECT:([Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS
    [T_CDO_RXLINK])) Index Scan
    OBJECT:([Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK]),
    FORCEDINDEX

               |--Index
    Scan(OBJECT:([Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS
    [T_CDO_RXLINK])) Index Scan
    OBJECT:([Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK]),
    FORCEDINDEX [T_CDO_RXLINK].[OID]

    If I make one compound index for the two fields then it works well, and time
    of executing in 10 time better.
    There are causes that I can not change the indexes.
    How can I force the Server to make good plan?

Relevant Pages

  • Re: Whats the deal with PAGEIOLATCH_SH?
    ... Does SQL Server have trouble when a single table is this size? ... About 20 minutes into the query everything goes bad. ... >> go over some threshold, which seems to vary, the disk queue length ... >> I'll work on getting the real execution plan, ...
    (microsoft.public.sqlserver.programming)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... query in my query 2 example. ... Tibor Karaszi, SQL Server MVP ... Actually, what I said was "IF this code is running in a stored procedure", not "BECAUSE this ... statement in the second execution with the execution plan generated for the first execution. ...
    (microsoft.public.sqlserver.tools)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... What you said was what I first planned to say, but then I read the Books Online which implies that the batch is indeed reused, so I held back from saying that. ... the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ...
    (microsoft.public.sqlserver.tools)
  • RE: sql performance
    ... You should look at the query execution plan, ... found in books online and in "Inside SQL Server 2000" by Kalen Delaney ISBN ... Overall performance tuning information can be found in the "SQL Server 2000 ...
    (microsoft.public.sqlserver.programming)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... Actually, what I said was "IF this code is running in a stored procedure", ... execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ... replace query 1 below with the syntax in query 2 below. ...
    (microsoft.public.sqlserver.tools)