CAST versus SUBSTRING
Mark Scranton asked whether cast(decimal to string) or substring(decimal from 1) is more expensive. Serge Rielau predicted substring costs more because it needs an implicit cast first. Art S. Kagel could not get substring to work, but found explicit ::char(20) slightly faster than concatenating an empty string, matching Serge's analysis. Paul Watson agreed substring should be slower.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
cast(<decimal> to string) VS. substring(<decimal> from 1) which is more expensive? can test it, but wanted opinions of the crowd first....thoughts? I know this looks like "too generic of a question", but consider it "all things equal" (if possible...) Thanks - Mark Scranton
mark.scranton@gmail.com wrote: > cast(<decimal> to string) VS. substring(<decimal> from 1) > > which is more expensive? can test it, but wanted opinions of the crowd > first....thoughts? I know this looks like "too generic of a question", > but consider it "all things equal" (if possible... I'm willing to place a bet: substring(<decimal> from 1) is more expensive. Namely by the cost of executing substring(<string> from 1). Substring is a string function. In order to operate on the decimal it needs to implicitly cast the decimal to the string first. Whether a cast is implicit or explicit won't make a difference. The work is the same. There is a possibility that substring(<string> from 1) is recognized as a no-op and eliminated by the SQL compiler. A long shot that can at best make them equal. There is no question about which one is more readable. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab IOD Conference http://www.ibm.com/software/data/ondemandbusiness/conf2006/
mark.scranton@gmail.com wrote: Couldn't get the substring to work, but compared <decimal col>::char(20) to <decimal col> || ''. I get that the explicit cast is a bit faster than the implicit cast and concatenate. That fits with Serge's analysis. Art S. Kagel > cast(<decimal> to string) VS. substring(<decimal> from 1) > > which is more expensive? can test it, but wanted opinions of the crowd > first....thoughts? I know this looks like "too generic of a question", > but consider it "all things equal" (if possible...) > > Thanks - > Mark Scranton >
Substring certainly should be Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 European Conference. Vienna, Austria. 2-6 October 2006 Visit http://www.iiug.org/conf for more information. -----Original Message----- From: mark.scranton@gmail.com [mailto:mark.scranton@gmail.com] Posted At: 15 August 2006 11:58 Posted To: comp.databases.informix Conversation: CAST versus SUBSTRING Subject: CAST versus SUBSTRING cast(<decimal> to string) VS. substring(<decimal> from 1) which is more expensive? can test it, but wanted opinions of the crowd first....thoughts? I know this looks like "too generic of a question", but consider it "all things equal" (if possible...) Thanks - Mark Scranton