[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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.

0
wilpitz
Asked:
wilpitz
  • 2
1 Solution
 
SteveH_UKCommented:
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.
0
 
David ToddSenior DBACommented:
Hi,

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.

HTH
  David
0
 
wilpitzAuthor Commented:
Thanks! I found the problem, it was a bad join and the tool found it.
0
 
SteveH_UKCommented:
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 :)
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now