effective query when there is lot of rows involved

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

General SQL Server Forums

New to SQL Server Programming

effective query when there is lot of rows involved

Author

Topic

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2014-12-17 : 06:48:41

i have 3 tables having refrential integrity on id on column on each table
TABLEA contains 5 million rows
TABLEB contains 50 thousand rows
TABLEC contails 5 thousand rows

i have a proc that have 3 in parameter that are actually values of some of the columns in that table one parameter for each table.

what is the optimized way to write query to get records on the basis of in parameters from these tables.

prithvi nath pandey

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-12-17 : 06:59:51

Primary keys, Foreign keys, and indexes are your friend.

And that is not a lot of rows.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2014-12-17 : 07:35:19

but only joins between column will work or any other specific change need to done.

prithvi nath pandey

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 08:46:17

Please post the query you are concerned about.

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2014-12-17 : 09:53:31

[code]
select a.*,b.*,c.*
from tablea a , tableb b , tablec c
where a.id = b.id
and a.id = c.id
and a.id =@pid1
and b.id = @pid2
and c.id = @pid3
[/code]

prithvi nath pandey

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 10:33:52

You should convert it to explicit joins:


select a.*,b.*,c.* 
from tablea a 
join tableb b 
on a.id = b.id
join tablec c
on a.id = c.id
where a.id = @pid1
--and b.id = @pid2
--and c.id = @pid3

Note that I commented the last two conditions. Basically they can never be true (because of the joins which force a.id = b.id = c.id) unless @pid1 = @pid2 = @pid3, in which case you don't need the tests anyway.

without knowing more, the only way I can see improving this query is by ensuring that the ID columns are indexed.

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2014-12-17 : 13:31:27

thanks gbritton,
actually this question was asked to me in a telephonic interview and the interviewer asked only the best way to get result from 3 table on the basis of 3 in parameter of a proc may be the parameter referred to some other column of tables except id columns but the 1 parameter only refer to one table column, and i believe that the number of rows play a imp role in this question as he told me that the one table contain 5 million rows other two contains 50 thousand and 5 thousand respectively.he only ask to write query to get records effectively not to create indexes as i remember he told me that all tables have referential integrity so there must be primary key on id column.

on the basis of that i derive that query but i am not sure that answer is correct or not.

so please help me to understand that what should be correct answer.

quote:
Originally posted by gbritton

You should convert it to explicit joins:

select a.*,b.*,c.*
from tablea a
join tableb b
on a.id = b.id
join tablec c
on a.id = c.id
where a.id = @pid1
--and b.id = @pid2
--and c.id = @pid3

Note that I commented the last two conditions. Basically they can never be true (because of the joins which force a.id = b.id = c.id) unless @pid1 = @pid2 = @pid3, in which case you don't need the tests anyway.

without knowing more, the only way I can see improving this query is by ensuring that the ID columns are indexed.

prithvi nath pandey

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 15:16:46

The question is very vague. For example, do the parameters in the proc correspond to columns in one of the tables, or all three, and how, precisely?

Note that the general rule of thumb is to write the query in the simplest way possible, trusting the optimizer to do its work. Note that the optimizer uses row counts in its calculations (assuming the statistics are up to date). We should only look at more sophisticated approaches if the first one exhibits bad performance. However, if it is possible to reduce the size of the joins, but eliminating rows in subqueries first, that can make a significant difference. Since the search columns are not indexed, the optimizer will probably do a hash merge and try to run the smaller tables against the bigger ones. Like many other things, though, it depends.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources