Slow performance caused by a “split” DSN

Friday 29 June 2012 @ 9:42 am

I was troubleshooting a report that was taking a long time to get data from the database.  The report used the same table configuration as many other reports, and none of those reports had performance problems.  But when going in to check the links I noticed that there were two different connections.  Then I got an info message from CR that said the report was using two different data sources, which was strange.  So I went into “Set DataSource Location” and, sure enough, one of the tables was listed separately from the others, with a separate connection.  The funny thing was that the two connections seemed identical.  Same name, same properties, etc.

So first I took lone table and tried to set it’s location to the connection in the bottom window that had the same name.  That didn’t change anything.  So then I took the connection for the other tables and set that location to the same connection at the bottom.  That consolidated all of the tables into one connection.  The info message went away and the performance of the report returned to normal.

Even now I am not sure what made CR consider them separate connections.  My guess is that the user who created the report was different than the user that was logged in when the last table was added and somehow the user name is part of the connection properties.  But I have noticed that sometimes when I add a single table to an existing report that it treats it as a different connection. I  have found that you can sometimes prevent this by connecting the existing report to the data source before adding the new table.  You can do this without even running the report by using “Database > Show SQL Query”.  Once you have connected the report it is usually easier to identify what connection is being used, and to take the table from that same connection.   But the bottom line is that when you move over the table you should make sure that it is grouped with the other tables under the same connection.  If not, you can use my method above to bring them all back together.

Leave a Reply

Recrystallize Pro