Posted by Erland Sommarskog on 12/03/06 15:26

rcamarda (robert.a.camarda@gmail.com) writes:
> I have created a table that contains buckets to hold activitives of
> enrollment for each of our admissions officer for each day of an
> enrollment session. I have an UPDATE that builds rolling totals and
> updates said table. I count unique students from a table that contains
> registration information.
> Everything works, however it runs pretty slowly and I am wondering if
> there is a better approach to writing the SQL. SQL's Engine Tuning
> advisor has 0% improvment.
> I'd appreciate any suggestions as to better ways to accomplish my goal.
> Or, compliments on my genius if there isnt a better way! *grin*

Query tuning is rarely a trivial game, and when you don't have
the completely picture, it is even more difficult. You posted two
of three tables, but you did not include any indexes.

And it would certainly have helped if you had posted a working query.
When I add:

CREATE TABLE D_BI_STUDENT(
STUDENT_ID char(20) NULL,
CURR_IND char(1) NULL,
TALISMA_AO_ID varchar(15) NULL,
STUDENT_STATUS varchar(19) NULL,
)
go

and fix your inconsistent usage of upper- and lowercase, the query dies
with:

Msg 209, Level 16, State 1, Line 12
Ambiguous column name 'ACTIVITY_DT'.
Msg 209, Level 16, State 1, Line 16
Ambiguous column name 'ACTIVITY_COUNT'.

So all I know is that you have a query that is slow, and two tables
without their indexes, but I don't even know the query.

I can make two reflections from the query as posted:

1) The self-join to F_BI_Registration_Tracking with alias BB serves no
purpose at all. If it appears in the real query, there may be some
performance to win by removing it.

2) The outer join to D_BI_STUDENT is in practice an inner join, since you
have the condition STUDENT_STATUS = 'NEW', which appears to relate to
this table. I don't know what the intention is, but you should probably
either move this condition to the ON clause, or you should make the
outer join an inner join. Not that I think it will boost performance
much, but it makes the query clearer.

As for performance, roiling totals is a difficult thing, since SQL Server
has no query construct to support this well.

For further assistance, I suggest that you post the actualy query you have
now, and that in you include the schema for all tables, as well as all
indexes you have now. It also helps if you use aliases for all columns,
and if you apply upper/lowercase consistently. (I run with a case-sensitive
collation, and in general I recommend this for development.)

And, of course, please state which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]