Hi Frank,
This might clear your doubt a bit The risk of using SQL connection is performance issues...
EFFECTS OF DIRECT SQL ON ARSYSTEM PERFORMANCE
______________________________________________
This document is intended to help an AR System administrator
tune the performance of an active link, filter, or menu
that is using Direct SQL access to a DB table. Before
reading this document, please refer to ARSystem Administrator's Guide
for the discussions of using Direct SQL in Active links,
filters, and menus.
This note discusses how the usage of direct SQL
may affect the performance of your ARSystem server.
In addition, it lists some of the elements that can change
the response time of direct SQL.
It is very critical to pay extra attention to the
performance of your Direct SQL calls because:
1. A direct SQL command runs on a fast server.
Having an operation that takes about 2-3 seconds for each
active link or filter would be a big performance degradation
on your arserver. A normal operation on a fast server takes
about 0.3-0.4 seconds. So, if you have an active link that runs
a direct SQL operation, and the active link/filter fires with each
submit/modify, then your submit/modify is going to be
9-10 times slower than a standard submit/modify.
2. A slow Direct SQL command can cause server time-outs.
The server has to wait until your Direct SQL operation
is done. Therefore, if the Direct SQL takes too long,
the server will time out and you may not receive the
result you expected to get from the Direct SQL.
WHAT AFFECTS DIRECT SQL?
1. Network.
If you are accessing an external DB which is remote
to your AR database, the performance of your Direct
SQL would be dependent on the performance on your
Network. To test that, the recommendation is to run
the same SQL statement on your SQLPLUS, ISQL, etc.
(depending on what database you are accessing), and see how
fast it would return the results. Make sure this test
is done on the machine on which arserver is running.
2. Efficient queries
You have to be very careful about the queries you use
and make sure they take the minimum possible time to
give you the results you want.
Here are some tips:
a. The field used in the WHERE clause must be
indexed.
b. Do not use != in the WHERE clause.
c. Do not use arithmetic operations in the WHERE clause.
d. Do not use "select *" if your table has many columns.
Just select the columns you need.
3. Stored procedures
Stored procedures by nature help performance on the SQL
engines. This is due to the fact that they are pre-compiled
and stored in the shared memory.
The performance hit that AR System might get from a stored
procedure is dependent on the operation that you programmed
the stored procedure to perform, not on the time taken for
compilation, loading, and retrieving it.
Therefore, make sure you measure the performance of your
stored procedure on SQLPLUS/ISQL before using it in AR System.
BENCHMARKS:
I Created 2 Active links:
#1 performs a setfield from a direct SQL query of
table T18 on and gets the field C8
which is short description. Although the
tables are from an ARSystem schema called ,
the operation was direct SQL to the table T18.
This operation does not use the arserver on the
remote machine.
#2 does a setfield from the same schema (schema
on ) and sets the short description on
the current schema to the short description from that
remote schema. This access is through arserver on
the remote machine.
Basically, both active links do exactly the
same thing. One goes through arserver, the
other goes directly with Direct SQL. They both
end up running the following SQL statement:
SELECT C1, C8 FROM T18 WHERE (C4 LIKE 'Mitra%') ORDER BY 1 ASC
Number of tickets on the schema: 50,000
C4's QBE is set to "Leading".
If the field C4 (Assigned-to) is not indexed,
Running the DSQL active link takes 13 seconds to return
results.
Running the AR active link takes 13 seconds as well.
After indexing the field C4, each active link
takes less than a second to return the results.
The trick here is that we don't need to use "ORDER BY 1 ASC"
at the end of the select statement when using it in the Direct SQL.
In that case, the query from Direct SQL takes much shorter time and
therefore, it gives a better performance.
Conclusion:
Running a query for an Active Link setfield through
Direct SQL takes the same amount of time as running
that operation through ARSystem. This is only true if
the exact same SQL command is used for both. In most
cases though you can come up with a more efficient SQL
command as it is more directed to your specific need. In
that case, you can get a better performance for your operation
in Direct SQL than you would from going through arserver.
The difference in the response time between when the
column is indexed and when it is not indexed shows how
critical it is to index a column which is being
used in a WHERE clause.
______________________________________________
This document is intended to help an AR System administrator
tune the performance of an active link, filter, or menu
that is using Direct SQL access to a DB table. Before
reading this document, please refer to ARSystem Administrator's Guide
for the discussions of using Direct SQL in Active links,
filters, and menus.
This note discusses how the usage of direct SQL
may affect the performance of your ARSystem server.
In addition, it lists some of the elements that can change
the response time of direct SQL.
It is very critical to pay extra attention to the
performance of your Direct SQL calls because:
1. A direct SQL command runs on a fast server.
Having an operation that takes about 2-3 seconds for each
active link or filter would be a big performance degradation
on your arserver. A normal operation on a fast server takes
about 0.3-0.4 seconds. So, if you have an active link that runs
a direct SQL operation, and the active link/filter fires with each
submit/modify, then your submit/modify is going to be
9-10 times slower than a standard submit/modify.
2. A slow Direct SQL command can cause server time-outs.
The server has to wait until your Direct SQL operation
is done. Therefore, if the Direct SQL takes too long,
the server will time out and you may not receive the
result you expected to get from the Direct SQL.
WHAT AFFECTS DIRECT SQL?
1. Network.
If you are accessing an external DB which is remote
to your AR database, the performance of your Direct
SQL would be dependent on the performance on your
Network. To test that, the recommendation is to run
the same SQL statement on your SQLPLUS, ISQL, etc.
(depending on what database you are accessing), and see how
fast it would return the results. Make sure this test
is done on the machine on which arserver is running.
2. Efficient queries
You have to be very careful about the queries you use
and make sure they take the minimum possible time to
give you the results you want.
Here are some tips:
a. The field used in the WHERE clause must be
indexed.
b. Do not use != in the WHERE clause.
c. Do not use arithmetic operations in the WHERE clause.
d. Do not use "select *" if your table has many columns.
Just select the columns you need.
3. Stored procedures
Stored procedures by nature help performance on the SQL
engines. This is due to the fact that they are pre-compiled
and stored in the shared memory.
The performance hit that AR System might get from a stored
procedure is dependent on the operation that you programmed
the stored procedure to perform, not on the time taken for
compilation, loading, and retrieving it.
Therefore, make sure you measure the performance of your
stored procedure on SQLPLUS/ISQL before using it in AR System.
BENCHMARKS:
I Created 2 Active links:
#1 performs a setfield from a direct SQL query of
table T18 on and gets the field C8
which is short description. Although the
tables are from an ARSystem schema called ,
the operation was direct SQL to the table T18.
This operation does not use the arserver on the
remote machine.
#2 does a setfield from the same schema (schema
on ) and sets the short description on
the current schema to the short description from that
remote schema. This access is through arserver on
the remote machine.
Basically, both active links do exactly the
same thing. One goes through arserver, the
other goes directly with Direct SQL. They both
end up running the following SQL statement:
SELECT C1, C8 FROM T18 WHERE (C4 LIKE 'Mitra%') ORDER BY 1 ASC
Number of tickets on the schema: 50,000
C4's QBE is set to "Leading".
If the field C4 (Assigned-to) is not indexed,
Running the DSQL active link takes 13 seconds to return
results.
Running the AR active link takes 13 seconds as well.
After indexing the field C4, each active link
takes less than a second to return the results.
The trick here is that we don't need to use "ORDER BY 1 ASC"
at the end of the select statement when using it in the Direct SQL.
In that case, the query from Direct SQL takes much shorter time and
therefore, it gives a better performance.
Conclusion:
Running a query for an Active Link setfield through
Direct SQL takes the same amount of time as running
that operation through ARSystem. This is only true if
the exact same SQL command is used for both. In most
cases though you can come up with a more efficient SQL
command as it is more directed to your specific need. In
that case, you can get a better performance for your operation
in Direct SQL than you would from going through arserver.
The difference in the response time between when the
column is indexed and when it is not indexed shows how
critical it is to index a column which is being
used in a WHERE clause.
Regards,
Roney Samuel Varghese
On 5/3/06, Frank Wall <[EMAIL PROTECTED]> wrote:
All,
I have a couple of questions for any one that has used MS SRS with Remedy.
1. Is there a way to have SRS to use a ARODBC connection?
2. If a ARODBC connection can not be used, what would be the risk if any
in using a SQL connection directly?
Thanks in advance for any input on questions I have.
Thanks,
Frank Wall
Administrator
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
__20060125_______________________This posting was submitted with HTML in it___
