Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Leonid Lepin
  Where is Leonid Lepin?
 Riga
 Latvia
 Leonid Lepin
 To: Russell Hill
  Where is Russell Hill?
 Sydney
 Australia
 Russell Hill
 Tags
Subject: RE: Build string with SQL?
Thread ID: 311134 Message ID: 311205 # Views: 60 # Ratings: 3
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Wednesday, June 29, 2011 4:44:30 PM         
   


> Hi all,
>
> I have a situation where I want to build a string representing a single column in a cursor.
>
>
SELECT TRANSKEY, ;
>        FP, ;
>        NAME, ;
>        MKTWPROB, ;
>        CAST(0 AS N(8,6)) AS MKTPPROB ;
>   FROM TRANSTABLE ;
>  WHERE TRANSKEY = rKeyId ;
>  ORDER BY FP ;
>   INTO CURSOR baseCursor READWRITE NOFILTER
> 
> ** Notice the cavalier but well-informed use of NOFILTER here. :)
> 
> probString = ''
> SCAN
>    probString = probString + STR(MKTWPROB,8,6) + '-'
> ENDSCAN

> The SCAN works perfectly well but it is within a loop traversing a very large table and, in the interests of achieving the highest possible speed, I was wondering whether it was possible to construct the string using a single SQL SELECT each time rather than using a SCAN hundreds of thousands of times. The '-' delimiter is not necessary - I've just been using that in testing for readability purposes.
>
> Any ideas would be most appreciated.
>
> TIA.
>
> Russell.

May be your problem lies in another plane. Concatenating a large amount of strings is very slow operation. It may be done sufficiently faster writting information to low level file. Check the next sample


create cursor tmp (f1 c(10))
for i = 1 to 1000000
	insert into tmp values ("1234567890")
next

m.sc = seconds()
probString = ''
SCAN
   probString = probString + f1
ENDSCAN
?seconds() - m.sc

m.sc = seconds()
m.hnd = fcreate("tmp.tmp")
?m.hnd
SCAN
   fwrite(m.hnd, f1)
ENDSCAN
fclose(m.hnd)
probString = filetostr("tmp.tmp")
erase ("tmp.tmp")
?seconds() - m.sc



Leonid

ENTIRE THREAD

Build string with SQL? Posted by Russell Hill @ 6/29/2011 3:31:04 AM
RE: Build string with SQL? Posted by Samir H. @ 6/29/2011 4:41:13 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/29/2011 2:28:45 PM
RE: Build string with SQL? Posted by Stefan Wuebbe @ 6/29/2011 8:13:05 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/29/2011 2:30:12 PM
RE: Build string with SQL? Posted by Borislav Borissov @ 6/29/2011 8:49:11 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/29/2011 2:32:48 PM
RE: Build string with SQL? Posted by Mike Yearwood @ 6/29/2011 4:30:02 PM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 1:01:10 AM
RE: Build string with SQL? Posted by Mike Yearwood @ 6/30/2011 4:46:23 PM
RE: Build string with SQL? Posted by Samir H. @ 6/30/2011 6:29:30 PM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 10:53:32 PM
RE: Build string with SQL? Posted by Samir H. @ 7/1/2011 5:24:32 AM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 11:29:56 AM
RE: Build string with SQL? Posted by Michel Levy @ 7/1/2011 11:00:28 AM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 11:29:11 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 10:51:19 PM
RE: Build string with SQL? Posted by Leonid Lepin @ 6/29/2011 4:44:30 PM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 12:56:49 AM
RE: Build string with SQL? Posted by Tom Saddul @ 6/30/2011 3:36:58 AM
RE: Build string with SQL? Posted by Russell Hill @ 6/30/2011 7:20:40 AM
RE: Build string with SQL? Posted by Anders Altberg @ 7/1/2011 12:40:07 AM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 2:43:58 AM
RE: Build string with SQL? Posted by Anders Altberg @ 7/1/2011 1:31:36 PM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 2:23:04 PM
RE: Build string with SQL? Posted by Anders Altberg @ 7/1/2011 3:31:26 PM
RE: Build string with SQL? Posted by Russell Hill @ 7/1/2011 11:29:12 PM
RE: Build string with SQL? Posted by Anders Altberg @ 7/2/2011 12:51:58 AM
RE: Build string with SQL? Posted by Russell Hill @ 7/2/2011 2:44:25 AM
RE: Build string with SQL? Posted by Anders Altberg @ 7/2/2011 2:05:49 PM
RE: Build string with SQL? Posted by Stefan Wuebbe @ 7/3/2011 10:40:49 AM
RE: Build string with SQL? Posted by Anders Altberg @ 7/3/2011 2:19:55 PM