Re: stored procs slower than 4GL?

In article <3qna3c$254@cssun.mathcs.emory.edu>, kmart!jregep@uunet.uu.net wrote: > Joe Lumbley writes: > > >One of our subsidiaries has some developers who are swearing that they are > >getting faster results using 4GL than they are with stored procedures. The > >stored procs are returning massive amounts of data with each fetch, but > >so is the 4GL. Does anyone know of any structural reasons or limitations > >that would cause the stored procs to be slower? > > Are their tables extremely dynamic? Perhaps the SPs are parsed and optimized > for a different looking tables. Need to run UPDATE STATISTIC more often, which > is an overhead in itself (a limitation in my book). > > Are they using too many SPs? In the class I took the instructor told a > story about some developers who wrote their entire application in SPs. All > that cashe'n of SPs slowed them way down. Actually, this is a good point. During my first Informix system installation, we learned most of the optimization lessons the hard way. One of the classic guidelines is to ensure that your tables have no more than 8 extents. Imagine my surprise when I discovered that the sysprocs table had over 20 extents! I don't claim to be an Informix guru, but from what nuggets of wisdom I've gleaned from this group, wouldn't that tend to slow down performance for the stored procedures? -- Jonathan Crawford Ark Technologies, Inc.