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.
| Author |
Topic |
|
sth_Weird
Starting Member
38 Posts |
 Posted - 2009-10-22 : 02:40:18
|
hi,I've got a question regarding the performance of sql queries (dbms: SqlServer).There are two tables (1:n dependency), and I need all entries in table1 that has entries in table2 AND columnXYZ in table2 has to have a certain value. Table1 has around 5000 entries and table2 has around 100000 entries (growing).I could do:- using an INNER JOINSELECT table1.* FROM table1 INNER JOIN table2 WHERE table1.ID = table2.ID and table2.columnXYZ = 123 or else using EXISTSSELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2 WHERE ID = table1.ID AND table2.columnXYZ = 123) which one is faster? Does it depend on the number of entries in a table or is it always the same?And another one...In my software (written in c#) I need to get data from the db to build objects out of it. The data may come from different (sometimes alternative or optional) tables. Now I don't know if it's faster to have one big query, using left joins, and check if a column in a certain table is null in the resulting dataset, or I could query each table individually (there are about 10 of them, so in the first approach I would need 1 query, in the second I would need about 10). Any idea?thanxsth_Weird |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-10-22 : 12:23:11
|
| As with a lot of things it depends. Since you are comparing over the entire table my preference would be to use a JOIN. EXISTS and such have their place, but to me a join makes more sense.Here is an article by Peso, which is the opposite of what you are doing. :) However, it might help you discover other techniques:http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx |
 |
|
|
|
|
|
Performance questions...