[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
