I don't think it will make any difference if there are no deleted records -
I am not 100% but I believe SYS(3054, .. reports partial because of the
issues around deleted records.
When I am testing stuff like that I use the high resolution timers - run
one and the other and look for any differences.
Viz:
DECLARE INTEGER QueryPerformanceCounter IN kernel32;
STRING @lpPerformanceCount
DECLARE INTEGER QueryPerformanceFrequency IN kernel32;
STRING @lpFrequency
*- Clear VFP Buffered Memory
Declare Integer SetProcessWorkingSetSize In kernel32 As
SetProcessWorkingSetSize ;
Integer hProcess , Integer dwMinimumWorkingSetSize , ;
Integer dwMaximumWorkingSetSize
DECLARE INTEGER GetCurrentProcess IN kernel32
FOR lnTstCase = 1 TO lnTestCases
*- Get Counter Frequency - this will vary per machine.
lnCntFreq = spQueryPerformanceFrequency()
lnStart = spQueryPerformanceCounter() / m.lnCntFreq
*-------------------------------------
*- DO SOME STUFF
*- At the end of your testing lnDuration will have the time in micro
second resolution (or close enough)
*-------------------------------------
*- Now get the duration (in seconds) between the start and now.
lnDuration = spQueryPerformanceCounter() / m.lnCntFreq - m.lnStart
*- Clear buffered memory (start with lelvel playing field for each test
case)
=spReleaseMemory()
ENDFOR
FUNCTION spQueryPerformanceFrequency()
LOCAL lcBuffer
lcBuffer = REPLICATE(CHR(0), 8)
QueryPerformanceFrequency(@lcBuffer) && note this will return .f. if we
don't have a counter..
RETURN spbuf2dword(SUBSTR(m.lcBuffer, 1, 4)) +
spbuf2dword(SUBSTR(m.lcBuffer, 5, 4)) * 4294967296
ENDFUNC
FUNCTION spQueryPerformanceCounter()
LOCAL lcBuffer
lcBuffer = REPLICATE(CHR(0), 8)
QueryPerformanceCounter(@lcBuffer)
RETURN spbuf2dword(SUBSTR(m.lcBuffer, 1, 4)) +
spbuf2dword(SUBSTR(m.lcBuffer, 5, 4)) * 4294967296
ENDFUNC
FUNCTION spbuf2dword (vcBuffer)
RETURN ASC(SUBSTR(m.vcBuffer, 1,1)) + ;
BITLSHIFT(ASC(SUBSTR(m.vcBuffer, 2,1)), 8) +;
BITLSHIFT(ASC(SUBSTR(m.vcBuffer, 3,1)), 16) +;
BITLSHIFT(ASC(SUBSTR(m.vcBuffer, 4,1)), 24)
ENDFUNC
FUNCTION spReleaseMemory
LOCAL lnProc
*- From
http://msdn.microsoft.com/en-us/library/windows/desktop/ms686234%28v=vs.85%2
9.aspx
*-
*- BOOL WINAPI SetProcessWorkingSetSize(
*- _In_ HANDLE hProcess,
*- _In_ SIZE_T dwMinimumWorkingSetSize,
*- _In_ SIZE_T dwMaximumWorkingSetSize
*- );
*-
*- If both dwMinimumWorkingSetSize and dwMaximumWorkingSetSize have the
value (SIZE_T)-1, ;
*- the function removes as many pages as possible from the working set of
the specified process.
lnProc = GetCurrentProcess()
SetProcessWorkingSetSize(m.lnProc, -1, -1)
ENDFUNC
-----Original Message-----
From: ProfoxTech [mailto:[email protected]] On Behalf Of MB
Software Solutions, LLC
Sent: Saturday, 20 July 2013 6:34 AM
To: [email protected]
Subject: Re: Why is this SQL not fully optimized?
On 7/19/2013 1:07 PM, Ted Roche wrote:
> Do you have a tag on DELETED()
No, I didn't. This data is only inserted into the database and then
queried; there's no updates or deletes. I'm going to test the SET DELETED
and/or index on DELETED strategy next week. Since no records will ever be
deleted, is it silly to have an index on deleted? Methinks going with SET
DELETED OFF would be a better move here.
>
> Rather than have a function within the SQL, replace DATE() with
> {^2012-01-01} so it is an explicit value, or use:
>
> m.MyTargetDate = DATE(2012,1,1)
>
> ad
>
> ... WHERE LastDOS = m.MyTargetDate
I had wondered about that. However, this test seems to indicate that just
using a SET DELETED OFF is enough to fix it:
SET DELETED ON
CREATE CURSOR MyTable (lastDOS d)
INDEX on lastDOS TAG lastDOS
? SYS(3054,1)
SELECT * FROM MyTable WHERE LastDOS = DATE() SET DELETED OFF SELECT * FROM
MyTable WHERE LastDOS = DATE()
Thanks for the ideas, Ted!
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
http://fabmate.com
http://twitter.com/mbabcock16
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
