Author Topic: Doing things in the server instead of in the client.  (Read 138 times)

Fim

  • Guru
  • ***
  • Posts: 131
    • View Profile
    • Fims sajt
Doing things in the server instead of in the client.
« on: December 06, 2017, 06:46:01 AM »
I have a Item Master Table ITE with 100,000 items. I want all the items whose item name contains all the keywords. The number of keywords can be 1 to 5.     
Current solution is shown below (maybe something wrong in the translation from Swedish to English)
Is it possible to solve the selection in the server using LIKE or like() ?
Code: [Select]
STARTING:
    slSel "SELECT ITEM_NO, ITEM_NAMN FROM ITE ORDER BY ITEM_NO"

FETCH_NEXT_ITEM:
    IF slGetRow = %FALSE THEN GOTO ENDING
    ITE.ITEM_NO   = slF(01)
    ITE.ITEM_NAME = slF(02)
    ITEM_NAME = SHRINK$(ITEM_NAME)

SELECT_ITEM:
    IF INSTR(ITEM_NAME, KEYWORD1) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD2) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD2) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD2) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD2) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD3) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD3) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD4) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD4) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD5) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD5) = 0 THEN GOTO FETCH_NEXT_ITEM
SELECT_ITEM_END:

    PRINT ITEM_NO, ITEM_NAMN
    GOTO FETCH_NEXT_ITEM   
   
ENDING:   
     
     
Example:
KEWORD1 = "FOR"
KEWORD2 = "SS"       

Item Names
1 CHASSI FOR SWITCH    <-- Hit
2 CHASSI-STOMME
3 FORMAT-BAND     
4 KASSERAD OMFORMARE   <-- Hit

/Fim W.

Fim

  • Guru
  • ***
  • Posts: 131
    • View Profile
    • Fims sajt
Re: Doing things in the server instead of in the client.
« Reply #1 on: December 06, 2017, 07:08:48 AM »
After some googling, I think I have a solution:
Code: [Select]
SELECT ITEM_NO, ITEM_NAME WHERE ITEM_NAME LIKE '%FOR%' AND ITEM_NAME LIKE '%SS%'I had difficulty imagining that LIKE %ABC% was so effective.
/Fim W.

cj

  • Master Geek
  • ****
  • Posts: 616
    • View Profile
Re: Doing things in the server instead of in the client.
« Reply #2 on: December 06, 2017, 08:20:27 AM »
I wouldn't think LIKE would be good enough.
Full text search like google might be a good way: http://www.sqlitetutorial.net/sqlite-full-text-search/
Fastest might be to just search a string using PowerBASIC function(s).
« Last Edit: December 06, 2017, 08:22:32 AM by cj »

Fim

  • Guru
  • ***
  • Posts: 131
    • View Profile
    • Fims sajt
Re: Doing things in the server instead of in the client.
« Reply #3 on: December 06, 2017, 08:47:25 AM »
To me, it seems that the LIKE function is sufficient for me.
On the other hand, it is sometimes faster to solve it in PowerBasic than with LIKE in the server.
Could probably depend that the server is a quite simple box , while my client is a powerful box.

I will have a look at "Full text search"

/Fim W.

cj

  • Master Geek
  • ****
  • Posts: 616
    • View Profile
Re: Doing things in the server instead of in the client.
« Reply #4 on: December 06, 2017, 10:02:59 AM »
Something like this or a binary search since SQLite is not needed

FUNCTION PBMAIN () AS LONG
 LOCAL sDictionary,sSearchFor,sFound AS STRING
 sDictionary$ = "one,two,three,four,five,"
 sSearchFor$  = "seven,six,five,one,nine,"
 sFound = Search(sDictionary$,sSearchFor$)
 ? sFound,,USING$("Found #",TALLY(sFound,",")) 'five,one,
END FUNCTION

FUNCTION Search(sDictionary AS STRING, sSearchFor AS STRING) AS STRING
 LOCAL x,NumberOfWords AS LONG,sWord,sFound AS STRING
 NumberOfWords = TALLY(sSearchFor,",")
 FOR x = 1 TO NumberOfWords
  sWord = PARSE$(sSearchFor,x) + ","
  IF INSTR(sDictionary,sWord) THEN sFound+=sWord
 NEXT
 FUNCTION = sFound
END FUNCTION                   

Bern Ertl

  • Master Geek
  • ****
  • Posts: 466
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Doing things in the server instead of in the client.
« Reply #5 on: December 07, 2017, 08:27:00 AM »
This is probably a good candidate for employing a SQLite custom function.

Examples:
http://sqlitening.com/support/index.php?topic=3539.msg18054#msg18054

http://www.sqlitening.com/support/index.php?topic=2936.0;nowap   (post #4 has code)

Fim

  • Guru
  • ***
  • Posts: 131
    • View Profile
    • Fims sajt
Re: Doing things in the server instead of in the client.
« Reply #6 on: December 07, 2017, 08:36:48 AM »
Cj, Bern,
Thank you, but I am satisfied with the current solution. Acceptable performance.
/Fim W.

cj

  • Master Geek
  • ****
  • Posts: 616
    • View Profile
Re: Doing things in the server instead of in the client.
« Reply #7 on: December 07, 2017, 08:39:50 AM »
Fim,
What was your solution, LIKE?
The post says "Do things in the server instead of the client".
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bern,
Is the letter file SQLiteningProcsA.bas?  (Never mind,  the SQLiteningProcs must start SQLiteningProcs + your choice of A,B, C  to match your later compiled DLL.
Is everything put into it?                       Yes or whatever you name your DLL SQLIteningProcsZ for example.   SQLiteningProcsA.bas contains samples.
Was slRunProc corrected in 2009?          Assuming it was.

Ok, getting there.   8:37 AM
1 Read everything in SQLiteningProcsA.bas
2 Read documentation on slRunProc
3 Then search this forum for slRunProc and it makes sense.
We need a library of useful server-side functions.
Note: SqliteningServerMonitor.exe is different.  It can be  shelled to if SQLiteningServer.bas is recompiled with correct flag.

Docs for slRunProc only explains  the first and last parameters, rsProcName and rsModChars
slRunProc (rsProcName String, blParm1 Long, blParm2 Long, bsParm3 String, bsParm4 String, [rsModChars String]) Long


'Unload custom functions when no longer needed
I guess this is called once at end of program, but no idea what Return1,Return2,sBuffer1,sBuffer2 values are?
slRunProc "[letter]", lReturn1, lReturn2, sBuffer1, sBuffer2, "U"

Is each function unloaded or just the name of the .DLL?

Not sure, if this registers all functions or just a single function ICF?
What are the values of Return1,Return2,sBuffer1,sBuffer2?

Is this register function or register all functions?
ICF is a single function.
...
   'Register functions and keep them persistent ...
   lResult = slRunProc( "[letter]ICF", lReturn1, lReturn2, sBuffer1, sBuffer2, "Eu")
...
   'Application Code
...
   'Unload custom functions when no longer needed
   slRunProc "[letter]", lReturn1, lReturn2, sBuffer1, sBuffer2, "U"
...

L modchar?  If used then does each function still need to be registered?
I am assuming each client must issue "U" to unload their copy of the DLL.

If a client crashes would that also cause the server to need to be unloaded?
CAUTION!! If you fail to unload it then SQLiteningServer will have to be stopped to get it unloaded.







« Last Edit: December 07, 2017, 10:20:39 AM by cj »

cj

  • Master Geek
  • ****
  • Posts: 616
    • View Profile
Re: Doing things in the server instead of in the client.
« Reply #8 on: December 07, 2017, 10:28:26 AM »
Created a new thread on slRunProc  http://sqlitening.com/support/index.php?topic=9691.0

Bern Ertl

  • Master Geek
  • ****
  • Posts: 466
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Doing things in the server instead of in the client.
« Reply #9 on: December 07, 2017, 11:08:32 AM »
cj - I answered your questions in the new thread.  :)

Fim

  • Guru
  • ***
  • Posts: 131
    • View Profile
    • Fims sajt
Re: Doing things in the server instead of in the client.
« Reply #10 on: December 08, 2017, 03:36:31 AM »
Cj,
Se Post #1
/Fim