October 26, 2004 at 8:32 pm
Hello All - I'm looking for a more efficient way of doing this query. It currently takes about 23 seconds, on the Web, which can be a lifetime to some users.
I have a table with ~1m rows. I need to count 3 (or more) particular type records, all with different criteria. I'm currently doing 3 nested queries, but I'm hoping there's a better way.
SELECT name, areaID,
(SELECT count(*) from table where areaID = e.areaID AND cat = 1 and type = 45 and id in (select id from...)) as reccnt1,
(SELECT count(*) from table where areaID = e.areaID AND cat = 1 and type = 53 and id in (select id from...)) as reccnt2,
(SELECT count(*) from table where areaID = e.areaID AND cat = 1 and type = 34 and id in (select id from...)) as reccnt3
FROM employees e
Any idea for a better/quicker method??
October 27, 2004 at 2:56 am
A lot will depend on what indexes you have (if any) and also what is missing in the 'select id from...'. What I do is to break down your query into it's smaller parts and to try to retrieve as much data as quickly as possible and use temp tables if needed and remove as much duplication as possible and then join the data together.
one possibility (straight of the top of my head) would be for 3 types
SELECT e.[name], e.areaID,
SUM(CASE WHEN c.type = 45 THEN ELSE END) AS [reccnt1],
SUM(CASE WHEN c.type = 53 THEN ELSE END) AS [reccnt2],
SUM(CASE WHEN c.type = 34 THEN ELSE END) AS [reccnt3]
FROM employees e
LEFT OUTER JOIN (
SELECT a.areaID,a.type,count(*) from a
WHERE a.cat = 1 and a.type IN (34, 45, 53)
INNER JOIN (select [id] from...) b
ON b.[id] = a.[id]
GROUP BY a.areaID,a.type
) c
ON c.areaID = e.areaID
GROUP BY e.areaID,e.[name]
The best thing to do is experiment by continually rearranging the query until you get the best results
Looking at the execution plan will give you the best ideas for tuning the query
if there is going to be many and variable number of types then I suggest that these are put in a separate table (temp or otherwise) and joined which should improve performance. Making type a column for grouping with counting will also help.
Far away is close at hand in the images of elsewhere.
Anon.
October 28, 2004 at 1:09 pm
Can you take a page out of the data warehousing book.
create a new table that looks like this:
Create table Summary_table ( areaID int, cat int, type int, sum_count int)
then put in a trigger to maintain the summary information on insert and delete update the summary +1 or -1.
Then your queries would go against the new table and will be lightning fast, because it will then be a direct key lookup. Your time will be invested in the extra 1/10 of a second that it will take to preform the insert or delete.
ok, it can be a pain to maintain an extra table, but the online users wont know the difference.
Eric
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply