View Complete Post
when we compare IN,EXISTS or INNER JOIN with performance wise which one is the best? Any Example to prove it?
I have two subqueries which build a string (varchar(200)), first subquery creates 200000 records and second 50000 records, how can I write a select statement to get records from subquery first with string existing in second subquery records.
Will sorting both the sub query results be helpful? Kindly advice for best performing select statement.
This question is sequel to my previous question in this forum:
Both of the WHERE clause will fetch equal number of records, but which one is best to use performance wise or just as a best practice?
where x > 9
where x >= 10
Edit: SQL Server Ver 2005 (on request of Kalen Delaney)
may i expect to have diff performance (reads, duration, ...) for:
from table1 as t1 inner join (select col1, col2 from table2) as t2
on t2.col1 = t1.col1
from table1 as t1 inner join table2 as t2
??? (consider i have indexes in table1 and table2 on col1)
i mean... does it matter if i join full table with full table or sub-selects (which reduce the number of columns)?
I have a query that is taking forever to run on the production server.
Do you see a reason why will it take forever to insert it to a temp table... If i put my query in a cte it works fine...
This procedure was using a table variable and i changed it to a temp table but it didnt help performance wise
I found out that when i just do the select statement it works the same amount of time that cte takes but when it inserts it a temp table (around 75 rows) it takes forever.
Any help will be appreciated.
If I do,
SELECT col1, col2....
FROM table1 AS A
(SELECT col3, col4....
FROM table2) B
ON A.col1 = B.col3
Or, I first take the inner query in a temp table first and then use that temp table directly in the JOIN? Consider that both the tables are really really huge (over 100 million records, and they have proper indexes).