I ran this and got the following results.
Note this is being done on my laptop which is a Sony VIO P3 933 256 ram.
This is a test database
63480
SET TIME FORMAT HH:MM:SS.SSS
SET VAR vstart = .#TIME
Sel MAX(breakdat) into vtemp from master where custnub2 = '6171'
SET VAR vend = .#TIME
SET VAR velapsed = (.vend - .vstart)
SET TIME FORMAT HH:MM:SS
RETURN
The Following is the structure of the master table and the index's are
working great for our web and in house programs. No slow performance.
Table: MASTER No Lock(s)
No. Column Name Attributes
--- ------------------
------------------------------------------------------
1 DATASOURCE Type : TEXT 4
Index : MULTI-COLUMN
Index : SINGLE-COLUMN
2 POn Type : TEXT 12
Index : SINGLE-COLUMN
3 CUSTNAME Type : TEXT 50
4 DRIVNAME Type : TEXT 35
5 TRACTORn Type : TEXT 10
Index : SINGLE-COLUMN
6 TRAILERn Type : TEXT 10
Index : SINGLE-COLUMN
7 TRACTYR Type : INTEGER
8 TRCTMAKE Type : TEXT 25
No. Column Name Attributes
--- ------------------
------------------------------------------------------
9 BREAKDAT Type : DATE
Index : SINGLE-COLUMN
10 ENGNTYPE Type : TEXT 10
11 VEHCITY Type : TEXT 25
Index : SINGLE-COLUMN
12 VEHSTATE Type : TEXT 2
Index : SINGLE-COLUMN
13 VEHSTRT Type : TEXT 75
14 LOCTDRIV Type : TEXT 40
15 LOCTPHON Type : TEXT 14
16 DRIVTIME Type : REAL
17 TIMEIN Type : REAL
Index : SINGLE-COLUMN
18 TIMETOT Type : REAL
Index : SINGLE-COLUMN
Compute: (timein- drivtime)
No. Column Name Attributes
--- ------------------
------------------------------------------------------
19 vendorn Type : TEXT 8
Index : SINGLE-COLUMN
20 VENDORNM Type : TEXT 40
21 VENDCITY Type : TEXT 25
22 VENDSTAT Type : TEXT 2
23 SPOKEWH Type : TEXT 10
24 VENDINVn Type : TEXT 10
Index : SINGLE-COLUMN
25 ROn Type : TEXT 3
26 DATEPROC Type : DATE
27 signatur Type : TEXT 3
28 CODES Type : INTEGER
Index : SINGLE-COLUMN
29 MEHODPAY Type : TEXT 10
Index : SINGLE-COLUMN
Default: P/O
No. Column Name Attributes
--- ------------------
------------------------------------------------------
30 CDIINV Type : TEXT 10
Index : SINGLE-COLUMN
31 BILLDATE Type : DATE
Index : SINGLE-COLUMN
32 REMEDY Type : TEXT 200
33 CAUSE Type : TEXT 200
34 DISPARTS Type : TEXT 60
35 CURRTIME Type : REAL
36 CUSTPO Type : TEXT 20
Index : SINGLE-COLUMN
37 ETA Type : TEXT 10
Default: 0
38 BILLSTAT Type : TEXT 3
Index : SINGLE-COLUMN
Default: A
No. Column Name Attributes
--- ------------------
------------------------------------------------------
39 REBILLn Type : TEXT 10
Index : SINGLE-COLUMN
40 REBILDT Type : DATE
Index : SINGLE-COLUMN
41 billcom Type : TEXT 400
42 DOORNAM Type : TEXT 20
Index : SINGLE-COLUMN
43 HUB Type : INTEGER
44 VENDCODE Type : TEXT 3
Index : SINGLE-COLUMN
Default: V
45 ORGAMT Type : CURRENCY
46 sign2 Type : TEXT 3
47 APCHECK Type : TEXT 7
Index : SINGLE-COLUMN
No. Column Name Attributes
--- ------------------
------------------------------------------------------
48 APDATE Type : DATE
Index : SINGLE-COLUMN
49 cardname Type : TEXT 20
50 CARDn Type : TEXT 17
51 CURRCUST Type : REAL
52 CUSTCALL Type : REAL
53 DIRECT Type : TEXT 4
54 LOADWHT Type : TEXT 8
55 FINALNM Type : TEXT 25
56 CUSTAPPV Type : TEXT 50
57 CUSTNUB2 Type : TEXT 30
Index : SINGLE-COLUMN
58 TOWNAME Type : TEXT 45
59 TOWPHON Type : TEXT 14
No. Column Name Attributes
--- ------------------
------------------------------------------------------
60 HUB2 Type : TEXT 8
61 TRACVIN Type : TEXT 18
Index : SINGLE-COLUMN
62 TRLVIN Type : TEXT 18
Index : SINGLE-COLUMN
63 REEFER Type : TEXT 15
64 WORKEDON Type : TEXT 8
65 TERMREGION Type : TEXT 5
66 CARDEXP Type : DATE
67 GONOW Type : TEXT 1
68 LEAVE Type : TEXT 20
69 MILES Type : INTEGER
Default: 0
70 HOURS Type : INTEGER
Default: 0
No. Column Name Attributes
--- ------------------
------------------------------------------------------
71 ESTPART Type : CURRENCY
Default: 0.00
72 ESTTIRE Type : CURRENCY
Default: 0.00
73 ESTLABOR Type : CURRENCY
Default: 0.00
74 MILEEST Type : CURRENCY
Default: 0.00
75 ESTSC Type : CURRENCY
Default: 0.00
76 SHOPEST Type : CURRENCY
Default: 0.00
77 NOTAX Type : TEXT 40
78 estmount Type : CURRENCY
Default: 0.00
No. Column Name Attributes
--- ------------------
------------------------------------------------------
79 etaexp Type : CURRENCY
Compute: ((miles* mileest) + esttire+
estpart+ (estlabor* hours) + estsc+
estmount)
80 vendhold Type : TEXT 1
Index : SINGLE-COLUMN
81 HOTLINE Type : TEXT 3
82 RTBDATE Type : DATE
83 RTBSIGN Type : TEXT 3
84 DISPTIME Type : REAL
85 COSTOK Type : TEXT 3
86 credit Type : TEXT 60
87 majorgroup Type : TEXT 8
Index : SINGLE-COLUMN
88 tractor_plate Type : TEXT 14
No. Column Name Attributes
--- ------------------
------------------------------------------------------
89 tractor_state Type : TEXT 2
90 trailer_plate Type : TEXT 14
91 trailer_state Type : TEXT 2
92 vendor_dispatched Type : TEXT 1
93 CreditInvNo Type : TEXT 10
94 CreditInvDate Type : DATE
95 CreditInvAmount Type : CURRENCY
96 TRLYEAR Type : INTEGER
97 TRLMAKE Type : TEXT 25
98 MICHDEAL Type : TEXT 1
99 DRn Type : TEXT 14
Index : SINGLE-COLUMN
100 SCINVn Type : TEXT 10
Index : SINGLE-COLUMN
No. Column Name Attributes
--- ------------------
------------------------------------------------------
101 ACTMILE Type : INTEGER
102 JOBTIME Type : REAL
103 sc_billdate Type : DATE
Index : SINGLE-COLUMN
104 PROBLEM Type : NOTE
105 SourcePO Type : TEXT 16
Index : SINGLE-COLUMN
Compute: (DataSource+ POn)
106 Rolling Type : INTEGER
Index : MULTI-COLUMN
Index : SINGLE-COLUMN
Compute: (ifnull(timein, 0, 1))
107 BreakSeqno Type : INTEGER AUTONUMBER
Index : SINGLE-COLUMN
No. Column Name Attributes
--- ------------------
------------------------------------------------------
108 Year4 Type : INTEGER
Index : SINGLE-COLUMN
Compute: (iyr4(breakdat))
109 Month2 Type : INTEGER
Index : SINGLE-COLUMN
Compute: (IMON(BREAKDAT))
110 TrctTireSize Type : TEXT 12
111 TrlrTireSize Type : TEXT 12
112 bd_advisement Type : TEXT 80
113 gavebilling Type : TEXT 20
114 shopestfaxed Type : TEXT 1
115 estfaxedby Type : TEXT 3
116 VENDOR2 Type : TEXT 8
Index : SINGLE-COLUMN
No. Column Name Attributes
--- ------------------
------------------------------------------------------
117 DRIVER_ERROR Type : TEXT 3
118 WHY_DRERROR Type : TEXT 50
119 NABPO Type : TEXT 20
Index : SINGLE-COLUMN
Current number of rows: 361361
-----Original Message-----
From: Javier Valencia [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 07, 2004 4:48 PM
To: [EMAIL PROTECTED]
Subject: [RBG7-L] - Re: View Speed
Steve:
I just run the following snippet:
SET TIME FORMAT HH:MM:SS.SSS
SET VAR vstart = .#TIME
Sel MAX(tcost_all) into vtemp from wemp_file where slot_no = 'zm-07'
SET VAR vend = .#TIME
SET VAR velapsed = (.vend - .vstart)
SET TIME FORMAT HH:MM:SS
RETURN
velapsed = 150
The file wemp_file has 240,000 records, neither tcost_all nor slot_no have
indices. The entire calculation takes 0.15 seconds (150 milliseconds) on my
local laptop; even if you account for a network it should not take that
long. You may want to rebuild your indices.
Javier,
Javier Valencia, PE
President
Valencia Technology Group, L.L.C.
14315 S. Twilight Ln, Suite #14
Olathe, Kansas 66062-4578
Office (913)829-0888
Fax (913)649-2904
Cell (913)915-3137
================================================
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
delete the material from all system and destroy all copies.
======================================================
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Charles Parks
Sent: Tuesday, December 07, 2004 12:03 PM
To: RBG7-L Mailing List
Subject: [RBG7-L] - Re: View Speed
How about
Create temp view tvMaster (custnub2, Breakdate) as select custnub2,
Breakdate from master where custnub2 = '6171' order by BreakDate Desc
sel max(breakdat) into vTest1 from tvmaster where limit = 1
________________________________
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve
Breen
Posted At: Tuesday, December 07, 2004 11:57 AM
Posted To: RB7-L
Conversation: [RBG7-L] - Re: View Speed
Subject: [RBG7-L] - Re: View Speed
Tried this from the R: prompt as:
set var vTest1 = n/a
Then
sel max(breakdat) into vTest1 from master where custnub2 = '6171'
This works but this is one of our larger customers and it takes 60
seconds or more to pull this data.
Any other ideas.
Thanks
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 07, 2004 12:35 PM
To: [EMAIL PROTECTED]
Subject: [RBG7-L] - Re: View Speed
Sel MAX(Breakdat) into varname from master where Custnub2 =
.vcust
should work much faster, especially if you have an index on
Custnub2. Have an exit EEP from the customer ID field look up
the value as such.
-------------- Original message --------------
Is there any way to speed up the following view or a
different method to be recommended for getting this information. I am
trying to display the date of the last time we handled a customers
business in a form as we enter the customer number in the form and begin
a new record.
(custnub2, Breakdate)
Sel Custnub2, max Breakdat
from master
group by custnub2
Thanks for any help.
Stephen Breen
CDI Services, Inc.
3610 Deahl Court
P.O. Box 3838 South Bend, IN 46628
574-271-2027 Ext: 200
Fax 574-247-4638
Fax: 574-247-4631
Email: [EMAIL PROTECTED]
Web Site www.911roadrepair.com
<http://www.911roadrepair.com/>
Pager 800-668-8023
Cell 574-339-2356
