Query performance problem

I'm having trouble figuring out why a query is having
varying response time. The following query takes anywhere
from a 2 seconds to 20 minutes:
select *
from tbl (nolock)
where ACTIVE = 1
There are 17 rows returned in the table. The largest data
in the text field is 34 KB, with most around 20 KB.
Anybody have any ideas?
Table structure is as follows:
CREATE TABLE [tbl] (
[ID] [int] IDENTITY (40, 1) NOT NULL ,
[ACTIVE] [bit] NOT NULL ,
[NAME] [varchar] (50) COLLATE NULL ,
[FILE] [text] NULL ,
CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
(
[SHEET_ID]
)
)What indexes do you have? And how many rows in the table? What query plan
does the optimizer select? Does it use an index on the Active column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||There is only one index, The PK on the field "ID" (see DDL
below).
There are 21 rows in the table, the query returns 17.
Notice that there is a text column in the tbl (20-30KB).
The optimizer does a full tablescan on the table. Also
note, I am running a trace for that table and there is not
any update DML.
Thanks,
Mark

>--Original Message--
>What indexes do you have? And how many rows in the table?
What query plan
>does the optimizer select? Does it use an index on the
Active column?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <anonymous@.discussions.microsoft.com>
wrote in message
>news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
anywhere
data
>
>.
>|||OK. Well, you could try creating a supporting index for the query, but it
seems like the major issue here is actually accessing the BLOB data. Bit
still it is not so much data that I would suspect so varying response times.
And just to be certain, check for blocking (I know you mention you are
running a profiler trace, but just to be certain). I guess you could
investigate where the wait time is, a recent SQL Server Magazine had a nice
article regarding this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
> There is only one index, The PK on the field "ID" (see DDL
> below).
> There are 21 rows in the table, the query returns 17.
> Notice that there is a text column in the tbl (20-30KB).
> The optimizer does a full tablescan on the table. Also
> note, I am running a trace for that table and there is not
> any update DML.
> Thanks,
> Mark
>
> What query plan
> Active column?
> wrote in message
> anywhere
> data|||Have you attempted to call just the column names instead of using the "*"?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||Another table has the "id" field as a fk. Could it be that
an exclusive lock is placed on the "id" when the other
table is inserted or updated?

>--Original Message--
>OK. Well, you could try creating a supporting index for
the query, but it
>seems like the major issue here is actually accessing the
BLOB data. Bit
>still it is not so much data that I would suspect so
varying response times.
>And just to be certain, check for blocking (I know you
mention you are
>running a profiler trace, but just to be certain). I
guess you could
>investigate where the wait time is, a recent SQL Server
Magazine had a nice
>article regarding this.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
>news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
DDL
not
table?
>
>.
>|||Yes, that is possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:c49201c40c6c$0fc53440$a601280a@.phx.gbl...
> Another table has the "id" field as a fk. Could it be that
> an exclusive lock is placed on the "id" when the other
> table is inserted or updated?
>
> the query, but it
> BLOB data. Bit
> varying response times.
> mention you are
> guess you could
> Magazine had a nice
> DDL
> not
> table?

query performance issue of Sql server 2005 Mobile edition on device

Executing a select query with left outer joins etc takes .53 seconds to execute on sqlce 2.0.

Same query on sql 2005 mobile ed. takes 11 minutes .

on database having same data.

Sample query

SELECT routes.location,routes.equipment_type, routes.contract_type,

routes.maintenance_interval,routes.bank_description,routes.Unit_Des,

routes.Unit_no,max(task_last_completed.date_completed)as date1,min(case when

task_last_completed.due_date is NULL then getdate()-1 else due_date end) as

due_date FROM routes left outer join tasks on tasks.model = routes.model and

tasks.eqtyp = routes.equipment_type inner join task_by_contract_type on

tasks.task_id = task_by_contract_type.task_id and

task_by_contract_type.contract_type = routes.contract_type and

task_by_contract_type.model = routes.model left outer join

task_last_completed on routes.unit_no = task_last_completed.equipment_Id and

tasks.task_Id = task_last_completed.task_Id WHERE routes.location LIKE

'S153825-01%' group by

routes.location,routes.equipment_type,routes.contract_type,routes.maintenanc

e_interval,routes.bank_description,routes.unit_des,routes.unit_no ORDER BY

routes.location, routes.bank_description, routes.Unit_Des

WHY?

Hi Vijay,

Can you answer the following questions to help us understand your scenario better:

What is the configuration of the device (make/OS version/processor type/processor speed/total memory/available memory for use at the time query was run) on which the query was run against SQL Mobile database?|||

Hi Vijay,

Can you answer the following questions to help us understand your scenario better:

What is the configuration of the device (make/OS version/processor type/processor speed/total memory/available memory for use at the time query was run) on which the query was run against SQL Mobile database?|||

Answer as follows:

1- verizon/Windows Mobile 5.0/os5.1.1700(build 14354.0.1.1)/PXA270-416MHz/49.45MB/~23MB

2 - Yes

3 - Yes as existing schema of Sqlce 2.0 is upgraded to sql mobile 5.0 using upgrade.exe

4 - 9.66 MB sql ce 2.0 and 8.09 MB sql mobile

5 - Running the query through query analyser.

6. can we set buffer pool etc. for query analyser on device.

One thing i have identified later in our datbase it hase number of duplicate records but even then sql ce performance is comparative very high.

Even after deleting duplicate records difference and read that sql mobile deals with the outer joins differently as compared to sqlce 2.0 but even then performance is the issue,

Sql ce 2.0 takes 0.53 Minutes

Sql Mobile takes 1.40 Minutes

Thanks

vijay

Query Performance

Running SQL 2005, SP1.
Any idea how to speed up the following query? It takes only a few seconds
when cached, but since it is only run once a week, by the time it gets run,
it is no longer in cache and takes over a minute.
SELECT 'The name of my database' AS DBName,
s.object_id,
s.name AS TableName,
i.index_id,
i.name AS IndexName,
i.fill_factor,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count,
ps.partition_number,
ps.index_type_desc,
ps.record_count,
ps.avg_page_space_used_in_percent,
ps.avg_fragment_size_in_pages,
ps.avg_record_size_in_bytes,
ps.min_record_size_in_bytes,
ps.max_record_size_in_bytes
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
'DETAILED') ps
ON s.object_id = ps.object_id
AND i.index_id = ps.index_id
WHERE s.type_desc = 'USER_TABLE'
AND i.index_id > 0
AND i.index_id < 255
AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ps.index_level = 0 --this essentially makes the query return just
Limited mode records, but with Detailed mode data
GROUP BY s.object_id,
s.name,
i.index_id,
i.name,
i.fill_factor,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count,
ps.partition_number,
ps.index_type_desc,
ps.record_count,
ps.avg_page_space_used_in_percent,
ps.avg_fragment_size_in_pages,
ps.avg_record_size_in_bytes,
ps.min_record_size_in_bytes,
ps.max_record_size_in_bytes
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1what does the query do ?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76e5a9a710d68@.uwe...
> Running SQL 2005, SP1.
> Any idea how to speed up the following query? It takes only a few seconds
> when cached, but since it is only run once a week, by the time it gets
> run,
> it is no longer in cache and takes over a minute.
> SELECT 'The name of my database' AS DBName,
> s.object_id,
> s.name AS TableName,
> i.index_id,
> i.name AS IndexName,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
> 'DETAILED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.index_level = 0 --this essentially makes the query return
> just
> Limited mode records, but with Detailed mode data
> GROUP BY s.object_id,
> s.name,
> i.index_id,
> i.name,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||I tried to run it and got the following error
Msg 297, Level 16, State 12, Line 1
The user does not have permission to perform this action.
I am a sysadmin on the server. hmmm.. Any reason why its failing ?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76e5a9a710d68@.uwe...
> Running SQL 2005, SP1.
> Any idea how to speed up the following query? It takes only a few seconds
> when cached, but since it is only run once a week, by the time it gets
> run,
> it is no longer in cache and takes over a minute.
> SELECT 'The name of my database' AS DBName,
> s.object_id,
> s.name AS TableName,
> i.index_id,
> i.name AS IndexName,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
> 'DETAILED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.index_level = 0 --this essentially makes the query return
> just
> Limited mode records, but with Detailed mode data
> GROUP BY s.object_id,
> s.name,
> i.index_id,
> i.name,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Hi Hassan
Perhaps you don't have a database with ID 19? Try replacing the 19 in the
call to sys.dm_db_index_physical_stats with this:
db_id('name of a real database on your server')
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:uBCbwOf4HHA.5796@.TK2MSFTNGP05.phx.gbl...
>I tried to run it and got the following error
> Msg 297, Level 16, State 12, Line 1
> The user does not have permission to perform this action.
>
> I am a sysadmin on the server. hmmm.. Any reason why its failing ?
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:76e5a9a710d68@.uwe...
>> Running SQL 2005, SP1.
>> Any idea how to speed up the following query? It takes only a few seconds
>> when cached, but since it is only run once a week, by the time it gets
>> run,
>> it is no longer in cache and takes over a minute.
>> SELECT 'The name of my database' AS DBName,
>> s.object_id,
>> s.name AS TableName,
>> i.index_id,
>> i.name AS IndexName,
>> i.fill_factor,
>> ps.page_count,
>> ps.avg_fragmentation_in_percent,
>> ps.fragment_count,
>> ps.partition_number,
>> ps.index_type_desc,
>> ps.record_count,
>> ps.avg_page_space_used_in_percent,
>> ps.avg_fragment_size_in_pages,
>> ps.avg_record_size_in_bytes,
>> ps.min_record_size_in_bytes,
>> ps.max_record_size_in_bytes
>> FROM sys.objects s
>> JOIN sys.indexes i
>> ON s.object_id = i.object_id
>> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
>> 'DETAILED') ps
>> ON s.object_id = ps.object_id
>> AND i.index_id = ps.index_id
>> WHERE s.type_desc = 'USER_TABLE'
>> AND i.index_id > 0
>> AND i.index_id < 255
>> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
>> AND ps.index_level = 0 --this essentially makes the query return
>> just
>> Limited mode records, but with Detailed mode data
>> GROUP BY s.object_id,
>> s.name,
>> i.index_id,
>> i.name,
>> i.fill_factor,
>> ps.page_count,
>> ps.avg_fragmentation_in_percent,
>> ps.fragment_count,
>> ps.partition_number,
>> ps.index_type_desc,
>> ps.record_count,
>> ps.avg_page_space_used_in_percent,
>> ps.avg_fragment_size_in_pages,
>> ps.avg_record_size_in_bytes,
>> ps.min_record_size_in_bytes,
>> ps.max_record_size_in_bytes
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Hi cbrichards
My guess is that the speed difference is not dependent on whether the query
is cached, but whether all the data that the query needs is cached. Your
query looks at every row on every page in every user table in an entire
database. Just because you are restricting the data actually returned
doesn't make it less work for SQL Server to find all the data and perform
the computations.
The reason that sys.dm_db_index_physical_stats function has a 'limited' mode
is because 'detailed' mode can take a long time on a big database. So to
speed this up, you can either use a database that is smaller, or change the
'detailed' mode to 'limited'
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76e5a9a710d68@.uwe...
> Running SQL 2005, SP1.
> Any idea how to speed up the following query? It takes only a few seconds
> when cached, but since it is only run once a week, by the time it gets
> run,
> it is no longer in cache and takes over a minute.
> SELECT 'The name of my database' AS DBName,
> s.object_id,
> s.name AS TableName,
> i.index_id,
> i.name AS IndexName,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
> 'DETAILED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.index_level = 0 --this essentially makes the query return
> just
> Limited mode records, but with Detailed mode data
> GROUP BY s.object_id,
> s.name,
> i.index_id,
> i.name,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||I believe Kalen answered your performance question. I assume that the
performance this weekly query is only any issue because you run it
interactively (report?) and perhaps for many databases. You might consider
inserting the results into a reporting/data-mart table and scheduling the
process to run off-hours. Not only will that that allow you to report from
a more optimized/specialized schema, it will allow you to keep historical
information too.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76e5a9a710d68@.uwe...
> Running SQL 2005, SP1.
> Any idea how to speed up the following query? It takes only a few seconds
> when cached, but since it is only run once a week, by the time it gets
> run,
> it is no longer in cache and takes over a minute.
> SELECT 'The name of my database' AS DBName,
> s.object_id,
> s.name AS TableName,
> i.index_id,
> i.name AS IndexName,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
> 'DETAILED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.index_level = 0 --this essentially makes the query return
> just
> Limited mode records, but with Detailed mode data
> GROUP BY s.object_id,
> s.name,
> i.index_id,
> i.name,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>

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