May 12, 2014 at 8:17 am
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
May 12, 2014 at 8:19 am
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
May 12, 2014 at 8:40 am
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.
May 12, 2014 at 8:45 am
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
May 12, 2014 at 8:58 am
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
May 12, 2014 at 9:26 am
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.
May 12, 2014 at 9:32 am
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
May 12, 2014 at 9:48 am
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
May 13, 2014 at 5:46 am
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..
May 13, 2014 at 6:03 am
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
May 13, 2014 at 6:19 am
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?
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
May 13, 2014 at 6:41 am
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.
May 13, 2014 at 6:47 am
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
May 13, 2014 at 6:59 am
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..
May 13, 2014 at 7:06 am
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