As you suggested, I put the phrase "DeletesAreVisible=true" in the
connection string box for my VFP Data Source. However, I must
have something wrong. My records tagged for delete (but not packed)
are still not showing up in my query.
I will send a screen shot by email.
Thanks
Also, I've apparently forgotten my password. I twice requested the
password be sent to me on you "forgot your password" section.
(Last night and then again tonight) The message said my password
would be sent to me shortly. But, I still have not received the password??
Thanks, Paul Hall
|
>My records tagged for delete (but not packed) are still not showing up in my query.
You can try "select * from yourTable where deleted()" to see whether you can get those deleted rows. If it returns rows, it's corrrect.
If your query is "SELECT COUNT(*) from myTable where ST = "AR"", and your ST is indexed with filtered condition !deleted(), then HXTT DBF won't return thosed deleted rows since it's using that index file to query.
|
According to the Note at http://www.hxtt.com/dbf/faq.html#interoperability .
Note: Connection String seems abnormal now. You should have to put Connection String into JDBC URL, for instance: jdbc:dbf:/c:data?delayedClose=15;maxCacheSize=6144;lockTimeout=2000;
It seems that your ColdFusion has that issue too. Please try to use jdbc:dbf:/c:/AAAMo/Dictionary?DeletesAreVisible=true
|
Yes!!! jdbc:dbf:/c:/AAAMo/Dictionary?DeletesAreVisible=true
I updated to your latest version and put this parameter into my
JDBC URL string and my count(*) queries now work very well !!
Thank you SO much.
I now must improve the performance of my table join queries.
Do you have any suggestions?
Again, Thank You !
|
>I now must improve the performance of my table join queries.
>Do you have any suggestions?
What's your sql sample?
|
It is a long SQL statement with a double join submitted
from a ColdFusion cfquery. But, it runs in under one
second in VFP.:
Thanks for any help you can provide.
Paul
SELECT cc.cid as cid,
dd.leadstatus as leadstatus,
dd.lstatcod as lstatcode,
alltrim(strtran(strtran(' ' + substr(ttoc(cc.nextfudat),1,16),' 0',' '),'200','0')) + lower(substr(ttoc(cc.nextfudat),21,1))+' ' as fudatm,
cc.tskstatus,
cc.tstatcod,
dd.recsourcep as recsource,
pp.lastnamp,
pp.firstnamp,
pp.addr1pc,
pp.addr2pc,
pp.emailpc,
cc.tskproduct,
cc.tskcomment,
cc.tskaction,
dd.coverage1 as quotenum,
dd.priorco,
dd.xdate ,
cc.agentidp as agent1 ,
cc.receivedc,
pp.branchlocp ,
pp.citypc ,
pp.stpc,
pp.zippc,
pp.phonepc1 ,
iif(inlist(pp.phonind,'X'),'Do Not Call',iif(pp.sdnp='1',iif(pp.dnp='1','st/fed ','st list '),iif(pp.dnp='1','fed list ',' '))) as dnc,
dd.commentsd as leadnotes1 ,
cc.hhnew as hhnew,
substr(alltrim(cc.membprod)+' ',1,12) as memprod,
memcid,
pp.memnonp as memnon,
dtos(nextfudat)+' '+substr(TTOC(nextfudat,1),9,4) as nextfu,
nextfudat
FROM (contacts cc left join Prospdet dd on cc.membprod=dd.membprod)
left join prospects pp on cc.hhnew=pp.hhnew
WHERE cc.hhnew>1 and
(not (inlist(dd.leadstatus,'Drop') or inlist(cc.tskstatus,'Canc'))) and
(not cc.tskstatus=iif('#pShowDone#'='false' ,'Done','QQ')) and
mid(cc.agentidp,1,len(trim(iif('#pSysLevel#'='7','#pCombAgent#','#pSysUserName#')))) =
iif('#pSysLevel#'='7','#pCombAgent#','#pSysUserName#') and
mid(dd.agentidp,1,len(trim(iif('#pSysLevel#'='7','#pCombAgent#','#pSysUserName#')))) =
iif('#pSysLevel#'='7','#pCombAgent#','#pSysUserName#') and
between(ttod(cc.nextfudat),iif(mid('#pFuT#',1,3)='All',ctod(' / / '),
iif(mid('#pFuT#',1,3)='Tod', ctod('#pCalDate#'),
iif(mid('#pFuT#',1,3)='Thi',ctod('#pCalDate#')-5,
iif(mid('#pFuT#',1,3)='Nex', ctod('#pCalDate#'),date() )))),
iif(mid('#pFuT#',1,3)='All',ctod('12/31/2010'),
iif(mid('#pFuT#',1,3)='Tod',ctod('#pCalDate#'),
iif(mid('#pFuT#',1,3)='Thi',ctod('#pCalDate#')+7,
iif(mid('#pFuT#',1,3)='Nex',ctod('#pCalDate#')+14,date())))))
ORDER BY nextfudat
|
I was able to speed up the query by converting the joins to
implicit inner joins (FROM contacts cc, prospdet dd, prospects pp WHERE
cc.membprod=dd.membprod and cc.hhnew=pp.hhnew and ...)
Thanks! I'm happy the query speed is working. May have some
outer join issues later...but, for now is not a problem.
|
|
Yeah. inner join is quicker than left/right/full join.
|
Left/Right/Full Joins are very quick in native VFP rushmore.
Do you have any plans to add these to your own speed
optimizing engine?
Thanks again for all your help.
|
|
Rushmore need many memory to construct temporary Join table. Now we haven't plan to quicken Left/Right/Full join, except for utlizing index.
|
Yeah you're right about the memory for Rushmore. Though, it's memory on the server, not the clients. So, I would gladly get extra memory for extra performance. However, I'm sure it would be too much extra effort to have a
"use Rushmore" option.
A java driver for VFP/Rushmore might do well against MySQL. Might be fun to show a side by side test on ColdFusion/Java/Flex forums. VFP/Rushmore does faster queries than even SQLSvr/ Oracle/ Informix for databases up to a million records, with the tuning we have.
So, I really appreciate your driver. Thanks. And, thanks for your support and answering my questions!
|
Sorry, I meant "for databases up to 10 million records" !
(which includes a lot of applications.)
|
Main
webmaster@hxtt.com