MSTR Engine SQL questions (temp tables)

[dinzana]

Hello All, A few MSTR Engine SQL questions......

Why does MSTR generate SQL (SQL Server 7.0) with temp table names that lead off with '#' only on occasion? Other times temp table names do not contain the '#'. Is there some algorythm that dicatates this? Can table naming conventions that MSTR SQL engine uses be changed?

Thanks for your input in advance.

-dinzana

 

[chael]

Dinzana - 7i will always show the temp tables being created with the #. In 7.0.x the pre-execution SQL would not show the # even though SQL Server is in fact using tempdb for the temp tables. Upon looking at the SQL AFTER a result set appears the # sign will appear. Moral of the story...don't trust the pre-execution SQL!!!

MicroStrategy called this a feature...funny how they took away this feature and made it better

Chael

 

[dinzana]

Chael,

Thanks for the info. I would never have known! I was beginning to worry that MSTR may create perm. tables in our warehouse / datamarts.

We will soon be upgrading to MSTR 7i, and look forward to the pre and post execution SQL match up.

Thanks again,

dinzana

 

[nlim]

if the version of sqlserver you are using supports derived tables, you might want to change your VLDB intermediate table type to derived tables.

That should eliminate about 50-60% of your TT in your multipass queries.