Query Tuning.... - 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 2000 Forums

SQL Server Development (2000)

Query Tuning....

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2006-05-08 : 00:13:21

Hi,

I am trying to write a small query that will run against two tables that has more than 50 million records.
This query is taking long time to execute. Just wanted to know if there is a way to rewrtie the query below that can run little faster:

I am joining the two tables: A and B on dw_id. DW_ID is primary key on table A and foreign key on Table B.

Table A COLUMNS ARE:

DW_ID INTEGER, -------> PRIMARY KEY
DW_ITEM VARCHAR,
INDV_ID CHAR(1), ----->There is no Index on this column
DW_AMOUNT DECIMAL
.....Other Columns

Table B COLUMNS ARE:

DW_ID INTEGER, -------> FOREIGN KEY
HH_ACT_ID CHAR(1), ----->There is no Index on this column
....... OTHER COLUMNS

select A.dw_id, A.dw_item
from

tableA as A left outer join tableB as B

on A.dw_id = B.dw_id
and B.HH_act_id = 'N'

where
B.dw_id is null
and
A.INDV_id = 'N'

I think one reason the query might be running slow is there are no indexes on the two columns: "INDV_id" , "HH_act_id" that might be causing full table scan in the query above. Due to some constraints we are not able to create any index on these two columns. We only have Select, Insert and Delete prevaileges on database.

Is there a way we can rewrite the query above that might run little faster. Any advice will be appreciated.

Thanks

Raj

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-08 : 02:09:05

'Show execution plan' in QA (or SSMS for 2005) should answer your question on how the server runs this query, and even which indexes you would benefit from. But without the ability to create indexes are you pretty stuck, this query is so simple that no significant rewrite seems possible. But feel free to try:


SELECT A.dw_id, A.dw_item 
FROM tableA as A
WHERE A.INDV_id = 'N' and
  (SELECT COUNT(*)
   FROM tableB as B
   WHERE A.dw_id = B.dw_id and B.HH_act_id = 'N') = 0

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

pootle_flump

1064 Posts

Posted - 2006-05-08 : 04:18:05

Assuming the entries for B.HH_act_id and A.INDV_id are N and Y only then a table scan would probably be more efficient than an index seek or scan anyway. The distribution of values is too low in that case.

A slight variation on PSamig's suggestion - worth a go too:


SELECT A.dw_id, A.dw_item 
FROM tableA as A
WHERE A.INDV_id = 'N' and NOT EXISTS
  (SELECT *
   FROM tableB as B
   WHERE A.dw_id = B.dw_id and B.HH_act_id = 'N')

HTH

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-08 : 04:27:48

I knew I had missed out on something, this way the scan stops on first occurance of a B match, mine would have kept on count to the end of B. I knew that, I really did, honestly !!!

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-05-08 : 04:38:16

How many rows are in tableA where indv_id = 'N'?
How many rows are in tableB where hh_acti_id = 'N'?
How many rows are returned by the query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources