We help IT Professionals succeed at work.

Tempdb filling up with compare query and running from Crystal XI

I have converted my databases from Access to Sql Server 2005 and I have a report that I run in Crystal Reports that compares 330,000 records based on a unique account id from two tables and looks for a change of balances plus or minus $50,000.

Now that I am running the report off of SQL the tempdb fills out the drive with 65gb of space.

Is there a way to trim the numbers Crystal looks at on the server side or create a new table of the acccounts that meet the balance swing.

Watch Question

It sounds to me like your query is suboptimal in two ways:

1)  the tables probably do not have the correct indices.  This is causing SQL to sort the entire table in the tempdb database.
2)  the joins look like they might be incorrect as 65GB would be about 200KB per record which sounds like a lot for what you are doing.

The first thing I'd do is get the estimated execution plan in SQL Server Management Studio and also check for missing indices.

You could benefit from an index on the account id on both tables at the very least.

You might also consider creating an indexed view of just the columns you need from both tables as this would reduce the per-row data requirement and allow for more efficient searching.
David ToddSenior Database Administrator


In the past I've found that Crystal isn't the smartest with filtering and grouping, and would tend to write the queries as views on SQL, and thus let cyrstal do more of the presenting and less of the data extract/filtering.



Thanks! I found the problem, it was a bad join and the tool found it.
Author comment:

Thanks! I found the problem, it was a bad join and the tool found it.


You're welcome.  Glad you've got it sorted, and thanks for the points :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.