Hallo everybody

If I run queries on SQL Server EM system almost always ends up with a timeout error. There are also some queries that system doesn't finished on Query Analyzer - "system is low on virtual memory" (192MB on workstation).
Does anybody know how to optimize system or query?

More info:
Timeout property is set to 0 (no timeout),
Select * from tfgld410 ends up with low on virtual memory.

Any help will be appreciated.
Regards
Anna

Don't know your system but on common Baan environments tfgld410 can contain up to several million records so selecting them all can cause this.


Can't you perform the select for a specific fyer?

Rgrds,
Han

Hallo Han
There are about 3,5 milion records in tfgld410.
I select year and account. None of these fields are keys.
I tried to make a few queries to limit number of records, but it didn't help.

Regards
Anna

What happens if you only select one field?


Sorry but I haven't used MS-SQL very often and I don't have a version working over here.

Rgrds,
Han

With one field it's working, with two field,s - working, with 6,also.
With 12 hangs up - low of memory.

Regards
Anna

Our system is SQL7 Enterprise and 4c4 SCH1 dual PIII and 512MB, had similar problems at about 2 million records. Setup archiving so now only have current and previous years data in operational company tfgld410 table.

Hi,

I think that the reason for your problems is that your query has fields from several tables, not only from tfgld410. We had the same problem when we tried to get the description of the item.

tfgld410.item --- > tiitm001.item ---> tiitm001.dsca

The relationship between these tables is not in the Baan DD.

You have to be sure that you do not make a join between these tables, which might cause the calculation of the carthesian product.

For example, if you write

tfgld410 - 1 million records
tiitm001 - 10.000 records

... where tfgld410.item = tiitm001.item .....

will create a temporary "table" with 10.000 million records and only then will extract the data. ... etc..... ( Baan only has level 1 driver for SQL Server. Moreover, Baan + SQL Server does not use a temporary tablespace, like Oracle does). Since SQL Server uses the memory for sorting, this is the reason why you get the error.

Suggestion:
Take the script of the query and put it in a Baan session where you will be able to write:

.... where tiitm001._index1 = {tfgld410.item} .....

This will work much better. You can also do this in Tools/SQL Queries, but only using the text Manager, not the wizard.

Regards,