Query performance - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query performance

Author

Topic

Masum7
Starting Member

33 Posts

Posted - 2009-05-31 : 06:29:26

I need to run a query like this:
=======================
weblinks table has id, sports
id title
1 Soccer
2 PartyBets
=======================
arbitrages table has sportsid, oponent, profit, description, profit
Here sportsid is a foreign key of weblinks
=======================
arbitragesodds table has id, arbid, bookerid
in this table bookerid is foreign key of weblinks
arbid is foreign key of arbitrages
=======================
SELECT u.title,v.id, v.oponent, v.profit, v.description , COUNT(v.oponent) AS count
FROM weblinks AS u,arbitrages AS v
WHERE u.id=v.sportid
AND u.id IN(26,27,22,20,19,25,24,17,21,18,10,28)
AND v.id IN(SELECT arbid FROM arbitragesodds WHERE bookerid IN(26,27,22,20,19,25,24,17,21,18,10,28))

AND profit>=0.00
GROUP BY v.oponent ORDER BY v.profit ASC

=======================
The problem is the query takes long time (over 30s)to get response

. During this time computer gets too busy. Can anyone please help me to increase performance of the query.

Masum

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-31 : 06:38:58

what are the indexes to the tables ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 06:41:31

try using join rather than in. also what are current indexes you've?

Masum7
Starting Member

33 Posts

Posted - 2009-05-31 : 06:42:22

Khtan
Thanks for your reply.
All the tables has "id" as pkey. There are no other indexing.

Masum

Masum7
Starting Member

33 Posts

Posted - 2009-05-31 : 06:45:42

Hi visakh16
Thanks for your help. But if I use join then same rows will come many times. Any solution then?

Masum

Masum7
Starting Member

33 Posts

Posted - 2009-05-31 : 11:05:49

Anyone to help me please?

Masum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 14:08:27

quote:
Originally posted by Masum7

Hi visakh16
Thanks for your help. But if I use join then same rows will come many times. Any solution then?

Masum

which table has multiple records for same id value? is it arbitragesodds?

Masum7
Starting Member

33 Posts

Posted - 2009-05-31 : 14:20:38

arbitragesodds is child table of arbitrages. So for one row of arbitrages there can be many rows in arbitragesodds. The foreign key of arbitragesodds is arbid which refers to id of arbitrages.

Masum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 14:35:52

[code]
SELECT u.title,v.id, v.oponent, v.profit, v.description , COUNT(v.oponent) AS count
FROM weblinks AS u
JOIN arbitrages AS v
ON u.id=v.sportid
INNER JOIN (SELECT DISTINCT ardid FROM arbitragesodds WHERE bookerid IN(26,27,22,20,19,25,24,17,21,18,10,28)) as
ON as.ardid =v.id
WHERE u.id IN(26,27,22,20,19,25,24,17,21,18,10,28)
AND profit>=0.00
GROUP BY v.oponent ORDER BY v.profit ASC

[/code]

Masum7
Starting Member

33 Posts

Posted - 2009-05-31 : 15:43:48

visakh16
I want to thank you million times from my heart. This query really runs much faster. You are real boss.

Thanks!

Masum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources