July 7, 2008 at 1:58 pm
Hey all,
Not sure if this is necessarily the right forum, but since the issue is performance, I figure someone with the knowledge on performance may have at least some idea what's going on. I have several Crystal Reports that are based on a queries that I can run in SSMS in 30 seconds or less, with only a couple that run in anything less than sub-second timeframes. When these Crystal Reports used these queries through ODBC, the run-times matched quite closely with the query run times in SSMS.
However, a decision was recently made to have me re-configure Crystal to use OLE DB for SQL Server, and suddenly, all these reports run at least 60 times longer. The sub-second ones running as long as a minute or two, and one that normally runs in 15 to 20econds now runs at least 17 minutes. Is anyone familiar with what's taking place in this scenario? And let me know if this needs to move to a different forum.
Thanks!
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 8, 2008 at 4:25 am
Hi Steve,
Have you used SQL Server Profiler to confirm it is the queries that are taking that long and the connection startup itself.
If not I would start using SQL Server Profiler, its a good starting point.
Regards
Richard....
http://www.linkedin.com/in/gbd77rc
July 8, 2008 at 6:40 am
First, I tried just looking at the execution plan, which I had never seen before because I so rarely encounter a query that runs for any significant amount of time, and because I had never really been exposed to having to measure and/or tune performance. I had to go get SHOWPLAN permissions, and was only able to get it for a test database, and it really didn't help much, as the query would run in exactly the same timeframe whether it ran against test or production. Thus the reason I didn't suspect the query is because I knew that the ONLY change had been moving from ODBC to OLE_DB, and previously, both the query in SSMS AND the Crystal Report had very similar run times (within a second of each other).
Having looked at the execution plan, I didn't really see any huge stinkers in the various steps, so just to try and mitigate performance, I took a couple of my CTE's and moved them into table variables, where I could add primary keys, and after moving every one that could reasonably be moved that way, the query was down to a second or two in SSMS, and then when tested in Crystal Reports using OLE_DB, I ended up right back where I should have been - a Crystal Report that runs in very similar time to the query as it runs in SSMS, only now instead of the 13 to 14 seconds this particular query would have run in, it was now a 1 to 2 seconds runtime. SUCCESS!!! No more 17 minute nightmare...
As I am NOT the DBA, and I don't even have SHOWPLAN permissions, save for a test database that's not terribly representative of the production database, I really can't analyze the execution plans as a regular event, so I have to rely on my experience, along with a fair chunk of what I learn here as well.
By the way, I did try to use Profiler, but couldn't figure out how you use it. If there's some basic guidance on the steps to look at a specific query, let me know. I'm fairly sure if I'd had that knowledge yesterday afternoon, I could have solved the problem much more quickly.
The only unfortunate thing is that I still have a potential nightmare sitting there in that using OLE DB as opposed to ODBC may have some serious gotchas with Crystal (XI). If anyone is aware of any OLE DB parameters that might impact how Crystal will operate with it, let me know. Thanks!
Steve
(aka smunson)
:):):)
richard.clarke (7/8/2008)
Hi Steve,Have you used SQL Server Profiler to confirm it is the queries that are taking that long and the connection startup itself.
If not I would start using SQL Server Profiler, its a good starting point.
Regards
Richard....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 9, 2008 at 4:43 am
If your permissions to the MS SQL Server are restricted, then Profiler probably won't be much use to you. Unfortunately, you need elevated rights (possibly even full System Admin, I can't remember exactly) to use Profiler. But it would help you identify where the delay is occurring.
Interesting that the query over ODBC runs so differently to OLE. That could be a fundamental problem with Crystal Reports, but it seems unlikely.
What was the reason to change from ODBC in the first place?
Andy
July 9, 2008 at 6:34 am
The reason for the change is to eliminate the need to maintain the ODBC configuration on multiple PC's, which is what should be an unnecessary step. The report will be located on SharePoint, and with OLE DB, there's no need for an ODBC connection at all.
Indeed, seeing the exact same query take such a significantly longer time over OLE DB is truly unique. It's not as if the Crystal Report was developed first and it's query then run in SSMS. It was developed the other way around - in SSMS first to verify that the query could produce correct results. Then that query was pasted into Crystal as a "Command" against the original ODBC source. Then the report was justs changed to point to OLE DB instead of ODBC. Not sure if it would have made a difference to recreate the report starting from scratch with OLE DB or not, and I don't have the time to find out. However, on the good side, at least I'm finding out how to optimize my queries WITHOUT even looking at Profiler. I'm always amazed at the results, because I keep finding that I can reduce runtime by large percentages just by moving certain things into a table variable with a primary key. In combination with realizing that some steps can be combined into one, and that you can often do much more work in a PIVOT or UNPIVOT query than merely the pivoting or unpivoting, the runtime reductions have been tremendous so far.
I would still like to know if it's just Crystal Reports XI or if there's a parameter for OLE DB that could have been added that would have made a difference.
Steve
(aka smunson)
:):):)
AndyD (7/9/2008)
If your permissions to the MS SQL Server are restricted, then Profiler probably won't be much use to you. Unfortunately, you need elevated rights (possibly even full System Admin, I can't remember exactly) to use Profiler. But it would help you identify where the delay is occurring.Interesting that the query over ODBC runs so differently to OLE. That could be a fundamental problem with Crystal Reports, but it seems unlikely.
What was the reason to change from ODBC in the first place?
Andy
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 9, 2008 at 6:59 am
If you are just running "Command" in Crystal, maybe you could simplify your SQL query (or start with a straightforward SELECT and then build up to the final query) and see at what point the performance takes a plunge?
July 9, 2008 at 7:09 am
If I do that, then I have to assume that the query is being fed to SQL Server in pieces, and given the results already achieved, that can't be true. I suspect some kind of time-out issue associated with longer-running queries, or some kind of automatic network throttling governance feature that may have "run amok", or made an inaccurate estimate of reality. Given that an equivalent query now runs in a couple of seconds, it's clearly not the data volume in the result set, as that hasn't changed.
I've been working with Crystal Reports since version 6, and I can tell you that sometimes, Crystal just does some of the weirdest things, with little or no explanation for the behavior, which often disappears after merely shutting down Crystal and starting it up again. In this situation, that was insufficient to fix the problem. I also know that Crystal has a nasty way of breaking up your query into pieces under the right conditions when you aren't using the "command" feature, and it's happened so often that I can no longer trust Crystal to do the right thing in that regard, and thus use the command capability exclusively.
As I said before, I just don't have the time to play with it at this point, but would love to hear from someone else who may have run into this and solved it some other way.
Steve
(aka smunson)
:):):)
AndyD (7/9/2008)
If you are just running "Command" in Crystal, maybe you could simplify your SQL query (or start with a straightforward SELECT and then build up to the final query) and see at what point the performance takes a plunge?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 9, 2008 at 7:14 am
Interesting to hear your views on Crystal Reports. I've not had much experience of Crystal directly; like you, I prefer to build the SQL queries in SSMS. Maybe someone else can shed some light...
Andy
July 9, 2008 at 7:24 am
I may be off in left field, but out of curiousity, what OLE-DB Provider are you using for your connection? Are you sure it is up-to date?
July 9, 2008 at 7:53 am
Whatever came with Crystal Reports XI, so no, I have no idea if it's up to date or not. Any ideas on how I can find out rather quickly? Unfortunately, I don't have the time right now to search the web looking for details that might be rather esoteric and hard to find.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 9, 2008 at 8:42 am
Hi
To find out if Crystal Reports is behavioring correct you really need to speak to a DBA to run SQL profiler. They will want to know why something is taking a long time work as this will more than likely impact production systems. The profiler will show what is being sent to the server and how it interacts with it. Maybe the report is sending the query/command multiple times when you are expecting it only once.
The OLE DB provider for SQL may have a performance overhead (we avoid it here mainly because it is easier to use SQL Native Driver with .NET apps), did you try the native sql driver? From what I can remember crystal supports that, well it did in X which was the latest version I touched. It was getting too over complex and blotted for its own good.
There may not be one property on the connection that will fix this issue I am afraid.
Regards
Richard...
http://www.linkedin.com/in/gbd77rc
July 9, 2008 at 9:14 am
Getting the SQL Native Client on each of the reporting PCs means having to go through that step/process for each one, in the same way that we desire NOT to do the ODBC configuration for each machine. As OLE DB avoids using either of those, that's why the choice was made (not under my control - I'm just the consultant writing the reports).
If I run into this again, I'll ask the DBA to look at it using SQL Profiler.
Thanks!
Steve
(aka smunson)
:):):)
richard.clarke (7/9/2008)
HiTo find out if Crystal Reports is behavioring correct you really need to speak to a DBA to run SQL profiler. They will want to know why something is taking a long time work as this will more than likely impact production systems. The profiler will show what is being sent to the server and how it interacts with it. Maybe the report is sending the query/command multiple times when you are expecting it only once.
The OLE DB provider for SQL may have a performance overhead (we avoid it here mainly because it is easier to use SQL Native Driver with .NET apps), did you try the native sql driver? From what I can remember crystal supports that, well it did in X which was the latest version I touched. It was getting too over complex and blotted for its own good.
There may not be one property on the connection that will fix this issue I am afraid.
Regards
Richard...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 12, 2008 at 8:40 am
Interesting post....CTE's and moved them into table variables can you explain a little more here. Example be nice.
We too just switched from ODBC to SQL OLE. We use the JSQLConnect driver.
What MDAC version do you have.
Can't say our BI groups measure changing from ODBC to SQLOLE i have to ask them.
We just changed our Financials over this week to use SQL OLE - That be interesting to test if they say it is slow.
Anyone know how you check SQL OLE for if it is flooded...
I know we had 6 ODBC before now down to one driver so im interested how you can monitor if the SQL OLE is overwhelmed.
July 14, 2008 at 6:55 am
As to the CTE's moving into table variables, I just took some queries that were CTE's and declared a table variable with a primary key to hold the results instead, using an INSERT INTO statement followed by the query. I don't have the knowledge to know exactly why it performed so much better, or the tools or permissions to pursue the answer, but knowing that I effectively have an index where there previously was none seems reasonably likely to have a lot to do with the results.
I really don't have the time to delve into MDAC versions right now, as I have too many other fish to fry, and most of your other questions are ones I would ask as well. I still don't have an answer on why it slowed down so much, but given my history with Crystal just seeming to want to do it's own thing for no particularly apparent reason, and on a somewhat regular basis, I'm going to leave well enough alone for the time being. Should someone happen to know what issues might exist there, feel free to let me in on it.;)
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2008 at 10:32 pm
Hi Richard,
Could you please explain why Crystal Engine send the query multiple times....I am facing the same issue here that was highligted when i gothrough with sql profiler
am using jdbc-odbc bridge to communicate with database
Regards,
Qaseem
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply