Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Faster Aggregate


I have a table as defined below:
wrkstnid int
cityid      int
stateid    int
countryid int
projectid  int
viewdate datetime

The viewdate is when the wrkstnid viewed the project, each wrkstn is
assigned a city,state and countryid (usually US)

I need to be able to sum up the views by city, or state or country. within a
defined time frame and for a project

current I use the following:

Select city,count(1) from tbl
where projectid = 4
and state = 43
and viewdate between '03/01/2007 00:00' and '03/30/2007 23:59'
group by city

the project,state and time range are all passed.

There is a clustered index on projectid, viewdate.  When the table has
around 100k rows this returns in a second or 2.  Now I have dummied up 4mill
rows and it runs like a dog.  Other than boosting the hardware which I
continue to recommend is there a better way to do this.

I have thought about going to AS but that would mean learning MDX, not sure
I'm ready for that yet, as it would mean a rewrite of the code.

I'm testing by doing some preagregation to speed it up also.

I would say that your clustered index is sub-optimal.  At a minimum, switch
the column order, assuming you normally do large date ranges such as the
example has.  Consider (test) dropping the projectid column altogether.

Have you performed regular (any) maintenance on your index?  It could well
be very fragmented.

Do you have any other indexes on the table?  How many distinct project and
city values are there?

What is the hardware?

How slow is "as a dog"?

--
TheSQLGuru
President
Indicium Resources, Inc.

"Thom" <T@discussions.microsoft.com> wrote in message

news:5687DD16-B358-4F2D-89D1-62DB262A6FC8@microsoft.com...

Switching column order causes it to run for about 15seconds on the smaill
100k table, there are limited projects currently under 15, number of cities
in 50-60k range.   Dropping the projectid causes it to run in the 15sec range.

Drop/Recreate/Rebuild all the same results approximately.

DualCore 3ghz with 2gig of ram, db is about 1.5 gig currently.

Other indexes on city and state.

-Have you tried putting it into a stored procedure?
-Creating an index over ProjectID and State?
-- Or using
viewdate >= @startRange
AND
viewdate <= @EndRange
instead of between?

Just some ideas off the top of my head
Nancy

"Thom" <T@discussions.microsoft.com> wrote in message

news:5687DD16-B358-4F2D-89D1-62DB262A6FC8@microsoft.com...

Hmm.  How many rows match the date range you have listed?  What is the query
plan chosen, query cost, and the number of reads performed?  (SET STATISTICS
IO ON, and click Show Actual Query Plan option).

Also, are there any other columns in the table?  If it is a fat table (lots
of data per row) you will almost certainly benefit from a compound index
that completely covers the query (i.e. includes city, projectid, state and
date (date is clustered so it should carry along).  Consider using Included
columns if sql 2005.  I have had tremendous performance gains for one of my
clients that has horribly bloated data structures by doing this.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Thom" <T@discussions.microsoft.com> wrote in message

news:167C7022-915C-476E-815C-1DA8BB96DEC0@microsoft.com...

Thom,

Are your stateids unique? In other words, will two different countries
never have the same stateid? Because if they do, your query will
probably return undesired results.

Now to your performance problem. Your query would seriously benefit from
an index on tbl(projectid, stateid, viewdate) INCLUDE (cityid). If the
table has a small viewdate range, or if there are few rows per city, you
could try an index on tbl(projectid, stateid, cityid, viewdate) instead.

HTH,
Gert-Jan

On May 29, 1:58 pm, Thom <T@discussions.microsoft.com> wrote:

One might also try to cover aggregate queries with indexes.
Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc