Any clever coders out there

Peterg · Post by **Peterg** » Fri May 16, 2008 2:19 pm

Hi all

I have a function which seeks for a price in a table which can have upto 64 different combinations. As a result of this there are 64 seeks per find
which is quite fast in dbf but horribly slow in sql.

Can anyone suggest a better way for writting this function.

I will pay for any good ideas

Thanks
Peter

code is

FUNCTION find_price(sp_site,sp_acc,sp_sit,sp_con,sp_was,sp_reg,sp_eff,sp_ctype,sp_disp)
LOCAL element := {"","","","","",""}
LOCAL alldone := .F.
LOCAL binary := 63
LOCAL old_area := SELECT()
PRIVATE element1,element2,element3,element4,element5,element6,spsite
spsite := sp_site
SELECT SKIPPRIC
DO WHILE !alldone .AND. binary >= 0
element1 := IF(LAND(32,binary),sp_acc,SPACE(10))
element2 := IF(LAND(16,binary),sp_sit,SPACE(10))
element3 := IF(LAND(8,binary),sp_ctype,SPACE(10))
element4 := IF(LAND(4,binary),sp_was,SPACE(10))
element5 := IF(LAND(2,binary),sp_reg,SPACE(10))
element6 := IF(LAND(1,binary),sp_disp,SPACE(10))
SKIPPRIC->(SR_SETFILTER("site_id="+STR(spsite)+" and sp_contain='"+Alltrim(sp_con)+"'"))

IF SKIPPRIC->(DBSEEK(STR(sp_site,2,0)+element1+element2+element3+sp_con+element4+element5+element6))

DO WHILE SKIPPRIC->SITE_ID == sp_site .AND. ;
SKIPPRIC->SP_CUSTNO == element1 .AND. ;
SKIPPRIC->UNIQ_NBR == element2 .AND. ;
SKIPPRIC->SP_CUSTYPE == element3 .AND. ;
SKIPPRIC->SP_CONTAIN == sp_con .AND. ;
SKIPPRIC->SP_WASTE == element4 .AND. ;
SKIPPRIC->SP_REGION == element5 .AND. ;
SKIPPRIC->SP_DISPSIT == element6 .AND. ;
!SKIPPRIC->(EOF())
IF ((SKIPPRIC->SP_DELDATE >= sp_eff .OR. ;
SKIPPRIC->SP_DELDATE == CTOD("")) .AND. ;
SKIPPRIC->SP_EFFDATE <= sp_eff)
alldone := .T.
EXIT
ENDIF
SKIPPRIC->(DBSKIP())
ENDDO
ENDIF
IF !alldone
--binary
ENDIF
ENDDO
SELECT(old_area)
RETURN alldone[

Roger Seiler · Post by **Roger Seiler** » Fri May 16, 2008 8:23 pm

How about using arrays for this?

At the beginning of the work session, you could load the data from SKIPPRIC into a separate array for each database field, and then using ASCAN() your lookups should be lightning fast, even with SQL.

James Bott · Post by **James Bott** » Sat May 17, 2008 12:36 am

Peter,

Can you provide a small self-contained program with the DBF and some example calls to the function and the proper results? This would allow us to test it and try to improve the speed.

James

James Bott · Post by **James Bott** » Sat May 17, 2008 12:38 am

Roger,

I think the problem is that the array has to be built for each call to the function, not per worksession (I am assuming "worksession" is each time the application is run). Still it might be faster.

James

Post by **Antonio Linares** » Sat May 17, 2008 7:53 am

Peter,

As you mention, the delay comes from the 64 seeks.

Don't do 64 seeks: Use just one SQL query sentence.

Roger Seiler · Post by **Roger Seiler** » Mon May 19, 2008 1:30 pm

James,

From Peter's data, it looks as though his pricing factors, though complex, are actually constants. In his pricing function, it seems that the price for a given situation is determined by the interrelation between the specifics of the case and all of the constants involved in his pricing factors. If so, he should only have to build the arrays once during a worksession, and then refer to the arrays each time he calls the pricing function, thereby avoiding the slow disk seeks. But if the pricing factors themselves are variables within the scope of a worksession, then you're right, the arrays approach definitely won't work.

- Roger

James Bott · Post by **James Bott** » Mon May 19, 2008 1:41 pm

Roger,

You are correct--there are a number of constants, and it is not clear exactly what he is doing.

Antonio may be correct in that one SQL statement would probably do it. But without really understanding how the search works it is hard to tell.

James

xProgrammer · Post by **xProgrammer** » Mon May 19, 2008 10:12 pm

Hi all

I think that we can only really offer the best advice if peterg gives an explanation of what his code needs to achieve, how often and when the data changes and the nature of the environment in which this needs to be achieved (eg single PC, LAN or WAN and if WAN what sort of speed)

Maybe then an appropriate index (if we are to stay with xBase tables) would mean that 64 seeks would not be required and / or setfilters would not be required. Maybe we can use arrays / hashes. Maybe the pricing table can be cached in memory so table access isn't required at all.

SQL can be much more efficient with multiple seeks across a slower link but on a local PC xBase table access using indices can be very efficient.

Regards
xProgrammer

Peterg · Post by **Peterg** » Tue May 20, 2008 8:00 am

Hi all
Firstly how do I upload a sample prg and dbf for testing?

The price file could be very large > 150K records and could be updated by any number of users during the course of the day so cerating an array is probably not possible.

A price can exist for the following
site (internal identifier)
customer type (a group of customer)
customer
delivery location
container
waste type
disposal lcoation
haulier
date

or any combination of the above with the container being the common factor.
The system has to try and find the best possible match before reporting that there are no prices setup. In dbfs this works lightning fast so is not a problem but in sql 64 seeks take way too long

Any ideas
Peter

Post by **Antonio Linares** » Tue May 20, 2008 10:41 am

Peter,

Try to build a SQL query that does it all.

Even if you can't, please start writting it here so we can help you, once we understand the exact query that you need

xProgrammer · Post by **xProgrammer** » Tue May 20, 2008 11:10 am

Hi Peter

If your SQL code pretty much follows your xBase code it will be inefficient. SQL is efficient where you "bundle up" the work into a single query. If you have a series of individual "seeks" SQL will be inefficient compared to xBase on a local machine.

From what I can see of your case if you need to use SQL you probably need to use a stored procedure. These can be precompiled on many vendor implementations and can be very fast.

Whilst I have used SQL in one major project, I am no expert and am not the right person to help you with writing a suitable stored procedure. They do exist although this may not be the best forum to locate them in.

I don't know how familiar you are with stored procedures. You could start with:

http://en.wikipedia.org/wiki/Stored_procedure
http://msdn.microsoft.com/en-us/library/aa174792.aspx
http://www.sql-server-performance.com/a ... cs_p1.aspx
http://databases.about.com/od/sqlserver ... cedure.htm
http://www.informit.com/articles/article.aspx?p=25288

The following article outlines some techniques that you may find to be of use.

http://vyaskn.tripod.com/passing_arrays ... edures.htm

And maybe consider registering on

http://searchsqlserver.techtarget.com/l ... %2C00.html

Regards
xProgrammer

Peterg · Post by **Peterg** » Tue May 20, 2008 11:20 am

I am not sure where to start but here goes

variable declarations
mSiteID = 1
mCustacc = "ABC123"
mUniqnbr = "0000000001"
mCustType = "EXTERNAL"
mContainer = "1100WB"
mWaste = "GENERAL"
mDisposer = "XYZ987"
mDate = DATE()

select * from skippric
where site_id = mSiteID and sp_custno = mcustacc and uniq_nbr = mUniwnbr and sp_contype = mContainer and sp_waste = mWaste and
sp_effdate<=mDate

but if this fails then it should test for all other combinations until there is nothing left to test.

Once it finds a record then i can use the prices contained in this record

Does that help?

Peter

Post by **Antonio Linares** » Tue May 20, 2008 11:49 am

Peter,

Use OR to check for the other combinations from the same SQL sentence

Peterg · Post by **Peterg** » Tue May 20, 2008 12:24 pm

Antonio
This will result in 64 seperate or statements. Do you think this will be faster

Peter

James Bott · Post by **James Bott** » Tue May 20, 2008 1:09 pm

Peter,

Can you design your query so that it returns all possible matches to a recordset, then scan the recordset for the best match?

James