Hi Peter, thanks for your interest...
We are using SQL 2000 with service pack 3. We have an index of 2 string
fields on the table. From your statements, I understand that the order of
index fields usage in where clauses should be in the same order in the index
definition?? I will check it out!!
-----ÃzgÃn Äleti-----
Kimden: Peter Friberg
[mailto:[EMAIL PROTECTED]
GÃnderilmiÅ: Ãar 03.03.2004 00:22
Kime: [EMAIL PROTECTED]
Bilgi:
Konu: SV: Table Performance
Hello ÃzgÃr,
I will try to help, allthough it would be easier, if we
could talk on a phone, since I have a couple of questions for
you:
Are you using a MS SQL or an Oracle database, and what
version?
A rule of thumb is of course that you use an Oracle
database, and that all
your search is done using
appropriate Index. I am currently supporting 3 different ERP systems (1 XAL
version 3.5, 1 AXAPTA version 2.1 and 1 AXAPTA version 2.5) Alle 3 ERP
systems have several million records in a single table for instance
StockTrans / InventTrans. All 3 ERP systems are running on an Oracle
database, and they all perform quite well, as long you remember to search
according to existing indexes, or make relevant indexes, if there is a
demand for better performance.
Of course, if you make a search, where there is no according
index, they all perform really bad!
//START CASE1
Existing Index:
Field1Field2IDX
Your search looks like this:
while select table
where
field1 == X
&& field2 ==
Y
...
This probably is
performing OK
//STOP CASE1
//START CASE2
Existing Index:
Field1Field2IDX
Your search looks like this:
while select table
where
field2 == Y
&& field1 ==
X
...
This is probably NOT
performing very well!
//STOP CASE2
Please feel free to respond to this mail/elaborate further
:-)
/Peter
-----Oprindelig meddelelse-----
Fra: ÃzgÃr Genà [mailto:[EMAIL PROTECTED]]
Sendt: ti 02-03-2004 17:13
Til: [EMAIL PROTECTED]
Cc:
Emne: Table Performance
Hi,
In our ERP project we have a table with approximately 2-3
milyon records. Upto now we had performance problems with this table both in
forms and in X++ sql statements. Instead of using X++ basic sql functions,
we rather used Connection and Statement objects (ADO) and it worked. Do u
know any other techniques in order to increase table sql performance, are
they anyone having same kind of table performance problems and solved it
anyway. Thanks a lot..
ÃzgÃr Genà / Veripark
