slow query due to index fragmentation?

• juute

  SSC Enthusiast

  Points: 169

  More actions

  November 24, 2010 at 3:04 am

  #243206

  Hi, actually there's an issue with a query that is generated by Cognos.

  I traced the report queries with SQL Server Profiler.

  One query takes about 11 minutes and has 30 filters.

  This query takes about 10 seconds without two certain filters. The attributes of these filters have indexes. So I rebuilt the indexes, but it didn't help.

  Thanks in advance for your response,

  Andre

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 24, 2010 at 3:47 am

  #1254972

  Please post query, table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• juute

  SSC Enthusiast

  Points: 169

  More actions

  November 24, 2010 at 5:47 am

  #1255013

  Here's the part of the query which slows the execution down:

  ...

  , T_02_RH_REGIONAL_HIERARCHY T14

  , T_14_PL_PLATFORM coguda62

  where (T_TD_DATE_HIERARCHIES_DAILY13.TD_KEY = 11

  or T_TD_DATE_HIERARCHIES_DAILY13.TD_KEY = 4)

  and (1 = 0 or T14.CF_CODE = '079')

  /* Following 2 statements slow the query down */

  and T14.RH_FRC_COUNTRY_CODE_02 = '079'

  and T14.RH_DISTRICT_CODE_02 = 'DCM'

  /* the 2 statements above slow the query down */

  and (1 = 0 or T_03_CE_CUSTOMER_ENTITIES_PL.CF_CODE = '079')

  You find the whole query, table definition, index definitions and the execution plans in the attachment.

  Please let me know, if you need more informations!