 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Neuza Alves
Guest
|
 Posted: Mon Aug 18, 2003 4:58 pm Post subject: How to improve this query? |
 |
|
Hi,
select A.* ,B.GPE_DESCR,C.SGE_DESCR,D.* from EITEMBASE A ,EGRUPO B,ESUBGRUPO C ,EITEMBASECARAC D where A.GPE_ID=B.GPE_ID and C.SGE_ID=A.SGE_ID and D.ITB_ID=A.ITB_ID
order by GPE_DESCR,SGE_DESCR,ITB_DESCR
This is query was made to be used with a report.
As a former clipper programmer,i'm not used to some concepts or
i really think that books in Brazil does not care too much with performance.
Let'suppose that the table EITEMBASE have 200.000 records.
that will make the server sent to the client 200.000 times the
columns b.GPE_dESCR and C.SGE_DESCR although this colums have
only five possible values.
Wouldn't it improve the performance if I returned a cursor
without this two columns,and return two auxiliar tables b and c.
In my program create a begin end, and everytime the fields
GPE_ID and SGE_ID changes in the table EITEMBASE I use locate
to look for the values of GPE_DESCR AND SGE_DESCR.
Basically the old clipper/dbf approach.
What is the best thing to do?
Kind Regards,
Neuza
|
|
| Back to top |
|
 |
Eric Hill
Guest
|
| Posted: Mon Aug 18, 2003 5:28 pm Post subject: Re: How to improve this query? |
|
|
I'll assume your master table has an integer key with an FK relationship to
one of the lookup tables. If an integer column takes 4 bytes to transfer,
and your lookup columns have a 30 byte character string (description or
something) then over 200,000 records the total difference in size of data
transferred is roughly 5.2MB. Over a modem, this may be substancial, but
over a WAN link it's not very large, especially considering you're already
transferring 200,000 records over the line anyway.
If you really want to trim the 5MB off the transfer, you could cache the
lookup tables locally and SELECT the integer field from the database, then
combine the two in memory on the client for the report engine.
You may also want to look into compression from the server to the client as
the duplicated data will compress *very* well.
Eric
|
|
| Back to top |
|
|
Emerson A. Carneiro
Guest
|
| Posted: Mon Aug 18, 2003 6:18 pm Post subject: Re: How to improve this query? |
|
|
Hi, Neuza
Well, I'm sending some tips:
1. Indent your query (it can help you to better visualization - see in
"Courier New" font)
SELECT A.*, B.GPE_DESCR, C.SGE_DESCR, D.*
FROM EITEMBASE A, EGRUPO B, ESUBGRUPO C, EITEMBASECARAC D
WHERE B.GPE_ID = A.GPE_ID
AND C.SGE_ID = A.SGE_ID
AND D.ITB_ID = A.ITB_ID
ORDER BY B.GPE_DESCR, C.SGE_DESCR, ITB_DESCR
2. See the "WHERE" clause adjust. Believe it: it's make difference (in long
and complex queries - and some databases).
3. Try to specify the "A." and "D." fields, only the really needed. This
will be help to reduce your network traffic.
4. The used component to display these data, it's very important. If you use
TListView, think about your replacement (see http://www.delphi-gems.com, and
search for TVirtualStringTree). If you use TDBGrid, try other more efficient
(see http://www.devexpress.com, and search for Developer Express Quantum
Grid).
5. Try to write code for manual load, by user action (TButton). If you use
one TComboBox component, the event OnChange will triggered after each
selection, and it's can't be desired. Then, insert one TButton with code
wich load these data and leave the user manage this action. It's only one
possibility !
Emerson A. Carneiro
Free Team Delphi
http://www.fteam.com
| Quote: |
Hi,
select A.* ,B.GPE_DESCR,C.SGE_DESCR,D.* from EITEMBASE A ,EGRUPO
B,ESUBGRUPO C ,EITEMBASECARAC D where A.GPE_ID=B.GPE_ID and |
C.SGE_ID=A.SGE_ID and D.ITB_ID=A.ITB_ID
| Quote: | order by GPE_DESCR,SGE_DESCR,ITB_DESCR
This is query was made to be used with a report.
As a former clipper programmer,i'm not used to some concepts or
i really think that books in Brazil does not care too much with
performance.
Let'suppose that the table EITEMBASE have 200.000 records.
that will make the server sent to the client 200.000 times the
columns b.GPE_dESCR and C.SGE_DESCR although this colums have
only five possible values.
Wouldn't it improve the performance if I returned a cursor
without this two columns,and return two auxiliar tables b and c.
In my program create a begin end, and everytime the fields
GPE_ID and SGE_ID changes in the table EITEMBASE I use locate
to look for the values of GPE_DESCR AND SGE_DESCR.
Basically the old clipper/dbf approach.
What is the best thing to do?
Kind Regards,
Neuza
|
|
|
| Back to top |
|
|
Neuza Alves
Guest
|
| Posted: Mon Aug 18, 2003 7:54 pm Post subject: Re: How to improve this query? |
|
|
"Eric Hill" <eric (AT) ijack (DOT) net> wrote:
| Quote: | I'll assume your master table has an integer key with an FK relationship to
Yes,It does. |
| Quote: | If you really want to trim the 5MB off the transfer, you could cache the lookup tables locally and SELECT the integer field from the database, then combine the two in memory on the client for the report engine.
using an SQL command or a begin.. end code like i suggested? |
With a begin... end I'll still have the advantage of using less RAM memory,But don't know if it pays the extra efforts.
| Quote: | You may also want to look into compression from the server to the client as the duplicated data will compress *very* well.
Do you suggest any component that does this? |
Thank you for your answer.
Regards,
Neuza
|
|
| Back to top |
|
|
Neuza Alves
Guest
|
| Posted: Mon Aug 18, 2003 8:17 pm Post subject: Re: How to improve this query? |
|
|
Hi,
"Emerson A. Carneiro" <e.carneiro (AT) fteam (DOT) com> wrote:
| Quote: | 1. Indent your query (
Thank you for the tip.
2. See the "WHERE" clause adjust. Believe it: it's make difference (in long and complex queries - and some databases).
You mean A. always coming after the = sign? |
| Quote: | 3. Try to specify the "A." and "D." fields, only the really needed. This will be help to reduce your network traffic.
I know that.The only column that is 'over'is the primary key of the EITEMBASECARAC table,i used * here just to make the query |
a little bit clear for me to explain.
Kind Regards,
Neuza
|
|
| Back to top |
|
|
Eric Hill
Guest
|
| Posted: Mon Aug 18, 2003 8:43 pm Post subject: Re: How to improve this query? |
|
|
| Quote: | using an SQL command or a begin.. end code like i suggested?
With a begin... end I'll still have the advantage of using less RAM
memory,But don't know if it pays the extra efforts. |
Depends on the database. For complete database independence, it will be
more than one call. Once to load the lookup tables, then once for the
actual data.
| Quote: | You may also want to look into compression from the server to the client
as the duplicated data will compress *very* well.
Do you suggest any component that does this?
|
If you use RO+DA from http://www.remobjects.com, you can set message
compression on a per-direction or per-message basis. Since you don't say
what component you're using, what database you're using, or what medium
you're using, I can't give you any more details.
Eric
|
|
| Back to top |
|
|
Neuza Alves
Guest
|
| Posted: Mon Aug 18, 2003 9:59 pm Post subject: Re: How to improve this query? |
|
|
Hi,
"Eric Hill" <eric (AT) ijack (DOT) net> wrote:
| Quote: | what component you're using, what database you're using, or what medium
you're using, I can't give you any more details.
Delphi 7,ADO,SQLSERVER 2000. |
Regards,
Neuza
|
|
| Back to top |
|
|
Ping Kam
Guest
|
| Posted: Mon Aug 18, 2003 11:08 pm Post subject: Re: How to improve this query? |
|
|
"Neuza Alves" <neuzaalves (AT) bol (DOT) com.br> wrote
| Quote: | If you really want to trim the 5MB off the transfer, you could cache the
lookup tables locally and SELECT the integer field from the database, then |
combine the two in memory on the client for the report engine.
| Quote: |
using an SQL command or a begin.. end code like i suggested?
With a begin... end I'll still have the advantage of using less RAM
memory,But don't know if it pays the extra efforts.
You can use local lookup. Cache the lookup table into a ClientDataset. |
Then add lookup fields to your main query component. If you bring up the
field editor and choose 'New field', you will have three choices. Choose
Lookup and enter values into the four boxes and there you go.
In your report, you display the lookup field instead of your key field.
HTH,
Ping Kam
|
|
| Back to top |
|
|
Eric Hill
Guest
|
| Posted: Tue Aug 19, 2003 2:15 am Post subject: Re: How to improve this query? |
|
|
| Quote: | what component you're using, what database you're using, or what medium
you're using, I can't give you any more details.
Delphi 7,ADO,SQLSERVER 2000.
|
In that case RO will help you with the DataSnap remoting, and you can set
compression to true on the message so data coming from the server is
compressed before transmit.
Also, do what Ping suggested and use a TClientDataSet to cache the lookups
locally. That will eliminate the ~5MB of data you didn't need to download.
Eric
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
