Hello,

we are new to the RE world, just going live this week so I'm still getting up to speed on best practices and the like.  I've been writing some Reporting Services reports and I am having a serious performance issue related to gift attributes.  SQL Server Query Analyzer is suggesting there are some new indexes that would drastically improve performance (99%), but I'm reticent to make any kind of changes as I don't know if this is acceptable or not.  Has anyone else added custom indexes?  Is there a better way to improve performance?

The other potential resolution is in my sql statement.  How do most people check if an attribute is the correct type for inclusion?  I'm currently looking up the description and using an IN clause or a JOIN?  Does anyone hard code ATTRIBUTETYPEID?  Is this bad technique?

Thanks in advance!

Matt

Hey Matt,

I would caution against custom indexes, if for no other reason than they may complicate schema changes made by upgrades to RE in the future. For example, the RE installer may receive an unexpected error from SQL Server about not being able to modify a column with a custom index, and they may not be properly trapping an error condition like that, which could prevent the installer from moving forward.

Troubleshooting performance issues in SQL Server is kind of a crapshoot anyway: Does your server have enough memory? What's the I/O on your disks? Do you have the database partitioned across disks and optimized for fast reading/writing or for searching?

Is there anything in particular you're noticing that's running slower than expected, or are you trying to get the server dialed in and running smoothly?

I've written a large number of Reporting Services reports that pull from RE, but I'm afraid I don't fully understand your question about attributes. Can you post the SQL, or just the relevant bit? If you're referencing a value in a code table (like dbo.tableentries), an INNER JOIN should be used if the results MUST contain that particular entry, or a LEFT OUTER JOIN if it's optional. I can get more specific if I had more details.