Click here to Skip to main content
15,919,178 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hi,

I have 3 table a,b,c.

SQL
select * from a

 select * from b where aid in (select id from a)

 select * from c where bid in (select Id from b where aid in (select id from a))


these quries in sp and giving correct result but as performance I need to optimise these
could you please suggest how do i improve perfomance. is any tools available to optimised
sql queries

Thanks
Posted
Updated 20-Dec-11 0:58am
v3
Comments
Karthik Harve 20-Dec-11 6:58am    
[Edit] pre tags added.

Rule one: replace "*" with a list of the field you actually want. Depending on the data you are retrieving, this can make a massive difference to the bandwidth / memory footprint.
 
Share this answer
 
Comments
to.areeb 19-Dec-11 3:53am    
Thanks for your reply. I did it but not much difference
That you have embedded your queries into StoredProcedures is a very good step because SQL Server will make some execution plans to run them.

Your queries seem to be at their most optimized state (read here in-vs-join-vs-exists/[^]) so try to work on application side. If you are working with millions of record do not try to show all of them to user try to group them in alphabetic manner or some other grouping criteria that you know better.

Adding a where clause and confining the result set should improve your application performance.

Also consider using some UI controls that support lazy loading.

Hope It Helps.
 
Share this answer
 
Looks like you're fetching the data in parts and storing intermediate results somewhere. Instead try using joins: http://msdn.microsoft.com/en-us/library/ms191517.aspx[^]

DFor example something like:
SQL
select * from a

select * 
from a, b, c
where b.aid = a.id 
and   c.bid = b.id

And as said in previous posts, fetch only what you need, not everything (*).
 
Share this answer
 
Hi....

Step 1 : Remove "*" and Add Column Name List in Select Statement.

Step 2 : Use JOINS (Don't Use Sub Query)

SQL
SELECT Column_List
FROM a
INNER JOIN b ON a.id=b.id
INNER JOIN c ON c.id=a.id


This will helps u....
 
Share this answer
 
v2
Comments
Karthik Harve 20-Dec-11 6:57am    
[Edit] pre tags added.
Hi,

below query will server the same thing,
SQL
select a.* from a,b,c where a.id = b.aid and c.bid = b.Id


if it is not giving the same answer please let me know, as i do not have all tables to test this query.

thanks,
-amit.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900