This document has not been formally reviewed for accuracy and is provided "as is" for your convenience.
Summary
ER created - SQL queries are coming across in Unicode instead of ANSI which is causing it to search the entire tables and not using indexing at all.
Question
When exporting a 9000 record report to excel using the web client, it is taking over 7 min to download. Sometimes the download times out before it finishes. I tried changing the fetch size and adding more CPU's to the server, none of which has helped. I worked with my DBA today and he found that the sql queries are coming across in Unicode instead of ANSI which is causing it to search the entire tables and not using indexing at all. Attached is the email I received from my DBA explaining what he thinks may be the issue. Keep in mind, we have tuned the database to use snapshot isolation. Not sure if this makes a difference or not.
Answer
R&D views this as an Enhancement Request, and has created one: QCCR1E144928
There are two workarounds in the meantime:
1) They can activate Unicode on their database through the Database Administrator.
2) They can enable the "Use Unicode string constants in Microsoft SQL Server queries" in the Database options, and then set the ConstAsTextForGet=1 and ConstAsTextForSelect=1 in the amdb.ini in order to remove the parameterization of the queries.