This explains why joins take so damn long:) I've only seen this error a few times, but I very often do queries that take up to 10 minutes on relatively small data sets, and this is using primary keys or indexes.
I will have to play around with DataCache, thanks for posting the hints.. Chris ----- Original Message ----- From: "Marcin P" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, December 13, 2003 9:56 AM Subject: Re: Space for result tables exhausted. > I'm afraid the problems you spotted are due > to rather ineficcient algorithm used by SAP DB > to make joins. > It copies a lot of data to temporary spaces and > this is the reason you get the error. > I observed the same behaviour when playing > with huge sets of data and managed to speed it up a lot. > My very basic hints are: > 1. Use the DESC to see what's going on with your query (view) > 2. Try to reduce the size of the fields (of course - as long > as it is possible) - my experiments has showed me that > even if you use Varchar(1000) and have a data which > occupies only 100 chars maximum during the making of joins > SAP DB behaves like all the rows got a 1000 chars length. > 3. Increase DataCache - it allows server to make this > operations in RAM instead of using disks. > 4. Try to join your data using primary keys where possible > and if not - indexed fields. > > You can also paste your data definions here (the definition of > tables which are used in your view and the definition > of the view as well) or and we'll try to give it a kick. > > Best regards, > Marcin > > > > Uzytkownik "Suraj Panicker" <[EMAIL PROTECTED]> napisal w wiadomosci > news:[EMAIL PROTECTED] > > Hello, > > > > I have a table, which has 50000 records in it, and a VIEW which operates > on > > this table. This view is a union of 4 selects. > > When I try to view the contents of the VIEW, I'm getting the following > > error. > > > > Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed > > General error;-904 POS(1) Space for result tables exhausted. > > SELECT * FROM "SAPADMIN"."SI_UNAVAILABLEFUND_V" > > > > Initially, I had only 100 MB of data volume. I had increased the data > volume > > size to 600 MB. It takes about 30 minutes for the SQL Studio to show this > > error message, which means It is operating on the query of the VIEW for > > almost 30 minutes, to provide no result at the end. > > > > This is a huge performance drawback. What should be done for this ? > > > > P.S : The VIEW just works fine with smaller number of records. > > > > Thanks in advaance, > > > > Suraj. > > > > > > -- > > MaxDB Discussion Mailing List > > For list archives: http://lists.mysql.com/maxdb > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
