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 6 3258
"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
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
"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.
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"
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.
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jy2003 |
last post by:
The query below is very long, and it takes about 5 seconds to get the
result. Of course I am not happy with the 5 seconds, and I am wondering if I
can get a better speed if I can break it into...
|
by: Karaoke Prince |
last post by:
Hi There,
I have an update statement to update a field of a table (~15,000,000
records). It took me around 3 hours to finish 2 weeks ago. After that
no one touched the server and no...
|
by: Gary |
last post by:
I am having a problem executing long running queries from an ASP application
which connects to SQL Server 2000. Basically, I have batches of queries that
are run using ADO in a loop written in...
|
by: djharrison |
last post by:
Greetings,
I was wondering if anyone could help me with a project involving MS
Acces as a front-end to an SQL Server 2000 database.
I am running a program that currently populates the Access...
|
by: Justin Koivisto |
last post by:
I am attempting to execute a *long* query string via a
ADODB.Recordset.Open (queryStr) call. Most of the time, the query string
will be less than 100 characters, but in some cases, it may be up to...
|
by: JJ |
last post by:
Hi,
I'm having a little problem. I use the following code to open a .pdf file
from one app. :
System.Diagnostics.ProcessStartInfo psi = new
System.Diagnostics.ProcessStartInfo();
string path...
|
by: William Sullivan |
last post by:
I've got a website that may, on occasion, display a large list of items in a
bulletedlist control. On the client side, it takes about 4 seconds to get a
response that weighs in at over 1mb. It...
|
by: jimmy |
last post by:
Hi,
I am running a query in a .NET windows service which takes a long time
to execute.
The query times out.
The same query doesn't time out from a console application.
I have put the...
|
by: cheesey_toastie |
last post by:
I have a long query which I have set off and would like to stop, and
rename one of the tables used.
My query is due to my lack of understanding of the underlying
structure of MSSQL-Server...
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |