visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Languages :: Delphi :: Dephi Database Performace Improvement
By: rejith76 U.S.A.  Date: 03/06/2003 00:00:00  English  Points: 125 Status: Answered
Quality : Excellent
I am using Delphi 6 as Front end PostGRE as backend .
I am accessing database through ADO Objects.

When I excute this simple query it take a huge amount of time.

SELECT
slr_ord_itm.acl_id,
slr_ord_itm.pch_prc,
slr_ord_itm.ord_qty,
acl_mtr.sht_dsc,
acl_mtr.fre_wrh_stk,
acl_mtr.cmr_rsv_stk
FROM slr_ord_hdr
INNER JOIN slr_ord_itm ON slr_ord_hdr.slr_ord_id = slr_ord_itm.slr_ord_id
INNER JOIN acl_mtr ON slr_ord_itm.acl_id = acl_mtr.acl_id
WHERE slr_ord_hdr.slr_no = supplierNo ;

Could someone tell what are the steps to be taken for faster database access.
What could be done for better peformance ?
By: kretzschmar Date: 04/06/2003 05:16:00 English  Type : Comment
just create indexes on your id-fields

btw.
delphi is not the point to get more performance,
because the delphi-app itself waits for the result
of the db-server -> there you must start to get better performance

meikl ;-)
By: Workshop_Alex Date: 04/06/2003 18:04:00 English  Type : Answer
As meikl suggests, don't seek a way to optimize Delphi. Optimize the database instead. Add more indices, give more memory to the database server, use faster disks or rewrite the query in a more optimized way. Sometimes it also helps to do the inner joins in a different order. Try:
[bla]
INNER JOIN acl_mtr ON slr_ord_itm.acl_id = acl_mtr.acl_id
INNER JOIN slr_ord_itm ON slr_ord_hdr.slr_ord_id = slr_ord_itm.slr_ord_id
[bla,bla]
instead and see if that helps a bit.

If that doesn't help, go to <A HREF="http://www.experts-exchange.com/Databases/">http://www.experts-exchange.com/Databases/</a> and ask your question again, to the database experts.

And when using ADO, consider moving to MS SQL Server since that database is very optimized to work with ADO. I've done database stuff on all kinds of databases but with ADO I discovered that SQL Server performs the best. For other databases you might actually prefer to find a more native componentset for that database, like the IBComponents for InterBase...
By: VGR Date: 04/06/2003 19:08:00 English  Type : Assist
You may also try to use implicit JOIN :

SELECT
slr_ord_itm.acl_id,
slr_ord_itm.pch_prc,
slr_ord_itm.ord_qty,
acl_mtr.sht_dsc,
acl_mtr.fre_wrh_stk,
acl_mtr.cmr_rsv_stk
FROM slr_ord_hdr, slr_ord_itm, acl_mtr WHERE slr_ord_hdr.slr_ord_id = slr_ord_itm.slr_ord_id AND slr_ord_itm.acl_id = acl_mtr.acl_id AND slr_ord_hdr.slr_no = supplierNo ;


This let's the DB's particular query optimizer do its job.

If your DB has it, you may try to query (from program on on its console) :

EXPLAIN [query above]

to check which indices are used, how many rows are scanned and how, to get the results, etc

If you know you'll have a maximum of N (why not N=1 ? :D ) results maximum from the query, you may also speed the query a lot by providing a final clause "LIMIT N"

regards
By: Workshop_Alex Date: 04/06/2003 19:29:00 English  Type : Comment
VGR,

A query with a join might actually return a slightly bigger result set than the query you're suggesting... Yet in this case an inner join is used and that's similar to the theta style you suggested.
By: junior_reporter Date: 04/06/2003 23:11:00 English  Type : Comment
rejith76
For better performance.. u can create Clustered index on id keys. I am not sure.if clustered index is available in post gre.. If ids are primary key in your table .. then most database will create the clusetered index.

(A clustered index is particularly efficient on columns that are often searched for ranges of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent.)
have fun.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 345.514060 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page