| Subject | Re: [firebird-support] Best Practice in Use of Colon |
|---|---|
| Author | LtColRDSChauhan |
| Post date | 2014-01-21T13:42:55Z |
1. Grateful for your response please. Apologies for putting up my question ambiguously.
2. I'm appending two stored procedures, one omits colon (Para 3 below) and another uses colon (Para 4 below) for fetching exactly the same data. The FlameRobin's Statistics TAB output on running the procedures (Firebird 3 on Windows 7 32 Bit) is listed as under for both the procedures, please notice the difference (omitting colon appears to be lowering performance) :
OMITS COLON:- USES COLON:-
Executing statement... Executing statement...
Statement executed (elapsed time: 0.000s). Statement executed (elapsed time: 0.000s).
16541 fetches, 2 marks, 5 reads, 2 writes. 760 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 72 index, 8064 seq. 0 inserts, 0 updates, 0 deletes, 71 index, 264 seq.
Delta memory: 170752 bytes. Delta memory: 126824 bytes.
Total execution time: 0.174s Total execution time: 0.049s
Script execution finished. Script execution finished.
3. Stored Procedure With Colon Omitted in the output parameters :-
SET TERM ^ ;
ALTER PROCEDURE SP_PATIENTREGISTRATION
RETURNS (
REGDID bigint,
BILLNO bigint,
INFOID bigint,
HOSPSRVCID smallint,
REFBYDRID integer,
CHIEFCONSULTANTID integer,
COMPANY varchar(80),
COMPANYEMPLOYEEID varchar(80),
HEALTHCHKUP varchar(80),
MLCINFORMANT varchar(80),
DISCHSUMBYTES integer,
MLCPOLICEIDNO varchar(80),
DISCHTYPE varchar(80),
DISCHSUMMARY blob sub_type 0,
IPDNO bigint,
TIMEADMIT time,
ROOM varchar(80),
BED varchar(80),
NAMERELATIVE varchar(80),
CONTACTNORELATIVE varchar(80),
LIKEDISCHARGEDATE timestamp,
DISCHARGEDATE timestamp,
REMARKS varchar(80) )
AS
BEGIN
FOR SELECT
a.REGDID,
a.BILLNO,
a.INFOID,
a.HOSPSRVCID,
a.REFBYDRID,
a.CHIEFCONSULTANTID,
a.COMPANY,
a.COMPANYEMPLOYEEID,
a.HEALTHCHKUP,
a.MLCINFORMANT,
a.DISCHSUMBYTES,
a.MLCPOLICEIDNO,
a.DISCHTYPE,
a.DISCHSUMMARY,
a.IPDNO,
a.TIMEADMIT,
a.ROOM,
a.BED,
a.NAMERELATIVE,
a.CONTACTNORELATIVE,
a.LIKEDISCHARGEDATE,
a.DISCHARGEDATE,
a.REMARKS
FROM PATIENTREGISTRATION a
INTO
REGDID,
BILLNO,
INFOID,
HOSPSRVCID,
REFBYDRID,
CHIEFCONSULTANTID,
COMPANY,
COMPANYEMPLOYEEID,
HEALTHCHKUP,
MLCINFORMANT,
DISCHSUMBYTES,
MLCPOLICEIDNO,
DISCHTYPE,
DISCHSUMMARY,
IPDNO,
TIMEADMIT,
ROOM,
BED,
NAMERELATIVE,
CONTACTNORELATIVE,
LIKEDISCHARGEDATE,
DISCHARGEDATE,
REMARKS
DO
BEGIN
SUSPEND;
END
END^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE SP_PATIENTREGISTRATION TO SYSDBA WITH GRANT OPTION;
4. Stored Procedure With Colon used in the output parameters :-
SET TERM ^ ;
ALTER PROCEDURE SP_PATIENTREGISTRATION_1
RETURNS (
REGDID bigint,
BILLNO bigint,
INFOID bigint,
HOSPSRVCID smallint,
REFBYDRID integer,
CHIEFCONSULTANTID integer,
COMPANY varchar(80),
COMPANYEMPLOYEEID varchar(80),
HEALTHCHKUP varchar(80),
MLCINFORMANT varchar(80),
DISCHSUMBYTES integer,
MLCPOLICEIDNO varchar(80),
DISCHTYPE varchar(80),
DISCHSUMMARY blob sub_type 0,
IPDNO bigint,
TIMEADMIT time,
ROOM varchar(80),
BED varchar(80),
NAMERELATIVE varchar(80),
CONTACTNORELATIVE varchar(80),
LIKEDISCHARGEDATE timestamp,
DISCHARGEDATE timestamp,
REMARKS varchar(80) )
AS
BEGIN
FOR SELECT
a.REGDID,
a.BILLNO,
a.INFOID,
a.HOSPSRVCID,
a.REFBYDRID,
a.CHIEFCONSULTANTID,
a.COMPANY,
a.COMPANYEMPLOYEEID,
a.HEALTHCHKUP,
a.MLCINFORMANT,
a.DISCHSUMBYTES,
a.MLCPOLICEIDNO,
a.DISCHTYPE,
a.DISCHSUMMARY,
a.IPDNO,
a.TIMEADMIT,
a.ROOM,
a.BED,
a.NAMERELATIVE,
a.CONTACTNORELATIVE,
a.LIKEDISCHARGEDATE,
a.DISCHARGEDATE,
a.REMARKS
FROM PATIENTREGISTRATION a
INTO
:REGDID,
:BILLNO,
:INFOID,
:HOSPSRVCID,
:REFBYDRID,
:CHIEFCONSULTANTID,
:COMPANY,
:COMPANYEMPLOYEEID,
:HEALTHCHKUP,
:MLCINFORMANT,
:DISCHSUMBYTES,
:MLCPOLICEIDNO,
:DISCHTYPE,
:DISCHSUMMARY,
:IPDNO,
:TIMEADMIT,
:ROOM,
:BED,
:NAMERELATIVE,
:CONTACTNORELATIVE,
:LIKEDISCHARGEDATE,
:DISCHARGEDATE,
:REMARKS
DO
BEGIN
SUSPEND;
END
END^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE SP_PATIENTREGISTRATION_1 TO SYSDBA WITH GRANT OPTION;
Thanks & Regards,
Rajiv
On Tue, Jan 21, 2014 at 3:55 PM, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>Where the use of : is optional, as best practice should : be used or avoided.Hi!
>Or in such cases use of : is irrelevant in terms of performance.
I think your question can be interpreted a couple of ways. If the question is "should I use a parameter or a constant in a query", then there's no big difference. However, using a parameter has two potentially important advantages: If a query will be executed several times, then it is much quicker to prepare a query once and just change the parameter(s) for each execution than to execute several different, but similar queries. Moreover, a query with parameters are not prone to sql injection attacks the same way as a dynamically created query.
If the question is "should I use parameters or separate queries if a field may or may not be part of the WHERE clause", then there's no set answer, using it like
SELECT ...
FROM ...
WHERE FieldName = COALESCE(:Parameter, FieldName)
means that no index can be used for FieldName, which is only OK on small tables or if there are other parts of the WHERE clause that can utilise indexes to limit the result set.
Please rephrase Your question if it is different from both ways I've interpreted it or something is still unclear.
HTH,
Set--Regards,Lt Col (Retd) Rajiv Dular Singh Chauhan9890900422_____________________________