Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General Discussion » View Thread |
Messages 1 to 8 of 8 total |
query with params vs views vs sp's |
Mon, Feb 18 2008 5:43 AM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent 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 AM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 |
This web page was last updated on Friday, April 19, 2024 at 07:09 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |