urgent help - complicated sql query for large rec

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

urgent help - complicated sql query for large rec

Author

Topic

ravjeetbedi
Starting Member

2 Posts

Posted - 2006-06-10 : 10:33:17

Hi,
I am working on a query where I need to fetch records from the same table for various where conditions. The size of the database is very large.
The table structure is like
Table One
ID Descr Category_Id Type_id Date
1 abc 2 1 1/1/2006
2 sf 1 2 2/1/2006
3 ddfd 5 6 2/1/2006
4 dg 1 3 3/1/2006
5 fdg 2 1 4/1/2006
6 dfg 4 5 4/1/2006

The query I want is something like
- find number of counts group by Category_id and Type_id
- find total number of records where date is greater than 2/1/2006(Some date)
- find total number of records where date is less than 3/1/2006(some date)
- find total number of records
and many more in a single query

If I do
Select count(*) as total,(Select count(*) from tableone where date > '2/1/2006') as total2,(Select count(*) from tableone where date < '/1/2006') as total3 from tableone
group by Category_id ,Type_id

then it displays the result but takes a lot of time in fetching it. can u provide me with a better solution

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-10 : 11:41:48

Do you have an index on the date?
You might want to create an aggregate table which holds the counts per date or per per month (and per date). You could maintain that via a trigger - which might populate a table to maintain the aggregates asynchronously.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-10 : 17:02:12

quote:

If I do
Select count(*) as total,(Select count(*) from tableone where date > '2/1/2006') as total2,(Select count(*) from tableone where date < '/1/2006') as total3 from tableone
group by Category_id ,Type_id

then it displays the result but takes a lot of time in fetching it. can u provide me with a better solution

I suspect that you have probably many of these (select count(*)...) subqueries in your select, for all different dates? They are taking a long time because each one requires processing of the table to return the calc. Instead, consider writing as:


select 
  Category_id ,
  Type_id, 
  sum(case when date > date1 then 1 else 0 end) as Count1,
  sum(case when date > date2 then 1 else 0 end) as Count2,
  ...
  sum(case when date > datex then 1 else 0 end) as Countx
from
  tableone
where
  Date between date1 and Datex
group by 
  Category_id ,Type_id

this will require only 1 pass through the table to calc the counts, as opposed to going through the table over and over.

Also, be sure that the "Date" column is indexed.

ravjeetbedi
Starting Member

2 Posts

Posted - 2006-06-10 : 23:53:11

Thanks!! I guess this would be really helpful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources