Comparing result set values of 2 queries ?

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ?

Please could someone out there offer some ideas ?

THANKS

JONselect t1.[id] from [inv table] t1 inner join [cost table] t2 on t1.[id]=t2.[id] where t1.amount+t1.vat<>t2.amount+t2.vat|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED

I have a slight problem though...

Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.

Table INV has single row entries for each ID(Primary Key)

Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.

ie.

COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177

INV
id7 78 99 ...TOTAL 177 ..In this case all is well

Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...

Am I making much sense...

CHEERS|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED

I have a slight problem though...

Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.

Table INV has single row entries for each ID(Primary Key)

Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.

ie.

COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177

INV
id7 78 99 ...TOTAL 177 ..In this case all is well

Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...

Am I making much sense...

CHEERS|||select t1.[id] from [inv table] t1 inner join (select t2.[id], cost_total=sum(t2.amount+t2.vat) from [cost table] t2 where t1.[id]=t2.[id] group by t2.[id]) co where t1.[id] = co.[id] and (t1.amount+t1.vat)<>co.cost_total

Comparing result set values of 2 queries ?

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ?

Please could someone out there offer some ideas ?

THANKS

JONI'd use:SELECT *
FROM inv
JOIN cost
ON (cost.id = inv.id)
WHERE inv.amount <> cost.amount
OR inv.vat <> cost.vat-PatP

Comparing result set values of 2 queries ?

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ?

Please could someone out there offer some ideas ?

THANKS

JONselect id, sum(amount) as inv_amount, sum(vat) as inv_vat,
cast(0, decimal(11,3)) as cost_amount, cast(0, decimal(11,3)) as cost_vat
into #inv
from INV
group by id

select id, sum(amount) as cost_amount, sum(vat) as cost_vat
into #cost
from cost
group by id

update x
set x.cost_amount = v.cost_amount,
x.cost_vat = v.cost_vat
from #inv x, #cost v
where x.id = v.id

select * from #inv|||How is this different from your first post (http://www.dbforums.com/t994762.html)?

-PatP|||???|||Originally posted by mkkmg
??? Click the link I posted. This isn't the first time they've posted that question.

-PatPsqlsql

Comparing queries for flagging conflicts

Hello all... I'm stuck, I cannot figure out how I should go about flagging
conflicts on a sheduling app. I currently have 8 columns (school grades)
that have class over the course of 9 periods. I am populating the asp page
fine, and making changes to the database with forms lists. I need to compare
all the results of one period (thats 8 results) so that i may find a
classroom conflict. Is there any solution in SQL?

This is my query:
sql = "SELECT * FROM schedule WHERE period ='"&num&"'"
I step through this 9 times in a for/next loop

Thanks in advance!
Alpay EnoAlpay Eno (eno@.spamsux.com) writes:
> Hello all... I'm stuck, I cannot figure out how I should go about
> flagging conflicts on a sheduling app. I currently have 8 columns
> (school grades) that have class over the course of 9 periods. I am
> populating the asp page fine, and making changes to the database with
> forms lists. I need to compare all the results of one period (thats 8
> results) so that i may find a classroom conflict. Is there any solution
> in SQL?

Dunno. If you post:
o CREATE TABLE statement(s) for the involved table(s)
o INSERT statements with sample data
o The desired output from that sample data

there are odds that you will get a more precise answer.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Compare two queries to see if their results are identical

Scenario - I would like to compare two queries to find out if their results
are identical. The first query returns all the values stored in an invoice
table for a given invoice no. The second query returns all the values
stored in a table holding booking information.
E.g.
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
InvoiceDetails WHERE InvoiceNo = 12345
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
BookingDetails WHERE BookingNo = 20
I need to know whether the results from the two queries are identical. One
way I can think of doing this is to perform two EXISTS queries, the first
one finding a row that does not exist in InvoiceDetails that does exist in
BookingDetails and a second query that finds a row that does not exist in
BookingDetails that does in InvoiceDetails. Then if one or the other tests
finds a row the answer is they are different otherwise they are the same.
But is there a better way to perform this test - can be be done is one test?
Thanks.One way is to do a full join and then look for NULLs on either side.
Another method is to UNION the two queries and in the result GROUP BY
<all columns> HAVING COUNT(*)=1 to show any mismatches. This second
method has the advantage that you can compare on the basis of NULL=NULL
if you wish.
David Portas
SQL Server MVP
--|||The first way is to create a cursor with your first query and within to
compare it with your second result, as you specify it with IF EXISTS.
You should also do the opposite operation with the second resultset.
But you should consider that cursor are not efiiciency for large table. So
you could also create tempory table from you resulstet and compare them
trough join operation,
Laurent
"Chris" <cw@.community.nospam> a crit dans le message de news:
eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||Try:
if exists (
select *
from InvoiceDetails as a inner join BookingDetails as b
on a.InvoiceNo = 12345 and b.BookingNo = 20
and a.Quantity = b.Quantity
and a.[Description] = b.[Description]
and a.UnitPrice = b.UnitPrice
and a.TaxPercentage = b.TaxPercentage
and a.Tax = b.Tax
and a.Price = b.Price
)
print 'identical'
else
print 'not identical'
go
AMB
"Chris" wrote:

> Scenario - I would like to compare two queries to find out if their result
s
> are identical. The first query returns all the values stored in an invoic
e
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical. On
e
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other test
s
> finds a row the answer is they are different otherwise they are the same.
> But is there a better way to perform this test - can be be done is one tes
t?
> Thanks.
>
>|||Can't you do a FULL OUTER JOIN and see if either side is NULL?
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||CREATE TABLE #T1(i int, j int)
CREATE TABLE #T2(i int, j int)
insert into #t1 values(1,2)
insert into #t2 values(1,2)
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- identical
--
1
update #t2 set j=3
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- different
--
2
drop table #t1
drop table #t2|||USE LRUKUVL
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL DROP TABLE #Table1
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL DROP TABLE #Table2
CREATE TABLE
#Table1
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
CREATE TABLE
#Table2
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
INSERT INTO #Table1
SELECT 1 , 4 , 'A'
UNION ALL SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , NULL , 'C'
UNION ALL SELECT 4 , 1 , 'D'
UNION ALL SELECT 5 , 1 , 'D'
UNION ALL SELECT 7 , 5 , NULL
INSERT INTO #Table2
SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , 2 , 'C'
UNION ALL SELECT 4 , 1 , NULL
UNION ALL SELECT 5 , 1 , 'E'
UNION ALL SELECT 6 , 1 , 'F'
UNION ALL SELECT 7 , 5 , NULL
/* return records that are different */
/* only id=2 and id=7 are equal - others are returned */
/* write a UDF to compaire a.data = b.data checking for equality, and is
nulls differences */
SELECT
*
FROM
#Table1 a
FULL OUTER JOIN
#Table2 b
ON
a.id = b.id
WHERE
a.id IS NULL
OR
b.id IS NULL
OR
( a.data1 IS NULL AND b.data1 IS NOT NULL )
OR
( a.data1 IS NOT NULL AND b.data1 IS NULL )
OR
( a.data1 != b.data1 )
OR
( a.data2 IS NULL AND b.data2 IS NOT NULL )
OR
( a.data2 IS NOT NULL AND b.data2 IS NULL )
OR
( a.data2 != b.data2 )
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
results
> are identical. The first query returns all the values stored in an
invoice
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
One
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other
tests
> finds a row the answer is they are different otherwise they are the same.|||Thanks to everyone for the replies
Chris
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>

Compare Two Queries - Help

Hi All,

I have two database that are duplicates of each other - but are on
different servers.

I need to write a script that will do a select from one table and then
compare it to another select of that table - but on the db on the other
server.

Is it possible to do that in a script? If so, how?

Thanks in advance.Brian Schultz (bdschultz@.gmail.com) writes:

Quote:

Originally Posted by

I have two database that are duplicates of each other - but are on
different servers.
>
I need to write a script that will do a select from one table and then
compare it to another select of that table - but on the db on the other
server.
>
Is it possible to do that in a script? If so, how?

SELECT ...
FROM localtbl l
FULL JOIN SERVER.db.dbo.remotetbl r ON l.keycol = r.keycol
WHERE l.keycol IS NULL
OR r.keycol IS NULL
OR a.col <b.col
OR a.col IS NULL AND b.col IS NOT NULL
OR a.col IS NOT NULL AND b.col IS NULL

SERVER is here a linked server that you have set up with sp_addlinkedserver.

If you need to do this on a large-scale basis, you should probably
consider a third-party product. I believe Red Gate has something called
DataCompare.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||you could try firefly. it's a free tool that i wrote:
http://www.getfirefly.net/
let me know if you have any problems.
thanks,
James

Compare Queries

I am creating an SP. I want to compare alternative ways of writing the query
to see which one is the most efficient. I am trying to use the execution
plan but it doesn't give an overall cost of the query so I can compare it to
the alternative query. Rather I get a break down of the various steps (this
query uses functions which produce aggregates and these steps are included
in the execution plan) . I want to explore the steps later, at this point I
just want to compare the two queries as a whole. What is the best way of
going about this? Regards, Chris.What I'd do to start is to fire up SQL Server Profiler, turn on the
SQL:BatchCompleted event, and collect the Reads, Writes, CPU, and Duration
columns. Run your queries and compare the output...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||Chris,
Put both queries in the same query window and run them. Then the
percentiles will reflect both queries. Therefore, if query1 and its
functions use 25% and query2 and its functions use 75%, that suggests that
query1 is more efficient. However, UDFs (and some other functions) do
degrade the reliability of these numbers. Still, it is a good first take.
However, running both queries repeatly and getting a set of actual execution
times for each will, in the final analysis, be a more accurate measure of
the queries.
RLF
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||The wording is a bit unclear. My SP could potentially contain a variety of
variables obtained from sub queries etc. This means there will be more than
just two queries to compare. How would you deal with a more complex scenario
with SP's that use temp tables or subqueries etc.
Regards, Chris.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
> Chris,
> Put both queries in the same query window and run them. Then the
> percentiles will reflect both queries. Therefore, if query1 and its
> functions use 25% and query2 and its functions use 75%, that suggests that
> query1 is more efficient. However, UDFs (and some other functions) do
> degrade the reliability of these numbers. Still, it is a good first take.
> However, running both queries repeatly and getting a set of actual
> execution times for each will, in the final analysis, be a more accurate
> measure of the queries.
> RLF
> "Chris" <nospam@.nospam.com> wrote in message
> news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>>I am creating an SP. I want to compare alternative ways of writing the
>>query to see which one is the most efficient. I am trying to use the
>>execution plan but it doesn't give an overall cost of the query so I can
>>compare it to the alternative query. Rather I get a break down of the
>>various steps (this query uses functions which produce aggregates and
>>these steps are included in the execution plan) . I want to explore the
>>steps later, at this point I just want to compare the two queries as a
>>whole. What is the best way of going about this? Regards, Chris.
>|||On Jun 29, 1:02 am, "Chris" <nos...@.nospam.com> wrote:
> The wording is a bit unclear. My SP could potentially contain a variety of
> variables obtained from sub queries etc. This means there will be more than
> just two queries to compare. How would you deal with a more complex scenario
> with SP's that use temp tables or subqueries etc.
> Regards, Chris.
> "Russell Fields" <russellfie...@.nomail.com> wrote in message
> news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
> > Chris,
> > Put both queries in the same query window and run them. Then the
> > percentiles will reflect both queries. Therefore, if query1 and its
> > functions use 25% and query2 and its functions use 75%, that suggests that
> > query1 is more efficient. However, UDFs (and some other functions) do
> > degrade the reliability of these numbers. Still, it is a good first take.
> > However, running both queries repeatly and getting a set of actual
> > execution times for each will, in the final analysis, be a more accurate
> > measure of the queries.
> > RLF
> > "Chris" <nos...@.nospam.com> wrote in message
> >news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
> >>I am creating an SP. I want to compare alternative ways of writing the
> >>query to see which one is the most efficient. I am trying to use the
> >>execution plan but it doesn't give an overall cost of the query so I can
> >>compare it to the alternative query. Rather I get a break down of the
> >>various steps (this query uses functions which produce aggregates and
> >>these steps are included in the execution plan) . I want to explore the
> >>steps later, at this point I just want to compare the two queries as a
> >>whole. What is the best way of going about this? Regards, Chris.- Hide quoted text -
> - Show quoted text -
Make each version of your SP. Then capture in profiler
SP:StatementCompleted
with read,writes,duration,CPU
You need to clear database and procedure cache before each
execution of SP to have accurate values
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Compare Queries

Chris,
Put both queries in the same query window and run them. Then the
percentiles will reflect both queries. Therefore, if query1 and its
functions use 25% and query2 and its functions use 75%, that suggests that
query1 is more efficient. However, UDFs (and some other functions) do
degrade the reliability of these numbers. Still, it is a good first take.
However, running both queries repeatly and getting a set of actual execution
times for each will, in the final analysis, be a more accurate measure of
the queries.
RLF
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>
On Jun 29, 1:02 am, "Chris" <nos...@.nospam.com> wrote:
> The wording is a bit unclear. My SP could potentially contain a variety of
> variables obtained from sub queries etc. This means there will be more than
> just two queries to compare. How would you deal with a more complex scenario
> with SP's that use temp tables or subqueries etc.
> Regards, Chris.
> "Russell Fields" <russellfie...@.nomail.com> wrote in message
> news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
>
>
>
> - Show quoted text -
Make each version of your SP. Then capture in profiler
SP:StatementCompleted
with read,writes,duration,CPU
You need to clear database and procedure cache before each
execution of SP to have accurate values
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Compare Queries

I am creating an SP. I want to compare alternative ways of writing the query
to see which one is the most efficient. I am trying to use the execution
plan but it doesn't give an overall cost of the query so I can compare it to
the alternative query. Rather I get a break down of the various steps (this
query uses functions which produce aggregates and these steps are included
in the execution plan) . I want to explore the steps later, at this point I
just want to compare the two queries as a whole. What is the best way of
going about this? Regards, Chris.What I'd do to start is to fire up SQL Server Profiler, turn on the
SQL:BatchCompleted event, and collect the Reads, Writes, CPU, and Duration
columns. Run your queries and compare the output...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||Chris,
Put both queries in the same query window and run them. Then the
percentiles will reflect both queries. Therefore, if query1 and its
functions use 25% and query2 and its functions use 75%, that suggests that
query1 is more efficient. However, UDFs (and some other functions) do
degrade the reliability of these numbers. Still, it is a good first take.
However, running both queries repeatly and getting a set of actual execution
times for each will, in the final analysis, be a more accurate measure of
the queries.
RLF
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||The wording is a bit unclear. My SP could potentially contain a variety of
variables obtained from sub queries etc. This means there will be more than
just two queries to compare. How would you deal with a more complex scenario
with SP's that use temp tables or subqueries etc.
Regards, Chris.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
> Chris,
> Put both queries in the same query window and run them. Then the
> percentiles will reflect both queries. Therefore, if query1 and its
> functions use 25% and query2 and its functions use 75%, that suggests that
> query1 is more efficient. However, UDFs (and some other functions) do
> degrade the reliability of these numbers. Still, it is a good first take.
> However, running both queries repeatly and getting a set of actual
> execution times for each will, in the final analysis, be a more accurate
> measure of the queries.
> RLF
> "Chris" <nospam@.nospam.com> wrote in message
> news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>|||On Jun 29, 1:02 am, "Chris" <nos...@.nospam.com> wrote:
> The wording is a bit unclear. My SP could potentially contain a variety of
> variables obtained from sub queries etc. This means there will be more tha
n
> just two queries to compare. How would you deal with a more complex scenar
io
> with SP's that use temp tables or subqueries etc.
> Regards, Chris.
> "Russell Fields" <russellfie...@.nomail.com> wrote in message
> news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
>
>
>
>
>
> - Show quoted text -
Make each version of your SP. Then capture in profiler
SP:StatementCompleted
with read,writes,duration,CPU
You need to clear database and procedure cache before each
execution of SP to have accurate values
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Common Table Expressions

CTE's appear to have 2 advantages:
1. Compliance with ANSI
2. Recursive queries.
Aside from these reasons, and perhaps a syntax preference by some, I do not
see a good reason to go out of my way to use them. The remaining situations
don't seem to give any benefit:
1. If I wanted to use the results of the CTE more than once, and the results
of the CTE did not change, I might as well use a temporary table so the CTE
doesn't have to be executed more than once.
2. In situations where I only use the CTE once, the CTE could frequently be
integrated into the existing query that leverages the CTE.
Comments? Thanks.
MarkMark,
Please see:
http://www.aspfaq.com/sql2005/show.asp?id=1
HTH
Jerry
"Mark" <mark@.nojunkmail.com> wrote in message
news:%23NoYQJC1FHA.3376@.TK2MSFTNGP14.phx.gbl...
> CTE's appear to have 2 advantages:
> 1. Compliance with ANSI
> 2. Recursive queries.
> Aside from these reasons, and perhaps a syntax preference by some, I do
> not see a good reason to go out of my way to use them. The remaining
> situations don't seem to give any benefit:
> 1. If I wanted to use the results of the CTE more than once, and the
> results of the CTE did not change, I might as well use a temporary table
> so the CTE doesn't have to be executed more than once.
> 2. In situations where I only use the CTE once, the CTE could frequently
> be integrated into the existing query that leverages the CTE.
> Comments? Thanks.
> Mark
>