Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Accessing SQL Server data Post Reply Post New Topic
Author Message
ChrisM
Newbie
Newbie


Joined: 10 Dec 2007
Location: United Kingdom
Online Status: Offline
Posts: 4
Quote ChrisM Replybullet Topic: Accessing SQL Server data
    Posted: 10 Dec 2007 at 5:09am
Hello
 
CR v11, SQL Server 2k.
I'm looking for something like "best practice" notes for accessing SQL Server data. I'm a TSQL programmer with limited Crystal experience. I've been asked to enhance a number of reports which have been flagged for poor performance. I've looked at the two worst offenders: the first used a command object containing a SELECT with 72 (yes, really) correlated subqueries. The second used a command object which returned 880,000 rows to Crystal, which then filtered out 16,000 for aggregation and display. I've "fixed" these two by using a command object to call a sp with the required parameters (all of them, required or not, so there's only 1 parameter sheet presented to the users). The remaining 17 reports are a mixed bag of command objects and linked tables. I'm tempted to change all of them to calling a sp. What's the current thinking on this? I'm sure there are valid arguments for each method...
 
Cheers
 
ChrisM
 
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 10 Dec 2007 at 5:31am
Well, Brian's book has a lot of information on optimizing SQL performance in Crystal.  I'd definitely take a look at it.  But, honestly, it sounds like you already have a pretty good feel for how to fix these monster reports.

A few common tips:

Do as much of the calculation server-side (i.e., in SQL Server itself) as you can.  SQL Server is much more optimized for that sort of operation, and is generally on a box that is better suited to the number crunching.

Parameters can be your friend.  They can also be a nightmare for the end user to navigate, if overdone.  It is generally far better to create four nearly identical reports with only a couple parameters each, than one report that will theoretically do anything, but has a dozen parameters.

It is generally easier to pull a lot of data, and suppress what you don't need.  It is generally more efficient to only pull what you need.
IP IP Logged
ChrisM
Newbie
Newbie


Joined: 10 Dec 2007
Location: United Kingdom
Online Status: Offline
Posts: 4
Quote ChrisM Replybullet Posted: 10 Dec 2007 at 5:38am
Hi Lugh
 
Many thanks for the speedy reply. Interesting - you've pretty much confirmed in writing what we've observed here in practice when I've tinkered with these reports - I'd guess the author was more of a Crystal expert than SQL Server! Sadly I only have a couple of days to "treat" them all, which is about how long it would take to obtain the book...but I might just get it anyway...
 
Thanks again
 
ChrisM
Beer will get you through times of no money better than money will get you through times of no beer.
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 10 Dec 2007 at 8:55pm
Wow, what a mess you got sucked into! I agree with Lugh that you already know what you are doing, you just need more time to do it in. One thing I discuss in the book that most people don't know is that if you have to join one or more stored procedures in the report, then this isn't going to be done server side. CR links stored procedures client side. So you really need to put everything into a single SP to get the most benefit. Also, if you do have to join a SP to another table or SP, then see if you can convert it to a View instead. CR will pass views to the server to be joined there and this can really speed things up. Of course, you can't use parameters with Views, but it's a good thing to keep in mind for future reference.

I cover databases and dozens of tips and tricks for SQL optimization in chapters 10 and 11 in my book Crystal Reports Encyclopedia.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.