[MonetDB-users] Long query response time with 'in' statement
Hi, I'm trying to create a benchmark MonetDB vs Postgres and for many types of queries, MonetDB quite faster than Postgres.But there is an interesting result when i try a query which contains an 'in' statement.The response times are MonetDB - 58 sec Postgres - 3 sec for the query : select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10, CABACT as CABACT where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') and CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf and RFOADV_10.rfoadvsup in ('5030', '5031', '5032', '5033', '5034', '5035', '5036', '5037', '5038') group by RFOADV_10.rfoadvsup i reform the query so that it can give the same result without 'in' : select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10 join CABACT on (CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf) join (select '5031' as pole UNION select '5032' as pole UNION select '5033' as pole UNION select '5034' as pole UNION select '5035' as pole UNION select '5036' as pole UNION select '5037' as pole UNION select '5038' as pole ) sub ON (RFOADV_10.rfoadvsup = sub.pole) where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') group by RFOADV_10.rfoadvsup the response time for MonetDB decreases 100 times (0.6 sec). Do you have any idea about it ? I run both Monetdb and Postgres on a server with 10GB Ram and the tables have CABACT : 677000 RFOADV : 140000 rows. Thanks, Mehmet -- Open WebMail Project (http://openwebmail.org)
Hi Mehmet, thank you very much for your interest in MonetDB! To find out whether the time goes, in particular with the "in" variant of your query, you can ask more information from MonetDB by prefixing your query witht he following keywords: - "plan" yields the logical n-ary relation algebra plan - "explain" yields the phisycal binary-relational algebra plan in MAL syntax - "trace" gives the detailed timing for each MAL operation that is executed during query evaluation. We'd be articularly interested in seeing the latter (for both variants of your query on your data) to check whether/where our optimizer fails to choose a better plan for your query. Regards, Stefan ps: We are also interested in hearing more about your experiences with comparing and benchmarking MonetDB and PostgreSQL. Will your benchmark be publically available? On Fri, Aug 21, 2009 at 03:03:08PM +0200, mkurtulus wrote:
Hi, I'm trying to create a benchmark MonetDB vs Postgres and for many types of queries, MonetDB quite faster than Postgres.But there is an interesting result when i try a query which contains an 'in' statement.The response times are MonetDB - 58 sec Postgres - 3 sec
for the query :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10, CABACT as CABACT where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') and CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf and RFOADV_10.rfoadvsup in ('5030', '5031', '5032', '5033', '5034', '5035', '5036', '5037', '5038') group by RFOADV_10.rfoadvsup
i reform the query so that it can give the same result without 'in' :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10 join CABACT on (CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf) join (select '5031' as pole UNION select '5032' as pole UNION select '5033' as pole UNION select '5034' as pole UNION select '5035' as pole UNION select '5036' as pole UNION select '5037' as pole UNION select '5038' as pole ) sub ON (RFOADV_10.rfoadvsup = sub.pole) where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') group by RFOADV_10.rfoadvsup
the response time for MonetDB decreases 100 times (0.6 sec).
Do you have any idea about it ?
I run both Monetdb and Postgres on a server with 10GB Ram and the tables have CABACT : 677000 RFOADV : 140000 rows.
Thanks, Mehmet
-- Open WebMail Project (http://openwebmail.org)
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
On Sat, Aug 22, 2009 at 10:07:24AM +0200, Stefan Manegold wrote:
Hi Mehmet,
thank you very much for your interest in MonetDB!
To find out whether the time goes, in particular with the "in" variant of ^^^^^^^ where ;-)
your query, you can ask more information from MonetDB by prefixing your query witht he following keywords:
- "plan" yields the logical n-ary relation algebra plan - "explain" yields the phisycal binary-relational algebra plan in MAL syntax - "trace" gives the detailed timing for each MAL operation that is executed during query evaluation.
We'd be articularly interested in seeing the latter (for both variants of your query on your data) to check whether/where our optimizer fails to choose a better plan for your query.
Regards, Stefan
ps: We are also interested in hearing more about your experiences with comparing and benchmarking MonetDB and PostgreSQL. Will your benchmark be publically available?
On Fri, Aug 21, 2009 at 03:03:08PM +0200, mkurtulus wrote:
Hi, I'm trying to create a benchmark MonetDB vs Postgres and for many types of queries, MonetDB quite faster than Postgres.But there is an interesting result when i try a query which contains an 'in' statement.The response times are MonetDB - 58 sec Postgres - 3 sec
for the query :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10, CABACT as CABACT where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') and CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf and RFOADV_10.rfoadvsup in ('5030', '5031', '5032', '5033', '5034', '5035', '5036', '5037', '5038') group by RFOADV_10.rfoadvsup
i reform the query so that it can give the same result without 'in' :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10 join CABACT on (CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf) join (select '5031' as pole UNION select '5032' as pole UNION select '5033' as pole UNION select '5034' as pole UNION select '5035' as pole UNION select '5036' as pole UNION select '5037' as pole UNION select '5038' as pole ) sub ON (RFOADV_10.rfoadvsup = sub.pole) where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') group by RFOADV_10.rfoadvsup
the response time for MonetDB decreases 100 times (0.6 sec).
Do you have any idea about it ?
I run both Monetdb and Postgres on a server with 10GB Ram and the tables have CABACT : 677000 RFOADV : 140000 rows.
Thanks, Mehmet
-- Open WebMail Project (http://openwebmail.org)
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
Hi Stefan, I send a reply message that contains the binary-relational algebra plan in MAL syntax( with explain) of two differentes queries last week(on friday).As they were too long, i send them in an attachement.Then i got the message : " Your mail to 'MonetDB-users' with the subject Re: [MonetDB-users] Long query response time with 'in' statement Is being held until the list moderator can review it for approval. The reason it is being held: Message body is too big: 306650 bytes with a limit of 40 KB " I hope that you got my mail.Or i can resend it.Waiting for your answer. Regards, Mehmet On Sat, 22 Aug 2009 10:10:33 +0200, Stefan Manegold wrote
On Sat, Aug 22, 2009 at 10:07:24AM +0200, Stefan Manegold wrote:
Hi Mehmet,
thank you very much for your interest in MonetDB!
To find out whether the time goes, in particular with the "in" variant of ^^^^^^^ where ;-)
your query, you can ask more information from MonetDB by prefixing your query witht he following keywords:
- "plan" yields the logical n-ary relation algebra plan - "explain" yields the phisycal binary-relational algebra plan in MAL syntax - "trace" gives the detailed timing for each MAL operation that is executed during query evaluation.
We'd be articularly interested in seeing the latter (for both variants of your query on your data) to check whether/where our optimizer fails to choose a better plan for your query.
Regards, Stefan
ps: We are also interested in hearing more about your experiences with comparing and benchmarking MonetDB and PostgreSQL. Will your benchmark be publically available?
On Fri, Aug 21, 2009 at 03:03:08PM +0200, mkurtulus wrote:
Hi, I'm trying to create a benchmark MonetDB vs Postgres and for many types of queries, MonetDB quite faster than Postgres.But there is an interesting result when i try a query which contains an 'in' statement.The response times are MonetDB - 58 sec Postgres - 3 sec
for the query :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10, CABACT as CABACT where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') and CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf and RFOADV_10.rfoadvsup in ('5030', '5031', '5032', '5033', '5034', '5035', '5036', '5037', '5038') group by RFOADV_10.rfoadvsup
i reform the query so that it can give the same result without 'in' :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10 join CABACT on (CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf) join (select '5031' as pole UNION select '5032' as pole UNION select '5033' as pole UNION select '5034' as pole UNION select '5035' as pole UNION select '5036' as pole UNION select '5037' as pole UNION select '5038' as pole ) sub ON (RFOADV_10.rfoadvsup = sub.pole) where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') group by RFOADV_10.rfoadvsup
the response time for MonetDB decreases 100 times (0.6 sec).
Do you have any idea about it ?
I run both Monetdb and Postgres on a server with 10GB Ram and the tables have CABACT : 677000 RFOADV : 140000 rows.
Thanks, Mehmet
-- Open WebMail Project (http://openwebmail.org)
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Open WebMail Project (http://openwebmail.org)
Mehmet, our list moderator is currently on holidays, so I did not see your mail neither the below notification, yet. I will see them, once our list moderator is back from holidays next week, or once I manage to accept your mail myself (won't have time for that before next week, either...) Hence, no need to resend (which won't make your logs smaller and hence result in the same mesage as below ...) Stefan On Wed, Sep 02, 2009 at 02:11:28PM +0200, mkurtulus wrote:
Hi Stefan, I send a reply message that contains the binary-relational algebra plan in MAL syntax( with explain) of two differentes queries last week(on friday).As they were too long, i send them in an attachement.Then i got the message : " Your mail to 'MonetDB-users' with the subject
Re: [MonetDB-users] Long query response time with 'in' statement
Is being held until the list moderator can review it for approval.
The reason it is being held:
Message body is too big: 306650 bytes with a limit of 40 KB "
I hope that you got my mail.Or i can resend it.Waiting for your answer.
Regards, Mehmet
On Sat, 22 Aug 2009 10:10:33 +0200, Stefan Manegold wrote
On Sat, Aug 22, 2009 at 10:07:24AM +0200, Stefan Manegold wrote:
Hi Mehmet,
thank you very much for your interest in MonetDB!
To find out whether the time goes, in particular with the "in" variant of ^^^^^^^ where ;-)
your query, you can ask more information from MonetDB by prefixing your query witht he following keywords:
- "plan" yields the logical n-ary relation algebra plan - "explain" yields the phisycal binary-relational algebra plan in MAL syntax - "trace" gives the detailed timing for each MAL operation that is executed during query evaluation.
We'd be articularly interested in seeing the latter (for both variants of your query on your data) to check whether/where our optimizer fails to choose a better plan for your query.
Regards, Stefan
ps: We are also interested in hearing more about your experiences with comparing and benchmarking MonetDB and PostgreSQL. Will your benchmark be publically available?
On Fri, Aug 21, 2009 at 03:03:08PM +0200, mkurtulus wrote:
Hi, I'm trying to create a benchmark MonetDB vs Postgres and for many types of queries, MonetDB quite faster than Postgres.But there is an interesting result when i try a query which contains an 'in' statement.The response times are MonetDB - 58 sec Postgres - 3 sec
for the query :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10, CABACT as CABACT where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') and CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf and RFOADV_10.rfoadvsup in ('5030', '5031', '5032', '5033', '5034', '5035', '5036', '5037', '5038') group by RFOADV_10.rfoadvsup
i reform the query so that it can give the same result without 'in' :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10 join CABACT on (CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf) join (select '5031' as pole UNION select '5032' as pole UNION select '5033' as pole UNION select '5034' as pole UNION select '5035' as pole UNION select '5036' as pole UNION select '5037' as pole UNION select '5038' as pole ) sub ON (RFOADV_10.rfoadvsup = sub.pole) where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') group by RFOADV_10.rfoadvsup
the response time for MonetDB decreases 100 times (0.6 sec).
Do you have any idea about it ?
I run both Monetdb and Postgres on a server with 10GB Ram and the tables have CABACT : 677000 RFOADV : 140000 rows.
Thanks, Mehmet
-- Open WebMail Project (http://openwebmail.org)
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Open WebMail Project (http://openwebmail.org)
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
Stefan, In fact the results for MonetDB vs Postgres is in favor of MonetDB when i use directly the SQL queries. But i also tried to test some Olap Cubes with MDX statements and as it's Mondrian that transforms MDX into SQL, MonetDB didn't like the structure of it's SQL query, for exemple the one with the 'in' statement.As we were thinking to use MonetDB instead of Postgres to increase response time for Olap Cubes, we wonder why MonetDB didn't like this statement. Thanks again for your interest. Mehmet On Wed, 2 Sep 2009 14:19:41 +0200, Stefan Manegold wrote
Mehmet,
our list moderator is currently on holidays, so I did not see your mail neither the below notification, yet.
I will see them, once our list moderator is back from holidays next week, or once I manage to accept your mail myself (won't have time for that before next week, either...)
Hence, no need to resend (which won't make your logs smaller and hence result in the same mesage as below ...)
Stefan
On Wed, Sep 02, 2009 at 02:11:28PM +0200, mkurtulus wrote:
Hi Stefan, I send a reply message that contains the binary-relational algebra plan in MAL syntax( with explain) of two differentes queries last week(on friday).As they were too long, i send them in an attachement.Then i got the message : " Your mail to 'MonetDB-users' with the subject
Re: [MonetDB-users] Long query response time with 'in' statement
Is being held until the list moderator can review it for approval.
The reason it is being held:
Message body is too big: 306650 bytes with a limit of 40 KB "
I hope that you got my mail.Or i can resend it.Waiting for your answer.
Regards, Mehmet
On Sat, 22 Aug 2009 10:10:33 +0200, Stefan Manegold wrote
On Sat, Aug 22, 2009 at 10:07:24AM +0200, Stefan Manegold wrote:
Hi Mehmet,
thank you very much for your interest in MonetDB!
To find out whether the time goes, in particular with the "in" variant of ^^^^^^^ where ;-)
your query, you can ask more information from MonetDB by prefixing your query witht he following keywords:
- "plan" yields the logical n-ary relation algebra plan - "explain" yields the phisycal binary-relational algebra plan in MAL syntax - "trace" gives the detailed timing for each MAL operation that is executed during query evaluation.
We'd be articularly interested in seeing the latter (for both variants of your query on your data) to check whether/where our optimizer fails to choose a better plan for your query.
Regards, Stefan
ps: We are also interested in hearing more about your experiences with comparing and benchmarking MonetDB and PostgreSQL. Will your benchmark be publically available?
On Fri, Aug 21, 2009 at 03:03:08PM +0200, mkurtulus wrote:
Hi, I'm trying to create a benchmark MonetDB vs Postgres and for many types of queries, MonetDB quite faster than Postgres.But there is an interesting result when i try a query which contains an 'in' statement.The response times are MonetDB - 58 sec Postgres - 3 sec
for the query :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10, CABACT as CABACT where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') and CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf and RFOADV_10.rfoadvsup in ('5030', '5031', '5032', '5033', '5034', '5035', '5036', '5037', '5038') group by RFOADV_10.rfoadvsup
i reform the query so that it can give the same result without 'in' :
select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10 join CABACT on (CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf) join (select '5031' as pole UNION select '5032' as pole UNION select '5033' as pole UNION select '5034' as pole UNION select '5035' as pole UNION select '5036' as pole UNION select '5037' as pole UNION select '5038' as pole ) sub ON (RFOADV_10.rfoadvsup = sub.pole) where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') group by RFOADV_10.rfoadvsup
the response time for MonetDB decreases 100 times (0.6 sec).
Do you have any idea about it ?
I run both Monetdb and Postgres on a server with 10GB Ram and the tables have CABACT : 677000 RFOADV : 140000 rows.
Thanks, Mehmet
-- Open WebMail Project (http://openwebmail.org)
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Open WebMail Project (http://openwebmail.org)
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Open WebMail Project (http://openwebmail.org)
participants (2)
-
mkurtulus -
Stefan Manegold