Please help to reduce query time

  • Hi,

    I am sure I will achieve this. Please help to reduce query time. I have two tables table1(number of columns = 11 and number of rows in it= 10757292 and in another table table2 (number of columns = 38 and number of rows = 159372 )

    table1 is partitioned on partitionID column. and clustered index in created on this column.

    select [selecting 9 columns all are decimal] from table1 where PartitionID between 1200 and 1000000

    number of rows returned = 1 million. time taken 10 seconds.

    select * from table2

    number of rows returned = 159372 and time taken 5 secs.

    BUT Problem start when I make join of these two tables as below

    select

    [select 9 columns from table1],

    [select 25 columns from table2]

    from table1 t1

    inner join

    table2 t2

    on t1.ID = t2.ID

    where PartitionID between 1200 and 1000000

    number of rows returned = 1 million and time taken 35 seconds. I think this should not be more than 20 secs. Please help where it is hurting....

    Thanks

    Ashok

  • Table definitions, index definitions and execution plan please.

    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
  • Table 1

    --------------------------------------------------------

    CREATE TABLE [dbo].pp(

    [PartitionID] [bigint] NOT NULL,

    PID [int] NOT NULL,

    [Date] [date] NOT NULL,

    CID [int] NOT NULL,

    PPID [int] NOT NULL,

    LE [decimal](38, 8) NOT NULL,

    SE [decimal](38, 8) NOT NULL,

    GE [decimal](38, 8) NOT NULL,

    NE [decimal](38, 8) NOT NULL,

    EE [float] NOT NULL,

    IsC [tinyint] NOT NULL

    )

    --Index Info

    -------------------------------------------------------------------------

    index_nameindex_description index_keys

    idx_ppclustered located on PS_pp PartitionID

    idxpp_ppIDnonclustered located on PS_ppPPID, CID, IsC

    --SERVER IO and TIME Statistics: for First Query

    ---------------------------------------------------------------------

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    (837345 row(s) affected)

    Table 'pp'. Scan count 2, logical reads 13370, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 984 ms, elapsed time = 9279 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table2

    --------------------------------------------------------------------------------

    CREATE TABLE [dbo].ss(

    [pLevel] [char](1) NOT NULL,

    [pLevelID] [int] NOT NULL,

    [er] [varchar](60) NOT NULL,

    [tryID] [int] NOT NULL,

    [terCountryID] [int] NULL,

    [yh] [varchar](10) NOT NULL,

    [uctTypeName] [varchar](120) NOT NULL,

    [ID] [int] NOT NULL,

    [terCurrID] [int] NULL,

    [per] [varchar](30) NOT NULL,

    [p] [varchar](12) NOT NULL,

    [No] [varchar](12) NOT NULL,

    [l] [varchar](7) NOT NULL,

      [varchar](30) NOT NULL,

      [hYear] [varchar](8) NOT NULL,

      [rityDate] [datetime] NULL,

      [onRate] [varchar](30) NOT NULL,

      [kePrice] [decimal](15, 8) NULL,

      [gory] [varchar](10) NOT NULL,

      [goryName] [varchar](120) NOT NULL,

      [tClass] [varchar](120) NOT NULL,

      [mbergIdentifier] [varchar](120) NULL,

      [mbergUnderlyingIdentifier] [varchar](120) NULL,

      [dentifier] [varchar](120) NULL,

      [nderlyingIdentifier] [varchar](120) NULL,

      [sureFactor] [float] NOT NULL,

      [or] [varchar](120) NULL,

      [ector] [varchar](120) NULL,

      [omSubSector] [varchar](120) NULL,

      [ng] [varchar](120) NULL,

      [rodID] [int] NOT NULL,

      [ModifiedDate] [datetime] NOT NULL,

      [iGroup1] [varchar](120) NULL,

      [iGroup2] [varchar](120) NULL,

      [iGroup3] [varchar](120) NULL,

      [uctName] [varchar](120) NOT NULL,

      [ysRatings] [varchar](120) NULL,

      [GroupID] [int] NULL,

      CONSTRAINT [pk_pp_pLevelID] PRIMARY KEY CLUSTERED

      (

      [pLevel] ASC,

      [pLevelID] ASC

      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

      ) ON [PRIMARY]

      --Index Info

      -------------------------------------------------------------------------

      index_nameindex_descriptionindex_keys

      idx_ss_pLevelectorTypenonclustered located on PRIMARYpLevel, ector, Type

      idx_ss_ernonclustered located on PRIMARYer

      idx_ss_ectorernonclustered located on PRIMARYector, er

      pk_ss_pLevelIDclustered, unique, primary keylocated on PRIMARYpLevel, pLevelID

      --SERVER IO and TIME Statistics: for Second Query

      ---------------------------------------------------------------------

      SQL Server parse and compile time:

      CPU time = 0 ms, elapsed time = 0 ms.

      SQL Server Execution Times:

      CPU time = 0 ms, elapsed time = 0 ms.

      SQL Server parse and compile time:

      CPU time = 0 ms, elapsed time = 1 ms.

      (159372 row(s) affected)

      Table 'ss'. Scan count 1, logical reads 4983, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

      (1 row(s) affected)

      SQL Server Execution Times:

      CPU time = 688 ms, elapsed time = 3904 ms.

      SQL Server parse and compile time:

      CPU time = 0 ms, elapsed time = 0 ms.

      SQL Server Execution Times:

      CPU time = 0 ms, elapsed time = 0 ms.

      Query

      Statistics

      --------------------------------------------------------------

      select

      [select 9 columns from table1],

      [select 25 columns from table2]

      from table1 t1

      inner join

      table2 t2

      on t1.PPID = t2.pLevelID

      and t2.pLevel = 'A'

      where PartitionID between 1200 and 1000000

      Time Taken 35 seconds, SQL Server 2012.

    1. Execution plan please (actual, not estimated)

      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
    2. Without seeing the execution plan we're just guessing, but this index:

      pk_ss_pLevelID clustered, unique, primary keylocated on PRIMARY pLevel, pLevelID

      Strikes me as potentially problematic. What does the data in pLevel look like? It's a CHAR(1), but it's the leading edge of your index. That means that the char(1) field is what defines the histogram for the statistics on that index. That could seriously impact it getting selected for use with the query you've provided. Or, it could be showing up as a scan when, with the columns reversed, you might see a seek (maybe, returning 1 million rows, a scan may be right).

      But, 30 seconds for a million rows is not crazy long. And that's 1/10 of the data in the table, so you're not exactly providing SQL Server with anything to filter. It's likely that the only way you can make this faster is to throw hardware at it.

      But, I'll wait and see what the execution plan looks like.

      "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
      - Theodore Roosevelt

      Author of:
      SQL Server Execution Plans
      SQL Server Query Performance Tuning

    3. Sorry, I cannot share execution plan..else I would like to help with whatever possible like any info on this further. I dropped the primary key clustered index and create a primary key with non clustered index on same columns and create a new clustered index on pLevelID but it is happen to be 32 seconds as compared to 34 seconds.. I think it did not help any... and as far as hardware is considered I think it is not required you can see if I perform individual select on both table it not hurting. and why join is hurting and it more than double.

    4. Problem is, without the execution plan we're doing nothing more than making guesses.

      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
    5. I'm with Gail. I'm just guessing without an execution plan.

      And joining millions of rows is more costly than simply selecting millions of rows. That's just how it works. Joining 2 rows is more work than selecting two rows.

      "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
      - Theodore Roosevelt

      Author of:
      SQL Server Execution Plans
      SQL Server Query Performance Tuning

    6. What you would like to suggest if I create a view of tables including columns used in select query and then create unique clustered index on PartitionID of first table..

    7. Again, because I can't see the behavior of your queries, I don't know.

      A view doesn't do anything but store the query. It won't help performance at all unless you're talking about creating a materialized view. And that might help, but, again, without seeing the behavior, I'm not sure.

      "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
      - Theodore Roosevelt

      Author of:
      SQL Server Execution Plans
      SQL Server Query Performance Tuning

    8. ashok84.kr (5/12/2014)


      ...number of rows returned = 1 million and time taken 35 seconds....

      How are you timing this? It could take 35 seconds just to return this number of rows to your desktop from the server.

      What's going to consume a million rows?

      “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

      For fast, accurate and documented assistance in answering your questions, please read this article.
      Understanding and using APPLY, (I) and (II) Paul White
      Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    9. I have stored procedure in which this simple query is being used to fetch records into temp table then consuming that table in another procedure and doing some grouping and joining with another tables etc. etc. when I profiled the procedure I found this statement is culprit which is consuming half of the procedure time. Now if I can reduce it to half it would be great. the ~ 32 seconds are taken from duration column of profiler. I believe it does not include SSMS and Network overhead.

    10. Try removing the interim storage. Instead of inserting into temp table then reading from temp table, do the grouping/joining directly.

      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
    11. As I said temp table is consumed in another procedure in which complex business logic is written and based on the data in this temp table various things happen in that procedure. it is not possible to change whole thing :w00t: we have to find a way to optimize it without increasing hardware which I think is optimum..

    12. ashok84.kr (5/13/2014)


      As I said temp table is consumed in another procedure in which complex business logic is written and based on the data in this temp table various things happen in that procedure. it is not possible to change whole thing :w00t: we have to find a way to optimize it without increasing hardware which I think is optimum..

      But you're moving 1/10th of the total table. How much of that are you actually consuming in this complex query?

      I'm being blunt and honest here, I see very little opportunity to tune this query. You should reassess the approach.

      "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
      - Theodore Roosevelt

      Author of:
      SQL Server Execution Plans
      SQL Server Query Performance Tuning

    Viewing 15 posts - 1 through 15 (of 25 total)

    You must be logged in to reply to this topic. Login to reply