|
P: n/a
|
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
| |
Share this Question
|
P: n/a
|
"Muharram Mansoorizadeh" <mu********@yahoo.com> wrote in message
news:a8**************************@posting.google.c om... 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
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 of
HD Regards,
M.Mansoorizadeh | |
|
P: n/a
|
Are there any indexes on the table on your group by columns?
What does the query plan say?
"Greg D. Moore (Strider)" <mo****************@greenms.com> wrote in message
news:p2*********************@twister.nyroc.rr.com. ..
"Muharram Mansoorizadeh" <mu********@yahoo.com> wrote in message
news:a8**************************@posting.google.c om... 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
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
of HD Regards,
M.Mansoorizadeh
| |
|
P: n/a
|
"David Rawheiser" <ra*******@hotmail.com> wrote in message news:<6q*********************@bgtnsc04-news.ops.worldnet.att.net>... Are there any indexes on the table on your group by columns?
What does the query plan say?
"Greg D. Moore (Strider)" <mo****************@greenms.com> wrote in message
news:p2*********************@twister.nyroc.rr.com. ..
"Muharram Mansoorizadeh" <mu********@yahoo.com> wrote in message
news:a8**************************@posting.google.c om... 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
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 of
HD Regards,
M.Mansoorizadeh
----
What set of indexes do you recommend ? Due to the very dynamic nbature
of the table, i've setup no index up to now. | |
|
P: n/a
|
Muharram Mansoorizadeh scratched out in the sand ----
What set of indexes do you recommend ? Due to the very dynamic nbature
of the table, i've setup no index up to now.
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" | |
|
P: n/a
|
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 mu********@yahoo.com (Muharram Mansoorizadeh) wrote in message news:<a8*************************@posting.google.c om>... "David Rawheiser" <ra*******@hotmail.com> wrote in message news:<6q*********************@bgtnsc04-news.ops.worldnet.att.net>... Are there any indexes on the table on your group by columns?
What does the query plan say?
"Greg D. Moore (Strider)" <mo****************@greenms.com> wrote in message
news:p2*********************@twister.nyroc.rr.com. ..
"Muharram Mansoorizadeh" <mu********@yahoo.com> wrote in message
news:a8**************************@posting.google.c om...
> 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
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
of
HD > Regards,
> M.Mansoorizadeh
----
What set of indexes do you recommend ? Due to the very dynamic nbature
of the table, i've setup no index up to now. | |
|
P: n/a
|
On 11 Oct 2004 00:12:31 -0700, Muharram Mansoorizadeh wrote: What set of indexes do you recommend ? Due to the very dynamic nbature
of the table, i've setup no index up to now.
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. | | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 2952
- replies: 6
- date asked: Jul 20 '05
|
