Query tuning

How do you guys go about compare the efficiency of two queries?
What I usually do is run the "Display estimated execution plan" in the query
analyser and see each query cost (relative to the batch) and pick one that
gives lower percentage. Is this a good way to compare queries? Am I
missing something just by looking at that number?
Do I actually have to check IO costs, CPU running time, run the profile (and
look for what)?
Correction, tips and suggestion of best practice will be appreciated.
ThanksJustin
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
Yes , sure , as well as looking at EXECUTION PLAN of the both queries
http://www.sql-server-performance.c...performance.asp
"Justin" <nospam@.nospam.com> wrote in message
news:Oe3YBjflGHA.4244@.TK2MSFTNGP02.phx.gbl...
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the
> query analyser and see each query cost (relative to the batch) and pick
> one that gives lower percentage. Is this a good way to compare queries?
> Am I missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>|||Justin wrote:
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the que
ry
> analyser and see each query cost (relative to the batch) and pick one that
> gives lower percentage. Is this a good way to compare queries? Am I
> missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile (a
nd
> look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>
The estimated plan is a good place to start, you can identify the most
expensive parts of the query from that. You should also look at the I/O
stats and the actual execution plan. From the I/O stats, you can
identify the tables that are hit the hardest, and focus on potential
indexes, etc for those tables. Looking at the actual execution plan
will help you identify potential new indexes, improved joins, sorts, etc..

Query to compare table data between Test and Production?

I am debugging one of our programs and ran the fix in Test. I would like
to compare table 1 between Production and Test. I want the query to output
column 1 if Production <> Test output.

What is the best way to achieve this?

jeff

--
Message posted via http://www.sqlmonster.comHow many columns did your fix affect? If it's just one, and it's not
your primary key (or unique identifier), then you could simply do
something like:

SELECT col1
FROM Production.dbo.Table 1 t1 JOIN Test.dbo.Table t2 ON
t1.col1=t2.col1
WHERE t1.AffectedColumn <> t2.AffectedColumn

If that ain't it, can you post more so we can narrow it down a bit?

Stu|||Stu,

No thanks that will do it...I just had a lapse in memory on how to achieve
this.

Jeff

--
Message posted via http://www.sqlmonster.com

Query syntax for count comparison

I have 2 tables, with 2 columns each: Seq# and Client.
I need the syntax of a query to compare count(Client) by Seq#, between Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
It would be easier for us to understand your requirement, if you posted some
sample data to work with, and the expected resultset.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:2DFCF9B8-D4C6-4330-932A-678B0687FC86@.microsoft.com...
I have 2 tables, with 2 columns each: Seq# and Client.
I need the syntax of a query to compare count(Client) by Seq#, between
Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
|||Take a look at FULL OUTER JOIN in SQL Server Books Online. In simple cases
similar to yours, one can use it to compare data across different tables.
Anith