Schroeder, Alexander a écrit :
Could you tell us what you've done in the DB Procedure to fetch the rows? Or is
it a simple
procedure that returns a result set?
Regards
Alexander Schröder
SAP DB, SAP Labs Berlin
-----Original Message-----
From: Xavier CAMIER [mailto:[EMAIL PROTECTED]
Sent: Montag, 12. September 2005 10:54
To: maxdb@lists.mysql.com
Subject: SELECT from a DBPROC or from Code ?
Good morning,
In my attempts to increase the data transfert speed between the server
and the client apps I realised that filling a grid with a huge amount of
rows was quicker when calling a SELECT statement from code than calling
a Stored Proc containing that SELECT statement. Both tests were maid
with the same server, a delphi 7 pro coded app communicating with
x_server through the ADO and ODBC stacks. I don't think such a
difference would come from the time needed to prepare the stored proc.
Does anybody have any idea about that ?
Kind regards
Xavier
Here are some more informations :
Here is how my app works :
- there's a view which "prepares" the datas I need.
- there's a form with a grid and 3 combo boxes used to filter datas
through IDBIBLIO, IDFAMILLE, IDSOUSFAMILLE.
- when lauching the app the whole content of the COMPOSANTS_VLISTE view
is displayed. Even without any filter there's a real speed difference
between a stored proc and a programatically built query.
- when selecting an item into the combos a data refresh is called :
* with the query : the parameter is dynamically added at the end of
the query. The order by clause too. The app then queries the database
with that programmatically built string.
* with the stored Proc : a test is executed according to 3
parameters to choose the query to execute. Whatever the case the query
exectuted is not different from the programatically built query.
Some more informations :
- The DBProc execution time and the query execution time can't explain
the observed speed difference.
- I use a TADODataSet component in the delphi code to query the
database. I don't think it is the bottleneck as it is able to execute
either a query other a Stored Proc. In think most of data fetching
mechanism are shared by both modes.
/* the
CREATE VIEW "DBA"."COMPOSANTS_VLISTE"
(
"IDELT",
"ETAT",
"CODEELT",
"TITREELT",
"PRIXACHAT",
"DATEVALID",
"ABREV",
"IDBIBLIO",
"IDFAMILLE",
"IDSOUSFAMILLE",
"IDUTILISATEUR",
"IDX_TITRE",
"IDX_DESCR"
)
AS SELECT "DBA"."ELEMENTS"."IDELT",
"DBA"."ELEMENTS"."ETAT","DBA"."ELEMENTS"."CODEELT",
"DBA"."ELEMENTS"."TITREELT", "DBA"."COMPOSANTS"."PRIXACHAT",
"DBA"."COMPOSANTS"."DATEVALID", "DBA"."UNITES".CODE,
"DBA"."ELEMENTS"."IDBIBLIO", "DBA"."ELEMENTS"."IDFAMILLE",
"DBA"."ELEMENTS"."IDSOUSFAMILLE","DBA"."BIBLIOTHEQUES"."IDUTILISATEUR",
MAPCHAR(TITREELT) AS IDX_TITRE, MAPCHAR(DESCELT) AS IDX_DESCR FROM
"DBA"."COMPOSANTS","DBA"."ELEMENTS","DBA"."UNITES",
"DBA"."BIBLIOTHEQUES" WHERE "DBA"."ELEMENTS"."MES"= FALSE AND
"DBA"."ELEMENTS"."IDELT"= "DBA"."COMPOSANTS"."IDELT" AND
"DBA"."ELEMENTS"."IDBIBLIO"="DBA"."BIBLIOTHEQUES"."IDBIBLIOTHEQUE" AND
"DBA"."COMPOSANTS"."IDUNITEVENTE"= "DBA"."UNITES"."IDUNITE"
the query main body
'SELECT IDELT, ETAT, CODEELT, TITREELT, ABREV, PRIXACHAT, DATEVALID,
IDUTILISATEUR,IDX_TITRE, IDX_DESCR FROM DBA.COMPOSANTS_VLISTE'
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
