insert query takes too long ....

• savibp3

  Right there with Babe

  Points: 752

  More actions

  March 23, 2017 at 6:41 am

  #328889

  I have a insert query in stored procedure it takes min 59 or 110 specs to insert into table.
  query is
  insert into tab1
  select distinct col1,col2,.....
  if I change select  distinct to top keyword it executes in secs....
  How to  improve performance of this query....

• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  March 23, 2017 at 8:01 am

  #1934514

  Well how fast does the just the select run without the insert?

  And how many rows does the distinct return vs. how many rows you were returning with the TOP X

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 23, 2017 at 8:17 am

  #1934519

  savibp3 - Thursday, March 23, 2017 6:41 AM

  I have a insert query in stored procedure it takes min 59 or 110 specs to insert into table.
  query is
  insert into tab1
  select distinct col1,col2,.....
  if I change select  distinct to top keyword it executes in secs....
  How to  improve performance of this query....

  In this case, the SELECT part of the query takes too long. Please post the actual execution plan of the SELECT part as a .sql file attachment. Thanks.

  ^{“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

• savibp3

  Right there with Babe

  Points: 752

  More actions

  March 23, 2017 at 8:25 am

  #1934525

  ZZartin - Thursday, March 23, 2017 8:01 AM

  Well how fast does the just the select run without the insert?

  And how many rows does the distinct return vs. how many rows you were returning with the TOP X

  well if itry to insert 4 lakh row with top key work it executes fast but with distinct to insert even 1 lakh rows it takes 59 secs

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  March 23, 2017 at 9:07 am

  #1934537

  You need to do what Chris is suggesting, get the execution plan. We can speculate all day why one query runs faster than another (although DISTINCT is an aggregation function and notorious for causing performance issues when it's used inappropriately, so there's that) but without evidence, all we're doing is guessing.

  "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

• savibp3

  Right there with Babe

  Points: 752

  More actions

  March 23, 2017 at 9:21 am

  #1934541

  Grant Fritchey - Thursday, March 23, 2017 9:07 AM

  You need to do what Chris is suggesting, get the execution plan. We can speculate all day why one query runs faster than another (although DISTINCT is an aggregation function and notorious for causing performance issues when it's used inappropriately, so there's that) but without evidence, all we're doing is guessing.

  Summary Count Max Total
  Reads 3 9002726 9032989
  Writes 1 33973 33973
  Elapsed Time 1 41593 41593
  CPU Time 1 29200 29200
  EXE Time 1 29200 29200

  Count Max Total Summary
  1 23272 23272 Worktable1
  1 9002726 9002726 resonvloader_event_queue
  1 6991 6991 open_orders
  1 33973 33973 
  # Proc Total IOs Table R Scan IOs/Time
  1 <batch>:0003 9066962 resonvloader_event_queue R 0 9002726
  2 <batch>:0003 9066962 Worktable1 R 1 23272
  3 <batch>:0003 9066962 open_orders R 1 6991
  4 <batch>:0003 9066962  W 1 33973
  5 <batch>:0003 0  Exec Time 0 29200
  6 <batch>:0003 0  CPU Time 0 29200
  7 <batch>:0003 0  Elapsed Time 0 41593

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  March 23, 2017 at 9:25 am

  #1934543

  savibp3 - Thursday, March 23, 2017 9:21 AM

  Grant Fritchey - Thursday, March 23, 2017 9:07 AM

  You need to do what Chris is suggesting, get the execution plan. We can speculate all day why one query runs faster than another (although DISTINCT is an aggregation function and notorious for causing performance issues when it's used inappropriately, so there's that) but without evidence, all we're doing is guessing.

  Summary Count Max Total
  Reads 3 9002726 9032989
  Writes 1 33973 33973
  Elapsed Time 1 41593 41593
  CPU Time 1 29200 29200
  EXE Time 1 29200 29200

  Count Max Total Summary
  1 23272 23272 Worktable1
  1 9002726 9002726 resonvloader_event_queue
  1 6991 6991 open_orders
  1 33973 33973 
  # Proc Total IOs Table R Scan IOs/Time
  1 <batch>:0003 9066962 resonvloader_event_queue R 0 9002726
  2 <batch>:0003 9066962 Worktable1 R 1 23272
  3 <batch>:0003 9066962 open_orders R 1 6991
  4 <batch>:0003 9066962  W 1 33973
  5 <batch>:0003 0  Exec Time 0 29200
  6 <batch>:0003 0  CPU Time 0 29200
  7 <batch>:0003 0  Elapsed Time 0 41593

  That is not the execution plan posted as an attachment to this thread. That is what you need to do for us to effectively help you.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• savibp3

  Right there with Babe

  Points: 752

  More actions

  March 23, 2017 at 9:35 am

  #1934545

  TheSQLGuru - Thursday, March 23, 2017 9:25 AM

  savibp3 - Thursday, March 23, 2017 9:21 AM

  Grant Fritchey - Thursday, March 23, 2017 9:07 AM

  You need to do what Chris is suggesting, get the execution plan. We can speculate all day why one query runs faster than another (although DISTINCT is an aggregation function and notorious for causing performance issues when it's used inappropriately, so there's that) but without evidence, all we're doing is guessing.

  Summary Count Max Total
  Reads 3 9002726 9032989
  Writes 1 33973 33973
  Elapsed Time 1 41593 41593
  CPU Time 1 29200 29200
  EXE Time 1 29200 29200

  Count Max Total Summary
  1 23272 23272 Worktable1
  1 9002726 9002726 resonvloader_event_queue
  1 6991 6991 open_orders
  1 33973 33973 
  # Proc Total IOs Table R Scan IOs/Time
  1 <batch>:0003 9066962 resonvloader_event_queue R 0 9002726
  2 <batch>:0003 9066962 Worktable1 R 1 23272
  3 <batch>:0003 9066962 open_orders R 1 6991
  4 <batch>:0003 9066962  W 1 33973
  5 <batch>:0003 0  Exec Time 0 29200
  6 <batch>:0003 0  CPU Time 0 29200
  7 <batch>:0003 0  Elapsed Time 0 41593

  That is not the execution plan posted as an attachment to this thread. That is what you need to do for us to effectively help you.

  Parse and Compile Time 0.	3613	10	null	3	01ZZZ	0	SERVER_NAME	1
  Adaptive Server cpu time: 0 ms.	0	10	null	3	null	1	SERVER_NAME	2
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	null	1	null	1	SERVER_NAME	3
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	4
  QUERY PLAN FOR STATEMENT 1 (at line 0).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	5
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	6
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	7
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	8
  The type of query is DECLARE.	6203	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	9
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	10
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	11
  QUERY PLAN FOR STATEMENT 2 (at line 10).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	12
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	13
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	14
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	15
  The type of query is SELECT.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	16
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	17
  1 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	18
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	19
  |ROOT:EMIT Operator (VA = 1)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	20
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	21
  |   |SCALAR Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	151	SERVER_NAME	22
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	23
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	24
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	25
  QUERY PLAN FOR STATEMENT 3 (at line 11).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	26
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	27
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	28
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	29
  The type of query is SELECT.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	30
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	31
  1 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	32
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	33
  |ROOT:EMIT Operator (VA = 1)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	34
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	35
  |   |SCALAR Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	151	SERVER_NAME	36
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	37
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	38
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	39
  QUERY PLAN FOR STATEMENT 4 (at line 23).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	40
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	41
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	42
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	43
  The type of query is BEGIN TRANSACTION.	6203	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	44
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	45
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	46
  QUERY PLAN FOR STATEMENT 5 (at line 26).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	47
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	48
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	49
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	50
  The type of query is COND.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	51
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	52
  1 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	53
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	54
  |ROOT:EMIT Operator (VA = 1)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	55
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	56
  |   |SCALAR Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	151	SERVER_NAME	57
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	58
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	59
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	60
  QUERY PLAN FOR STATEMENT 6 (at line 28).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	61
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	62
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	63
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	64
  The type of query is INSERT.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	65
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	66
  4 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	67
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	68
  |ROOT:EMIT Operator (VA = 4)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	69
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	70
  |   |INSERT Operator (VA = 3)	6248	10	PROCNAME	0	01ZZZ	116	SERVER_NAME	71
  |   |  The update mode is direct.	6204	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	72
  |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	73
  |   |   |HASH DISTINCT Operator (VA = 2)	6248	10	PROCNAME	0	01ZZZ	113	SERVER_NAME	74
  |   |   | Using Worktable1 for internal storage.	10263	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	75
  |   |   |  Key Count: 6	6248	10	PROCNAME	0	01ZZZ	118	SERVER_NAME	76
  |   |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	77
  |   |   |   |RESTRICT Operator (VA = 1)(0)(2)(0)(0)(3)	6248	10	PROCNAME	0	01ZZZ	128	SERVER_NAME	78
  |   |   |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	79
  |   |   |   |   |SCAN Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	132	SERVER_NAME	80
  |   |   |   |   |  FROM TABLE	6215	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	81
  |   |   |   |   |  open_orders	6217	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	82
  |   |   |   |   |  oo	6217	10	PROCNAME	0	01ZZZ	6	SERVER_NAME	83
  |   |   |   |   |  Index : pk_open_orders	6225	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	84
  |   |   |   |   |  Forward Scan.	6276	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	85
  |   |   |   |   |  Positioning at index start.	6282	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	86
  |   |   |   |   |  Index contains all needed columns. Base table will not be read.	6286	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	87
  |   |   |   |   |  Using I/O Size 16 Kbytes for index leaf pages.	6272	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	88
  |   |   |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.	6273	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	89
  |   |	6248	10	PROCNAME	0	01ZZZ	106	SERVER_NAME	90
  |   |  TO TABLE	6214	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	91
  |   |  resonvloader_event_queue	6217	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	92
  |   |  Using I/O Size 2 Kbytes for data pages.	10240	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	93
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	94
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	95
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	96
  QUERY PLAN FOR STATEMENT 7 (at line 41).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	97
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	98
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	99
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	100
  The type of query is COND.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	101
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	102
  1 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	103
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	104
  |ROOT:EMIT Operator (VA = 1)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	105
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	106
  |   |SCALAR Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	151	SERVER_NAME	107
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	108
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	109
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	110
  QUERY PLAN FOR STATEMENT 8 (at line 43).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	111
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	112
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	113
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	114
  The type of query is INSERT.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	115
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	116
  4 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	117
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	118
  |ROOT:EMIT Operator (VA = 4)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	119
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	120
  |   |INSERT Operator (VA = 3)	6248	10	PROCNAME	0	01ZZZ	116	SERVER_NAME	121
  |   |  The update mode is direct.	6204	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	122
  |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	123
  |   |   |HASH DISTINCT Operator (VA = 2)	6248	10	PROCNAME	0	01ZZZ	113	SERVER_NAME	124
  |   |   | Using Worktable1 for internal storage.	10263	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	125
  |   |   |  Key Count: 6	6248	10	PROCNAME	0	01ZZZ	118	SERVER_NAME	126
  |   |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	127
  |   |   |   |RESTRICT Operator (VA = 1)(0)(2)(0)(0)(5)	6248	10	PROCNAME	0	01ZZZ	128	SERVER_NAME	128
  |   |   |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	129
  |   |   |   |   |SCAN Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	132	SERVER_NAME	130
  |   |   |   |   |  FROM TABLE	6215	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	131
  |   |   |   |   |  netting..order_allocation	6217	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	132
  |   |   |   |   |  oo	6217	10	PROCNAME	0	01ZZZ	6	SERVER_NAME	133
  |   |   |   |   |  Using Clustered Index.	6224	10	PROCNAME	0	01ZZZ	3	SERVER_NAME	134
  |   |   |   |   |  Index : XC_order_allocation	6225	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	135
  |   |   |   |   |  Forward Scan.	6276	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	136
  |   |   |   |   |  Positioning at index start.	6282	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	137
  |   |   |   |   |  Index contains all needed columns. Base table will not be read.	6286	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	138
  |   |   |   |   |  Using I/O Size 16 Kbytes for index leaf pages.	6272	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	139
  |   |   |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.	6273	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	140
  |   |	6248	10	PROCNAME	0	01ZZZ	106	SERVER_NAME	141
  |   |  TO TABLE	6214	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	142
  |   |  resonvloader_event_queue	6217	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	143
  |   |  Using I/O Size 2 Kbytes for data pages.	10240	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	144
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	145
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	146
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	147
  QUERY PLAN FOR STATEMENT 9 (at line 55).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	148
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	149
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	150
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	151
  The type of query is COND.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	152
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	153
  1 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	154
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	155
  |ROOT:EMIT Operator (VA = 1)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	156
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	157
  |   |SCALAR Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	151	SERVER_NAME	158
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	159
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	160
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	161
  QUERY PLAN FOR STATEMENT 10 (at line 57).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	162
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	163
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	164
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	165
  The type of query is INSERT.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	166
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	167
  4 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	168
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	169
  |ROOT:EMIT Operator (VA = 4)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	170
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	171
  |   |INSERT Operator (VA = 3)	6248	10	PROCNAME	0	01ZZZ	116	SERVER_NAME	172
  |   |  The update mode is direct.	6204	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	173
  |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	174
  |   |   |HASH DISTINCT Operator (VA = 2)	6248	10	PROCNAME	0	01ZZZ	113	SERVER_NAME	175
  |   |   | Using Worktable1 for internal storage.	10263	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	176
  |   |   |  Key Count: 6	6248	10	PROCNAME	0	01ZZZ	118	SERVER_NAME	177
  |   |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	178
  |   |   |   |RESTRICT Operator (VA = 1)(0)(2)(0)(0)(3)	6248	10	PROCNAME	0	01ZZZ	128	SERVER_NAME	179
  |   |   |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	180
  |   |   |   |   |SCAN Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	132	SERVER_NAME	181
  |   |   |   |   |  FROM TABLE	6215	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	182
  |   |   |   |   |  netting..order_alloc_link	6217	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	183
  |   |   |   |   |  oo	6217	10	PROCNAME	0	01ZZZ	6	SERVER_NAME	184
  |   |   |   |   |  Using Clustered Index.	6224	10	PROCNAME	0	01ZZZ	3	SERVER_NAME	185
  |   |   |   |   |  Index : XPK_order_alloc_link	6225	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	186
  |   |   |   |   |  Forward Scan.	6276	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	187
  |   |   |   |   |  Positioning at index start.	6282	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	188
  |   |   |   |   |  Index contains all needed columns. Base table will not be read.	6286	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	189
  |   |   |   |   |  Using I/O Size 16 Kbytes for index leaf pages.	6272	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	190
  |   |   |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.	6273	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	191
  |   |	6248	10	PROCNAME	0	01ZZZ	106	SERVER_NAME	192
  |   |  TO TABLE	6214	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	193
  |   |  resonvloader_event_queue	6217	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	194
  |   |  Using I/O Size 2 Kbytes for data pages.	10240	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	195
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	196
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	197
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	198
  QUERY PLAN FOR STATEMENT 11 (at line 69).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	199
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	200
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	201
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	202
  The type of query is COND.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	203
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	204
  1 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	205
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	206
  |ROOT:EMIT Operator (VA = 1)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	207
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	208
  |   |SCALAR Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	151	SERVER_NAME	209
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	210
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	211
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	212
  QUERY PLAN FOR STATEMENT 12 (at line 71).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	213
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	214
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	215
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	216
  The type of query is INSERT.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	217
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	218
  4 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	219
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	220
  |ROOT:EMIT Operator (VA = 4)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	221
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	222
  |   |INSERT Operator (VA = 3)	6248	10	PROCNAME	0	01ZZZ	116	SERVER_NAME	223
  |   |  The update mode is direct.	6204	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	224
  |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	225
  |   |   |HASH DISTINCT Operator (VA = 2)	6248	10	PROCNAME	0	01ZZZ	113	SERVER_NAME	226
  |   |   | Using Worktable1 for internal storage.	10263	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	227
  |   |   |  Key Count: 6	6248	10	PROCNAME	0	01ZZZ	118	SERVER_NAME	228
  |   |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	229
  |   |   |   |RESTRICT Operator (VA = 1)(0)(2)(0)(0)(5)	6248	10	PROCNAME	0	01ZZZ	128	SERVER_NAME	230
  |   |   |   |	6248	10	PROCNAME	0	01ZZZ	123	SERVER_NAME	231
  |   |   |   |   |SCAN Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	132	SERVER_NAME	232
  |   |   |   |   |  FROM TABLE	6215	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	233
  |   |   |   |   |  netting..trade_allocation	6217	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	234
  |   |   |   |   |  oo	6217	10	PROCNAME	0	01ZZZ	6	SERVER_NAME	235
  |   |   |   |   |  Index : inc1_trade_allocation	6225	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	236
  |   |   |   |   |  Forward Scan.	6276	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	237
  |   |   |   |   |  Positioning at index start.	6282	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	238
  |   |   |   |   |  Index contains all needed columns. Base table will not be read.	6286	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	239
  |   |   |   |   |  Using I/O Size 16 Kbytes for index leaf pages.	6272	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	240
  |   |   |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.	6273	10	PROCNAME	0	01ZZZ	5	SERVER_NAME	241
  |   |	6248	10	PROCNAME	0	01ZZZ	106	SERVER_NAME	242
  |   |  TO TABLE	6214	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	243
  |   |  resonvloader_event_queue	6217	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	244
  |   |  Using I/O Size 2 Kbytes for data pages.	10240	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	245
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	246
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	247
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	248
  QUERY PLAN FOR STATEMENT 13 (at line 82).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	249
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	250
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	251
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	252
  The type of query is SELECT.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	253
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	254
  1 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	255
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	256
  |ROOT:EMIT Operator (VA = 1)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	257
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	258
  |   |SCALAR Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	151	SERVER_NAME	259
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	260
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	261
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	262
  QUERY PLAN FOR STATEMENT 14 (at line 84).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	263
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	264
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	265
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	266
  The type of query is COND.	10262	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	267
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	268
  1 operator(s) under root	6248	10	PROCNAME	0	01ZZZ	167	SERVER_NAME	269
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	270
  |ROOT:EMIT Operator (VA = 1)	6248	10	PROCNAME	0	01ZZZ	169	SERVER_NAME	271
  |	6248	10	PROCNAME	0	01ZZZ	170	SERVER_NAME	272
  |   |SCALAR Operator (VA = 0)	6248	10	PROCNAME	0	01ZZZ	151	SERVER_NAME	273
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	274
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	275
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	276
  QUERY PLAN FOR STATEMENT 15 (at line 86).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	277
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	278
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	279
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	280
  The type of query is ROLLBACK TRANSACTION.	6203	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	281
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	282
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	283
  QUERY PLAN FOR STATEMENT 16 (at line 87).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	284
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	285
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	286
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	287
  The type of query is PRINT.	6203	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	288
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	289
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	290
  QUERY PLAN FOR STATEMENT 17 (at line 91).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	291
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	292
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	293
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	294
  The type of query is COMMIT TRANSACTION.	6203	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	295
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	296
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	297
  QUERY PLAN FOR STATEMENT 18 (at line 93).	6289	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	298
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	299
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	300
  STEP 1	6201	10	PROCNAME	0	01ZZZ	1	SERVER_NAME	301
  The type of query is PRINT.	6203	10	PROCNAME	0	01ZZZ	2	SERVER_NAME	302
  	6248	10	PROCNAME	0	01ZZZ	4	SERVER_NAME	303
  Parse and Compile Time 0.	3613	10	PROCNAME	0	01ZZZ	0	SERVER_NAME	304
  Adaptive Server cpu time: 0 ms.	0	10	PROCNAME	0	null	1	SERVER_NAME	305
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	PROCNAME	10	null	1	SERVER_NAME	306
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	PROCNAME	11	null	1	SERVER_NAME	307
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	PROCNAME	23	null	1	SERVER_NAME	308
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	PROCNAME	26	null	1	SERVER_NAME	309
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	PROCNAME	41	null	1	SERVER_NAME	310
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	PROCNAME	55	null	1	SERVER_NAME	311
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	PROCNAME	69	null	1	SERVER_NAME	312
  Table: resonvloader_event_queue scan count 0, logical reads: (regular=9149872 apf=0 total=9149872), physical reads: (regular=0 apf=0 total=0), apf IOs used=0	3615	10	PROCNAME	71	01ZZZ	0	SERVER_NAME	313
  Table: Worktable1 scan count 1, logical reads: (regular=26236 apf=0 total=26236), physical reads: (regular=24392 apf=0 total=24392), apf IOs used=0	3615	10	PROCNAME	71	01ZZZ	0	SERVER_NAME	314
  Table: netting..trade_allocation (oo) scan count 1, logical reads: (regular=15060 apf=0 total=15060), physical reads: (regular=1890 apf=0 total=1890), apf IOs used=0	3615	10	PROCNAME	71	01ZZZ	0	SERVER_NAME	315
  Total writes for this command: 3457	3614	10	PROCNAME	71	01ZZZ	0	SERVER_NAME	316
  Execution Time 334.	3612	10	PROCNAME	71	01ZZZ	0	SERVER_NAME	317
  Adaptive Server cpu time: 33400 ms.  Adaptive Server elapsed time: 51826 ms.	0	10	PROCNAME	71	null	1	SERVER_NAME	318
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	PROCNAME	82	null	1	SERVER_NAME	319
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 3 ms.	0	10	PROCNAME	84	null	1	SERVER_NAME	320
  Total writes for this command: 12242	3614	10	PROCNAME	91	01ZZZ	0	SERVER_NAME	321
  Execution Time 3.	3612	10	PROCNAME	91	01ZZZ	0	SERVER_NAME	322
  Adaptive Server cpu time: 300 ms.  Adaptive Server elapsed time: 3593 ms.	0	10	PROCNAME	91	null	1	SERVER_NAME	323
  Total number of events generated is : 992188	0	10	PROCNAME	93	null	1	SERVER_NAME	324
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.	0	10	PROCNAME	93	null	1	SERVER_NAME	325
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 3 ms.	0	10	null	3	null	1	SERVER_NAME	326

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 23, 2017 at 9:52 am

  #1934550

  Sybase??

  ^{“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

• savibp3

  Right there with Babe

  Points: 752

  More actions

  March 23, 2017 at 10:05 am

  #1934553

  ChrisM@Work - Thursday, March 23, 2017 9:52 AM

  Sybase??

  yes

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 23, 2017 at 10:10 am

  #1934556

  savibp3 - Thursday, March 23, 2017 10:05 AM

  ChrisM@Work - Thursday, March 23, 2017 9:52 AM

  Sybase??

  yes

  This is the v2012 section of a SQL Server forum. Have you tried posting in a Sybase forum? You might get lucky here but it's a long shot.

  ^{“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

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  March 23, 2017 at 11:39 am

  #1934596

  Sorry, I don't know Sybase.

  This forum is for Microsoft SQL Server. You might be better off tracking down a forum for Sybase.

  "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