Query Performance

I have two tables.
Employee
EmployeeCode int Primary Key

Employee_Stock
EmployeeCode int
StockCode varchar(10)
Primay key on (Employeecode, StockCode)..

There is no foreign key relation between these 2 tables.
Now my question is which query give more performance. and why?
1. Select * from
Employee INNER JOIN Employee_Stock on Employee.Employeecode = Employee_Stock.EmployeeCode

2. Create a foreign Key between Employee and Employee_Stock for EmployeeCode. and run the same query.

Actually we forgot to put the foreign key relationship between these 2 tables and we have lot of queries joining them..
Now if we add foreignkey, is it going to improve the performance or not?

Thanks
RameshForeign keys enforce business rules and relational integrity, but in themselves do not increase performance. In some cases they may actually decrease performance while the database maintains relational integrity during inserts, updates, and deletes. You should still establish them to save you all sorts of headaches later when you find that your data does not make sense.

If you have already have indexes on your joined columns then I don't think your query is about as efficient as it is going to get. Show a query plan and verify that the indexes are being used.

Query performance

Hi
There are two tables TableA and TableB , where TableA consists of 100,000
rows and TableB consits of 60,000 rows.
In the TableA Id is the Primary Key (Clustered Index) and in the TableB
MessageId is the Foreign Key for TableA (Id), which is a non-Clustered Index.
When i Write a query by joining two tables which will give the rows that
satisfies some condition.
Ex: SELECT TableA.Id FROM TableA
INNER JOIN TableA.Id = TableB.MessageId
WHERE TableB.ResourceId = 12
This is taking more than a second to retrieve 1200 rows to get the matching
Ids with the specified criteria.
Cannot we decrease efficiency of the query ? what are the other options so
that the query performance is decreased.
maybe you can put in the tableA some of the most requested filters.
For example, add a resourceId column in tableA, this eliminate the join.
But before this, what is the principal usage of your database?
OLTP system? (so operational) or informational.
What response time do you expect?
I think your bottleneck is your processor, the tables are small. Look at the
statistics behind your queries to find what to do or change.
"Ramnadh" <Ramnadh@.discussions.microsoft.com> a crit dans le message de
news: C8D3EF70-DD5D-4C2E-B46F-DEADC291A8FC@.microsoft.com...
> Hi
> There are two tables TableA and TableB , where TableA consists of 100,000
> rows and TableB consits of 60,000 rows.
> In the TableA Id is the Primary Key (Clustered Index) and in the TableB
> MessageId is the Foreign Key for TableA (Id), which is a non-Clustered
> Index.
> When i Write a query by joining two tables which will give the rows that
> satisfies some condition.
> Ex: SELECT TableA.Id FROM TableA
> INNER JOIN TableA.Id = TableB.MessageId
> WHERE TableB.ResourceId = 12
> This is taking more than a second to retrieve 1200 rows to get the
> matching
> Ids with the specified criteria.
> Cannot we decrease efficiency of the query ? what are the other options so
> that the query performance is decreased.
|||Ramnadh,
The example you have here is not a valid query (it's an INNER JOIN
without an ON clause). In order to speculate on why a query is slower
than you like, it's important to see the exact query. Can you cut and
paste an example that you've tested in Query Analyzer, and if possible,
include the table definitions and indexes and constraints, as well?
Steve Kass
Drew University
Ramnadh wrote:

>Hi
>There are two tables TableA and TableB , where TableA consists of 100,000
>rows and TableB consits of 60,000 rows.
>In the TableA Id is the Primary Key (Clustered Index) and in the TableB
>MessageId is the Foreign Key for TableA (Id), which is a non-Clustered Index.
>When i Write a query by joining two tables which will give the rows that
>satisfies some condition.
>Ex: SELECT TableA.Id FROM TableA
> INNER JOIN TableA.Id = TableB.MessageId
> WHERE TableB.ResourceId = 12
>This is taking more than a second to retrieve 1200 rows to get the matching
>Ids with the specified criteria.
>Cannot we decrease efficiency of the query ? what are the other options so
>that the query performance is decreased.
>

QUERY performance

when I issue a join query between T1(87 rows) and a
subselect(x10^6 rows)
the optimizer estimation row count is only 1
the query has to scan the primary clustered indexes in
all the tables that partcipate in the subselect (no other
way).
I already rum update statistics on all the objects
involved without success
that causes the server to use T1 as the outher table in a
nested loop join which makes the query run for 10 hours
instead of 10 minutes
Any ideas?
Perhaps you could try updating the statistics with the FULLSCAN option?
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Amit" <amit.eshet@.intel.com> wrote in message
news:190f101c44c8e$c9932ff0$a401280a@.phx.gbl...
> when I issue a join query between T1(87 rows) and a
> subselect(x10^6 rows)
> the optimizer estimation row count is only 1
> the query has to scan the primary clustered indexes in
> all the tables that partcipate in the subselect (no other
> way).
> I already rum update statistics on all the objects
> involved without success
> that causes the server to use T1 as the outher table in a
> nested loop join which makes the query run for 10 hours
> instead of 10 minutes
> Any ideas?

Query performance

I have a query as follow

Select product.name, vendor.name from product join vendor on product.vendor_key = vendor_key

Vendor_key is the primary key on Vendor Table

If I want to increase the performance should I use Stored Procedure or create index for the vendor_key on Product Table

The first thing to check is that both tables have indices on the linking columns.

Vendor(Vendor_Key) is a PK, so it is automatically indexed.

Does Product(Vendor_Key) have an index?

|||Product(Vendor_key) has no index|||Performance should improve if you add an Index for Product(Vendor_Key).|||

A sproc will only help if you call the query repeatedly. Also, if you are calling it remotely with ADO and are using a bad cursor type.

It seems that you should have vendor.vendor_key as the last part of that query, right?

Also, if you are doing read-only activity, consider using (NOLOCK) hint for both tables, which provides a small performance gain and also improves concurency.

|||

Also, the index will really help the performance if it has product.name as an Included column.

As always, when creating secondary indexes, you should consider the added cost for insert/update/delete operations.

|||you means to add the Product(name) index or Product(name, vendor_key) index. Is it every time I use the join statement, then I will add the index on the foreign key column to increase the performance.

Query performance

Hi
There are two tables TableA and TableB , where TableA consists of 100,000
rows and TableB consits of 60,000 rows.
In the TableA Id is the Primary Key (Clustered Index) and in the TableB
MessageId is the Foreign Key for TableA (Id), which is a non-Clustered Index.
When i Write a query by joining two tables which will give the rows that
satisfies some condition.
Ex: SELECT TableA.Id FROM TableA
INNER JOIN TableA.Id = TableB.MessageId
WHERE TableB.ResourceId = 12
This is taking more than a second to retrieve 1200 rows to get the matching
Ids with the specified criteria.
Cannot we decrease efficiency of the query ? what are the other options so
that the query performance is decreased.maybe you can put in the tableA some of the most requested filters.
For example, add a resourceId column in tableA, this eliminate the join.
But before this, what is the principal usage of your database?
OLTP system? (so operational) or informational.
What response time do you expect?
I think your bottleneck is your processor, the tables are small. Look at the
statistics behind your queries to find what to do or change.
"Ramnadh" <Ramnadh@.discussions.microsoft.com> a écrit dans le message de
news: C8D3EF70-DD5D-4C2E-B46F-DEADC291A8FC@.microsoft.com...
> Hi
> There are two tables TableA and TableB , where TableA consists of 100,000
> rows and TableB consits of 60,000 rows.
> In the TableA Id is the Primary Key (Clustered Index) and in the TableB
> MessageId is the Foreign Key for TableA (Id), which is a non-Clustered
> Index.
> When i Write a query by joining two tables which will give the rows that
> satisfies some condition.
> Ex: SELECT TableA.Id FROM TableA
> INNER JOIN TableA.Id = TableB.MessageId
> WHERE TableB.ResourceId = 12
> This is taking more than a second to retrieve 1200 rows to get the
> matching
> Ids with the specified criteria.
> Cannot we decrease efficiency of the query ? what are the other options so
> that the query performance is decreased.|||Ramnadh,
The example you have here is not a valid query (it's an INNER JOIN
without an ON clause). In order to speculate on why a query is slower
than you like, it's important to see the exact query. Can you cut and
paste an example that you've tested in Query Analyzer, and if possible,
include the table definitions and indexes and constraints, as well?
Steve Kass
Drew University
Ramnadh wrote:
>Hi
>There are two tables TableA and TableB , where TableA consists of 100,000
>rows and TableB consits of 60,000 rows.
>In the TableA Id is the Primary Key (Clustered Index) and in the TableB
>MessageId is the Foreign Key for TableA (Id), which is a non-Clustered Index.
>When i Write a query by joining two tables which will give the rows that
>satisfies some condition.
>Ex: SELECT TableA.Id FROM TableA
> INNER JOIN TableA.Id = TableB.MessageId
> WHERE TableB.ResourceId = 12
>This is taking more than a second to retrieve 1200 rows to get the matching
>Ids with the specified criteria.
>Cannot we decrease efficiency of the query ? what are the other options so
>that the query performance is decreased.
>

QUERY performance

when I issue a join query between T1(87 rows) and a
subselect(x10^6 rows)
the optimizer estimation row count is only 1
the query has to scan the primary clustered indexes in
all the tables that partcipate in the subselect (no other
way).
I already rum update statistics on all the objects
involved without success
that causes the server to use T1 as the outher table in a
nested loop join which makes the query run for 10 hours
instead of 10 minutes
Any ideas?Perhaps you could try updating the statistics with the FULLSCAN option?
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Amit" <amit.eshet@.intel.com> wrote in message
news:190f101c44c8e$c9932ff0$a401280a@.phx
.gbl...
> when I issue a join query between T1(87 rows) and a
> subselect(x10^6 rows)
> the optimizer estimation row count is only 1
> the query has to scan the primary clustered indexes in
> all the tables that partcipate in the subselect (no other
> way).
> I already rum update statistics on all the objects
> involved without success
> that causes the server to use T1 as the outher table in a
> nested loop join which makes the query run for 10 hours
> instead of 10 minutes
> Any ideas?

query or stored procedure to insert values into pr. key field

Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

query or stored procedure to insert values into pr. key field

Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)
bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.
|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Query Optimize.

Hi all,

Table 'A' is having 105 fields & 233000 records.
One Clusterd Primary Key & 10 nonclusterd key.

If I joined with table 'A' or 'Select * from 'A' , Query takes more time so please let me know best way to structure query or table, Indexes etc.

Reply to me asap positivaly ...

Regards,
M. G.Without having the DDL (the CREATE TABLE and CREATE INDEX statements in particular), and the query(s) that are running slow, I'm pretty severly handicapped in giving you much advice.

The only real insight that I can give without more informations is to be sure that your statistics are current using the UPDATE STATISTICS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4vxn.asp) statement.

-PatP

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

query needed

Hi guys!

I need a query to remove all records from my database except the
latest 100 added. I use an id as the primary key. But those records
are also often removed manually depending on some other values.

So I can not simply use : delete * from db where id < last_id - 100
because the last id could be 348 while the 100th would be 124.

I hope somone understands what I mean and can help :)

Thank you

Yang
--[posted and mailed, please reply in news]

Yang Li Ke (yanglike@.sympatico.ca) writes:
> I need a query to remove all records from my database except the
> latest 100 added. I use an id as the primary key. But those records
> are also often removed manually depending on some other values.
> So I can not simply use : delete * from db where id < last_id - 100
> because the last id could be 348 while the 100th would be 124.
> I hope somone understands what I mean and can help :)

If you want people to understand what you mean, you need to provide:

o CREATE TABLE statment of the table.
o INSERT statements with sample data (and possibly also DELETE
statements in this case).
o The desired output.

With this information you can get a tested solution.

Without this information, you can get a untested guess. Here my stab:

DELETE tbl
WHERE id < (SELECT MIN(id)
FROM (SELECT TOP 100 id
FROM tbl
ORDER BY id DESC) x)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Yang,

I would try something like that. No need to say to test it before really
using it because I did not try it!

DELETE FROM db
WHERE id NOT IN (
SELECT TOP 100 id
FROM db
ORDER BY id DESC)

"Yang Li Ke" <yanglike@.sympatico.ca> wrote in message
news:TgJFb.4375$d%1.948103@.news20.bellglobal.com.. .
> Hi guys!
> I need a query to remove all records from my database except the
> latest 100 added. I use an id as the primary key. But those records
> are also often removed manually depending on some other values.
> So I can not simply use : delete * from db where id < last_id - 100
> because the last id could be 348 while the 100th would be 124.
> I hope somone understands what I mean and can help :)
> Thank you
> Yang
> --
>

Query needed

Hi All,
I have a table
Task(
idTask int primary key,
sTask varchar(60)
)
with the following data.
idTask sName
--
1 B
2 A
3 C
4 B
5 D
6 E
7 B
8 D
Now i want a query which return the data in the following format
2 A
1,4,7 B
3 C
5,8 D
TIA
Satyahttp://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Satya" wrote:

> Hi All,
> I have a table
> Task(
> idTask int primary key,
> sTask varchar(60)
> )
> with the following data.
> idTask sName
> --
> 1 B
> 2 A
> 3 C
> 4 B
> 5 D
> 6 E
> 7 B
> 8 D
> Now i want a query which return the data in the following format
> 2 A
> 1,4,7 B
> 3 C
> 5,8 D
> TIA
> Satya
>|||This is called violationof First Normal Form and good SQL programmers
do not do it. Display is handled in the front end in a tiered
architecture. This is more fundamental than just SQL, so you might
want to take a class in software engineering.|||No sql coding necessary with the RAC utility for S2k.
See the @.concatenate operator in the Help file.
RAC and QALite @.
www.rac4sql.net|||Yes this is not relational approach.
It should be handled in presentation layer.
I am missing basics :(
Thanks for the post
Satya
"--CELKO--" wrote:

> This is called violationof First Normal Form and good SQL programmers
> do not do it. Display is handled in the front end in a tiered
> architecture. This is more fundamental than just SQL, so you might
> want to take a class in software engineering.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1111762443.359407.260920@.f14g2000cwb.googlegroups.com...
>..........'good SQL programmers'
A contradiction in terms? What do you think C.Date would say about this:)