Performance

Useful shares from PivotCube VCL - code example, algorithm solutions etc

Moderator: Alex Zencovich

Performance

Postby José Luiz » Mon Dec 08, 2008 6:36 pm

Hello,
I have some queries that are JOIN with 25 tables in the database, right? but the performance of the system falls far. What I did then, generated in a single table all records but with 278,000 lines and performance is also bad, to get an idea each query takes 5 to 6 minutes ..

Is there a procedure that can do?
José Luiz
User
 
Posts: 15
Joined: Wed Nov 26, 2008 8:52 pm

Re: Performance

Postby AlexZencovich » Mon Dec 08, 2008 7:26 pm

Sorry, may be I miss some - each query from 25 table takes 5 or 6 min? Which DB source you have used?
Best regards,

Alex Zencovich
-------------------------------------------------------------------------------------------------------------------------------------------
www.pivotcube.com - OLAP solution for Delphi, C++ Builder and ActiveX environment
AlexZencovich
Site Admin
 
Posts: 580
Joined: Sun Jun 18, 2006 10:09 am

Re: Performance

Postby José Luiz » Mon Dec 08, 2008 7:57 pm

Hi

i use sqlserver 2000 but i have one component query (Tquery) and comand is

select * from dwClients ( where dwClients is table contain 278,000 lines)

You have solution, because my result between 5 and 6 minutes

Best regards
José Luiz
User
 
Posts: 15
Joined: Wed Nov 26, 2008 8:52 pm

Re: Performance

Postby AlexZencovich » Mon Dec 08, 2008 8:40 pm

how many dimension and measures you have?

btw - check if you do not get RecordCount property somewhere. If you read that property - avoid it.
Best regards,

Alex Zencovich
-------------------------------------------------------------------------------------------------------------------------------------------
www.pivotcube.com - OLAP solution for Delphi, C++ Builder and ActiveX environment
AlexZencovich
Site Admin
 
Posts: 580
Joined: Sun Jun 18, 2006 10:09 am

Re: Performance

Postby José Luiz » Tue Dec 09, 2008 10:22 am

I have 13 dimension and 5 measures.

btw? whats it is ?

I'm not using any command to count the record, but it can be an active component of the property?

It is correct I only have a table with all the records?
or the right is to have a separate table for example, City, Customers, Products, Sales?

I'm sure I'm doing something wrong, because it takes too much consultation, I see that the demo of the component does not take much, but I can not find where it is.

Best regards
José Luiz
User
 
Posts: 15
Joined: Wed Nov 26, 2008 8:52 pm

Re: Performance

Postby AlexZencovich » Tue Dec 09, 2008 7:56 pm

José Luiz wrote:I have 13 dimension and 5 measures.

btw? whats it is ?

I'm not using any command to count the record, but it can be an active component of the property?

It is correct I only have a table with all the records?
or the right is to have a separate table for example, City, Customers, Products, Sales?

I'm sure I'm doing something wrong, because it takes too much consultation, I see that the demo of the component does not take much, but I can not find where it is.

Best regards


1. BTW - By the way :)

2. Yes, better to have separate tables/quesries for dimension items and indices to items in fact table - it allows load dimensions BEFORE fill facts (Star schema, see PivotCube manual). SOmetims it is impossible (for date field for example) but anyway change Table schema to Star wil increase perfomance

3. I suppose you have probelm with with cache in somewhere in SQL driver. Try to do next

1. Do not open query/table before start build cube
2. Make example code like
i := 0;
Query.Open; // facts query
while not Query.Eof do
begin
Query.Next;
inc(i);
end;
Query.Close;
ShowMessage(IntToStr(i)); // check if actual value like you expect
Best regards,

Alex Zencovich
-------------------------------------------------------------------------------------------------------------------------------------------
www.pivotcube.com - OLAP solution for Delphi, C++ Builder and ActiveX environment
AlexZencovich
Site Admin
 
Posts: 580
Joined: Sun Jun 18, 2006 10:09 am

Re: Performance

Postby José Luiz » Wed Dec 10, 2008 10:30 am

Hello,

btw hehehe :oops:

Okay, I'll separate the tables and then relational.

I hope the performance increase ...

best regards
José Luiz
User
 
Posts: 15
Joined: Wed Nov 26, 2008 8:52 pm


Return to PivotCube VCL

Who is online

Users browsing this forum: No registered users and 5 guests

cron