Issues running queries requiring joining to the his.itemdata table

[Jason_Ricker]

We’re currently on OnBase 16 and just started using reporting dahsboards after our most recent update. We’ve been using Onbase products since early 2000. The issues I’m having are related to trying to query data that requires me to link through the his.itemdata table. To give some scale the first 500,000 log entries go from March of 2000 to the beginning of May 2000 and we are exponentially larger now than at that point with the number of daily importing and using WF extensively now. We have roughly 1,000 users that could be touching OnBase during a normal day with 150-200 using it as a primary part of their job. We use some workflow inbox and activity data providers but neither are able to offer the data we need individually.

When attempting to run queries that require us to use the itemdata table to join Workflow, userdata, doctype or Keyword table the queries generally time out and fail before completing. We assume because of the size of our database. Are they any recommendations or best practices we should look into that could help with overall performance? Even running small (top10 results queries with no joining) from this point in time (Dec 2017) fail.

[Thomas_Reu]

I don't have nearly the volume of data that you have.  However, I don't get any data using a like statement on a datetime field and that is even when I include the % wildcard - which a like statement requires.

 I do get data when I do the following:

SELECT itemname, itemdate
FROM [hsi].[itemdata]
where itemdate between '2017-12-06' and '2017-12-07'

[William_Pawul1]

Hi Jason,

Thanks for using Community.

We recommend using parameters with your custom sql data providers. In your case it would allow your users to pick the date or date range they are interested in reporting on.  Here's an example with all 4 types of date parameters, you only need to use one.

you would then need to configure the parameter:

and specify the data type:

you can then set a default value for your end users if you wish:

I hope this helps!  Have a great day.

William