Archives of the TeradataForum

Message Posted: Thu, 18 Mar 2004 @ 15:58:44 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

For your users benefit, you want the best SQL possible so the manager is doing you a favor. The manager needs to go to the next step and tell you WHY your queries are too complex and inefficient and make suggestions for improvement. You and/or he/she should review the explain.

In many cases, a query that runs out of spool with X amount of space will also run out on 2X... and 3X. Assuming that enough is allocated to the user. Most of the time, I have found that something else is wrong and simply adding spool causes the query to run longer but still does not complete.

A user id shares spool space across sessions. A query that runs fine with one session running for a user could spool out if the same user is logged on twice and running another query at the same time.

Spool space is allocated to the userid. A query might run fine for and user with lots of spool then spool out for another user.

Distribution of data can cause spool problems. You might have a lumpy amp that has a larger portion of data than the other amps. Spool is divided across all the amps. When one amp runs out of spool, the whole query spools out.

Adding amps can cause spool problems. We added nodes (which added amps) and the spool gets divided into smaller chunks. We had to add spool to the userids so they had the same amount per amp.

Jim

	<Prev	Next>		<<First	<Prev	Next>	Last>>