My suggestion here would be to change custnub2 into an integer, or create a computed col custnub3 which converts it into an integer and index that. An index on text 30 is not very efficient.
Troy -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Breen Sent: Tuesday, December 07, 2004 3:57 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: View Speed 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
