Stored proc with select/into #tmp table shows a lot of optimization time per call.

[sladebe]

I have an application calling a stored proc repeatedly during an application run. In the stored proc, 4 #temp tables are created using a select/into call with an index being created for one of the tables. Then those 4 #temp tables are used for various update and read operations within the stored proc.

Looking at monProcessActivity.QueryOptimizationTime (time is per process, not per command) joined to monProcessSQLText, I can see a second or two seconds of optimization time per stored proc call for one stored proc. This optimization time doesn't seem to show up in monCachedProcedures.CPUTime. Note, the "with recompile" option is *not* used.

I found docs about optimizing query performance for #temp tables in stored procs, but this isn't about query performance, it's about optimization time.

Any hints as to prevent ASE from spending so much time optimizing this kind of stored proc every time it runs?

Thanks in advance
Ben

[former_member188958]

You can avoid optimization time if you add Abstract Plan clauses for the queries inside the procedure.

-bret

[sladebe]

Kewl beans! Why didn't I think of that 😉