Hello Alexander,
I'am sorry I didn't pasted the complete statemant in the first mail. The
correct select-statement with group-by-clause is:
SELECT
COUNT("ID") AS REQUESTCOUNT,
"SESSIONID" AS SESSIONID,
MIN("PSESSIONID") AS PSESSIONID,
MAX("PORTALID") AS PORTALID,
MIN("MEDIUM") AS MEDIUM,
MAX("MEDIUMNO") AS MEDIUMNO,
MIN("CREATIONDATE") AS CREATIONDATE,
"HOST" AS HOST,
MAX("COOKIEAGE") AS COOKIEAGE,
MIN("ID") AS FIRSTID,
MAX("ID") AS LASTID,
(NUM(MIN("CREATIONDATE") ) DIV 10000000000) AS DATEREF,
MIN("IPADDRESS") AS IPADDRESS
FROM "MAIN"."REQUEST"
GROUP BY "SESSIONID", "HOST"
So the result with the EXPLAIN is:
OWNER TABLENAME STRATEGY PAGECOUNT
---------------------------------------------------------------------------
MAIN REQUEST TABLE SCAN 208270
INTERNAL TEMPORARY RESULT TABLE SCAN 500
RESULT IS COPIED, COSTVALUE IS 744639
I'm sorry but I don't know what I can use this information for. Do I habe to
increase the pagesize? And with which parameter can I do so.
PS: At the last time I used the statement it took 6 hours. Is it possible
that the insert is the problem?
Thanks a lot!
Ralf Weisser
> --- Ursprüngliche Nachricht ---
> Von: "Schroeder, Alexander" <[EMAIL PROTECTED]>
> An: "Ralf Udo Weisser" <[EMAIL PROTECTED]>, <maxdb@lists.mysql.com>
> Betreff: RE: Performance-Problem with 6 million rows and CPU load with 5%
> Datum: Fri, 28 Apr 2006 09:27:07 +0200
>
> Hello Ralf,
>
> is the
>
> SELECT
> COUNT("ID") AS REQUESTCOUNT,
> "SESSIONID" AS SESSIONID,
> MIN("PSESSIONID") AS PSESSIONID,
> MIN("CREATIONDATE") AS CREATIONDATE,
> MIN("IPADDRESS") AS IPADDRESS
> >FROM "MAIN"."REQUEST"
>
> (I assume you missed a GROUP BY SESSIONID in the writing)
>
> faster in producing output or comes it to a grinding halt
> too? What is the result of an
>
> EXPLAIN
> COUNT("ID") AS REQUESTCOUNT,
> "SESSIONID" AS SESSIONID,
> MIN("PSESSIONID") AS PSESSIONID,
> MIN("CREATIONDATE") AS CREATIONDATE,
> MIN("IPADDRESS") AS IPADDRESS
> >FROM "MAIN"."REQUEST"
> GROUP BY SESSIONID
>
> Regards
> Alexander Schröder
> SAP DB, SAP Labs Berlin
>
> -----Original Message-----
> From: Ralf Udo Weisser [mailto:[EMAIL PROTECTED]
> Sent: Donnerstag, 27. April 2006 18:03
> To: maxdb@lists.mysql.com
> Subject: Performance-Problem with 6 million rows and CPU load with 5%
>
> Hi,
>
> I am a beginner in MaxDB issues.
> My Problem is:
> I save data of requests from a server-system in one table. This table has
> nearly 6 million rows. Now I want to aggregate this data. In this task I
> make a INSERT in another table. This statement insertes the aggregated
> data
> >from the request-table. In this table it has to be written nearly 5
> million
> rows, too.
> In this Process the performance of the database is very slow, although
> different index are set on this colums, where a maximum/minimum is
> calculated. This calculation takes more than 6 hours. During this
> aggragation I looked at the peformance of the system and I noticed, that
> the
> system is not working to full capacity. The kernel-process of the database
> was only at 5%.
> My system has Windows XP Prof and 2 processors with 1 GB ram. I allocated
> the database 500MB ram for the kernel-process.
>
> Now my question is: Are there special parameters for the database, which
> has
> to be set, so that it increases more performance and more processor load?
>
> I can't invisage, that the problem arise from failure index or the
> statement. As a precaution here is the statement:
>
> INSERT INTO "MAIN"."T_VISITS"
> SELECT
> COUNT("ID") AS REQUESTCOUNT,
> "SESSIONID" AS SESSIONID,
> MIN("PSESSIONID") AS PSESSIONID,
> MIN("CREATIONDATE") AS CREATIONDATE,
> MIN("IPADDRESS") AS IPADDRESS
> >FROM "MAIN"."REQUEST"
>
> with the Index to PSESSIONID, CREATIONDATE,IPADDRESS
>
>
> Thank you very much.
>
> Ralf Weisser
>
> --
> "Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
> Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
Analog-/ISDN-Nutzer sparen mit GMX SmartSurfer bis zu 70%!
Kostenlos downloaden: http://www.gmx.net/de/go/smartsurfer
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
