How to make the query execute faster??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to make the query execute faster???

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-02-03 : 12:11:50

Hi,

I have an ASP page with 4 select boxes, based on the selections the user makes in the select boxes the query has to dynamically built upon...If the user makes selection in all the 4 select boxes then my query will look like this...

select count(distinct(recruiterinfo.recruiter_id)) from
recruiterinfo,recruiter_specialisation,recruiter_spl_category,
recruiter_cities,recruiter_country where
recruiter_specialisation.spl_id in (1, 2, 3) and
recruiter_specialisation.recruiter_id = recruiterinfo.recruiter_id or
recruiter_spl_category.category_id in (1, 2, 3) and
recruiter_spl_category.recruiter_id = recruiterinfo.recruiter_id or
recruiter_cities.city_id in (1, 2, 3) and
recruiter_cities.recruiter_id = recruiterinfo.recruiter_id or
recruiter_country.country_id in (1, 2, 3) and
recruiter_country.recruiter_id = recruiterinfo.recruiter_id and
recruiterinfo.fresh=1 and recruiterinfo.junior=1

But the problem for me is the execution time of the query...When I executed the query in the query analyser even after 15 mins the query was still executing...The user visiting the website will not wait for more than a minute to know the result.So how i improve this query such that I get the result sooner..
since the query is dynamically build i'am not sure whether i can put this in a StoredProcedure..Even If I put it in SP,I'am not sure whether It will improve the performance....

Karunakaran

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-03 : 16:15:12

I take it everything has an index on recruiter_id.
You could make all the indexes compound
e.g. recruiter_specialisation.recruiter_id, recruiter_specialisation.spl_id

If you have a lot of spl_id's and are only selecting a few then try an index hint on this .

You could make this a union query instead of all the or statements - it would make it easier to optimise and identify the problems and would stop the server doing a cartesian product before a scan.

There is nothing to stop you running this as a stored procedure and passing in the parameters. It would make it easier to test.
It might be worth putting the values into a temp table rather than using an in clause.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-02-04 : 07:13:29

Can you people tell me whats the error in this query???....

select count(distinct(A.recruiter_id)) from
recruiterinfo A INNER JOIN
recruiter_specialisation B INNER JOIN recruiter_spl_category C INNER JOIN
recruiter_cities D INNER JOIN recruiter_country E
ON
B.recruiter_id=A.recruiter_id
ON
C.recruiter_id=A.recruiter_id
ON
D.recruiter_id=A.recruiter_id
ON
E.recruiter_id=A.recruiter_id
where
B.spl_id in (1, 2, 3)
or
C.category_id in (1, 2, 3)
or
D.city_id in (1, 2, 3)
or
E.country_id in (1, 2, 3)
and
A.fresh=1 and A.junior=1

When I executed the above query I got the following errors

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'B' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'A' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'A' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'A' does not match with a table name or alias name used in the query.

Karunakaran

Nazim
A custom title

1408 Posts

Posted - 2002-02-04 : 07:39:31

After every join its On condition should be applied immediatly .

select count(distinct(A.recruiter_id)) from
recruiterinfo A INNER JOIN
recruiter_specialisation B
ON
B.recruiter_id=A.recruiter_id
INNER JOIN recruiter_spl_category C
ON
C.recruiter_id=B.recruiter_id
INNER JOIN
recruiter_cities D
ON
D.recruiter_id=C.recruiter_id
INNER JOIN recruiter_country E
ON
E.recruiter_id=A.recruiter_id
where
B.spl_id in (1, 2, 3)
or
C.category_id in (1, 2, 3)
or
D.city_id in (1, 2, 3)
or
E.country_id in (1, 2, 3)
and
A.fresh=1 and A.junior=1

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-04 : 08:57:00

You need LEFT OUTER JOINs if you want to keep the semantics of the original query.

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-02-04 : 10:44:57

Hi Nazim,

I tried the way you mentioned,Again I had the same problem..That is even after 15 minutes the query was still executing...without any results...

So I tried the query based on the suggestions by NR..Now the query look like this

select distinct(recruiterinfo.recruiter_id) from
recruiterinfo,recruiter_specialisation
where
recruiter_specialisation.spl_id in (1, 2, 3) and
recruiter_specialisation.recruiter_id = recruiterinfo.recruiter_id
union
select distinct(recruiterinfo.recruiter_id) from
recruiterinfo,recruiter_spl_category
where
recruiter_spl_category.category_id in (1, 2, 3) and
recruiter_spl_category.recruiter_id = recruiterinfo.recruiter_id
union
select distinct(recruiterinfo.recruiter_id) from
recruiterinfo,recruiter_cities
where
recruiter_cities.city_id in (1, 2, 3) and
recruiter_cities.recruiter_id = recruiterinfo.recruiter_id
union
select distinct(recruiterinfo.recruiter_id) from
recruiterinfo,recruiter_country
where
recruiter_country.country_id in (1, 2, 3) and
recruiter_country.recruiter_id = recruiterinfo.recruiter_id
select distinct(recruiterinfo.recruiter_id) from
recruiterinfo
where
recruiterinfo.fresh=1 and recruiterinfo.junior=1

Now the query retrieved all the distinct recruiters_id values,Now How can I get the total count of these recruiters_id....

I tried this way....

1.Create a view in a randam name with the above query
2.Get the count of the recruiters_id from the view
3.Finally drop the view

But one of my friend said it will take more time and it would be problem when multiple users come in to the picture...

can some one suggest me a better solution....

Karunakaran

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-04 : 11:52:17

It surprises me that moving the join conditions out of the OR'd WHERE condition hasn't fixed the performance, but perhaps I'm working from faulty assumptions.
Much simplified, I had assumed this sort of thing:


CREATE TABLE a (
  i int PRIMARY KEY
)


CREATE TABLE b (
  i int NOT NULL FOREIGN KEY REFERENCES a,
  d int NOT NULL,
  PRIMARY KEY CLUSTERED (i,d)
)


CREATE TABLE c (
  i int NOT NULL FOREIGN KEY REFERENCES a,
  d int NOT NULL,
  PRIMARY KEY CLUSTERED (i,d)
)

With data looking somewhat like this


SET NOCOUNT ON
DECLARE @i AS int, @d AS int
SET @i = 0
WHILE @i < 200
BEGIN
  INSERT INTO a VALUES (@i)
  SET @d = 0
  WHILE @d < 10
  BEGIN
    IF RAND() < 0.5 BEGIN
      INSERT INTO b VALUES (@i, @d)
    END
    IF RAND() < 0.2 BEGIN
      INSERT INTO c VALUES (@i, @d)
    END
    SET @d = @d + 1
  END
  SET @i = @i + 1
END
SET NOCOUNT OFF

Even given that small amount, the difference between these two queries is enormous!


SELECT COUNT(DISTINCT a.i)
FROM a, b, c
WHERE a.i = b.i AND b.d IN (1,2,3)
   OR a.i = c.i AND c.d IN (1,2,3)


SELECT COUNT(DISTINCT a.i)
FROM a
LEFT JOIN b ON a.i = b.i
LEFT JOIN c ON a.i = c.i
WHERE b.d IN (1,2,3)
   OR c.d IN (1,2,3)

These two are good with the test data too:


SELECT COUNT(i)
FROM a
WHERE i IN (SELECT i FROM b WHERE d IN (1,2,3))
   OR i IN (SELECT i FROM c WHERE d IN (1,2,3))


SELECT COUNT(i) FROM (
  SELECT a.i
  FROM a
  INNER JOIN b ON a.i = b.i
  WHERE b.d IN (1,2,3)
 UNION
  SELECT a.i
  FROM a
  INNER JOIN c ON a.i = c.i
  WHERE c.d IN (1,2,3)
) u

Edited by - Arnold Fribble on 02/04/2002 12:21:42

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-04 : 14:56:23

Doh! I am an idiot! Of course the LEFT JOIN query I posted is slow: SQL Server saves up all the OR'd INs until after it's joined everything. Doesn't make much difference on 1+2 tables, but give it 1+7 and it will take ages. If you want to use a join-based approach, this should improve matters:


SELECT COUNT(DISTINCT a.i)
FROM a
LEFT JOIN b ON a.i = b.i AND b.d IN (1,2,3)
LEFT JOIN c ON a.i = c.i AND c.d IN (1,2,3)
LEFT JOIN d ON a.i = d.i AND d.d IN (1,2,3)
LEFT JOIN e ON a.i = e.i AND e.d IN (1,2,3)
LEFT JOIN f ON a.i = f.i AND f.d IN (1,2,3)
LEFT JOIN g ON a.i = g.i AND g.d IN (1,2,3)
LEFT JOIN h ON a.i = h.i AND h.d IN (1,2,3)
WHERE COALESCE(b.i, c.i, d.i, e.i, f.i, g.i, h.i) IS NOT NULL

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-02-05 : 15:08:16

Thanks Arnold....

The query worked very much fine..And I got the result quickly...Atlast

...

Thank you All....

Karunakaran

Edited by - karuna on 02/05/2002 15:09:31

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources