SQL Server Performance Forum – Threads Archive
SQL performance questions from a newbie
Hi Guys!! Im a newbie programmer and just learned the basics of SQL. My boss gave me a program which I need to improve the Query performance. The query involves 4 tables and this tables contains thousands of data. I already managed to link the tables to their key fields but still the my boss demanded that it should be faster. I’m thinking it’s impossible since these tables contains huge data,Please correct me if Im wrong, but the only way to make it faster is to reduce the data in the tables.Is there anything else I can do? And guys, another question, Do I still need to link the tables in the exist subquery to the keys of the tables on the main? What I mean is this: Select……
from table1 1,table2 2,table3 3
where 1.key = 2.field
and 2.key = 3.field
and exists(Select ‘x’ from table1 a,table2 b,table c
where a.key = b.field
b.key = c.field) Do I still need to link a’s key fields to 1’s fields so with b and c
since they are referring to the same table? I tried searching google but then I can only find basics and I believe this is advanced topic. I have limited time, can anyone help? Also if you can give me links to SQL webs for advanced topics? Thanks Guys!!
[B)]
Try: Select ….
From table1 a
Inner Join table2 b on a.id = b.id — Key field links
Inner Join table3 c on b.id2 = c.id2 — Key field links Can you perhaps paste some sample code that you are trying to optimize? Sanette SQL Developer
JustEnough Software Corporation
Walking on water and coding of a spec is easy, as long as both are frozen – A friend
The most benefit is gained from optimising application and queries, then indexes, then server configuration, and hardware, in this order. Refer this link in this websitehttp://www.sql-server-performance.com/sql_2000_tools_tutorial.asp to take advantage. And also this linkhttp://www.sql-server-performance.com/statistics_io_time.asp _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
One way to improve performance of this query is to make sure columns in join have indexes. You should also take a look at the execution plan in QA and see how your query is processed. Bambola.
in one word the answer to your question is ‘yes’ The query written as above won’t be very efficient since the subselect does not mention the other tables at all… I’m not sure if SQL is clever enough to create an efficient plan for this The query given can be simplified however Select
……
from table1 t1,table2 t2,table3 t3
where t1.key = t2.field
and t2.key = t3.field
and exists (Select ‘x’ from table t where t2.key = t.field) and as Sanette pointed out using the ANSI join is a better style Select
……
from table1 t1
inner join table2 t2 on t1.key = t2.field
inner join table3 t3 on t2.key = t3.field
and exists(Select ‘x’ from table t where t2.key = t.field) then as bamlbola suggested use QA to check the query plan…
]]>