performance difference between dbaccess/ESQLC

Hi, I execute below SQL in the dbaccess, it take about 10 second. SELECT /*+ full(abpf10)*/ SUM(ab10bal) INTO :vDsck1:vDzsq FROM abpf10 WHERE ab10cur='01' AND ab10stat <> '1'; The same SQL take about 3 minutes using ESQL/C with the same instance. the ESQL/C program include this SQL only. Do anybody know what's the difference between DBACCESS and ESQL/C API? thanks for your time.

Is the hint crucial to DB-Access performance? If so, try using {+ full(abfp10)} instead of /*+ full(abfp10)*/ and see whether that makes a difference. I have an explanation if it does; I don't if it doesn't. Also, did you run with SET EXPLAIN ON? Did you compare the query plans? On Mon, Jul 1, 2013 at 11:07 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Hi, > > I execute below SQL in the dbaccess, it take about 10 second. > > SELECT /*+ full(abpf10)*/ SUM(ab10bal) INTO :vDsck1:vDzsq FROM abpf10 > > WHERE ab10cur='01' AND ab10stat <> '1'; > > The same SQL take about 3 minutes using ESQL/C with the same instance. the > ESQL/C program include this SQL only. > > Do anybody know what's the difference between DBACCESS and ESQL/C API? > > thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --001a11c1bc04184cb204e0813bf8

Actually dbaccess is written in ESQL/C. The difference is that when dbaccess passes the SQL string to the engine it passes it as a quoted string. If your ESQL/C program includes the SQL below as an EXEC SQL block (or preceded by a dollar sign ($)) then the C style comments you used below will be stripped out by the C preprocessor and never seen by the engine. I assume the reason for the full(ab10bal) optimizer directive is to improve the performance of this query and that it will run slowly without that directive. Well, as written below, it will indeed run slowly. Just change the comments you use to the curly bracket variety and PUFF! it will be fine: EXEC SQL SELECT {+ full(abpf10)} SUM(ab10bal) INTO :vDsck1:vDzsq FROM abpf10 WHERE ab10cur='01' AND ab10stat <> '1'; Bottom line: Don't use "C" style comments in SQL that's embedded in ESQL/C code! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jul 2, 2013 at 2:07 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Hi, > > I execute below SQL in the dbaccess, it take about 10 second. > > SELECT /*+ full(abpf10)*/ SUM(ab10bal) INTO :vDsck1:vDzsq FROM abpf10 > > WHERE ab10cur='01' AND ab10stat <> '1'; > > The same SQL take about 3 minutes using ESQL/C with the same instance. the > ESQL/C program include this SQL only. > > Do anybody know what's the difference between DBACCESS and ESQL/C API? > > thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c365a6aeb81504e085d5db