Question

indexed sql query

Workflow

Updated November 4, 2015

asked on November 3, 2015

In our workflows we lookup to several smallish data tables (anywhere from 200 rows to 7000 rows). In running a SQL trace it became apparent to our DBA that our data tables are set up as HEAP. I believe the initial setup of these tables was done by Laserfiche, so my question is is that a recommended approach?

We are considering if placing an index on any of these tables would make the queries faster, and after running a trace on a query done via the Query Data tool, our DBA said that Dynamic SQL was used so an index wouldn't work. So I converted it to a Custom Query and it seems that in the trace two queries occur, one still using dynamic SQL and one without.

So my questions are:

1) Any insight on why was HEAP used to begin with?

2) Is it only possible to get benefits from indexes when using the Custom Query?

0 0

Replies

replied on November 3, 2015

The MSDN docs lay out some guidelines for when a heap is appropriate. Your larger tables might be larger than MS would recommend for a heap; for the smaller tables it depends on your usage patterns. Like most decisions about indexes, it's a question of performance or resource tradeoffs and not one of correctness - if your DBA thinks performance might improve by adding a clustered index there's no reason not to try.

0 0

replied on November 4, 2015 • Show version history

The HEAP guidelines are helpful.

My other question remains - even with a clustered index, do the standard query activities benefit from an index? I'm being told no by our DBA because the queries are formatted using dynamic SQL, which causes the SQL optimizer creates a new plan each time. (I'm using Workflow 9.0, so this may be a limitation of the version?)

0 0

You are not allowed to follow up in this post.

Question

Question

indexed sql query

Replies

Sign in to reply to this post.