Re: [Emerald] Call Consolidation

Dale E. Reed Jr. ( (no email) )
Fri, 03 Dec 1999 09:25:36 -0800

Emerald wrote:
> I"m not an SQL type, but I've been looking at the call consolidation
> routine. Correct me if I'm wrong but does this thing go thru the whole calls
> table for each customer?
> Wouldn't it be a lot faster to go thru the calls table once, create an index
> or something for how it needs to be done, then go thru it a second time to
> do the work instead of this way?
> I've got a 1.4 million record calls table, this thing is running on a
> PIII-600 with half a gig of ram and it's taking 2 days to complete. This is
> nuts, I've got the equivalent of a 1980 mainframe here and it's taking days.
> Something is definitely not right with the way the call consolidation does
> it's thing. It's repeatedly going thru the whole table for some reason, over
> and over again.

There is two ways to do the consolidation. The first is to take a
big chunk and make your SQL Server crawl and fail authentication while
working. This is how 2.2 worked and wasn't very nice. The other choice
to take smaller pieces and work on each one. Thats what 2.5 does. Its
more friendly on your SQL Server.

I'm guessing if you are seeing continual table scans, that you have some
out of date indexes/stats or you are missing some indexes. Check
and see if you have the seven indexes on your calls table that it lists.
One missing index can change a query from 2 seconds to 2 hours, on a
table like the calls table.

> Most of it seems to be disk activity. The cpu never goes much over 10% usage
> and it never maxes out the memory either. But the whole time it's beating
> crap out of my disks.

What kind of disk subsystem does it have? SQL Server is really
for some operations, which don't suit well for IDE based disk
Typically a hardware RAID5 disk controller is recommended to handle the
IO. I rarely see SQL Servers that are CPU bound. Most are either
or IO/Disk bound.

Also, double check your memory settings and make sure your giving SQL
you can. That should help on the IO processing some.

> How big is your tempdb? Also, since switching to SQL7 I have not figured out
> how to put tempdb in RAM like I did on SQL 6.5, is there a way to do that?

Its not an option in 7.0 They changed how the memory works and this is
wrapped up into the general memory settings now.


Dale E. Reed Jr. Emerald and RadiusNT__________________________________________IEA Software, Inc.

For more information about this list (including removal) go to: