> >
> > Lyrad,
> >
> > If you are using VFP to access a VFP backend stored procedure, you do not need the SQLEXEC() function. Try this instead
> >
> >
> > IF NOT DBUSED("MyDBC")
> > OPEN DATABASE "Path2\MyDBC.DBC"
> > ENDIF
> >
> > lnResult = MyStoredProcedureOrFunction()
> >
> > Ken
> > You shall know the truth - and the truth shall set you free. (John 8:33)
>
> Thank you Ken, I raised this question before here in foxite and I got this the same answer. What I am trying achieve here is, If it will make the processes run faster using stored procedure, will it make our system run faster? In our newly made system (Using stored Procedure) we already done this using MySQL as our backend and it made alot difference. but in our old system (which uses remote views) were trying to run it more a bit faster in term of processes since we used native VFP Database as our backend.
>
> Anyway, I just want to know if i will be using other PL (for example: Visual Basic) as my front-end and native VFP Database as my back-end is calling of stored procedure is possible? if this possible, how?
>
> Thanks again in advance.
>
>
>
Fox Blood Live in Us...FOXITE is everything under the FoxWorld!In a VFP backend, it probably will not be much faster to use a stored proc. In a SQL backend it will be a LOT faster to use a stored proc. Why is this? The VFP backend is a "file server" type of back end. All it really does is "store things." SQL is a bit different - it gives you a "client server" type of back end. It actually does some of the processing. When you run a VFP stored procedure, that procedure runs on the workstation - not the server. Lets assume that your procedure calls a SELECT statement like:
SELECT ... FROM SomeTable WHERE SomeField == SomeValue
that SomeField == SomeValue condition is evaluated at the workstation. Lets assume the worst case scenario. You do not have any indexes on SomeField so VFP needs to ship each record in SomeTable across the network to the workstation. At the workstation, VFP looks at SomeField and compares it to SomeValue. Obviously, if this is a large table, it is going to take a great deal of time, and the limiting factor will be your network speed. Even if you have a fairly fast network, shipping data across the network is going to be the slowest part of the process. OK - nobody is going to run a query over a large table without the appropriate indexes in place. VFP's Rushmore optimization uses indexes to evaluate that WHERE condition, so even with the right kind of indexes in place, you still have to ship the entire index across the network. MUCH faster than shipping the entire table across the network, but compared to a client server situation still slow.
In a client server situation, you call that stored procedure and it is executed AT THE SERVER. When you issue your SQLEXEC(), you are simply passing a short string across the network (very quick.) Now, when the string gets to the server, it is parsed out as an SQL function call which is executed at the server. Lets assume that your stored proc returns a single integer. When the proc is complete, it ships that integer (4 bytes) back across the network.
Compare this to the VFP backend where you have to ship the entire index across the network. Even the smallest index over a logical field would be 4 bytes (plus one bit) per record. If you have 10,000 records in a VFP backend table (fairly small) then you need to ship about 40KB worth of index across the network. With an SQL backend, your network traffic is reduced to about 34 bytes. This is where the speed is coming from.
Hope that helps.
Ken
You shall know the truth - and the truth shall set you free. (John 8:33)