SQL SERVER perfomance ?

Question

I Joined two tables , each one containing more than 5000 records . I used two methods to join . In the first query I used JOIN keyword and gave the condition in ON condition . In the second query I just used a comma operator between the tables and gave the joining condition in WHERE clause .

Which one will be effective regarding time and cost ....?

When i look into the execution plan First query took 61 % cost for the TABLE SCAN and 38% for the HASH MATCH ( INNER JOIN )

The second query took 69 % cost for the TABLE SCAN and 31% for the HASH MATCH ( INNER JOIN ) .

Can you explain me this difference ?

Iam assuming that First one is effective , Because the table scan cost is less . Is it right...?

Answer 1

Define 'effective'. If speed is your goal, select 'show client statistics', run both queries at least a dozen times (in different query windows, of course), and evaluate the results.

Answer 2

the two techniques are logically equivalent but can have vastly different performance hits depending on how the parser plans the query. You'll need to consider two key factors when deciding between the techniques:

1. Which version produces a query plan that is better optimized.

2. Which is more maintainable should the code need to be retrofitted later to incorporate more joined (INNER or OUTER) tables.

INNER JOIN vs WHERE clause

Answer 3

TABLE SCAN's means it has to read through every single row in a table. Normally with Execution plans you try to get rid of table scan by using indexes etc where possible. Table scans are generally the main cause of slow running queries. (in my experience)

Percentage decrease is only an improvement if the overall time the SQL takes to run is reduces compared to what it was previously

For example if the first query with a slightly reduced TABLE SCAN took 20 seconds instead of 10, then it’s not as effective.

Of course if your looking to optimise your query then there is a whole host of methods you can apply to try and improve it. (Index Tuning Wizard, Create Indexes (Clustered/Non Clustered), View Execution Plan - Identify potential bottlenecks etc…)

SQL Optimisation its an artform in itself

Answer 4

It sounds to me like the query plan was the same in both cases... since you had a table scan and a hash match both times...

Before you try and compare those percentages... let me illustrate the danger with this question:

which is bigger

61% of 465

or

69% of 234

??

You might see in this case that 69% of 234 would be by far "quicker" than 61% for 465. Its all relative to the total cost of the query.

Be careful about just comparing percentages because you don't know what the actual value that corresponds to 100% in both cases is... e.g. the TOTAL execution cost might have been lower in the second case.