10 min query. there must be a better way

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

Transact-SQL (2000)

10 min query. there must be a better way

Author

Topic

plocke
Starting Member

15 Posts

Posted - 2001-12-11 : 13:50:01

dear friends,

the ROUTING table has 50,000 records.
the following gives me the correct result but takes 10 minutes to
run on mysql.

There has to be a much better way. *weeping and nashing teeth*

SELECT First_Name,Last_Name,Division_Name
FROM USERS
LEFT JOIN ROUTING as vendors on vendors.User_Id = USERS.User_Id
LEFT JOIN ROUTING as groups on groups.User_Id = USERS.User_Id
LEFT JOIN ROUTING as programs on programs.User_Id = USERS.User_Id
LEFT JOIN DIVISIONS on DIVISIONS.Division_Id = USERS.Contact_Id
WHERE
vendors.Xref_Id IN ('291','290','283','282','280','279','310','277')
AND
programs.Xref_Id = IN ('217','301','302','254','306','296','299','305','289','305')
AND
groups.Xref_Id = IN ('36','33','37','35','38')

GROUP BY USERS.User_Id
ORDER BY Division_Name

Edited by - plocke on 12/11/2001 14:00:20

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-11 : 14:09:34

I don't understand why you're joining USERS to ROUTING 3 times, can't you combine them like this (or is it getting too late in the day for my brain already?


SELECT First_Name,Last_Name,Division_Name
FROM USERS
LEFT JOIN ROUTING ON ROUTING.User_Id = USERS.User_Id
LEFT JOIN DIVISIONS on DIVISIONS.Division_Id = USERS.Contact_Id
WHERE ROUTING.Xref_Id IN ('291','290','283','282','280','279','310','277',
  '217','301','302','254','306','296','299','305','289','305',
  '36','33','37','35','38')
GROUP BY USERS.User_Id
ORDER BY Division_Name

Edit: Disregard that! Clearly the answer is 'Yes, I am already half-asleep'.

Edited by - Arnold Fribble on 12/11/2001 14:15:19

barmalej
Starting Member

40 Posts

Posted - 2001-12-11 : 14:14:42

As for me first of all I would try simply to ADD to your query 3 extra 'good' restrictions for Query Optimizer. I mean for example:

vendors.Xref_Id BETWEEN '277' AND '310'

and so on (I included min and max values). If you have many records then a lot of ones will be processed perhaps in a quicker way. May be it will be enough to speed up your query to a satisfactory speed.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-11 : 14:24:57

Try that again. If my previous effort runs in reasonable time (though obviously it gives the wrong result), you could try patching it up with a HAVING like this:


SELECT First_Name,Last_Name,Division_Name
FROM USERS
LEFT JOIN ROUTING ON ROUTING.User_Id = USERS.User_Id
LEFT JOIN DIVISIONS on DIVISIONS.Division_Id = USERS.Contact_Id
WHERE ROUTING.Xref_Id IN ('291','290','283','282','280','279','310','277',
  '217','301','302','254','306','296','299','305','289','305',
  '36','33','37','35','38')
GROUP BY USERS.User_Id
HAVING COUNT(CASE WHEN ROUTING.Xref_Id
    IN('291','290','283','282','280','279','310','277')
    THEN 1 ELSE NULL END) > 0
  AND COUNT(CASE WHEN ROUTING.Xref_Id
    IN ('217','301','302','254','306','296','299','305','289','305')
    THEN 1 ELSE NULL END) > 0
  AND COUNT(CASE WHEN ROUTING.Xref_Id IN ('36','33','37','35','38')
    THEN 1 ELSE NULL END) > 0
ORDER BY Division_Name

Edited by - Arnold Fribble on 12/11/2001 14:28:01

plocke
Starting Member

15 Posts

Posted - 2001-12-11 : 14:55:56

simper example:

SELECT * FROM users WHERE
user_id IN (1,2)
AND
user_id IN (1,2)
AND
user_id IN (1,2)

returns two two rows.
but i want six in this case..

plocke
Starting Member

15 Posts

Posted - 2001-12-11 : 15:06:39

interesting.. thanks. im learning a bushel.

adding an index

CREATE INDEX k_user_id ON ROUTING(User_Id);

brought it down from 10 minutes at 99%cpu to 1 second!
Whoa!

your suggestion will speed it up even more.

ToddV
Posting Yak Master

218 Posts

Posted - 2001-12-11 : 15:29:43

To get your simplified solution:

SELECT * FROM users WHERE user_id IN (1,2)
UNION ALL
SELECT * FROM users WHERE user_id IN (1,2)
UNION ALL
SELECT * FROM users WHERE user_id IN (1,2)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-11 : 16:09:08

10 minutes to 1 second? Wow. I think that says something about unindexed tables and mysql's query processor.
Something I didn't mention earlier is that I can't see why the joins to ROUTING are LEFT rather than INNER: all the users without ROUTINGs will get excluded by the WHERE conditions, won't they?

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2001-12-11 : 16:09:45

quote:

interesting.. thanks. im learning a bushel.

adding an index

CREATE INDEX k_user_id ON ROUTING(User_Id);

brought it down from 10 minutes at 99%cpu to 1 second!
Whoa!

your suggestion will speed it up even more.

And you should consider indexes on Xref_Id, Division_Id and Contact_ID. Certainly Xref_ID if you're using that to filter rows from the query.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources