Performance problem

Hello there
I have two tables with combination of two fields that aren't unique in both
tables.
To make them unique i must, create view of the last id of the fields.
example SELECT Fld1, Fld2, max(id)
FROM tbl
GROUP BY Fld1, Fld2
This cause serious performace problem.
Is there a way to handle it?1)How much of a performance problem?
2)can you send details of indexes implemented?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u8JN8LSYGHA.3604@.TK2MSFTNGP02.phx.gbl...
> Hello there
> I have two tables with combination of two fields that aren't unique in
both
> tables.
> To make them unique i must, create view of the last id of the fields.
> example SELECT Fld1, Fld2, max(id)
> FROM tbl
> GROUP BY Fld1, Fld2
> This cause serious performace problem.
> Is there a way to handle it?
>|||Whell Jack:
1. according to execution plan it caust at least 10 subtree cost more. and
on data which is more then 1,000,000 records any query can take more then an
hour.
2. fld1, and fld2 are indexes as non clustered.
What i need to do?
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:Beydna5nIMCvcdzZnZ2dnUVZ8qWdnZ2d@.bt
.com...
> 1)How much of a performance problem?
> 2)can you send details of indexes implemented?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:u8JN8LSYGHA.3604@.TK2MSFTNGP02.phx.gbl...
> both
>|||Hi Roy
Do you have an index on [ID] column?
I suggest you to create a COVERING index as
CREATE INDEX Covering_idx on TableName(Fld1, Fld2, id) which may very useful
particular along with ORDER BY clause
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ug0QbaSYGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Whell Jack:
> 1. according to execution plan it caust at least 10 subtree cost more. and
> on data which is more then 1,000,000 records any query can take more then
> an hour.
> 2. fld1, and fld2 are indexes as non clustered.
> What i need to do?
> "Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
> news:Beydna5nIMCvcdzZnZ2dnUVZ8qWdnZ2d@.bt
.com...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> I have two tables with combination of two fields that aren't unique in
> both tables.
> To make them unique i must, create view of the last id of the fields.
> example SELECT Fld1, Fld2, max(id)
> FROM tbl
> GROUP BY Fld1, Fld2
> This cause serious performace problem.
> Is there a way to handle it?
Maybe there is, but with the tiny amount of information you have posted,
it is impossible to give any useful advice.
I suggest that you post:
1) CREATE TABLE statements for you tables.
2) CREATE INDEX statements for the tables.
3) CREATE VIEW statement for your view.
4) A sample query from the view.
5) An indication of the number of rows in the table.
Output from SET STATISTICS PROFILE ON is also good.
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|||That you need to do is redesign your tables to have a primary key. If
you can't alter this schema, then consider implementing a reporting
database that has a correct schema and design a process to move data
from the original source to your reporting warehouse.
Performance may be an issue, but if you're only moving data
periodically, that issue is mitigated. As others have posted, a
clearer definition would be helpful.
HTH,
Stu|||Yes Uri. The ID is the primary key clustered index.
I have also indexes on Fld1 and Fld2
Whay this is not enouth?
"Uri Dimant" <test@.test.com> wrote in message
news:Od9nH6SYGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Hi Roy
> Do you have an index on [ID] column?
> I suggest you to create a COVERING index as
> CREATE INDEX Covering_idx on TableName(Fld1, Fld2, id) which may very
> useful particular along with ORDER BY clause
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ug0QbaSYGHA.3328@.TK2MSFTNGP02.phx.gbl...
>