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

Query optimiser bug?

Hi all,
I'm having a real problem with a simple query. The table in question has
about 2 million entries with a clustered index and 2 normal indicies.
This query:
select * from uk_streets where search_thoroughfare like 'agsts drv%'
works fine and fast but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets where search_thoroughfare like @.q
takes about 20 times longer to execute. I figured it's to do with the query
plan being used by the optimiser so I've added an index hint:
select * from uk_streets (index=uk_streets) where search_thoroughfare
like 'agsts drv%'
is still very fast and uses the index but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets (index=uk_streets) where search_thoroughfare
like @.q
ignores the hint and still takes ages (using the clustered index instead).
I'm sure that I'm missing something but it's driving me bonkers! Can anyone
shed any light on the problem please?
Thanks in advance
Jamie.
PS: Here's the table and index structure:
CREATE TABLE UK_Streets
(
locality_key int NOT NULL ,
search_thoroughfare varchar (80) NOT NULL ,
outcode varchar (4) NOT NULL ,
sector char (1) NOT NULL ,
thoroughfare_key int NOT NULL,
thoroughfare_descriptor_key int NOT NULL
)
CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
search_thoroughfare)
CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
search_thoroughfare)Try out with updating The table Statistics ,still if you get the Same
check out the fragmentation using Dbcc and if its bad rebuild the Index
and See
Thanks,
Saradhi|||Hi Saradhi
Thanks for the message - I've run DBREINDEX and it's worked! Thank you so
much - I'd already rebuilt the stats which had no effect but that's
brilliant.
Thanks again!
Cheers
Jamie.
"saradhi" <saradhiY@.gmail.com> wrote in message
news:1124984188.908657.122330@.g43g2000cwa.googlegroups.com...
> Try out with updating The table Statistics ,still if you get the Same
> check out the fragmentation using Dbcc and if its bad rebuild the Index
> and See
> Thanks,
> Saradhi
>|||I am surprised that updating statistics made any difference at all..
The problem is around using variables in your where clause... When the query
is optimized, the optimizer can not see the value of the local variable, and
therefore is unable to use index stats to determine the best index. However
when the value is a literal , index stats can be used to determine the best
index, which can greatly improve performance.
This is not an issue with parameters which are passed in to a Stored proc
and used directly in a where clause... The optimizer CAN see and optimize
for this value - this feature is called Parameter sniffing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jamie Turner" <jamieturner@.fernhillsolutions.net> wrote in message
news:%23bKy5lXqFHA.712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I'm having a real problem with a simple query. The table in question has
> about 2 million entries with a clustered index and 2 normal indicies.
> This query:
> select * from uk_streets where search_thoroughfare like 'agsts drv%'
> works fine and fast but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets where search_thoroughfare like @.q
> takes about 20 times longer to execute. I figured it's to do with the
> query plan being used by the optimiser so I've added an index hint:
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like 'agsts drv%'
> is still very fast and uses the index but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like @.q
> ignores the hint and still takes ages (using the clustered index instead).
> I'm sure that I'm missing something but it's driving me bonkers! Can
> anyone shed any light on the problem please?
> Thanks in advance
> Jamie.
>
> PS: Here's the table and index structure:
> CREATE TABLE UK_Streets
> (
> locality_key int NOT NULL ,
> search_thoroughfare varchar (80) NOT NULL ,
> outcode varchar (4) NOT NULL ,
> sector char (1) NOT NULL ,
> thoroughfare_key int NOT NULL,
> thoroughfare_descriptor_key int NOT NULL
> )
> CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
> search_thoroughfare)
> CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
> CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
> search_thoroughfare)
>

Query optimiser bug?

Hi all,
I'm having a real problem with a simple query. The table in question has
about 2 million entries with a clustered index and 2 normal indicies.
This query:
select * from uk_streets where search_thoroughfare like 'agsts drv%'
works fine and fast but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets where search_thoroughfare like @.q
takes about 20 times longer to execute. I figured it's to do with the query
plan being used by the optimiser so I've added an index hint:
select * from uk_streets (index=uk_streets) where search_thoroughfare
like 'agsts drv%'
is still very fast and uses the index but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets (index=uk_streets) where search_thoroughfare
like @.q
ignores the hint and still takes ages (using the clustered index instead).
I'm sure that I'm missing something but it's driving me bonkers! Can anyone
shed any light on the problem please?
Thanks in advance
Jamie.
PS: Here's the table and index structure:
CREATE TABLE UK_Streets
(
locality_key int NOT NULL ,
search_thoroughfare varchar (80) NOT NULL ,
outcode varchar (4) NOT NULL ,
sector char (1) NOT NULL ,
thoroughfare_key int NOT NULL,
thoroughfare_descriptor_key int NOT NULL
)
CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
search_thoroughfare)
CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
search_thoroughfare)Try out with updating The table Statistics ,still if you get the Same
check out the fragmentation using Dbcc and if its bad rebuild the Index
and See
Thanks,
Saradhi|||Hi Saradhi
Thanks for the message - I've run DBREINDEX and it's worked! Thank you so
much - I'd already rebuilt the stats which had no effect but that's
brilliant.
Thanks again!
Cheers
Jamie.
"saradhi" <saradhiY@.gmail.com> wrote in message
news:1124984188.908657.122330@.g43g2000cwa.googlegroups.com...
> Try out with updating The table Statistics ,still if you get the Same
> check out the fragmentation using Dbcc and if its bad rebuild the Index
> and See
> Thanks,
> Saradhi
>|||I am surprised that updating statistics made any difference at all..
The problem is around using variables in your where clause... When the query
is optimized, the optimizer can not see the value of the local variable, and
therefore is unable to use index stats to determine the best index. However
when the value is a literal , index stats can be used to determine the best
index, which can greatly improve performance.
This is not an issue with parameters which are passed in to a Stored proc
and used directly in a where clause... The optimizer CAN see and optimize
for this value - this feature is called Parameter sniffing.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jamie Turner" <jamieturner@.fernhillsolutions.net> wrote in message
news:%23bKy5lXqFHA.712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I'm having a real problem with a simple query. The table in question has
> about 2 million entries with a clustered index and 2 normal indicies.
> This query:
> select * from uk_streets where search_thoroughfare like 'agsts drv%'
> works fine and fast but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets where search_thoroughfare like @.q
> takes about 20 times longer to execute. I figured it's to do with the
> query plan being used by the optimiser so I've added an index hint:
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like 'agsts drv%'
> is still very fast and uses the index but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like @.q
> ignores the hint and still takes ages (using the clustered index instead).
> I'm sure that I'm missing something but it's driving me bonkers! Can
> anyone shed any light on the problem please?
> Thanks in advance
> Jamie.
>
> PS: Here's the table and index structure:
> CREATE TABLE UK_Streets
> (
> locality_key int NOT NULL ,
> search_thoroughfare varchar (80) NOT NULL ,
> outcode varchar (4) NOT NULL ,
> sector char (1) NOT NULL ,
> thoroughfare_key int NOT NULL,
> thoroughfare_descriptor_key int NOT NULL
> )
> CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
> search_thoroughfare)
> CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
> CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
> search_thoroughfare)
>

Query optimiser bug?

Hi all,
I'm having a real problem with a simple query. The table in question has
about 2 million entries with a clustered index and 2 normal indicies.
This query:
select * from uk_streets where search_thoroughfare like 'agsts drv%'
works fine and fast but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets where search_thoroughfare like @.q
takes about 20 times longer to execute. I figured it's to do with the query
plan being used by the optimiser so I've added an index hint:
select * from uk_streets (index=uk_streets) where search_thoroughfare
like 'agsts drv%'
is still very fast and uses the index but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets (index=uk_streets) where search_thoroughfare
like @.q
ignores the hint and still takes ages (using the clustered index instead).
I'm sure that I'm missing something but it's driving me bonkers! Can anyone
shed any light on the problem please?
Thanks in advance
Jamie.
PS: Here's the table and index structure:
CREATE TABLE UK_Streets
(
locality_key int NOT NULL ,
search_thoroughfare varchar (80) NOT NULL ,
outcode varchar (4) NOT NULL ,
sector char (1) NOT NULL ,
thoroughfare_key int NOT NULL,
thoroughfare_descriptor_key int NOT NULL
)
CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
search_thoroughfare)
CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
search_thoroughfare)
Try out with updating The table Statistics ,still if you get the Same
check out the fragmentation using Dbcc and if its bad rebuild the Index
and See
Thanks,
Saradhi
|||Hi Saradhi
Thanks for the message - I've run DBREINDEX and it's worked! Thank you so
much - I'd already rebuilt the stats which had no effect but that's
brilliant.
Thanks again!
Cheers
Jamie.
"saradhi" <saradhiY@.gmail.com> wrote in message
news:1124984188.908657.122330@.g43g2000cwa.googlegr oups.com...
> Try out with updating The table Statistics ,still if you get the Same
> check out the fragmentation using Dbcc and if its bad rebuild the Index
> and See
> Thanks,
> Saradhi
>
|||I am surprised that updating statistics made any difference at all..
The problem is around using variables in your where clause... When the query
is optimized, the optimizer can not see the value of the local variable, and
therefore is unable to use index stats to determine the best index. However
when the value is a literal , index stats can be used to determine the best
index, which can greatly improve performance.
This is not an issue with parameters which are passed in to a Stored proc
and used directly in a where clause... The optimizer CAN see and optimize
for this value - this feature is called Parameter sniffing.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jamie Turner" <jamieturner@.fernhillsolutions.net> wrote in message
news:%23bKy5lXqFHA.712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I'm having a real problem with a simple query. The table in question has
> about 2 million entries with a clustered index and 2 normal indicies.
> This query:
> select * from uk_streets where search_thoroughfare like 'agsts drv%'
> works fine and fast but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets where search_thoroughfare like @.q
> takes about 20 times longer to execute. I figured it's to do with the
> query plan being used by the optimiser so I've added an index hint:
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like 'agsts drv%'
> is still very fast and uses the index but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like @.q
> ignores the hint and still takes ages (using the clustered index instead).
> I'm sure that I'm missing something but it's driving me bonkers! Can
> anyone shed any light on the problem please?
> Thanks in advance
> Jamie.
>
> PS: Here's the table and index structure:
> CREATE TABLE UK_Streets
> (
> locality_key int NOT NULL ,
> search_thoroughfare varchar (80) NOT NULL ,
> outcode varchar (4) NOT NULL ,
> sector char (1) NOT NULL ,
> thoroughfare_key int NOT NULL,
> thoroughfare_descriptor_key int NOT NULL
> )
> CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
> search_thoroughfare)
> CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
> CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
> search_thoroughfare)
>

query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
Andrew
Yes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:

> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew

query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew

query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would b
e
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this databas
e
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:

> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records i
n
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_t
id
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew