I have about 12 SQL statements in series pulling data from 3 separate tables (due to major differences in schemas). The results are displayed integrated on one html page.
The results are returned in a reasonable time, but not lightning fast. I have metamorph indexes and no linear searching allowed. I use the ROW statement to preserve memory, but it seems that most of the overhead is in switching from one SQL statement to the next. I can see the lag (in terms of bytes returned), in netscape, as it hops from one sql to the next. Any recommendations on fine-tuning this type of searching?
btw, Each sql statement is very fast if executed separately.
Subsequent SQL statements should be somewhat faster as the database is already open. 12 SQL statements is quite a few though, and even if each is lightning fast, 12 could be perceptibly slower. A review to make sure you are doing the fewest queries as efficiently as possible may be in order.
To fine tune you can put the 12 SQL statements in a file, and use tsql with -t -t which will time each statement, as well as the overall time. This will help see which SQL statements may be the major culprits.
The primary difference between tsql and a Vortex script is that Vortex does cache SQL statements in case you reuse them. The <SQLCACHE> directive controls how many are kept open. You might try changing the value to see if it has an impact.
Thanks for the suggestions. I went back to my script and noticed that I goofed up. The first table I was searching was a small one which I allowed linear searching against! I failed to turn it off before resuming searching on the other tables. It is lightning fast now, even with 12 SQL statements!
bonne annee!
P.S. I know someone who remembers you from Ecole Internationale. He is actually my neighbour and I think he went there as well during your time. Small world...
Happened to come across this email as I was looking around for some technical info, and noticed a ref to the Ecole Internationale. Seeing as I went there myself, and did know a John Turnbull, was wondering if you are one and the same.
