I have two tables that stores company information in a database. Since
retrieving data from the tables is too slow, the structure need to be
improved. The current structure of the tables is as follows:
First table contains
table1
companyNo : unique field that is created by sequence
companyTaxNumber:have a unique index
companyName : have an index, but not unique
companyAddress
companyCountry
timestamp
trigger on table1:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
Second Table Contains
table2
Up_companyNo2 : unique field that is created by sequence
Up_companyTaxNumber2:have a unique index
Up_companyName2 : have an index, but not unique
Up_companyAddress2
Up_companyCountry2
timestamp
trigger on table2:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber 2
and timestamp=1
the problem is the application uses table1 details, and give reference to
"companyTaxNumber" in forms. When the company information is updated (rows
are not updated, inserted with a new companyNo via sequence) and inserted
into table1 from table2, the new forms will use the updated information.
However, old forms cannot use updated ones, since reports cannot be changed.
in conclusion,
table2 contains last updated company information, the initial information
before updates are not stored in table2
table 1 contains all the updates as new entries since forms that use these
entries cannot change the references. So, table1 gets bigger and datas
retrieved slowly.
Can you give me clear advices about the improvement techniques about indexes
and triggers? How can I handle this situation?
It is still not clear as to the logical seperation or business uses between
the two tables. Some clarification would be helpful.
Nevertheless, in both tables, I would use a UNIQUE CONSTRAINT for the
companyTaxNumber and Up_companyTaxNumber2 attributes instead of just a
UNIQUE INDEX. Now, all SQL Server tables should have a CLUSTERED INDEX
defined. Normally, I see users choose these IDENTITY surrogate primary
keys, or none at all. Both are bad choices. You have three candidates, or
a combination, that would server you better and, perhaps, assist you in your
querying issues: companyTaxNumber (my choice), companyName, or timestamp.
The other thing I noticed was your use of triggers. I would be careful
about this; I think I understand why you went this route instead of the
timestamp datatype, but it is serving the same purpose, just at the record
instead of the table level. That being said, you need to be careful when
using triggers because they execute T-SQL statements, that is, they are
SET-BASED, NOT ROW-BASED. A trigger is fired--ONECE--whenever an indicated
CRUD statement type is executed regardless of the number of rows impacted.
This means that your INSERTED and/or DELETED tables could be NULL, 1, or
many records with each trigger exectution. DO NOT ASSUME 1 RECORD.
In the trigger on Table2, the timestamp field is ambiguous as you are
joining Table1 and Table2, each with its own timestamp fields.
With the number of fields you have, and if you add the Clustered Index to
the correct attribute, I could see these sets of tables being able to
service several millions of records each with negligble query reporting
duration, dependent on your hardware, of course.
Are you joining these tables to others in your queries that would be
affecting your performance? Could you provide an example of a badly
performing query with the associated shcemea of any referenced tables.
Hope this helps.
Sincerely,
Anthony Thomas
"Sinem" <Sinem@.discussions.microsoft.com> wrote in message
news:2095306F-37BF-43F4-9ECC-C873E71ACAEF@.microsoft.com...
I have two tables that stores company information in a database. Since
retrieving data from the tables is too slow, the structure need to be
improved. The current structure of the tables is as follows:
First table contains
table1
companyNo : unique field that is created by sequence
companyTaxNumber:have a unique index
companyName : have an index, but not unique
companyAddress
companyCountry
timestamp
trigger on table1:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
Second Table Contains
table2
Up_companyNo2 : unique field that is created by sequence
Up_companyTaxNumber2:have a unique index
Up_companyName2 : have an index, but not unique
Up_companyAddress2
Up_companyCountry2
timestamp
trigger on table2:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber 2
and timestamp=1
the problem is the application uses table1 details, and give reference to
"companyTaxNumber" in forms. When the company information is updated (rows
are not updated, inserted with a new companyNo via sequence) and inserted
into table1 from table2, the new forms will use the updated information.
However, old forms cannot use updated ones, since reports cannot be changed.
in conclusion,
table2 contains last updated company information, the initial information
before updates are not stored in table2
table 1 contains all the updates as new entries since forms that use these
entries cannot change the references. So, table1 gets bigger and datas
retrieved slowly.
Can you give me clear advices about the improvement techniques about indexes
and triggers? How can I handle this situation?
|||Hi,
As I mentioned in the problem, I have two tables called table1 and table2. In
the application, there are numerous references in the fields belong to
table1,
while creating a report. After a report have been created, no changes can be
done on the reports. However, details on table1 can be changed and updated
occasionally. On each change, a new row inserted into table1. Old rows never
changes. By the way, while viewing old reports, references are not lost. This
results in decrease of the performance. Since table1 gets larger.
There is a problem with performance not the query. The real problem is how to
design these two tables in oracle database and find an appropriate solution?
Thanks for your convenience..
I'll post a piece of code in an hour..
sozmen
"AnthonyThomas" wrote:
> It is still not clear as to the logical seperation or business uses between
> the two tables. Some clarification would be helpful.
> Nevertheless, in both tables, I would use a UNIQUE CONSTRAINT for the
> companyTaxNumber and Up_companyTaxNumber2 attributes instead of just a
> UNIQUE INDEX. Now, all SQL Server tables should have a CLUSTERED INDEX
> defined. Normally, I see users choose these IDENTITY surrogate primary
> keys, or none at all. Both are bad choices. You have three candidates, or
> a combination, that would server you better and, perhaps, assist you in your
> querying issues: companyTaxNumber (my choice), companyName, or timestamp.
> The other thing I noticed was your use of triggers. I would be careful
> about this; I think I understand why you went this route instead of the
> timestamp datatype, but it is serving the same purpose, just at the record
> instead of the table level. That being said, you need to be careful when
> using triggers because they execute T-SQL statements, that is, they are
> SET-BASED, NOT ROW-BASED. A trigger is fired--ONECE--whenever an indicated
> CRUD statement type is executed regardless of the number of rows impacted.
> This means that your INSERTED and/or DELETED tables could be NULL, 1, or
> many records with each trigger exectution. DO NOT ASSUME 1 RECORD.
> In the trigger on Table2, the timestamp field is ambiguous as you are
> joining Table1 and Table2, each with its own timestamp fields.
> With the number of fields you have, and if you add the Clustered Index to
> the correct attribute, I could see these sets of tables being able to
> service several millions of records each with negligble query reporting
> duration, dependent on your hardware, of course.
> Are you joining these tables to others in your queries that would be
> affecting your performance? Could you provide an example of a badly
> performing query with the associated shcemea of any referenced tables.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Sinem" <Sinem@.discussions.microsoft.com> wrote in message
> news:2095306F-37BF-43F4-9ECC-C873E71ACAEF@.microsoft.com...
> I have two tables that stores company information in a database. Since
> retrieving data from the tables is too slow, the structure need to be
> improved. The current structure of the tables is as follows:
> First table contains
> table1
> companyNo : unique field that is created by sequence
> companyTaxNumber:have a unique index
> companyName : have an index, but not unique
> companyAddress
> companyCountry
> timestamp
> trigger on table1:
> if insertion occurs: timestamp:0
> if update occurs : increment timestamp by 1
> Second Table Contains
> table2
> Up_companyNo2 : unique field that is created by sequence
> Up_companyTaxNumber2:have a unique index
> Up_companyName2 : have an index, but not unique
> Up_companyAddress2
> Up_companyCountry2
> timestamp
> trigger on table2:
> if insertion occurs: timestamp:0
> if update occurs : increment timestamp by 1
> update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber 2
> and timestamp=1
> the problem is the application uses table1 details, and give reference to
> "companyTaxNumber" in forms. When the company information is updated (rows
> are not updated, inserted with a new companyNo via sequence) and inserted
> into table1 from table2, the new forms will use the updated information.
> However, old forms cannot use updated ones, since reports cannot be changed.
> in conclusion,
> table2 contains last updated company information, the initial information
> before updates are not stored in table2
> table 1 contains all the updates as new entries since forms that use these
> entries cannot change the references. So, table1 gets bigger and datas
> retrieved slowly.
>
> Can you give me clear advices about the improvement techniques about indexes
> and triggers? How can I handle this situation?
>
>
