Hi,
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.
Cheers,
David
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
