Welcome to the new Gigaspaces XAP forum. To recover your account, please follow these instructions.

Ask Your Question
0

Space Query - out of memory

Topology
2 partitioned async to backup cluster.

I got 1.1 Million records in space. When I do query

Query1: Runs good no issues
select * from MarketData.TradeObj where and rownum<5000

Query2: Gives out of memory error
select * from MarketData.TradeObj where ChannelID in (11,12) and TradeTime > 633730848000000000L and rownum<3000

Question: Does "in" operation gets all records to local JVM and sort them out?
What would be best todo in this case?

-Venkat

This thread was imported from the previous forum.
For your reference, the original is available here

asked 2009-04-24 13:31:43 -0500

venkatg gravatar image

updated 2013-08-08 09:52:00 -0500

jaissefsfex gravatar image
edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
0

The in SQL query is broken to multiple queries.
Sorting is done at the client side once the objects are returned. Still , I don't see sort by in your queries.

You might want to considering running these queries at the client side. You should use executors for such.
Are you sure all the fields you use are indexed?

Try to break the in query into different separate queries. This might resolve the out of memory problem.

Shay

answered 2009-04-24 16:28:46 -0500

shay hassidim gravatar image
edit flag offensive delete link more

Comments

Only ChannelID is Indexed and TradeTime is not, does that make any difference. TradeTime is almost all the time unique.

Are "Executors" API available in .Net?

Will try to split the query and see how it goes.

-Venkat

venkatg gravatar imagevenkatg ( 2009-04-27 08:21:23 -0500 )edit

Only ChannelID is Indexed and TradeTime is not, does that make any difference. TradeTime is almost all the time unique.

Are "Executors" API available in .Net?

Will try to split the query and see how it goes.

-Venkat

venkatg gravatar imagevenkatg ( 2009-04-27 08:22:23 -0500 )edit

Hey Shay thanks for the reply

I made all Queriable fields Indexed. But didn't make much difference.

Following is some thing I noticed. I am using spaceBrowser.
I am watching Network traffic on the machine.

Query1:
select * from MarketData.TradeObj where and rownum<5000
-- I see one spike in network and objects are displayed.

Query2:
select * from MarketData.TradeObj where ChannelID in (11,12) and TradeTime > 633730848000000000L and rownum<3000
-- I see 2 big spikes in the network trafic one after the other and after some time data is displayed. Looks like it is splitting query and executing one by one and doing the calculation at the client side jvm.

Correct me if my observation are wrong. I am using DotNet, what would be the suggested approach for this kind of queries??

-Venkat

venkatg gravatar imagevenkatg ( 2009-04-27 10:48:48 -0500 )edit

.Net 6.6.4 does not support executors. You can implement these via polling containers very easily. There is something wrong with the query optimizer. Such queries should not have any problems. Please note the UI SQL query view works a bit differently than a regular client. Do you have the same problems with a .Net client submitting such queries via the API? Shay

shay hassidim gravatar imageshay hassidim ( 2009-04-29 08:10:16 -0500 )edit

Thanks for the reply

Yes I got same problem executing the query from the code.

I splited the query into multi queries and removed "in" operation and every thing seems to be working good. Just that I am doing union operation in code.

-Venkat

venkatg gravatar imagevenkatg ( 2009-05-08 10:41:05 -0500 )edit

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower

Stats

Asked: 2009-04-24 13:31:43 -0500

Seen: 26 times

Last updated: Apr 24 '09