Newbie stumped - Large SQL Select is SLOW. need Speed Please!

[sparf]

ok i have a database, 4 tables.

table 1 - "ATBAT" it contains 1,400,000 records
table 2 - "RESULT" has 130 records
table 3 - "PLAYER" has 2500 records
table 4 - "TEAM" has 30 records


so i do a SELECT on ATBAT and that gives me 1.4 mil records, too much and i go to GROUP BY RESULT, PLAYER and it goes down quite a bit. now it runs in 6-8 seconds. ATBAT contains links to the other three unrealated sibling tables.

ok - so i am at 6 seconds - this is ok for me.

but.

i would like to have the user select from the three other tables any criteria they want. like 20 of the 30 teams, 2 of the players, or all of them or whatever combination. this i have written - works fine.

but but but!!! when i do the joins on the ATBAT table, ( left outers ) and run the select it takes over 4 minutes to return the data. i dont get errors and the RS is correct - but its soooo freeakin sloooow.

could someone write the select statement - i feel i am probably just mis-writing it. maybe use a SELECT inside another SELECT? to whittle stuff down? when i attach just ONE join - any of the three and run it, it comes back in 6-8 seconds - so when i add the next one i get SLOW... :




select result, player, sum(hits) as hits from ATBAT

left join PLAYERS ON ATBAT.player_name=PLAYERS.name
left join TEAM ON ATBAT.team_name=TEAM.name
left join RESULT ON ATBAT.result_name=RESULT.name

where PLAYERS.export='Y' and TEAM.export='Y' and RESULT.export='Y'

group by result, player
order by result, player



could i somehow select between 2 tables, this takes 8 seconds, and use that result set to then select off (exclude) more records based on a third table and then do it one more time with a fourth table? seems it would get much faster each time.

especially with the grouping in the first part. somehow select ATBAT joined with TEAM then have just a few thousand records and the last two would go really fast?!?!?!


eeek.

thanks

 

[r937]

if you place conditions into the WHERE clause on a column from the right table in a LEFT OUTER JOIN, you will never return unmatched rows, it will behave like an INNER JOIN

consequently you should write it as an INNER JOIN (not sure if this will have a significant effect on performance, though, except that the optimizer is now free to examine other paths)

presumably you have indexes on all the necessary columns?

r937.com | rudy.ca

 

[mrdenny]

Check the execution plan. This will tell you were you are loosing all the time.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sparf]

heya - ok i checked,

i have indexes on the name fields of the 3 sib tables

i also would like only the records from the left table, not some cross tabbed thingy. so not sure if inner join would help?

it currently runs in 3 and 1/2 minutes. kinda slow still, are you guys sure you cant write this as some sorta select of another select. when i run it with just one other join it runs in 6 seconds.

should i put the logic on the export='y' into another section of the select?

i ran the select with the timer thingy on, the one that tells you where it is spending all of its time...

33% hash match (aggragate) on ATBAT
25% hash match (inner join) on ATBAT
25% hash match (inner join) on other two tables
22% clustered index scan on ATBAT



so... - not sure what that means????

 

[fredericofonseca]

not sure if this will give the results you need, but from your input so far it seems likely.

select result, player, sum(hits) as hits

from ATBAT
INNER join TEAM
ON ATBAT.team_name=TEAM.name
and TEAM.export='Y'
INNER join RESULT
ON ATBAT.result_name=RESULT.name
and RESULT.export='Y'
INNER join PLAYERS
ON ATBAT.player_name=PLAYERS.name
AND PLAYERS.export='Y'

group by result, player
order by result, player

This can eventually reduce your data, but without knowing how it is on all the tables, its hard to say.
Use the joins on the order I mention them.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com