Forgot to include this to the list...
Also, how does available RAM affect performance? What kind of RAM/hardware
is recommend for the number of records I'm dealing with (up to a million in
our metrics table)?
Many thanks,
David
---------------------- Forwarded by David Hay/Lex/Lexmark on 10/06/2003
11:22 AM ---------------------------
David Hay
10/06/2003 10:45 AM
To: "Becker, Holger" <[EMAIL PROTECTED]>
cc:
Subject: RE: Query taking LOADS of extra room AND TIME (Document link:
David Hay)
Hi Holger,
Thanks for the suggestion - I'd already done that, tried it again, and it
hasn't improved anything.
Using Explain, it seems that whenever I add a SUM(x) it causes the pages
required to jump alarmingly.
But even just doing just a join without a sum such as:
explain SELECT Forms.Title, Kiosks.Name FROM Metrics INNER JOIN Kiosks ON
Metrics.Kiosk_Id = Kiosks.Id INNER JOIN Forms ON Metrics.Form_Id = Forms.Id
gives me a page count of
KIOSK METRICS METRICS_KIOSKID_FORMID_EXECTIME INDEX SCAN
42317 *
KIOSK FORMS FORMS_FORMS_ID_AVAILABLE JOIN VIA RANGE OF MULTIPLE INDEXED
COL. 1
ID (USED INDEX COLUMN)
KIOSK KIOSKS ID JOIN VIA KEY COLUMN
1
KIOSK RESULT IS COPIED , COSTVALUE
IS 34671
--------------------------------------------------------------------------------------------------------
34671! That's 270MB right?!!!
The main concern though, is SPEED, or lack of it. The three joins below are taking
upwards of 20 minutes to run. That is crazy compared to 40
seconds by the "other" database.
Please can you let me know what kind of performance we can expect for a join of a
million records of forms printed, with say 80 locations and 200
forms. The total for forms printed at each location for the relevant time period must
be calculated.
We are pretty desperate at this point, and not sure which direction to go on. My
understanding was that SAP could cope with much greater work load
than this?
By the way, does having multiple data volumes make any difference?
Pleae help!!!
Thanks,
David
"Becker, Holger" <[EMAIL PROTECTED]> on 10/06/2003 06:05:33 AM
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>,
[EMAIL PROTECTED]
cc:
Subject: RE: Query taking LOADS of extra room AND TIME
[EMAIL PROTECTED] wrote:
> I am really struggling to get some complicated queries to run quicker
> (please see note below too).
>
> I have a million records in the Metrics table, just one in the Kiosks
> table, and one in the Forms table.
>
> I then run three queries as follows to generate a report:
>
> //get the locations for this report
> SELECT DISTINCT Kiosks.Name, UPPER(Kiosks.Name)
> FROM Kiosks INNER JOIN Metrics ON Kiosks.ID=Metrics.Kiosk_Id
> WHERE ExecTime IS NOT NULL AND Copies IS NOT NULL
> ORDER BY UPPER(Kiosks.Name)
>
> With just one record in the Kiosks table (normally there
> would be more) I then execute:
>
> //get data for this/these locations
> SELECT Forms.Title AS Form, SUM(CASE WHEN
> Kiosks.Name='bodie-macon 2' THEN
> Metrics.Copies ELSE 0 END) AS "bodie-macon 2",
> SUM(Metrics.Copies) AS Total
> FROM Metrics INNER JOIN Kiosks ON Metrics.Kiosk_Id =
> Kiosks.Id INNER JOIN
> Forms ON Metrics.Form_Id = Forms.Id
> WHERE ExecTime IS NOT NULL AND Copies IS NOT NULL
> GROUP BY Forms.Title
> ORDER BY UPPER(Forms.Title)
>
> I then get the totals using:
>
> SELECT 'Total', SUM(CASE WHEN Kiosks.Name='bodie-macon 2' THEN
> Metrics.Copies ELSE 0 END) AS "bodie-macon 2",
> SUM(Metrics.Copies) as Total
> FROM Kiosks INNER JOIN Metrics ON Kiosks.ID=Metrics.Kiosk_Id
> WHERE ExecTime IS NOT NULL AND Copies IS NOT NULL
>
> When I do this, the tempory space used is approximately half
> a GIG! And it
> takes about 20 minutes to finish!
>
> On another highly-controversion-db-at-the-moment it takes
> about 40 seconds!
>
> So, what am I doing wrong? I have indexes where I can put
> them, I believe,
> and apart from adding extra columns TITLEUPPER and NAMEUPPER
> I can't see
> how to improve things. Even with these columns, my tests weren't much
> better. Surely this is not the performance I can expect?
>
> PLEASE HELP!!!
>
> Details of tables, indexes, explain on above statements, and
> db init file
> are below. Please let me know if you need any other info.
>
> Many thanks,
>
> David
>
> Explain for 1st query:
> --------------------------------------------------------------
> ------------------------------------------------------
> KIOSK METRICS TABLE SCAN 42317
> KIOSK KIOSKS ID JOIN VIA KEY COLUMN 1
> KIOSK RESULT IS COPIED , COSTVALUE IS 86490
> --------------------------------------------------------------
> ------------------------------------------------------
>
>
> Explain for 2nd query:
> --------------------------------------------------------------
> ------------------------------------------------------
> KIOSK METRICS TABLE
> SCAN 42317
> KIOSK KIOSKS ID JOIN
> VIA KEY COLUMN 1
> KIOSK FORMS FORMS_FORMS_ID_AVAILABLE JOIN
> VIA RANGE OF MULTIPLE INDEXED COL. 1
> ID
> (USED INDEX COLUMN)
> INTERNAL TEMPORARY RESULT TABLE
> SCAN 500
> INTERNAL TEMPORARY RESULT TABLE
> SCAN 500
> KIOSK
> RESULT IS COPIED , COSTVALUE IS 29427
> --------------------------------------------------------------
> ------------------------------------------------------
>
> Explain for 3rd query:
> --------------------------------------------------------------
> ------------------------------------------------------
> KIOSK METRICS TABLE SCAN
> 10527
> KIOSK KIOSKS ID JOIN VIA KEY COLUMN
> 1
> INTERNAL TEMPORARY RESULT TABLE SCAN
> 500
> KIOSK RESULT IS COPIED ,
> COSTVALUE IS 32471
> --------------------------------------------------------------
> ------------------------------------------------------
>
> Database init file
> --------------------------------------------------------------
> ------------------------------------------------------
> param_startsession
> param_init
> param_put RESTART_SHUTDOWN AUTO
> param_put MAXUSERTASKS 50
> param_put MAXDATADEVSPACES 256
> param_put SESSION_TIMEOUT 0
> param_checkall
> param_commitsession
> param_addvolume 1 LOG LOG_001 F 2050
> param_addvolume 1 DATA DAT_001 F 8192
> db_admin
> util_connect
> util_execute INIT CONFIG
> util_execute set log auto overwrite on
> util_activate kiosk,kiosk
> util_release
> load_systab -ud kiosk
> --------------------------------------------------------------
> ------------------------------------------------------
>
> Table def's:
> CREATE TABLE accounts (Id INTEGER NOT NULL, name VARCHAR(80),
> password VARCHAR(80),PRIMARY KEY (Id))
> //
> CREATE SEQUENCE ACCOUNTS_IDSEQ START WITH 1 INCREMENT BY 1
> NOMAXVALUE CACHE 10
> //
> INSERT INTO accounts VALUES (ACCOUNTS_IDSEQ.NEXTVAL, 'admin','admin')
> //
> CREATE TABLE categories (Id INTEGER NOT NULL, Name
> VARCHAR(80), PRIMARY KEY (Id))
> //
> CREATE SEQUENCE CATEGORIES_IDSEQ START WITH 1 INCREMENT BY 1
> NOMAXVALUE CACHE 10
> //
> CREATE TABLE categorykiosk (Category_Id INTEGER, Kiosk_Id INTEGER)
> //
> CREATE INDEX categorykiosk_Category_Id ON categorykiosk
> (Category_Id, Kiosk_Id)
> //
> CREATE INDEX categorykiosk_Kiosk_Id ON categorykiosk
> (Kiosk_Id, Category_Id)
> //
> CREATE TABLE defaultsettings (Num_Days VARCHAR(10),
> Resolution VARCHAR(50), Orientation VARCHAR(50), Color_Model
> VARCHAR(50),Collate
> VARCHAR(50),Duplex VARCHAR(50),Finishing
> VARCHAR(50),Paper_Size VARCHAR(50), Paper_Source
> VARCHAR(50),Max_Copies INTEGER,Economode
> VARCHAR(50),Macon_Title VARCHAR(15),PrintScript_Id
> VARCHAR(10),AutoFillScript_Id VARCHAR(10),Prints VARCHAR(10))
> //
> INSERT INTO defaultsettings
> VALUES('7','300','Portrait','CMYK','On','Off','None','Printer
> Default','Printer Default',10,'Off', 'Forms', '1', '2',
> 'neither')
> //
> CREATE TABLE formcategory (Form_Id INTEGER, Category_Id INTEGER)
> //
> CREATE INDEX formcategory_Form_Id ON formcategory (Form_Id,
> Category_Id)
> //
> CREATE INDEX formcategory_Category_Id ON formcategory
> (Category_Id, Form_Id)
> //
> CREATE TABLE forms (Id INTEGER NOT NULL, Title VARCHAR(83),
> Number VARCHAR(40) NOT NULL, Revision_Date TIMESTAMP,
> Revision_Number INTEGER,
> Submission_Date TIMESTAMP, Summary VARCHAR(128) DEFAULT NULL,
> Keywords VARCHAR(128) NOT NULL, Pages INTEGER, Orientation
> VARCHAR(50), Resolution
> VARCHAR(50), Color_Model VARCHAR(50),Economode VARCHAR(50),
> Duplex VARCHAR(50), Paper_Size VARCHAR(50), Paper_Source
> VARCHAR(50), Max_Copies INTEGER,
> Collate VARCHAR(50), Finishing VARCHAR(50),CycloneFormSetName
> VARCHAR(80), IsFormset BOOLEAN, PrintScript_Id INTEGER NOT
> NULL, AutoFillScript_Id
> INTEGER,Available BOOLEAN,ExpirationDateTime TIMESTAMP
> DEFAULT NULL,PRIMARY KEY (Id))
> //
> CREATE INDEX forms_Title ON forms (Title)
> //
> CREATE INDEX forms_Keywords ON forms (Keywords)
> //
> CREATE INDEX forms_Number ON forms (Number)
> //
> CREATE INDEX forms_Available ON forms (Available)
> //
> CREATE INDEX forms_Revision_Date ON forms (Revision_Date)
> //
> CREATE INDEX forms_forms_id_available ON forms (Id, Available)
> //
> CREATE SEQUENCE FORMS_IDSEQ START WITH 1 INCREMENT BY 1
> NOMAXVALUE CACHE 10
> //
> CREATE TABLE kiosks (Id INTEGER NOT NULL, Name VARCHAR(83),
> Address VARCHAR(80), Primary_Printer VARCHAR(80),
> Backup_Printer VARCHAR(80), Printer
> VARCHAR(50),Kiosk_Type VARCHAR(50),Macon_Title
> VARCHAR(15),Available BOOLEAN,PRIMARY KEY (Id))
> //
> CREATE INDEX kiosks_Available ON kiosks (Available)
> //
> CREATE SEQUENCE KIOSKS_IDSEQ START WITH 1 INCREMENT BY 1
> NOMAXVALUE CACHE 10
> //
> CREATE TABLE metrics (Kiosk_Id INTEGER, Form_Id INTEGER,
> Copies INTEGER, Pages INTEGER, Script_Id INTEGER, AutoFilled
> BOOLEAN, ExecTime TIMESTAMP)
> //
> CREATE INDEX metrics_kioskId_formId_exectime ON metrics
> (Kiosk_Id, Form_Id, ExecTime)
> //
> CREATE TABLE Scripts (Id INTEGER NOT NULL, Name VARCHAR(80)
> ,Description VARCHAR(80) ,CycloneScriptName VARCHAR(80)
> ,ReqsFormSet BOOLEAN, Prints
> BOOLEAN,HasPrompts BOOLEAN,HasLocalVars
> BOOLEAN,ExpirationDateTime TIMESTAMP,Deleted BOOLEAN,System
> BOOLEAN,PRIMARY KEY (Id))
> //
> CREATE INDEX Scripts_Id_ReqsFormSet ON Scripts (Id, ReqsFormSet)
> //
> CREATE INDEX Scripts_Id_HasPrompting ON Scripts (Id, HasPrompts)
> //
> CREATE INDEX Scripts_Id_HasLocalVars ON Scripts (Id, HasLocalVars)
> //
> CREATE SEQUENCE SCRIPTS_IDSEQ START WITH 1 INCREMENT BY 1
> NOMAXVALUE CACHE 10
> //
> INSERT INTO Scripts VALUES (SCRIPTS_IDSEQ.NEXTVAL, 'Print',
> 'Default Print Script', 'kioskprint',FALSE, TRUE, FALSE,
> FALSE,null,FALSE,TRUE)
> //
> INSERT INTO Scripts VALUES (SCRIPTS_IDSEQ.NEXTVAL,
> 'PrintPostScript', 'PostScript Print Script',
> 'kioskprintps',FALSE,TRUE,FALSE,FALSE,null,FALSE,TRUE)
> //
> CREATE TABLE Licenses (Id INTEGER NOT NULL, LicenseKey
> VARCHAR(35) NOT NULL ,DateAdded TIMESTAMP,Number
> INTEGER,RunningTotal INTEGER,PRIMARY KEY
> (Id))
> //
> CREATE INDEX Licenses_Id_RunningTotal ON Licenses (Id, RunningTotal)
> //
> CREATE SEQUENCE LICENSES_IDSEQ START WITH 1 INCREMENT BY 1
> NOMAXVALUE CACHE 10
> //
> INSERT INTO Licenses VALUES
> (LICENSES_IDSEQ.NEXTVAL,'INITIAL-LICENSE',now,1108746273,1108746273)
> //
> CREATE TABLE info (version VARCHAR(10), serial_number VARCHAR(7))
> //
> INSERT INTO info VALUES('3.1.1',null)
>
>
>
> ---------------------- Forwarded by David Hay/Lex/Lexmark on
> 09/06/2003
> 06:53 PM ---------------------------
>
>
> David Hay
> 08/06/2003 09:51 PM
>
> To: [EMAIL PROTECTED]
> cc:
>
> Subject: Query taking LOADS of extra room (Document link:
> David Hay)
>
> Hi,
>
> I am still trying to simulate "autogrow" with a thread that checks the
> database size and adds volumes if required.
>
> I have a complicated query that creates a "cross-tab" result (aka MS
> Access) for our reporting, but when we get up into millions
> of records, it
> seems to take large amounts of space to execute.
>
> Here's the query:
>
> SELECT Forms.Title AS Form, SUM(CASE WHEN Kiosks.Name='loc1'
> THEN Metrics.Copies
> ELSE 0 END) AS "loc1", SUM(CASE WHEN Kiosks.Name='loc2' THEN
> Metrics.Copies
> ELSE 0 END) AS "loc2", SUM(CASE WHEN Kiosks.Name='loc3' THEN
> Metrics.Copies
> ELSE 0 END) AS "loc3", SUM(CASE WHEN Kiosks.Name='loc4' THEN
> Metrics.Copies ELSE 0 END)
> AS "loc4", SUM(CASE WHEN Kiosks.Name='loc5' THEN
> Metrics.Copies ELSE 0 END) AS "loc5",
> SUM(Metrics.Copies) AS Total FROM Metrics INNER JOIN Kiosks
> ON Metrics.Kiosk_Id = Kiosks.Id
> INNER JOIN Forms ON Metrics.Form_Id = Forms.Id WHERE ExecTime
> IS NOT NULL AND Copies
> IS NOT NULL GROUP BY Forms.Title ORDER BY UPPER(Forms.Title)
>
> The more locations we have, the more sum(case...) clauses we have.
>
> Does anyone know any way around this?! Is there a way to
> optimize the query? (I realise that adding an UpperTitle
> field would help).
>
> Also, is it still possible/necessary to set the MAXDATAPAGES
> parameter? I get an error whenever I try to.
Try to update the statistics of the involved tables for more information
see
http://www.sapdb.org/htmhelp/3b/86f067458411d3a98200a0c9449261/frameset.htm
HTH.
Kind regards,
Holger
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
