query performance

2.7 Million rows in accounting_tran table
3.1 Million rows in charge table
Following query only returns 333 rows.
SELECT c.accounting_tran_id
FROM accounting_tran at
JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
WHERE at.lctn_id = 'VA437'
and at.acct_tran_status_typ <> 'e'
and c.fiscal_period = 200504
Problem: query cost over 50. Can't get around table scan or index scan on
charge table. Accounting_Tran table using index s,
Charge Table Indexes:
ix_fp fiscal_period
ix_test1 accounting_tran_id, fiscal_period
ix_test2 charge_id, accounting_tran_id, fiscal_period
Any ideas on how to improve performance?
Thanks in advance,
ChrisCan you paste in here the query plan of the query to see where the
bottleneck is (if there is any)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F7F01F8C-5153-4A34-8195-BA3C8207CCCF@.microsoft.com...
> 2.7 Million rows in accounting_tran table
> 3.1 Million rows in charge table
> Following query only returns 333 rows.
> SELECT c.accounting_tran_id
> FROM accounting_tran at
> JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
> WHERE at.lctn_id = 'VA437'
> and at.acct_tran_status_typ <> 'e'
> and c.fiscal_period = 200504
> Problem: query cost over 50. Can't get around table scan or index scan on
> charge table. Accounting_Tran table using index s,
> Charge Table Indexes:
> ix_fp fiscal_period
> ix_test1 accounting_tran_id, fiscal_period
> ix_test2 charge_id, accounting_tran_id, fiscal_period
> Any ideas on how to improve performance?
> Thanks in advance,
> Chris
>|||DDL, please, including which tables these indices are on...
"Chris" wrote:

> 2.7 Million rows in accounting_tran table
> 3.1 Million rows in charge table
> Following query only returns 333 rows.
> SELECT c.accounting_tran_id
> FROM accounting_tran at
> JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
> WHERE at.lctn_id = 'VA437'
> and at.acct_tran_status_typ <> 'e'
> and c.fiscal_period = 200504
> Problem: query cost over 50. Can't get around table scan or index scan on
> charge table. Accounting_Tran table using index s,
> Charge Table Indexes:
> ix_fp fiscal_period
> ix_test1 accounting_tran_id, fiscal_period
> ix_test2 charge_id, accounting_tran_id, fiscal_period
> Any ideas on how to improve performance?
> Thanks in advance,
> Chris
>|||You can use SET SHOWPLAN option to get a text version of the query plan.
Rick Sawtell
MCT, MCSD, MCDBA
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:Ot$0dcLVFHA.584@.TK2MSFTNGP15.phx.gbl...
> Can you paste in here the query plan of the query to see where the
> bottleneck is (if there is any)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:F7F01F8C-5153-4A34-8195-BA3C8207CCCF@.microsoft.com...
on
>|||StmtText StmtId NodeId Parent PhysicalO
p
LogicalOp Argument DefinedValues
EstimateRows EstimateIO EstimateCPU
AvgRowSize TotalSubtreeCost OutputList Warnings Type
Parallel EstimateExecutions
-- -- -- --
-- -- --
-- -- --
-- -- -- --
-- -- -- --
SET STATISTICS PROFILE ON 76 1 0 NULL
NULL 1 NULL
NULL NULL NULL
NULL NULL NULL NULL SETSTATON
0 NULL
(1 row(s) affected)
StmtText
StmtId
NodeId Parent PhysicalOp LogicalOp
Argument
DefinedValues
EstimateRows EstimateIO
EstimateCPU AvgRowSize TotalSubtreeCost OutputList
Warnings Type
Parallel EstimateExecutions
----
----
---
-- -- -- --
--
----
---
----
-- -- --
-- --
---- --
-- -- --
SELECT c.accounting_tran_id
FROM accounting_tran at
JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
WHERE at.lctn_id = 'VA437'
and at.acct_tran_status_typ <> 'e'
and c.fiscal_period = 200504 77 1 0
NULL NULL 1
NULL
7533.1636 NULL NULL
NULL 51.250423 NULL
NULL SELECT 0 NULL
|--Parallelism(Gather Streams)
77
3 1 Parallelism Gather Streams
NULL
NULL
7533.1636 0.0
4.2613383E-2 15 51.249668
[c].[accounting_tran_id] NULL PLAN_ROW
-1 1.0
|--Hash Match(Inner Join,
HASH:([at].[accounting_tran_id])=([c].[accounting_tran_id]))
77 4 3 Hash
Match Inner Join
HASH:([at].[accounting_tran_id])=([c].[accounting_tran_id])
NULL
7533.1636 0.0 0.22781526
15 51.207058 [c].[accounting_tran_id]
NULL PLAN_ROW -1
1.0
|--Bitmap(HASH:([at].[accounting_tran_id]),
DEFINE:([Bitmap1002]))
77 5 4 Bitmap
Bitmap Create HASH:([at].[accounting_tran_id])
[Bitmap1002] 6729.1655
0.0 4.5124404E-2 121 41.125393
[at].[accounting_tran_id] NULL
PLAN_ROW -1 1.0
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS:([at].[accounting_tran_id]))
77 6 5 Parallelism
Repartition Streams PARTITION COLUMNS:([at].[accounting_tran_id])
NULL
6729.1655 0.0
4.5124404E-2 121 41.125393
[at].[accounting_tran_id] NULL PLAN_ROW
-1 1.0
| |--Filter(WHERE:([at].[acct_tran_status_typ]<'e' OR
[at].[acct_tran_status_typ]>'e'))
77
7 6 Filter Filter
WHERE:([at].[acct_tran_status_typ]<'e' OR
[at].[acct_tran_status_typ]>'e') NULL
6729.1655
0.0 2.9812064E-3 121 41.080269
[at].[accounting_tran_id] NULL
PLAN_ROW -1 1.0
| |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([RetailNightly].[dbo].[Accounting_Tran] AS [at]))
77 8 7 Bookmark Lookup Bookmark
Lookup BOOKMARK:([Bmk1000]),
OBJECT:([RetailNightly].[dbo].[Accounting_Tran] AS [at])
[at].[accounting_tran_id], [at].[acct_tran_status_typ]
6775.4692 41.056252 3.726508E-3
121 41.077286 [at].[accounting_tran_id],
[at].[acct_tran_status_typ] NULL PLAN_ROW -1
1.0
| |--Index
S(OBJECT:([RetailNightly].[dbo].[Accounting_Tran].[Lctn_Id] AS [at]),
SEEK:([at].[LCTN_ID]='VA437') ORDERED FORWARD)
77 10 8 Index
S Index S
OBJECT:([RetailNightly].[dbo].[Accounting_Tran].[Lctn_Id] AS [at]),
SEEK:([at].[LCTN_ID]='VA437') ORDERED FORWARD [Bmk1000]
6775.4692 0.01353462
3.7759214E-3 46 1.7310541E-2 [Bmk1000]
NULL PLAN_ROW
-1 1.0
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([c].[accounting_tran_id]), WHERE:(PROBE([Bitmap1002])=TRUE))
77 18 4 Parallelism
Repartition Streams PARTITION COLUMNS:([c].[accounting_tran_id]),
WHERE:(PROBE([Bitmap1002])=TRUE) NULL
74968.383 0.0
0.21370938 23 9.8538456
[c].[accounting_tran_id] NULL PLAN_ROW
-1 1.0
|--Index
Scan(OBJECT:([RetailNightly].[dbo].[Charge].[IX_test2] AS [c]),
WHERE:([c].[fiscal_period]=200504))
77 19
18 Index Scan Index Scan
OBJECT:([RetailNightly].[dbo].[Charge].[IX_test2] AS [c]),
WHERE:([c].[fiscal_period]=200504) [c].[fiscal_period],
[c].[accounting_tran_id] 74968.383 7.1294303
1.7479717 23 8.8774023
[c].[fiscal_period], [c].[accounting_tran_id] NULL PLAN_ROW
-1 1.0
(10 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp
LogicalOp Argument DefinedValues
EstimateRows EstimateIO EstimateCPU
AvgRowSize TotalSubtreeCost OutputList Warnings Type
Parallel EstimateExecutions
-- -- -- --
-- -- --
-- -- --
-- -- -- --
-- -- -- --
SET STATISTICS PROFILE OFF 78 1 0 NULL
NULL 1 NULL
NULL NULL NULL
NULL NULL NULL NULL SETSTATON
0 NULL
(1 row(s) affected)
"Jens Sü?meyer" wrote:

> Can you paste in here the query plan of the query to see where the
> bottleneck is (if there is any)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:F7F01F8C-5153-4A34-8195-BA3C8207CCCF@.microsoft.com...
>
>|||Chris,
That is a remarkable query plan you posted. Did you actually get that
one with the indexes you described below? By the way, which index is
clustered (if any)?
I am surprised, because scanning index ix_test1 looks more favorable
than ix_test2 (assuming both are nonclustered).
The query benefits from a nonclustered index on Charge(fiscal_period,
accounting_tran_id) or a clustered index on Charge(accounting_tran_id)
You could also test if rewriting the query as below makes any
difference:
SELECT accounting_tran_id
FROM accounting_tran at
WHERE at.lctn_id = 'VA437'
AND at.acct_tran_status_typ <> 'e'
AND EXISTS (
SELECT 1
FROM charge c
WHERE c.accounting_tran_id = at.accounting_tran_id
AND c.fiscal_period = 200504
)
Hope this helps,
Gert-Jan
Chris wrote:
> 2.7 Million rows in accounting_tran table
> 3.1 Million rows in charge table
> Following query only returns 333 rows.
> SELECT c.accounting_tran_id
> FROM accounting_tran at
> JOIN charge c on at.accounting_tran_id = c.accounting_tran_id
> WHERE at.lctn_id = 'VA437'
> and at.acct_tran_status_typ <> 'e'
> and c.fiscal_period = 200504
> Problem: query cost over 50. Can't get around table scan or index scan on
> charge table. Accounting_Tran table using index s,
> Charge Table Indexes:
> ix_fp fiscal_period
> ix_test1 accounting_tran_id, fiscal_period
> ix_test2 charge_id, accounting_tran_id, fiscal_period
> Any ideas on how to improve performance?
> Thanks in advance,
> Chris