Hello all,
Continuing my problem regarding connection to MSSQL, here is the latest
progress (and question) about my effort to find the solution.
Because my thread 'A lot of lockings and connections to SQL Server. Is
it normal or bad?' in this ng didn't get any reply, I decided to cross
post in microsoft.public.sqlserver.programming (I put the discussion in
this post for your convenience in case you're willing to read). Anyway
after giving more thought about the problem, here is what I found: I
found out that each time I run any form in my apps (the form is child
window, in-top-level, private-data-session), after I close the form,
there were two or three external connections still intact. I can easily
'kill' those external connections by running following break.prg.
*!* Break.PRG
ON ERROR *
FOR i = 1 TO 200
SQLDISCONNECT(i)
ENDFOR
ON ERROR
*!* end of break.prg
Regardless whether I kill those connections or not, after I run another
form, there would be other two or three external connections added (and
other locking as well - although I don't understand what kind of locking
MSSQL do). All connections belong to one apps would be immediatelly
close if I closed the apps.
Yes, I know that this would be my own fault and I should learn more.
That's why i already purchase a copy of Client Server with VFP6, and
will certainly read the book cover-to-cover. Anyway, what I need now is
a quick-and-dirty fix to my problem. What I have in mind is to
explicitly close the connection with SQLDISCONNECT( ) on the Destroy
event of each form. I can easily put that in my baseclass form and it
will affect all forms in my apps.
Problem:
Anybody knows how to get (and to traverse) all cursors (remote views and
cursor from SPT) in current (active) data session? I cannot run
break.prg on Destroy event, because that would kill other connections
needed by other window.
I'm looking forward to hearing from you!
TIA,
Willianto
Following is the discussion I mentioned above:
My original post:
---------------------------------------------------------
Hi all,
First of all:
*** HAPPY NEW YEAR TO YOU ALL!!! :) ***
then, here it goes; I've downloaded SQL Spy 6.0 from Hybridx (got it
from http://files.webattack.com/localdl834/sql_spy_setup.zip). It's a
freeware tools to monitor SQL Server activity. I use this tool to watch
how my apps (created with Microsoft Visual FoxPro 8.0 SP1, connect with
SQL Server thru ODBC) interact with SQL Server. I found out that on some
parts of my apps (where there are lots
of traverse and calculation), the number of locks on the SQL Server
could go up to 700 and the external connection could go up to 15 or 20.
And I tested that on my box (that means; I run my apps on the server and
no workstation connect to the server).
Whilst the apps so far running fine, it's never been really tested to
the max. The traffic are quite low and the concurrent user has never
exceed eight users. Yet, looking at the numbers, I'm afraid that there
something that I don't do effectively.
My Questions:
1. Is the number normal?
2. Anybody here can give me advices on C/S programming, I mean just a
quick thumb rules what should and what, like should I make a connection,
get the data, and disconnect immediately. Or should I share all SPT in
one connection?, or any other rule what should and shouldn't?
Thanks in advance
Willianto
======================================
Following is the reply from Louis
---------------------------------------------------------
This is the open question of all times. Without looking at the code,
the
best that can be said is that you might be fine, or you might have
written a
terrible app (something we all have done at one time or another :)
Lots of
locks are fine, as long as you don't have lots of users contending for
those
locks. You say you have 8 users, is that all you are planning for? If
everyone is happy, it might be fine.
Bottom line is that you need to get some literature and read up on
design,
internals, optimization, etc before you can decide if your application
is
well written, and experience is a must. Without being able to magically
aquire this (and it takes a while and sadly we learn from failure!) I
would
suggest that you take this statement:
> Whilst the apps so far running fine, it's never been really tested to
> the max.
Define what max means and test for it. If it works fine under max
conditions, then your application is fine.
======================================
And my reply again:
---------------------------------------------------------
Hi Louis:
Thanks for your reply.
> best that can be said is that you might be fine, or you might have
> written a terrible app (something we all have done at one time or
> another :) Lots of .. [skipped]
Looks like my case is the second one :(
As I stated in my post, the application runs fine, but it runs with a
very minimum load. That's because the company using my apps is a
machinery trading company. The activity is very low. To give you a clue
how 'low' is that; they only have to create less than 10 invoices a day.
Actually, that's the best numbers they got for years (that means less
than ten sales a day - but, hey, the profit of selling one machine could
cover three months expenditure!). Anyway, what I did is I backup the
database, call up some of my fellow, line up eight computers, and
massively attack the system with all the apps feature (purchasing,
receive goods, stock preview, stock opname, delivery order, customer
order, etc...). After an hour we manage to hang the server which by
then, got about 350,000 locking and 80,000 external connections :(
So, using SQL Server as a backend is not _that_ easy... Oh well, at
least I know for sure that the problem won't appear in my client for a
near future.
Regards,
Willianto