Re: [firebird-support] Partial update
Den 13.02.2017 20:40, skrev Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]: > Hello, All. > > If I use prepared statement like this "update table set field1=?, > field2=? where > field3=?" for performance reasons, is there a way to update only some fields > and leave > other untouched without overhead of building separate queries on the fly or > selecting old > values with different query? I'm more of a SELECT than UPDATE guy (and haven't tried my suggestion), but wouldn't update table set field1=coalesce(?, field1), field2=coalesce(?, field2) where field3=? get the right result (well, unless you could have instructions like "Set field2 to ;")? Theoretically, I would of course expect such a statement to "change" the value to the existing value rather than leaving it untouched when the parameter is . HTH, Set ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] Partial update
> > Is that the real issue, you want to eliminate the "prepare" stage? > >"Prepare" does much more than just parsing of query and building plan. It > also check for permissions and this stage cannot be cached. Not if the cache was in the context of the current connection, so permissions could be checked just once. Then the prepare would only be done for the first execution of each statement variant. As for the PLAN, arguably this could be handled by a process which would clear cache statements whenever DDL was applied to the database -- in essence causing the next execution to re-prepare the statement. Maybe I am just being selfish but it seems my suggestion is more along the lines of how other engines think of optimizing performance, rather than your "skip parameter" approach. It also has the advantage of applying to all use cases, not just operations using native API. Sean
Re: [firebird-support] Partial update
14.02.2017 16:21, 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] wrote: > If you are using UPDATE, then is it not reasonable to expect that you would > know what the > original values for of each field? It is not reasonable: I have no way to know them. All I have at hand is new values for some (not all) fields. For example, you have a table with 10 fields. And you got following instructions: 1) Change value of field1 to 10; 2) Change value of field2 to 20; 3) Change value of field3 to 30; ... and so on. These instructions are coming one-by-one and must be executed immediately. What would you do? Construct queried ad-hock, right? Simple solution, bad performance. > If not, what is the "real cost" (time) of preparing statement? It is unpredictable and for update of single record used to be bigger than execute time. > Must a statement with parameters be prepared before it can be executed via > the client? Yes. > Cus, it seems that what you are asking the engine to maintain a cache of > previously executed statements. No. All I would like to ask for - to skip assignment of new values for those fields that are not provided in my data message. > So if you execute any un-prepared statements, the engine would compare the > SQL text against the cache list and re-use the previously prepared version, > instead of preparing the statement a new -- thus bypassing the "prepare" > stage. > > Is that the real issue, you want to eliminate the "prepare" stage? "Prepare" does much more than just parsing of query and building plan. It also check for permissions and this stage cannot be cached. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] Partial update
>If I use prepared statement like this "update table set field1=?, field2=? > where field3=?" for performance reasons, is there a way to update only > some fields and leave other untouched without overhead of building > separate queries on the fly or selecting old values with different query? If you are using UPDATE, then is it not reasonable to expect that you would know what the original values for of each field? So, it should not be difficult to provide same in the UPDATE statement. If not, what is the "real cost" (time) of preparing statement? Must a statement with parameters be prepared before it can be executed via the client? Cus, it seems that what you are asking the engine to maintain a cache of previously executed statements. So if you execute any un-prepared statements, the engine would compare the SQL text against the cache list and re-use the previously prepared version, instead of preparing the statement a new -- thus bypassing the "prepare" stage. Is that the real issue, you want to eliminate the "prepare" stage? Sean
