SQL Seek.. a thought / feedback please

[EzLogic]

VFP 9 SP2
SQL server 2012 (express) backend.
Very new to SQL integration (though I know little of sql)

Little background:

App written in vfp, currently uses vfp backend data.
I am in the process of re-writing the app so it will use sql back end instead.
I am using a modified sql wrapper from west-wind. Very nice class, works well.

In many of my forms or prgs, i tend to use seek() to find certain values before other codes executes.

So, I wrote this little SQLSeek() method that, for now, seems like its giving me what i want.

FUNCTION SQLSeek(tcField,tcValue,tcTable)
	LOCAL lcCursor, lnCount, lcSQL
	lcCursor = SYS(2015)
	
	IF PCOUNT()< 3
		MESSAGEBOX("Error in SQL Seek Method." + CHR(13) + ;
				   "Method requires 3 parameters.",16,"SQL Seek Error")
		RETURN .f.
	ENDIF
	
	
	IF TYPE('tcValue') = 'C'
		tcValue = [']+tcValue+[']
	ENDIF 
	
	IF TYPE('loSQL') <> "O"
		loSQL = createfactory("EzCellSQL")
	ENDIF 
	
	TEXT TO lcSQL TEXTMERGE NOSHOW 
		SELECT TOP 1 <<tcField>> as RecFound FROM <<tcTable>> where <<tcField>> = <<tcValue>> order BY <<tcField>>
	ENDTEXT 
	losql.execute(lcsql,lcCursor)
	IF !empty(losql.cErrorMsg)
		MESSAGEBOX("Error in SQL Seek Method." + CHR(13) + ;
				   "Error: " + loSQL.cErrorMsg,16,"SQL Seek Error")
		RETURN .f.
	ENDIF
	
	IF USED(lcCursor)
		SELECT (lcCursor)
		LOCATE 
		lnCount = RECCOUNT(lcCursor)
		USE IN SELECT(lcCursor)
		RETURN IIF(lnCount>0,.t.,.f.)
	ELSE
		RETURN .f.
	ENDIF 
		
ENDFUNC

My question:
How do you normally handle the equivalent of seek() in vfp, but, now in sql?
is this a bad way of "seeking"? what is the file table was really large now?



Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Olaf Doschke]

SQL Server also optimizes queries with indexes, the overhead just is determining which one. In very short, my advice is, to not select TOP 1, but COUNT(1). TOP in general needs an additional index to order records to determine the top one and if an index for ordering isn't available would need to query the whole group of records to sort them anyway.

More important though, you should perhaps change xbase ways to queries overall and not solve the atomic problem of emulating a SEEK, but replace xbase loops with a single query, for example.

Other things I would change sum up to:

Function SQLSeek(tcField,tvValue,tcTable)
   Private nCount
   Local lcCursor, lnCount, lcSQL, loSQL, llFound, lcMessage
   lcCursor = Sys(2015)

   If Pcount()< 3
      lcMessage = "Error in SQL Seek Method. 3 parameters required."
      Debugout lcMessage
      Error lcMessage
   EndIf
   
   If NOT Vartype(tcField)+Vartype(tcTable)=="CC"
      lcMessage = "Error in SQL Seek Method. Wrong parameter types for table and/or field."
      Debugout lcMessage
      Error lcMessage
   EndIf 

   loSQL = createfactory("EzCellSQL")

   lcSQL=Textmerge("SELECT Count(1) as nCount FROM <<tcTable>> Where <<tcField>> = ?tvValue")
   loSQL.execute(lcSQL,lcCursor)
   If !Empty(loSQL.cErrorMsg) && could perhaps also be handled in the EzCellSQL class
      lcMessage = "Error in SQL Seek Method:"+loSQL.cErrorMsg
      Debugout lcMessage
      Error lcMessage
   Endif

   If Used(lcCursor)
      Select (lcCursor)
      llFound = (nCount = 1)
      Use In Select(lcCursor)
   Else
      lcMessage = Textmerge("Error in SQL Seek Method. No result for <<tcTable>>.<<tcField>>")
      Debugout lcMessage
      Error lcMessage
   EndIf 

   Return llFound
Endfunc

In regard of creating the loSQL object, your way would recreate it always anyway, as private vars are released after the method executes, the same way as local vars are released. The only way aside of a global loSQL to have a more permanent loSQL object is to let the cratefactory() function store it for more than one usage and return the object reference instead of creating a new instance. It would be good to put the decision about one-time vs. permanent instanciation into the factory, eg the factory could store objects of classes, which could be used public into a collection for future reference and only use CREATEOBJECT(), if such an object is not in the collection of instanciated classes, the same way a formhandler may only show an already started form instead of starting a new one. The factory then only needs to know which classes are good for global usage, and which not. Eg the a class for global (re)use should rather not be bound to a certain data session or any other reason against it's public use.

Aside of that see

http://blog.sqlauthority.com/2009/0...uction-to-force-index-query-hints-index-hint/

Bye, Olaf.

 

[Olaf Doschke]

By the way, to get the same Seek result as VFP, you would need to consider SET("EXACT") and do a LIKE query.

And in regard to solving the overall need for SEEK as SQL equivalent: I mostly don't need it, I do a query to give me a result, and if that result is empty, I know a key does not exist, for example.

Bye, Olaf.

 

[Mike Lewis]

Ali,

If I've understood it right, your aim is to return .T, if one or more records are found that meet the WHERE condition, otherwise .F.

If that's right, I would do it with a SQL command similar to this:

SELECT 1 AS RecFound WHERE EXISTS(SELECT <<tcField>> FROM <<tcTable>> where <<tcField>> = <<tcValue>>)

This will give you a single-record cursor if the condition is met, otherwise an empty cursor. I think that would be more efficient on the server side than using a TOP N and ORDER BY (Olaf will tell me if I'm wrong about that). It would also mean that the smallest possible result set would be returned over the network.

By the way, if you do decide to keep the TOP N clause, note that this does not require an ORDER BY clause, unlike in VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

If you google this question you find both EXISTS and COUNT(1) to quit at finding a first record. If TOP 1 can be used without ORDER BY it might be ok, too, if you don't query the field data itself, because depending on the field type this could be much load, as in memos or blobs. Nothing you would normally SEEK anyway.

But indeed TOP is not advised.

Bye, Olaf.

 

[Olaf Doschke]

Hm, I found a reference saying Count(1) would only result in either 0 or 1 (and stop at 1), but other references say it's exactly like Count(*) or Count(expr) with any expression different from NULL. Then my code sample would be wrong, unless the value is unique in the table.

Mikes query or a TOP 1 Cast(1 as int) as nCount without ORDER BY might both be equal in performance, or the HINT clause to enforce usage of an index.

Bye, Olaf.

 

[JRB-Bldr]

Just a suggestion....

Rather than writing individual Functions for specific 'backend' operations (such as SEEK()), you might consider writing a more general VFP Function that will execute whatever you pass to it. Basically a general VFP Function that does a SQL Pass-thru but, if execution is successful, returning the results in a ReadWrite Cursor.

Something like:

cSQLCmd = "SELECT * FROM MySQLTable WHERE CustID = 'ABC'"
lRet = SQLDataAction("ThisSQLDatabase",cSQLCmd,'ResultCursor')
IF lRet
   SELECT ResultCursor  && Now you have a VFP Cursor with the resultant record set.
   <do whatever>
ENDIF

Within the VFP Function SQLDataAction() you could have various SQL Connection strings to connect to various SQL Databases if your data tables happened to be distributed across multiple ones.

Then that single general VFP Function FireSQL() could be used in any way to support your VFP needs.
* UPDATE MySQLTable SET <whatever> WHERE <whatever>
* SELECT * FROM MySQLTable WHERE <whatever>
* DELETE FROM MySQLTable WHERE <whatever>
* etc.

And when you need to do actions on multiple records in the SQL tables I have found that instead of running this multiple times, I can merely create a single VFP Array that holds multiple SQL Command lines and pass it once - something like:

DIMENSION arySQLCmd(1)
arySQLCmd(1) = ''
* --- Populate Array With Multiple SQL Commands (perhaps UPDATE's or DELETE's)---
FOR CmdCntr = 1 to <whatever>
   cThisSQLCmd = <whatever>
   arySQLCmd(1) = ALLTRIM(arySQLCmd(1));
      + CHR(13);
      + cThisSQLCmd
ENDFOR
* --- Execute the Multiple SQL Commands Within the Array ---
lRet = SQLDataAction("ThisSQLDatabase",arySQLCmd(1))  && Note Result Cursor Is Optional
IF lRet
   <do whatever>
ENDIF

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Another point about your function, Ali - although nothing to do with SQL.

You are using a MESSAGEBOX() to display details of programming errors. I feel this is wrong. If the error occurs in production, the message will be seen by the end user, who will not have a clue what it means, and will likely feel intimated or worried by it. Worse, after displaying the error, your program will continue to run, even though it is in an "error condition". Also, you (the programmer) will have no way of knowing the error occurred, unless the user happens to mention it.

I suggest that, instead of using MESSAGEBOX(), you issue an ERROR command. This has the advantage of calling your custom error-handler, which (one would hope) produces some sort of error log for the programmer's benefit, displays a message that will mean something to the user, and then closes the application safely.

I can understand that you have inserted these MESSAGEBBOX()s for your own benefit during development. But, in that case, ERROR is still a better way of doing it, because, even if you don't have an error-handler in force, it will invoke VFP's Cancel / Suspend / Ignore dialogue, thus giving you quick access to the debugger.

An alternative approach is to use ASSERT. After you have finished testing, you mereley have to issue SET ASSERT OFF, which means you can leave the debugging code in place, safe in the knowledge that an end-user will never see it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

JRB-Bldr,

it seems the "EzCellSQL" class is already exactly that, the SQL query is passsed to it's execute() method and I assume the class already encapsulates connecting to the server, error handling (we at least see there is a cErrorMsg property maintained by it) and more.

I also assume the createfactory("EzCellSQL") call creates it only once (per datasession) to reuse a connection. There are many ways to do that, too.

Bye, Olaf.

 

[Olaf Doschke]

Mike, I agree.

My version of this contains DEBUGOUT and ERROR commands for that very reason.

Bye, Olaf.

 

[EzLogic]

Thanks guys.

yes olaf ezcellsql class handles all that and it handles connections, errors sql parameters, stored procedures etc..

losql is global. I didn't mean it robe local in this func

mike, yes those msgboxes were for me

i will try the sql query and will see how it works

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Olaf Doschke]

>losql is global. I didn't mean it robe local in this func

Well, you know conventionally it would be goSQL, then, but on the other side too much public vars only may lead to collisions with further libraries.

You did

	IF TYPE('loSQL') <> "O"
		loSQL = createfactory("EzCellSQL")
	ENDIF

In case loSQL does not preexist, you create a private variable this way, not a global.

What I have done to not use global vars is maintaining an object reference collection in a factory object, of which I return items when requested and already instancited. As you have the createfactory() function, that could also work with a local loSQL, which nontheless is of course not recreated for each single seek, as you most likely also won't want to reconnect every single time.

as in pseudo code

procedure createfactory(tcClass)
* prerequisites: a global goInstances collection (in my case rather a sub object of goApp)
Local loObject
Try 
   loObject = goInstances.item(tcClass)
Catch
   loObject = CreateObject(tcClass)
   goInstances.Add(loObject, tcClass)
Endtry 
Return loObject

Yes you could use a global variable, but then it must be predefined, and your catch of the variable not existing should then be fixed to

	IF TYPE('loSQL') <> "O"
                PULBIC loSQL
		loSQL = createfactory("EzCellSQL")
	ENDIF

Otherwise you create a private loSQL variable, which is released in the same way as a local var, when the method finishes - remember the scope of a private variable ends when the execution stack level is left, not when the current object ends, private vars are not having the scope/lifetime as properties - and your seek will be slow as you always recreate an loSQL instance.

Bye, Olaf.

 

[EzLogic]

Thanks guys.

my SQL table is indexed on the fields that I usually want to seek.

I did a quick speed test.
thought the table is not that large yet, but, i have real data in it: 109k records

** Mike: 0.011 secs
** Olaf: 0.017 secs
** Mine: 0.018 secs


Seems like Mike's Query is the fastest.
When i tested it the speed, i made sure i completely quit VFP and ran it to make sure the data or the query was not cashed.

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Mike Lewis]

Seems like Mike's Query is the fastest.

Good. But keep in mind that my query only returns an indication of whether the record exists. It doesn't actually return the record. Is that what you want?

Also, I have to say that I agree with JRB about not having individual functions for specific purposes. Better to put the VFP-specific commands and functions (such as Seek) out of your mind, and try to focus instead on the language that is specific to the back end.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

it seems the "EzCellSQL" class is already exactly that

Olaf - thanks for the input.

I have never even heard of the "EzCellSQL" class.

Thanks,
JRB-Bldr

 

[EzLogic]

jrbbldr,

I have written my own EzCelLSQL class, which is a SQL wrapper that handles connection, execution, calling stored procedures, bulkxml upload, error handling etc..

It's my own wrapper business object wrapper class, in one place.

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[EzLogic]

it's based on the original wwsql class from west-wind btw, i am not the original author of the wwSQL, Rick Stahl is and a friend of mine, Paul Mrozowski.

just for the record.

i merely wrote some functions and methods and subclassed his class, and i call it 'EzCellSQL' (as my app is called EzCell ERP)

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Olaf Doschke]

I get this with execution times lower than a millisecond (seconds() value before/after does not change) from a LAN server from a table with 450k records using any query.

In regard to the cache effect the sql server itself would be more relevant than restarting VFP or even your whole client computer.

In some cases the TOP query returns slower, measurable, so indeed I can also vote against TOP, even without the ORDER BY, but the execution plan shows all three queries have their cost in an index seek only:

The EXISTS query looks most complicated with it's Semi Join, but the cost (Kosten) are at the Index Seek only, too.

Bye, Olaf.

 

[Olaf Doschke]

JRB-Builder,

"I have never even heard of the "EzCellSQL" class."

just read the inital source code, loSQL = createfactory("EzCellSQL") comes from there. I also didn't know what this clas is, I just concluded it from the whole code, as there is no connection made etc, and the query was passed to loSQL, this must be such a class.

Your idea of executing an array of queries is fine, though, I often simply pass in a query string which in itself is a script with multiple queries, but that's limited to one result anyway, and passing in an array with queries and reuslt names would be fine in some cases, too.

Bye, Olaf.

 

[Olaf Doschke]

Now I see one can only view the screenshot I posted, when logged in to my G+ account, I'll repost this picture:

Bye, Olaf.