SQL Server takes too long to run a query

[Muharram Mansoorizadeh]

Hi there,
I've a table with 18 millions of recordes shaped like this :
Code nvarchar(80) , State int , school int , class int , Term nvarchar(80)
The following query takes too long to run ( more than 2 hours )
select State , school , class , term , count (term) as freq
Group by state , school , class , term

How may I speed up the query?
My Pc is PIV (3.6 GHz) Intell , Win2003 Server , 512 MB of RAM, 80 GB of HD
Regards,
M.Mansoorizadeh

[Greg D. Moore \(Strider\)]

Re: SQL Server takes too long to run a query


"Muharram Mansoorizadeh" <muharram_m@yah oo.com> wrote in message
news:a85d0c0e.0 410100711.66fee ce1@posting.goo gle.com...[color=blue]
> Hi there,
> I've a table with 18 millions of recordes shaped like this :
> Code nvarchar(80) , State int , school int , class int , Term[/color]
nvarchar(80)[color=blue]
> The following query takes too long to run ( more than 2 hours )
> select State , school , class , term , count (term) as freq
> Group by state , school , class , term[/color]

Well, do you really want to return all 18 million rows?

Assuming you do, what indices do you have on your table?

[color=blue]
>
> How may I speed up the query?
> My Pc is PIV (3.6 GHz) Intell , Win2003 Server , 512 MB of RAM, 80 GB of[/color]
HD[color=blue]
> Regards,
> M.Mansoorizadeh[/color]

[David Rawheiser]

Re: SQL Server takes too long to run a query

Are there any indexes on the table on your group by columns?
What does the query plan say?

"Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co m> wrote in message
news:p2dad.2914 85$bp1.184175@t wister.nyroc.rr .com...[color=blue]
>
> "Muharram Mansoorizadeh" <muharram_m@yah oo.com> wrote in message
> news:a85d0c0e.0 410100711.66fee ce1@posting.goo gle.com...[color=green]
> > Hi there,
> > I've a table with 18 millions of recordes shaped like this :
> > Code nvarchar(80) , State int , school int , class int , Term[/color]
> nvarchar(80)[color=green]
> > The following query takes too long to run ( more than 2 hours )
> > select State , school , class , term , count (term) as freq
> > Group by state , school , class , term[/color]
>
> Well, do you really want to return all 18 million rows?
>
> Assuming you do, what indices do you have on your table?
>
>[color=green]
> >
> > How may I speed up the query?
> > My Pc is PIV (3.6 GHz) Intell , Win2003 Server , 512 MB of RAM, 80 GB[/color][/color]
of[color=blue]
> HD[color=green]
> > Regards,
> > M.Mansoorizadeh[/color]
>
>[/color]

[Muharram Mansoorizadeh]

Re: SQL Server takes too long to run a query

"David Rawheiser" <rawhide58@hotm ail.com> wrote in message news:<6qmad.685 890$Gx4.589114@ bgtnsc04-news.ops.worldn et.att.net>...[color=blue]
> Are there any indexes on the table on your group by columns?
> What does the query plan say?
>
> "Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co m> wrote in message
> news:p2dad.2914 85$bp1.184175@t wister.nyroc.rr .com...[color=green]
> >
> > "Muharram Mansoorizadeh" <muharram_m@yah oo.com> wrote in message
> > news:a85d0c0e.0 410100711.66fee ce1@posting.goo gle.com...[color=darkred]
> > > Hi there,
> > > I've a table with 18 millions of recordes shaped like this :
> > > Code nvarchar(80) , State int , school int , class int , Term[/color][/color]
> nvarchar(80)[color=green][color=darkred]
> > > The following query takes too long to run ( more than 2 hours )
> > > select State , school , class , term , count (term) as freq
> > > Group by state , school , class , term[/color]
> >
> > Well, do you really want to return all 18 million rows?
> >
> > Assuming you do, what indices do you have on your table?
> >
> >[color=darkred]
> > >
> > > How may I speed up the query?
> > > My Pc is PIV (3.6 GHz) Intell , Win2003 Server , 512 MB of RAM, 80 GB[/color][/color]
> of
> HD[color=green][color=darkred]
> > > Regards,
> > > M.Mansoorizadeh[/color]
> >
> >[/color][/color]

----
What set of indexes do you recommend ? Due to the very dynamic nbature
of the table, i've setup no index up to now.

[filesiteguy]

Re: SQL Server takes too long to run a query

Muharram Mansoorizadeh scratched out in the sand

[color=blue]
> ----
> What set of indexes do you recommend ? Due to the very dynamic nbature
> of the table, i've setup no index up to now.[/color]

By "dynamic" you mean what?

Is the schema changing? Did you mean that there is a lot of transactions?
Placing an index on the table won't slow you down if there are simply many
transactions.

Have you thought about using an integer (or double) instead of an nvarchar
as a primary key? What is your pk? Are your state and school fields
foreign keys to another table? If so you may (for reporting purposes) want
to de-normalize and put the actual values in the table.

Looking at your query, you may also want to do some subqueries to get raw
data (term) then the frequencies.

--
kai - kai at 3gproductions dot com
www.gamephreakz.com || www.filesite.org
"friends don't let friends use windows xp"

[Ryan]

Re: SQL Server takes too long to run a query

No indexes ! No wonder it runs slowly.

Try (on a backup copy first) running the query in Query Analyzer and
using the options for 'Perform Index Analysis' and 'Show Execution
Plan' (seperately).

I'd look at the execution plan first personally out of curiosity. It
should give you a good idea of the area that is taking the time. You
can run this before and after to see any areas of change in the
execution plan. I'd bet that there is a lot of table scanning going
on, hence the length of time for the query.

If you run the Index analyzer, odds are it will want to create an
index on the fields you are grouping by, but it should come up with
the best suggestion for you.

I'd also suggest reading up on indexes as a good understanding will
make your life a lot easier.

Hope that helps

Ryan

muharram_m@yaho o.com (Muharram Mansoorizadeh) wrote in message news:<a85d0c0e. 0410102312.5885 284@posting.goo gle.com>...[color=blue]
> "David Rawheiser" <rawhide58@hotm ail.com> wrote in message news:<6qmad.685 890$Gx4.589114@ bgtnsc04-news.ops.worldn et.att.net>...[color=green]
> > Are there any indexes on the table on your group by columns?
> > What does the query plan say?
> >
> > "Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co m> wrote in message
> > news:p2dad.2914 85$bp1.184175@t wister.nyroc.rr .com...[color=darkred]
> > >
> > > "Muharram Mansoorizadeh" <muharram_m@yah oo.com> wrote in message
> > > news:a85d0c0e.0 410100711.66fee ce1@posting.goo gle.com...
> > > > Hi there,
> > > > I've a table with 18 millions of recordes shaped like this :
> > > > Code nvarchar(80) , State int , school int , class int , Term[/color][/color]
> nvarchar(80)[color=green][color=darkred]
> > > > The following query takes too long to run ( more than 2 hours )
> > > > select State , school , class , term , count (term) as freq
> > > > Group by state , school , class , term
> > >
> > > Well, do you really want to return all 18 million rows?
> > >
> > > Assuming you do, what indices do you have on your table?
> > >
> > >
> > > >
> > > > How may I speed up the query?
> > > > My Pc is PIV (3.6 GHz) Intell , Win2003 Server , 512 MB of RAM, 80 GB[/color]
> > of
> > HD[color=darkred]
> > > > Regards,
> > > > M.Mansoorizadeh
> > >
> > >[/color][/color]
>
> ----
> What set of indexes do you recommend ? Due to the very dynamic nbature
> of the table, i've setup no index up to now.[/color]

[Ross Presser]

Re: SQL Server takes too long to run a query

On 11 Oct 2004 00:12:31 -0700, Muharram Mansoorizadeh wrote:
[color=blue]
> What set of indexes do you recommend ? Due to the very dynamic nbature
> of the table, i've setup no index up to now.[/color]

As a first guess, a covering index consisting of
state, school, class, term

should allow the entire query to be run from the index, not requiring
access to the table at all.

Of course you probably do many other things with this table, so a different
index or indices may be a better idea.