Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread query with params vs views vs sp's
Mon, Feb 18 2008 5:43 AMPermanent Link

"Harry de Boer"
LS

I guess that an EDBQuery with a parameter:

SELECT camp.Id_Campagne, camp.Naam_Campagne
FROM camp
JOIN cage ON camp.Id_Campagne = cage.Id_Campagne
WHERE cage.Id_Gebruiker = :g

Could also be rewritten as a View (and then using an EDBtable with a filter)

or as a

Stored Procedure (EDBStoredProc) with parameters.

What would be the best way to do it (pro's/con's in terms of ease of use,
maintainabily, performance)?

Regards, Harry

Mon, Feb 18 2008 3:42 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Harry,

<< What would be the best way to do it (pro's/con's in terms of ease of use,
maintainabily, performance)? >>

The stored procedure would be the least amount of trips to the ElevateDB
Server, if using C/S access.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Feb 19 2008 3:21 AMPermanent Link

"Harry de Boer"
Tim,

Thanks. Must -or can- you also prepare a statement in a SP, or is it not
necessary? Furthetmore, could you give me an example of how to do my
statement in a SP (this because my inexperience with SP writing), if
possible?

Regards, Harry


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:30DAAD2C-F731-443C-B85D-FEBBC81762A1@news.elevatesoft.com...
> Harry,
>
> << What would be the best way to do it (pro's/con's in terms of ease of
use,
> maintainabily, performance)? >>
>
> The stored procedure would be the least amount of trips to the ElevateDB
> Server, if using C/S access.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Tue, Feb 19 2008 9:50 AMPermanent Link

"Harry de Boer"
Tim,

Diving into the SP world...
The stored procedure below is working now. At first I also did an UNPREPARE
but then there are never results. Is this correct? Don't you use UNPREPARE
ever in these situations? If you use this sp a lot, must you prepare it
every time then? Is this the right approach or is there a better one?

BEGIN
DECLARE cur CURSOR WITH RETURN for stmt;
 PREPARE stmt from
 ' SELECT camp.Id_Campagne, Naam_Campagne
   FROM camp
   JOIN cage ON camp.Id_Campagne = cage.id_campagne
   WHERE cage.Id_Gebruiker = ? ';
 OPEN cur USING iGebruiker;
 //UNPREPARE stmt
END

Regards, Harry


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:30DAAD2C-F731-443C-B85D-FEBBC81762A1@news.elevatesoft.com...
> Harry,
>
> << What would be the best way to do it (pro's/con's in terms of ease of
use,
> maintainabily, performance)? >>
>
> The stored procedure would be the least amount of trips to the ElevateDB
> Server, if using C/S access.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Tue, Feb 19 2008 4:25 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Harry,

<< Thanks. Must -or can- you also prepare a statement in a SP, or is it not
necessary? >>

You have to in order to associate the statement text with statement
allocated for the cursor.

See my other reply for more information.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Feb 19 2008 4:29 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Harry,

<< The stored procedure below is working now. At first I also did an
UNPREPARE but then there are never results. Is this correct? >>

Yes, UNPREPARE will get rid of everything, including the open cursor.

<< Don't you use UNPREPARE ever in these situations? >>

Only when the stored procedure is de-allocated by the engine.  This
effectively allows the engine to "cache" the statement, even though it is
inside of stored procedure.

<< If you use this sp a lot, must you prepare it very time then? >>

Do you mean "prepare the stored procedure", or "prepare the statement inside
of the stored procedure" ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Feb 20 2008 3:28 AMPermanent Link

"Harry de Boer"
Tim,

> Do you mean "prepare the stored procedure", or "prepare the statement
inside
> of the stored procedure" ?

Well, what I am really asking is that if you use a query, preparing (once)
helps the performance when using the same query with different values for
the parameter. Is there a way to do that by using a SP (if so, how), or is
it always as fast as can be?

Regards, Harry


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:1A79C902-6CC2-4B18-B947-1471E6726D56@news.elevatesoft.com...
> Harry,
>
> << The stored procedure below is working now. At first I also did an
> UNPREPARE but then there are never results. Is this correct? >>
>
> Yes, UNPREPARE will get rid of everything, including the open cursor.
>
> << Don't you use UNPREPARE ever in these situations? >>
>
> Only when the stored procedure is de-allocated by the engine.  This
> effectively allows the engine to "cache" the statement, even though it is
> inside of stored procedure.
>
> << If you use this sp a lot, must you prepare it very time then? >>
>
> Do you mean "prepare the stored procedure", or "prepare the statement
inside
> of the stored procedure" ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Wed, Feb 20 2008 10:49 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Harry,

<< Well, what I am really asking is that if you use a query, preparing
(once) helps the performance when using the same query with different values
for the parameter. Is there a way to do that by using a SP (if so, how), or
is it always as fast as can be? >>

Stored procedures are always prepared once and executed many times, so they
always get the benefit of a prepare.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image