I am working at a client site with an accounting database, and we are dealing with a simple query:
select count(*)
from Contract
inner join Charge on Contract.ContractID = Charge.ContractID
Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.
Our test server is an exact restore of our production server.
Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.
The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.
Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
The problem occurs on our production server regardless of whether other users are using the system.
I have a couple questions:
1) Any ideas on why the query would run so much slower on our production server?
2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.
3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?
Thanks!
blindmanRunning trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
--------------
Check indexes for fragmentation.|||I tried dropping and recreating the indexes, to no effect.
I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.
I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.
blindman|||Originally posted by blindman
I tried dropping and recreating the indexes, to no effect.
I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.
I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.
blindman
Try to use performance monitor - very useful thing. Compare results for servers.|||uhhh...bounce the box?
This is MS BTW
Can you be down for a couple?
What's in the Error Log?
Did you do DBCC CHECKDB?|||blocking ?
Originally posted by blindman
I am working at a client site with an accounting database, and we are dealing with a simple query:
select count(*)
from Contract
inner join Charge on Contract.ContractID = Charge.ContractID
Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.
Our test server is an exact restore of our production server.
Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.
The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.
Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
The problem occurs on our production server regardless of whether other users are using the system.
I have a couple questions:
1) Any ideas on why the query would run so much slower on our production server?
2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.
3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?
Thanks!
blindman|||Bouncing the server did speed up the processing the other day, but the performance quickly degraded again.
Problem with buffer pages, perhaps?
Nothing in the Error Log.
DBCC checks done regularly, and databases are spittin' images of eachother.
No blocks detected, and problem occurs regardless of whether other users are logged in.
blindman|||How big are the tranny logs?
How about the allocation to tempdb?
Is it a high level of OLTP?
But..
The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
Should be an Index Scan...shouldn't it?
And 45 minutes is a long time....(7 hours is an eternity)...
Can it be the network? Are you executing localy on dev and remotley to PROD (even that shouldn't matter)
I'll keep thinking...
You can try the brain trust at:
http://www.sqlteam.com/Default.asp|||I know SQL Server is supposed to be doing this on its own every now and then, but what happens when you run
update statistics table with fullscan
on both tables? Kind of reaching here, because the test database is a restore of the other (and therefore identical). How does the hardware stack up?|||How does the hardware stack up?
...And if I read the REST of the post... Disregard that last bit, Blindman.
Showing posts with label charge. Show all posts
Showing posts with label charge. Show all posts
Friday, March 23, 2012
Tuesday, March 20, 2012
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
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 SOBJECT:([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
Subscribe to:
Posts (Atom)