472,776 Members | 2,424 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,776 software developers and data experts.

SQL Server takes too long to run a query

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
Jul 20 '05 #1
6 3226

"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

Jul 20 '05 #2
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


Jul 20 '05 #3
"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.
Jul 20 '05 #4
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"
Jul 20 '05 #5
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.

Jul 20 '05 #6
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.
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
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...
4
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...
4
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...
2
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...
2
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...
0
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...
1
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...
4
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...
1
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... ...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.