Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Russell Hill
  Where is Russell Hill?
 Sydney
 Australia
 Russell Hill
 To: Mike Yearwood
  Where is Mike Yearwood?
 Toronto
 Canada
 Mike Yearwood
 Tags
Subject: RE: Build string with SQL?
Thread ID: 311134 Message ID: 311260 # Views: 32 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Thursday, June 30, 2011 1:01:10 AM         
   


> > 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.
>
> I like Borislav's second approach best - passing the var by reference is faster than by value.
>
> I had another idea - bit of a guess though. If you do STR(MKTWPROB,8,6) in the SQL, it should be faster overall because that becomes part of a single command. If that was done by SQL Server on the backend, it would absolutely be faster to not do it in your scan.
>
> Oh, and Vive Le Cavalier! :)
>
> Mike Yearwood
> Microsoft MVP Visual FoxPro 2008, 2009
> We have enough youth. We need a fountain of smart!
> There may be many ways to skin a cat, but there are very few right ways to do it.

Thanks Mike.

I'll definitely try that approach. Just can't do it at the moment. Time is my master.

Vive le horloge! (Or is it la? I didn't study French at school).

Regards,

Russell.

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