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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:

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.

wilpitzAuthor Commented:
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 :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.