Hi Gene
To get started on creating the stored procedures you can right click on the
"Search Action" that you are using and then slect SQL Query.
Copy and paste that into your Direct to Database Action or into a SQL query
window to test the query.
Also when I want to use a Direct to Database Action rather than a stored
procedure I assign the SQL to a vaiable:
<@ASSIGN NAME="sql_contactDD" VALUE='SELECT
v1.id_de300orgdep,v1.de300name,v1.id_de450role,v1.de450name,v1.id_ct100cont,
v1.ct100nmlast,v1.ct100nmfirst,v1.id_ct125role,v1.ct100dob,v1.id_re100rq
FROM physio.v_ct100_de400 v1
WHERE (v1.id_de100org=<@VAR v_Dept_de100 SCOPE=Request> AND
v1.id_ct100cont=<@VAR contactID SCOPE=Request>)
ORDER BY 1 ASC,6 ASC' Scope="Request">
_______________or a more comples sample______________
<@ASSIGN NAME="sql_contactDD" VALUE='SELECT DISTINCT
v1.id_de300orgdep,v1.id_de450role,v1.id_ct100cont,v1.ct100nmlast,v1.ct100nmf
irst,v1.ct100dob,v1.de450name
FROM physio.v_ct100_de400 v1,physio.ct100 c2
WHERE (c2.id_ct100cont=v1.id_ct100cont)
<@IF EXPR="(<@ARG range>>=1)"><@! COMMENT="range = org or dept">
AND (v1.id_de100org=<@VAR v_Dept_de100 SCOPE=Request>)
</@IF>
<@! COMMENT="if search by ORG then range=1 and is not = to2
BUT if search by dept then range=2 which is = to 2 and gretaer than 1">
<@IF EXPR="(<@ARG range>=2)"><@! COMMENT="range = more narrow by dept">
AND (v1.id_de300orgdep=<@VAR v_Dept_de300 SCOPE=Request>)
</@IF>
<@! COMMENT="name,contactID,phone">
<@IF EXPR="('<@LENGTH STR="<@VAR sql_criteria SCOPE=Request>">'>=1)">
AND <@VAR sql_criteria SCOPE=Request>
</@IF>
<@! COMMENT="de450 is role">
<@IF EXPR="('<@LENGTH STR="<@VAR sql_criteria_de450
SCOPE=Request>">'>=1)AND('<@ARG searchde450>'>=1)">
AND <@VAR sql_criteria_de450 SCOPE=Request>
</@IF>
ORDER BY v1.ct100nmlast ASC,v1.ct100nmfirst ASC' Scope="Request">
This way I can even LOOP thru results from previous arrays to creat the new
SQL!
<@ASSIGN NAME="sql_getRE400_PA_group" VALUE="SELECT id_pa100authztn,
MAX(re400startdate) AS re400startdate
FROM physio.re400
WHERE (id_rp350profileid IS NOT NULL) AND (id_ct100cont =
@@Request$v_pracid)
GROUP BY id_pa100authztn
HAVING (MAX(re400startdate) < CONVERT(DATETIME, '<@VAR
v_datestring_y_m_d SCOPE=Request> 00:00:00', 102))
AND(
<@ROWS START=1 STEP=1 STOP=<@VAR nr_pa100id SCOPE=Request>
ARRAY="a_pa100id">
(id_pa100authztn = <@COL NUM="1">)
<@IF EXPR='(<@CURROW> <<@VAR nr_pa100id SCOPE=Request>)' TRUE='OR'
FALSE=''>
</@ROWS>
)
ORDER BY MAX(DISTINCT id_rp350profileid) DESC, MAX(DISTINCT re400startdate)
DESC" SCOPE="Request">
Then in the DDB just use the VAR: <@VAR sql_contactDD SCOPE=Request>
I have a lot of DDB like this on Microsoft SQL and the speeds run about 40
miliseconds to process.
I prefer to use stored procedures but sometimes cant beat a DDB.
Janet Case
_____
From: Wolf, Gene [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 28, 2007 8:50 AM
To: witango-talk@witango.com
Subject: Witango-Talk: Very puzzling...
I have a situation here that I find very puzzling. So I am going back to
the experts for advice.
We have a few applications that, by their nature, perform thousands of
requests against a SQL database and return thousands of records. I am quite
sure that the coding of the application isn't as good as it could be and we
will look into rewriting it. Here's our issue: This program, from start to
the time it displays the returning page will take about 3 minutes. (stop
groaning). We are testing this same application on a new box containing 8
dual core cpu's. I would expect one of those cpus to be pegged while Witango
rips through these thousands of requests. Just the opposite. The cpus
indicate hardly anything happening on the box. The same is true of SQL
server. Even though I am literally throwing thousands of requests at SQL
2005 I'm seeing virtually no cpu activity on that box either.
It looks almost like Witango is waiting for something, processes a
request, waits a bit more, processes the next request, waits again, etc. The
environment is Windows Server 2003. One Box is running Witango 5.5 with 8
dual core processors and the other box is Windows Server 2003 running SQL
Server 2005. Yes, this program is coded and does some looping but I would
still expect it to run one heck of a lot faster than it is.
Any suggestions?
Gene Wolf
Supervisor, Business Systems
DRS Sensors & Targeting Systems-Optronics
2330 Commerce Park Drive NE
Palm Bay, Florida 32905
Phone: 321-309-0685
321-309-0202 (fax)
Dictionary.com Word of the Day
<http://dictionary.reference.com/wordoftheday/>
http://dictionary.reference.com/wordoftheday/
This e-mail, including any attached files, may contain confidential and
privileged information for the sole use of the intended recipient. Any
review, use, distribution, or disclosure by others is strictly prohibited.
If you are not the intended recipient (or authorized to receive information
for the intended recipient), please contact the sender by reply e-mail and
delete all copies of this message.
"This (document/presentation) may contain technical data as defined in the
International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of
this material is restricted by the Arms Export Control Act (22 U.S.C. 2751
et seq.) and may not be exported to foreign persons without prior written
approval from the U.S. Department of State."
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
