Hi guys!

Pardon for jumping in.

I just ran following test:

cc='"1234567812345678123456781234567812345678123456781234567812345678"'
*cc='"12345678123456781234567812345678"'
select &cc as t1, ;
&cc as t2, &cc as t3, &cc as t4, &cc as t5, &cc as t6, &cc as t7, &cc as t8, &cc as t9, ;
&cc as t10, &cc as t11, &cc as t12, &cc as t13, &cc as t14, &cc as t15, &cc as t16, ;
&cc as t17, &cc as t18, &cc as t19, &cc as t20, &cc as t21, &cc as t22, &cc as t23, ;
&cc as t24, &cc as t25, &cc as t26, &cc as t27, &cc as t28, ;
&cc as t29, &cc as t30, &cc as t31, &cc as t32, &cc as t33, &cc as t34, &cc as t35, ;
&cc as t36, &cc as t37, &cc as t38, &cc as t39, &cc as t40, &cc as t41, ;
&cc as t42, &cc as t43, &cc as t44, &cc as t45, &cc as t46, &cc as t47, ;
&cc as t48, &cc as t49, &cc as t50, &cc as t51, &cc as t52, &cc as t53, ;
&cc as t54, &cc as t55, &cc as t56, &cc as t57, &cc as t58, &cc as t59, &cc as t60, ;
&cc as t61, &cc as t62, &cc as t63, &cc as t64, &cc as t65, &cc as t66, &cc as t67, ;
&cc as t68, &cc as t69, &cc as t70, &cc as t71, &cc as t72, &cc as t73, &cc as t74, ;
&cc as t75, &cc as t76, &cc as t77, &cc as t78, &cc as t79, &cc as t80, &cc as t81, ;
&cc as t82, &cc as t83, &cc as t84, &cc as t85, &cc as t86, &cc as t87, &cc as t88, ;
&cc as t89, &cc as t90, &cc as t91, &cc as t92, &cc as t93, &cc as t94, &cc as t95, ;
&cc as t96, &cc as t97, &cc as t98, &cc as t99, &cc as t100, &cc as t101, &cc as t102, ;
&cc as t103, &cc as t104, &cc as t105, &cc as t106, &cc as t107, &cc as t108, &cc as t109, ;
&cc as t110, &cc as t111, &cc as t112, &cc as t113, &cc as t114, &cc as t115, &cc as t116, ;
&cc as t117, &cc as t118, &cc as t119, &cc as t120, &cc as t121, &cc as t122, &cc as t123, ;
&cc as t124, &cc as t125, &cc as t126, &cc as t127, &cc as t128, &cc as t129, &cc as t130, ;
&cc as t131, &cc as t132, &cc as t133, &cc as t134, &cc as t135, &cc as t136, ;
&cc as t137, &cc as t138, &cc as t139, &cc as t140, &cc as t141, &cc as t142, ;
&cc as t143, &cc as t144, &cc as t145, &cc as t146, &cc as t147, &cc as t148, &cc as t149, ;
&cc as t150 from table1

With 68 bytes string query says "string is too long". With 34 bytes is runs ok and even displays results correctly in the browse window. As you see, VFP limitation for command length is related to the command AFTER all macro expanded.

Sergey, if you really managed to run VFP query with 16000 chars long, I would also really like to know how to do this. I suspect this was just like replacing "&cc" to "(m.cc)" in the query above, so it will use variable instead of macro and thus will run correctly because command length really is not larger than 8192 characters.


When talking about time, the slow down will be following when splitting query to two queries:

(-(/)* +

When query runs from tables across network, this would be really slow. Speed of cursors joining is done locally and is quite simple query or process, so its will be quick enough to do not account it. You can calculate a speed of selecting all fields using the same query running with all fields and with one expression constant field and then compare their speeds. Tried to be as exact as possible, though the formula is not exact because there are a lot more of other factors that have influence on the speed results.

When talking about approaches for joining cursors, better do not use manual scan. There is also a problem with records sorting. Joining by the SELECT statement will allow sorting of the final result. The best speed, indeed, will have custom scan of records and than using of SORT command into final result (that most likely will cut off all field names to 10 character) or indexing the final result set (that is hard to do when you have mixed ascending/descending sorting of the query result). So only the best and the most generic case (from speed, reliability and implementatoin point of view) is to join result sets by a SELECT command with ORDER BY clause. Indexing each cursor before joining query is a good idea, though this require testing.


>>Hi Sergey,
>>
>>One more thing Macro is OK but if can take all small SQLs into 1 prg
>>then I don't need to use any Macro. The question is if I save any
>>time by breaking it to small SQLs and combing it, versus this Macro
>>substitution.
>
>I would recomend that you go with macro substitution in this case. The only overhead in using macro substitution is a time foxpro needs to process macro substitutions. It doesn't affect sql select itself. You wouldn't save time by spliting sql select "verticaly" as you mention at the begining of the thread. Also I think the code would be simplier because all you need is to build each part of sql select separatly. You can also combine some clauses that aren't big if you want.

Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.com
ICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs

It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.