Query Help

SQL DB Query Help
I have several suppliers of products.
When a customer does a Search
I want to return only the Lowest cost items by Part Number.
The query could return many different Part Numbers.
I also need to return Description, Part Number, Qty on Hand, Supplier etc.
These are all in the table.
Note that Description, Qty and Supplier are usually different.
Example items in DB
Part Desc Cost Qty Supplier
123 Widget 1.00 10 1
123 A Widget 2.00 5 2
123 Widget A 3.00 20 3
567 B Widget 9.00 3 1
567 Widget B 8.00 17 2
567 Widget 12.00 8 3

I would like to return
Part Desc Cost Qty Supplier
123 Widget 1.00 10 1
567 Widget B 8.00 17 2

Thanks in advance

Perhaps something like this:

SELECT
P.* FROM Parts P
INNER JOIN
(
SELECT P3.part as PartNum, MIN(P3.Cost) as MinCost
FROM Parts P3
GROUP BY P3.Part) AS P2
ON P.Part = P2.partnum
AND P.cost = P2.mincost

Query Governor Cost Limit

I have enabled the query governor on our SQL2000 SP2 server with a
threshold of 3600. Now, some of the maintenance jobs fail due to the
limit being to low (e.g. one of the user databases integrity check
fails nightly).

I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
before the line in the step which reads 'EXECUTE
master.dbo.xp_sqlmaint N'-Plan etc'
but it has no effect.

Does anyone know how to get around this situation without using
sp_configure to change the query governor settings at a systemwide
level?

GC."Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0311060139.37e6f333@.posting.google.c om...
> I have enabled the query governor on our SQL2000 SP2 server with a
> threshold of 3600. Now, some of the maintenance jobs fail due to the
> limit being to low (e.g. one of the user databases integrity check
> fails nightly).
> I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
> before the line in the step which reads 'EXECUTE
> master.dbo.xp_sqlmaint N'-Plan etc'
> but it has no effect.
> Does anyone know how to get around this situation without using
> sp_configure to change the query governor settings at a systemwide
> level?
> GC.

I'm not entirely sure about this, however xp_sqlmaint is a wrapper for
sqlmaint.exe, and I assume that sqlmaint.exe connects to the server as a
separate connection. According to BOL, the SET option only affects the
current connection, ie. the one where you execute xp_sqlmaint. sqlmaint.exe
is effectively an entirely separate client program.

One workaround would be to write your own maintenance procedures with all
the DBCC commands etc. in a single batch and executing in the same
connection. Or set the server default back to zero, and use the SET option
only in the code that needs it, if that's less work.

Simon|||Thanks - what I've tried to do is use the below commands but it still
ignores the QUERY GOVERNOR COST LIMIT setting so it's back to the
drawing board...

SET QUERY_GOVERNOR_COST_LIMIT 0
exec xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\sqlmaint.exe" -S SERVER3 -D data -Rpt
e:\mssql\MSSQL\LOG\data_DB_Maintenance_Plan2.txt -DelTxtRpt 4WEEKS
-WriteHistory -CkDB'

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<3faa94e6$1_2@.news.bluewin.ch>...
> "Garry Clarke" <gclarke@.euro.banta.com> wrote in message
> news:fed38413.0311060139.37e6f333@.posting.google.c om...
> > I have enabled the query governor on our SQL2000 SP2 server with a
> > threshold of 3600. Now, some of the maintenance jobs fail due to the
> > limit being to low (e.g. one of the user databases integrity check
> > fails nightly).
> > I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
> > before the line in the step which reads 'EXECUTE
> > master.dbo.xp_sqlmaint N'-Plan etc'
> > but it has no effect.
> > Does anyone know how to get around this situation without using
> > sp_configure to change the query governor settings at a systemwide
> > level?
> > GC.
> I'm not entirely sure about this, however xp_sqlmaint is a wrapper for
> sqlmaint.exe, and I assume that sqlmaint.exe connects to the server as a
> separate connection. According to BOL, the SET option only affects the
> current connection, ie. the one where you execute xp_sqlmaint. sqlmaint.exe
> is effectively an entirely separate client program.
> One workaround would be to write your own maintenance procedures with all
> the DBCC commands etc. in a single batch and executing in the same
> connection. Or set the server default back to zero, and use the SET option
> only in the code that needs it, if that's less work.
> Simon

Query Governor

Hello all,
Im setting up a SQL-Server and there is an option named Query
Governor to avoid queries to exceed a specific cost. How is this cost
measured ? Milliseconds ?
best regards,
Evandro
You can set this in enterprise manager under server properties using the
server settings tab.
Or
EXEC sp_configure 'show advanced option', '1'
exec sp_configure N'query governor cost limit', 100 (or the max elapsed time
in seconds)
http://www.schemamania.org/jkl/books..._server_51.htm
http://msdn.microsoft.com/library/de...onfig_73u6.asp
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:eBM64z28EHA.1396@.tk2msftngp13.phx.gbl...
> Hello all,
> Im setting up a SQL-Server and there is an option named Query
> Governor to avoid queries to exceed a specific cost. How is this cost
> measured ? Milliseconds ?
>
> best regards,
> Evandro
>
|||Hi Evandro,
EXEC sp_configure N'query governor cost limit', 100 is a server-wide
setting. Unless you have a very specific reason, do not set this option.
At the client level, statementwise one can use
SET QUERY_GOVERNOR_COST_LIMIT
Thanks
Yogish

Query Governor

Hello all,
I´m setting up a SQL-Server and there is an option named Query
Governor to avoid queries to exceed a specific cost. How is this cost
measured ? Milliseconds ?
best regards,
EvandroYou can set this in enterprise manager under server properties using the
server settings tab.
Or
EXEC sp_configure 'show advanced option', '1'
exec sp_configure N'query governor cost limit', 100 (or the max elapsed time
in seconds)
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/1_server_51.htm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_73u6.asp
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:eBM64z28EHA.1396@.tk2msftngp13.phx.gbl...
> Hello all,
> I´m setting up a SQL-Server and there is an option named Query
> Governor to avoid queries to exceed a specific cost. How is this cost
> measured ? Milliseconds ?
>
> best regards,
> Evandro
>|||Hi Evandro,
EXEC sp_configure N'query governor cost limit', 100 is a server-wide
setting. Unless you have a very specific reason, do not set this option.
At the client level, statementwise one can use
SET QUERY_GOVERNOR_COST_LIMIT
--
Thanks
Yogishsql

Query Governor

Hello all,
Im setting up a SQL-Server and there is an option named Query
Governor to avoid queries to exceed a specific cost. How is this cost
measured ? Milliseconds ?
best regards,
EvandroYou can set this in enterprise manager under server properties using the
server settings tab.
Or
EXEC sp_configure 'show advanced option', '1'
exec sp_configure N'query governor cost limit', 100 (or the max elapsed time
in seconds)
http://www.schemamania.org/jkl/book...1_server_51.htm
http://msdn.microsoft.com/library/d... />
g_73u6.asp
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:eBM64z28EHA.1396@.tk2msftngp13.phx.gbl...
> Hello all,
> Im setting up a SQL-Server and there is an option named Query
> Governor to avoid queries to exceed a specific cost. How is this cost
> measured ? Milliseconds ?
>
> best regards,
> Evandro
>|||Hi Evandro,
EXEC sp_configure N'query governor cost limit', 100 is a server-wide
setting. Unless you have a very specific reason, do not set this option.
At the client level, statementwise one can use
SET QUERY_GOVERNOR_COST_LIMIT
Thanks
Yogish

Query for Segments

All
I have a table that contains data for when a stretch of road was last
resurfaced and I am trying to return the cost per year for each completed
road segment. In the example below the road was resurfaced in 1971 and 1973
.
In 1971 the road was resurfaced from 1 kilometre to 4 kilometres and 5
kilometres to 9 kilometres. In 1973 the kilometre in between was resurfaced
.
I only want to show from start to finish the complete parts of the road that
were resurfaced and when as illustrated below:
CREATE TABLE rd_resurface
(
RoadNo NVARCHAR(20),
KMStart INT,
KMEnd INT,
Cost NUMERIC(13, 2),
Deprec NUMERIC(13, 2),
[Year] SMALLINT
)
GO
INSERT rd_resurface SELECT 'H001', 1, 2, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 2, 3, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 3, 4, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 4, 5, 100.00, 10.00, 1973
INSERT rd_resurface SELECT 'H001', 5, 6, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 6, 7, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 7, 8, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 8, 9, 100.00, 10.00, 1971
I am trying to return the results below:
RoadNo KMStart KMEnd Cost Deprec Year
H001 1 4 $300.00 $30.00 1971
H001 4 5 $100.00 $10.00 1973
H001 5 9 $400.00 $40.00 1971
Thanks for any assistance that can be provided.David,
I'm not sure I got it right as you didn't say if the same km of road can
appear more than once in the same year. Assuming it can't...
The following query calculates a grouping factor which is the last kmstart
value within the segment:
select *,
(select min(kmstart)
from rd_resurface as r2
where r2.roadno = r1.roadno
and r2.year = r1.year
and r2.kmstart >= r1.kmstart
and not exists
(select *
from rd_resurface as r3
where r3.roadno = r2.roadno
and r3.year = r2.year
and r3.kmstart = r2.kmend)) as grp
from rd_resurface as r1
RoadNo KMStart KMEnd Cost Deprec Year grp
-- -- -- -- -- -- --
H001 1 2 100.00 10.00 1971 3
H001 2 3 100.00 10.00 1971 3
H001 3 4 100.00 10.00 1971 3
H001 4 5 100.00 10.00 1973 4
H001 5 6 100.00 10.00 1971 8
H001 6 7 100.00 10.00 1971 8
H001 7 8 100.00 10.00 1971 8
H001 8 9 100.00 10.00 1971 8
The rest is simply to group the data and return the desired aggregates:
select roadno, year, min(kmstart) as kmstart, max(kmend) as kmend,
sum(cost) as cost, sum(deprec) as deprec
from (select *,
(select min(kmstart)
from rd_resurface as r2
where r2.roadno = r1.roadno
and r2.year = r1.year
and r2.kmstart >= r1.kmstart
and not exists
(select *
from rd_resurface as r3
where r3.roadno = r2.roadno
and r3.year = r2.year
and r3.kmstart = r2.kmend)) as grp
from rd_resurface as r1) as d
group by roadno, year, grp
roadno year kmstart kmend cost deprec
-- -- -- -- -- --
H001 1971 1 4 300.00 30.00
H001 1971 5 9 400.00 40.00
H001 1973 4 5 100.00 10.00
BG, SQL Server MVP
www.SolidQualityLearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:3A73BA53-4633-4E5C-B72C-EBB3D4B73472@.microsoft.com...
> All
> I have a table that contains data for when a stretch of road was last
> resurfaced and I am trying to return the cost per year for each completed
> road segment. In the example below the road was resurfaced in 1971 and
> 1973.
> In 1971 the road was resurfaced from 1 kilometre to 4 kilometres and 5
> kilometres to 9 kilometres. In 1973 the kilometre in between was
> resurfaced.
> I only want to show from start to finish the complete parts of the road
> that
> were resurfaced and when as illustrated below:
> CREATE TABLE rd_resurface
> (
> RoadNo NVARCHAR(20),
> KMStart INT,
> KMEnd INT,
> Cost NUMERIC(13, 2),
> Deprec NUMERIC(13, 2),
> [Year] SMALLINT
> )
> GO
> INSERT rd_resurface SELECT 'H001', 1, 2, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 2, 3, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 3, 4, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 4, 5, 100.00, 10.00, 1973
> INSERT rd_resurface SELECT 'H001', 5, 6, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 6, 7, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 7, 8, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 8, 9, 100.00, 10.00, 1971
> I am trying to return the results below:
> RoadNo KMStart KMEnd Cost Deprec Year
> H001 1 4 $300.00 $30.00 1971
> H001 4 5 $100.00 $10.00 1973
> H001 5 9 $400.00 $40.00 1971
>
> Thanks for any assistance that can be provided.sql

Query estimate cost and real run time different

I have a query, before I do any change, the estimate execute plan show me th
e cost is 1400, the real run time is about 1 minute 33 seconds.
After I eliminated some concatinated column, the estimate execute plan shows
me cost reduce to 66, I expect the query will run quite faster than the ori
ginal one, but real run time still keep in 1 minute 27 seconds.
I know estimate execute cost is not accurate, but should not be such a big d
ifference.
Any body can tell me why, what is the good way to tunning my query?
Thanks in advance,
HGHG,
The best way to tune your query is to actually run it and see what it does.
Examine the execution plan and focus on the parts of the plan that are most
expensive. If one step takes 75% of the effort and five other steps each
take 5%, then examine the 75% and see what you can do about it. (Better
join criteria, another index, recasting the code to use a different
approach, etc.)
Estimated plan is sometimes mildly helpful, but (as you note) cannot be
relied upon. Also, even the actual execution plan has blind spots. For
example, it will not measure how much time is spent in a UDF, the IS_MEMBER
function, and so forth, viewing them as nearly free.
Therefore, in addition to the execution plan, test with timing statements
that show how much clock time the steps take. If you run several tests you
will get a good measure of the execution time.
Russell Fields
"HG" <anonymous@.discussions.microsoft.com> wrote in message
news:C01707AF-3B18-45AC-B188-52D313EBE860@.microsoft.com...
> I have a query, before I do any change, the estimate execute plan show me
the cost is 1400, the real run time is about 1 minute 33 seconds.
> After I eliminated some concatinated column, the estimate execute plan
shows me cost reduce to 66, I expect the query will run quite faster than
the original one, but real run time still keep in 1 minute 27 seconds.
> I know estimate execute cost is not accurate, but should not be such a big
difference.
> Any body can tell me why, what is the good way to tunning my query?
> Thanks in advance,
> HG|||I am new to sql. Can you provide an example of a timing statement that will
show me how long a querry took?|||Larry,
DECLARE @.StartTime DATETIME
SET @.StartTime = GETDATE()
Execute your code here
SELECT DATEDIFF(ms,@.StartTime, GETDATE()) AS ElapsedMilliseconds
Because there are variable, run this a few times to get a best time.
Compare best times of two different strategies to determine how they
compare.
Russell Fields
"Larrry Pensil" <anonymous@.discussions.microsoft.com> wrote in message
news:56CC0769-A7B1-4E97-ADE6-FEF9AF056A77@.microsoft.com...
> I am new to sql. Can you provide an example of a timing statement that
will show me how long a querry took?

Query cost and execution plan

Hi all,
I have written two queries that provide the same results(using different
types of joins.).
For choosing the better one, I pasted both of them in one script in query
analyzer and executed them.
Here is the summery of the result in execution plan:
First query:
- Query Cost: 55%
- I/O Cost: Scan count 7, logical reads 14
- Execution plan: Looks simple(a few icons..)
Second query:
- Query Cost: 45%
- I/O Cost: Scan count 9, logical reads 34
- Execution plan: Looks more complicated(a lot of icons!)
Which query will have better performance? Should I consider the query
cost(relative to the batch) or the I/O cost is more important? I don't know
that why the second query has lower cost while it has more I/O and more
complicated plan!
Thanks in advance,
Amin
On Mon, 10 May 2004 21:03:39 +0430, Amin Sobati wrote:

>Hi all,
>I have written two queries that provide the same results(using different
>types of joins.).
>For choosing the better one, I pasted both of them in one script in query
>analyzer and executed them.
>Here is the summery of the result in execution plan:
>First query:
>- Query Cost: 55%
>- I/O Cost: Scan count 7, logical reads 14
>- Execution plan: Looks simple(a few icons..)
>Second query:
>- Query Cost: 45%
>- I/O Cost: Scan count 9, logical reads 34
>- Execution plan: Looks more complicated(a lot of icons!)
>Which query will have better performance? Should I consider the query
>cost(relative to the batch) or the I/O cost is more important? I don't know
>that why the second query has lower cost while it has more I/O and more
>complicated plan!
>Thanks in advance,
>Amin
Hi Amin,
With only this information to go on, I'd say use the first query. The
reason for the lower query cost might well be that some (or maybe even
all) required data is read from disk for the first query, but still
available in the cache for the second query.
You get a beter test if you issue DBCC DROPCLEANBUFFERS before each
query. This will ensure that the cache is empty.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo!
Actually there was no change in result(by using DBCC DROPCLEANBUFFERS),
which is confusing for me too!
Now, should I prefer the second query to the first?
Amin
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7jpv9097l4u4bdnm5blhd4ql72oped6f70@.4ax.com... [vbcol=seagreen]
> On Mon, 10 May 2004 21:03:39 +0430, Amin Sobati wrote:
know
> Hi Amin,
> With only this information to go on, I'd say use the first query. The
> reason for the lower query cost might well be that some (or maybe even
> all) required data is read from disk for the first query, but still
> available in the cache for the second query.
> You get a beter test if you issue DBCC DROPCLEANBUFFERS before each
> query. This will ensure that the cache is empty.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Tue, 11 May 2004 11:37:02 +0430, Amin Sobati wrote:

>Thanks Hugo!
>Actually there was no change in result(by using DBCC DROPCLEANBUFFERS),
>which is confusing for me too!
>Now, should I prefer the second query to the first?
>Amin
Hi Amin,
Hard to say. Some other things you might try:
1. Reverse the order in which you execute the queries in your test
scripts (should not make any difference with DBCC DROPCLEANBUFFERS,
but you never know).
2. Add "select getdate()" before and after each of the two queries, so
you can compare the actual execution time.
3. Add DBCC FREEPROCCACHE as well (sorry, forgot to mention that when
I suggested DBCC DROPCLEANBUFFERS). This will force recompilation of
your queries, so you can be sure it's not executed with an old
execution plan (that might be based on outdated statistics).
4. Post the queries here. Maybe I (or someone else) can make some
comments based on that. (Do add relevant table definitions, in the
form of CREATE TABLE statements as well!)
I assume you are already aware that you should run your performance
tests on the production system or on a test system with about the same
amount and distribution of data and the same specifications, that you
should try to test when no other users or processes use SQL Server (or
other apps on the same server) and that you should make sure your
table statistics are updated before running your tests.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I assume you posted the estimations below. I would not base conclusions
on these estimations. Whenever possible I would run the query with I/O
count and CPU count turned on. In the end, the query (plan) with the
lowest elapsed time is the winner...
Gert-Jan
Amin Sobati wrote:
> Hi all,
> I have written two queries that provide the same results(using different
> types of joins.).
> For choosing the better one, I pasted both of them in one script in query
> analyzer and executed them.
> Here is the summery of the result in execution plan:
> First query:
> - Query Cost: 55%
> - I/O Cost: Scan count 7, logical reads 14
> - Execution plan: Looks simple(a few icons..)
> Second query:
> - Query Cost: 45%
> - I/O Cost: Scan count 9, logical reads 34
> - Execution plan: Looks more complicated(a lot of icons!)
> Which query will have better performance? Should I consider the query
> cost(relative to the batch) or the I/O cost is more important? I don't know
> that why the second query has lower cost while it has more I/O and more
> complicated plan!
> Thanks in advance,
> Amin
(Please reply only to the newsgroup)
|||Hugo,
Thank you for the tips!
Actually this is my case:
I have created "Students" table and "StudentExam" to track the exams that
each student has taken. I know that I must have an "Exams" table to keep
exam IDs, but I ignore that for this stage.
This is the script:
create table Students (
SID int,
SName varchar(10))
create table StudentExam (
SID int,
EName varchar(10))
-- inserting some records for each table
insert Students values(1,'David')
insert Students values(2,'Peter')
insert Students values(3,'Sarah')
insert StudentExam values(1,'sql')
insert StudentExam values(1,'vb')
insert StudentExam values(1,'asp')
insert StudentExam values(2,'vb')
insert StudentExam values(2,'asp')
insert StudentExam values(3,'xml')
insert StudentExam values(3,'access')
--Execute this insert statement for 12 times to have around 30,000 records:
insert StudentExam select * from StudentExam
--Now creating indexes:
create clustered index IX1 on StudentExam(sid)
create index IX2 on StudentExam(EName)
-- Now suppose that I need to delete the students who have NOT taken 'sql'
exam.
--These are two delete statements that i want to choose the perfect one:
set statistics IO ON
begin tran
delete Students from StudentExam where Students.sid not in (select
StudentExam.sid from StudentExam where StudentExam.EName='sql')
rollback tran
begin tran
delete Students from StudentExam where 'sql' not in (select
StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
rollback tran
The first delete statement has 12 logical reads, but the second one has 38.
The cost for first one is 54%, that I think it must have lower cost because
of the IO.
If I create the second index as clustered, and the first one as
non-clustered, the logical read for second delete statement will be 14, but
no change in the main cost.
Thanks in advance,
Amin
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:r141a0dp9l5ufrlnen38mgpclkih1l6b7d@.4ax.com...
> On Tue, 11 May 2004 11:37:02 +0430, Amin Sobati wrote:
>
> Hi Amin,
> Hard to say. Some other things you might try:
> 1. Reverse the order in which you execute the queries in your test
> scripts (should not make any difference with DBCC DROPCLEANBUFFERS,
> but you never know).
> 2. Add "select getdate()" before and after each of the two queries, so
> you can compare the actual execution time.
> 3. Add DBCC FREEPROCCACHE as well (sorry, forgot to mention that when
> I suggested DBCC DROPCLEANBUFFERS). This will force recompilation of
> your queries, so you can be sure it's not executed with an old
> execution plan (that might be based on outdated statistics).
> 4. Post the queries here. Maybe I (or someone else) can make some
> comments based on that. (Do add relevant table definitions, in the
> form of CREATE TABLE statements as well!)
> I assume you are already aware that you should run your performance
> tests on the production system or on a test system with about the same
> amount and distribution of data and the same specifications, that you
> should try to test when no other users or processes use SQL Server (or
> other apps on the same server) and that you should make sure your
> table statistics are updated before running your tests.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Gert-Jan,
Thank you for your reply. Would you please read my reply to Hugo and write
me your thoughts
Amin
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:40A125B3.AB8FF522@.toomuchspamalready.nl...[vbcol=seagreen]
> I assume you posted the estimations below. I would not base conclusions
> on these estimations. Whenever possible I would run the query with I/O
> count and CPU count turned on. In the end, the query (plan) with the
> lowest elapsed time is the winner...
> Gert-Jan
>
> Amin Sobati wrote:
query[vbcol=seagreen]
know
> --
> (Please reply only to the newsgroup)
|||"Amin Sobati" <amins@.morva.net> wrote in message
news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> Hugo,
> Thank you for the tips!
> Actually this is my case:
> I have created "Students" table and "StudentExam" to track the exams that
> each student has taken. I know that I must have an "Exams" table to keep
> exam IDs, but I ignore that for this stage.
You can't ignore that and expect to get any meaningful performance data.
Here's a more complete example with primary keys, referential integrity a
whole lot of students, 90% of whom have taken the SQL exam.
Both queries use exactly the same plan, and have exactly the same cost.
David
create table Students (
SID int not null identity primary key,
SName varchar(10))
create table Exams(EName varchar(10) not null primary key)
create table StudentExam (
SID int not null references Students on delete cascade,
EName varchar(10) not null references Exams on delete cascade,
constraint pk_StudentExam
primary key (SID,EName)
)
create index IX2 on StudentExam(EName)
-- inserting some records for each table
insert Students values('David')
insert Students values('Peter')
insert Students values('Sarah')
-- let the students start breeding
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Exams(EName) values('sql')
insert Exams(EName) values('asp')
insert Exams(EName) values('vb')
insert Exams(EName) values('xml')
insert Exams(EName) values('access')
--add exams for each SID,EName pair
insert StudentExam(SID,EName)
select SID, EName
from Students cross join Exams
--erase the SQL exam for 10% of the students
delete studentExam
where EName = 'sql'
and sid%10=0
-- Now suppose that I need to delete the students who have NOT taken
'sql'exam.
--These are two delete statements that i want to choose the perfect one:
set statistics IO ON
print ' QUERY 1'
begin tran
delete Students where Students.sid not in (select
StudentExam.sid from StudentExam where StudentExam.EName='sql')
rollback tran
print ' QUERY 2'
begin tran
delete Students where 'sql' not in (select
StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
rollback tran
/*
QUERY 1
(1228 row(s) affected)
Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
read-ahead reads 0.
Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
read-ahead reads 0.
QUERY 2
(1228 row(s) affected)
Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
read-ahead reads 0.
Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
read-ahead reads 0.
*/
|||David,
Thank you for the great example.
I admit that I must do everything right to get the best result, but I cannot
recognize that why a query might have more IO but lower cost(in my example).
Can I count on the cost(relative to the batch) as a criteria for choosing a
query?
Thanks,
Amin
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
that
> You can't ignore that and expect to get any meaningful performance data.
> Here's a more complete example with primary keys, referential integrity a
> whole lot of students, 90% of whom have taken the SQL exam.
> Both queries use exactly the same plan, and have exactly the same cost.
> David
>
> create table Students (
> SID int not null identity primary key,
> SName varchar(10))
> create table Exams(EName varchar(10) not null primary key)
>
> create table StudentExam (
> SID int not null references Students on delete cascade,
> EName varchar(10) not null references Exams on delete cascade,
> constraint pk_StudentExam
> primary key (SID,EName)
> )
> create index IX2 on StudentExam(EName)
> -- inserting some records for each table
> insert Students values('David')
> insert Students values('Peter')
> insert Students values('Sarah')
> -- let the students start breeding
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
>
> insert Exams(EName) values('sql')
> insert Exams(EName) values('asp')
> insert Exams(EName) values('vb')
> insert Exams(EName) values('xml')
> insert Exams(EName) values('access')
> --add exams for each SID,EName pair
> insert StudentExam(SID,EName)
> select SID, EName
> from Students cross join Exams
> --erase the SQL exam for 10% of the students
> delete studentExam
> where EName = 'sql'
> and sid%10=0
> -- Now suppose that I need to delete the students who have NOT taken
> 'sql'exam.
> --These are two delete statements that i want to choose the perfect one:
> set statistics IO ON
>
> print ' QUERY 1'
> begin tran
> delete Students where Students.sid not in (select
> StudentExam.sid from StudentExam where StudentExam.EName='sql')
> rollback tran
> print ' QUERY 2'
> begin tran
> delete Students where 'sql' not in (select
> StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
> rollback tran
> /*
> QUERY 1
> (1228 row(s) affected)
> Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> read-ahead reads 0.
> Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> read-ahead reads 0.
> QUERY 2
> (1228 row(s) affected)
> Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> read-ahead reads 0.
> Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> read-ahead reads 0.
> */
>
|||The query optimizer's effort depends on the estimated cost of the query.
First, the query optimizer will search for any 'obvious' query plans. If
the estimated time of these solutions is no more than 1 or 2 seconds, it
will not look any further, and simply use that plan. These kinds of
query plans can depend on the way the query is written. However, if
there is any performance difference between different query plans, the
difference will be small.
If there is no obvious fast query plan, the full compilation phase is
started. This means (virtually) all possible query plans will be
evaluated. If this phase starts to take too long, then the it is cut
short, and the fastest plan that was found thus far will be used.
This is why you are seeing that in David's example, SQL-Server will use
the same query plan for both queries. I can add another syntax (see
below), which SQL-Server will probably use the same query plan for.
print ' QUERY 3'
begin tran
delete Students where not exists (
select 1
from StudentExam
where Students.sid=StudentExam.sid
and StudentExam.EName='sql'
)
rollback tran
So what I am basically saying, is that the differences you are seeing
are not significant.
Gert-Jan
Amin Sobati wrote:[vbcol=seagreen]
> David,
> Thank you for the great example.
> I admit that I must do everything right to get the best result, but I cannot
> recognize that why a query might have more IO but lower cost(in my example).
> Can I count on the cost(relative to the batch) as a criteria for choosing a
> query?
> Thanks,
> Amin
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...
> that
> 0,
> 0,
(Please reply only to the newsgroup)

Query cost and execution plan

Hi all,
I have written two queries that provide the same results(using different
types of joins.).
For choosing the better one, I pasted both of them in one script in query
analyzer and executed them.
Here is the summery of the result in execution plan:
First query:
- Query Cost: 55%
- I/O Cost: Scan count 7, logical reads 14
- Execution plan: Looks simple(a few icons..)
Second query:
- Query Cost: 45%
- I/O Cost: Scan count 9, logical reads 34
- Execution plan: Looks more complicated(a lot of icons!)
Which query will have better performance? Should I consider the query
cost(relative to the batch) or the I/O cost is more important? I don't know
that why the second query has lower cost while it has more I/O and more
complicated plan!
Thanks in advance,
AminOn Mon, 10 May 2004 21:03:39 +0430, Amin Sobati wrote:
>Hi all,
>I have written two queries that provide the same results(using different
>types of joins.).
>For choosing the better one, I pasted both of them in one script in query
>analyzer and executed them.
>Here is the summery of the result in execution plan:
>First query:
>- Query Cost: 55%
>- I/O Cost: Scan count 7, logical reads 14
>- Execution plan: Looks simple(a few icons..)
>Second query:
>- Query Cost: 45%
>- I/O Cost: Scan count 9, logical reads 34
>- Execution plan: Looks more complicated(a lot of icons!)
>Which query will have better performance? Should I consider the query
>cost(relative to the batch) or the I/O cost is more important? I don't know
>that why the second query has lower cost while it has more I/O and more
>complicated plan!
>Thanks in advance,
>Amin
Hi Amin,
With only this information to go on, I'd say use the first query. The
reason for the lower query cost might well be that some (or maybe even
all) required data is read from disk for the first query, but still
available in the cache for the second query.
You get a beter test if you issue DBCC DROPCLEANBUFFERS before each
query. This will ensure that the cache is empty.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo!
Actually there was no change in result(by using DBCC DROPCLEANBUFFERS),
which is confusing for me too!
Now, should I prefer the second query to the first?
Amin
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7jpv9097l4u4bdnm5blhd4ql72oped6f70@.4ax.com...
> On Mon, 10 May 2004 21:03:39 +0430, Amin Sobati wrote:
> >Hi all,
> >I have written two queries that provide the same results(using different
> >types of joins.).
> >For choosing the better one, I pasted both of them in one script in query
> >analyzer and executed them.
> >Here is the summery of the result in execution plan:
> >
> >First query:
> >- Query Cost: 55%
> >- I/O Cost: Scan count 7, logical reads 14
> >- Execution plan: Looks simple(a few icons..)
> >
> >Second query:
> >- Query Cost: 45%
> >- I/O Cost: Scan count 9, logical reads 34
> >- Execution plan: Looks more complicated(a lot of icons!)
> >
> >Which query will have better performance? Should I consider the query
> >cost(relative to the batch) or the I/O cost is more important? I don't
know
> >that why the second query has lower cost while it has more I/O and more
> >complicated plan!
> >
> >Thanks in advance,
> >
> >Amin
> Hi Amin,
> With only this information to go on, I'd say use the first query. The
> reason for the lower query cost might well be that some (or maybe even
> all) required data is read from disk for the first query, but still
> available in the cache for the second query.
> You get a beter test if you issue DBCC DROPCLEANBUFFERS before each
> query. This will ensure that the cache is empty.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 11 May 2004 11:37:02 +0430, Amin Sobati wrote:
>Thanks Hugo!
>Actually there was no change in result(by using DBCC DROPCLEANBUFFERS),
>which is confusing for me too!
>Now, should I prefer the second query to the first?
>Amin
Hi Amin,
Hard to say. Some other things you might try:
1. Reverse the order in which you execute the queries in your test
scripts (should not make any difference with DBCC DROPCLEANBUFFERS,
but you never know).
2. Add "select getdate()" before and after each of the two queries, so
you can compare the actual execution time.
3. Add DBCC FREEPROCCACHE as well (sorry, forgot to mention that when
I suggested DBCC DROPCLEANBUFFERS). This will force recompilation of
your queries, so you can be sure it's not executed with an old
execution plan (that might be based on outdated statistics).
4. Post the queries here. Maybe I (or someone else) can make some
comments based on that. (Do add relevant table definitions, in the
form of CREATE TABLE statements as well!)
I assume you are already aware that you should run your performance
tests on the production system or on a test system with about the same
amount and distribution of data and the same specifications, that you
should try to test when no other users or processes use SQL Server (or
other apps on the same server) and that you should make sure your
table statistics are updated before running your tests.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I assume you posted the estimations below. I would not base conclusions
on these estimations. Whenever possible I would run the query with I/O
count and CPU count turned on. In the end, the query (plan) with the
lowest elapsed time is the winner...
Gert-Jan
Amin Sobati wrote:
> Hi all,
> I have written two queries that provide the same results(using different
> types of joins.).
> For choosing the better one, I pasted both of them in one script in query
> analyzer and executed them.
> Here is the summery of the result in execution plan:
> First query:
> - Query Cost: 55%
> - I/O Cost: Scan count 7, logical reads 14
> - Execution plan: Looks simple(a few icons..)
> Second query:
> - Query Cost: 45%
> - I/O Cost: Scan count 9, logical reads 34
> - Execution plan: Looks more complicated(a lot of icons!)
> Which query will have better performance? Should I consider the query
> cost(relative to the batch) or the I/O cost is more important? I don't know
> that why the second query has lower cost while it has more I/O and more
> complicated plan!
> Thanks in advance,
> Amin
--
(Please reply only to the newsgroup)|||Gert-Jan,
Thank you for your reply. Would you please read my reply to Hugo and write
me your thoughts:)
Amin
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:40A125B3.AB8FF522@.toomuchspamalready.nl...
> I assume you posted the estimations below. I would not base conclusions
> on these estimations. Whenever possible I would run the query with I/O
> count and CPU count turned on. In the end, the query (plan) with the
> lowest elapsed time is the winner...
> Gert-Jan
>
> Amin Sobati wrote:
> >
> > Hi all,
> > I have written two queries that provide the same results(using different
> > types of joins.).
> > For choosing the better one, I pasted both of them in one script in
query
> > analyzer and executed them.
> > Here is the summery of the result in execution plan:
> >
> > First query:
> > - Query Cost: 55%
> > - I/O Cost: Scan count 7, logical reads 14
> > - Execution plan: Looks simple(a few icons..)
> >
> > Second query:
> > - Query Cost: 45%
> > - I/O Cost: Scan count 9, logical reads 34
> > - Execution plan: Looks more complicated(a lot of icons!)
> >
> > Which query will have better performance? Should I consider the query
> > cost(relative to the batch) or the I/O cost is more important? I don't
know
> > that why the second query has lower cost while it has more I/O and more
> > complicated plan!
> >
> > Thanks in advance,
> >
> > Amin
> --
> (Please reply only to the newsgroup)|||Hugo,
Thank you for the tips!
Actually this is my case:
I have created "Students" table and "StudentExam" to track the exams that
each student has taken. I know that I must have an "Exams" table to keep
exam IDs, but I ignore that for this stage.
This is the script:
----
create table Students (
SID int,
SName varchar(10))
create table StudentExam (
SID int,
EName varchar(10))
-- inserting some records for each table
insert Students values(1,'David')
insert Students values(2,'Peter')
insert Students values(3,'Sarah')
insert StudentExam values(1,'sql')
insert StudentExam values(1,'vb')
insert StudentExam values(1,'asp')
insert StudentExam values(2,'vb')
insert StudentExam values(2,'asp')
insert StudentExam values(3,'xml')
insert StudentExam values(3,'access')
--Execute this insert statement for 12 times to have around 30,000 records:
insert StudentExam select * from StudentExam
--Now creating indexes:
create clustered index IX1 on StudentExam(sid)
create index IX2 on StudentExam(EName)
-- Now suppose that I need to delete the students who have NOT taken 'sql'
exam.
--These are two delete statements that i want to choose the perfect one:
set statistics IO ON
begin tran
delete Students from StudentExam where Students.sid not in (select
StudentExam.sid from StudentExam where StudentExam.EName='sql')
rollback tran
begin tran
delete Students from StudentExam where 'sql' not in (select
StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
rollback tran
----
The first delete statement has 12 logical reads, but the second one has 38.
The cost for first one is 54%, that I think it must have lower cost because
of the IO.
If I create the second index as clustered, and the first one as
non-clustered, the logical read for second delete statement will be 14, but
no change in the main cost.
Thanks in advance,
Amin
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:r141a0dp9l5ufrlnen38mgpclkih1l6b7d@.4ax.com...
> On Tue, 11 May 2004 11:37:02 +0430, Amin Sobati wrote:
> >Thanks Hugo!
> >Actually there was no change in result(by using DBCC DROPCLEANBUFFERS),
> >which is confusing for me too!
> >Now, should I prefer the second query to the first?
> >Amin
> Hi Amin,
> Hard to say. Some other things you might try:
> 1. Reverse the order in which you execute the queries in your test
> scripts (should not make any difference with DBCC DROPCLEANBUFFERS,
> but you never know).
> 2. Add "select getdate()" before and after each of the two queries, so
> you can compare the actual execution time.
> 3. Add DBCC FREEPROCCACHE as well (sorry, forgot to mention that when
> I suggested DBCC DROPCLEANBUFFERS). This will force recompilation of
> your queries, so you can be sure it's not executed with an old
> execution plan (that might be based on outdated statistics).
> 4. Post the queries here. Maybe I (or someone else) can make some
> comments based on that. (Do add relevant table definitions, in the
> form of CREATE TABLE statements as well!)
> I assume you are already aware that you should run your performance
> tests on the production system or on a test system with about the same
> amount and distribution of data and the same specifications, that you
> should try to test when no other users or processes use SQL Server (or
> other apps on the same server) and that you should make sure your
> table statistics are updated before running your tests.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||"Amin Sobati" <amins@.morva.net> wrote in message
news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> Hugo,
> Thank you for the tips!
> Actually this is my case:
> I have created "Students" table and "StudentExam" to track the exams that
> each student has taken. I know that I must have an "Exams" table to keep
> exam IDs, but I ignore that for this stage.
You can't ignore that and expect to get any meaningful performance data.
Here's a more complete example with primary keys, referential integrity a
whole lot of students, 90% of whom have taken the SQL exam.
Both queries use exactly the same plan, and have exactly the same cost.
David
create table Students (
SID int not null identity primary key,
SName varchar(10))
create table Exams(EName varchar(10) not null primary key)
create table StudentExam (
SID int not null references Students on delete cascade,
EName varchar(10) not null references Exams on delete cascade,
constraint pk_StudentExam
primary key (SID,EName)
)
create index IX2 on StudentExam(EName)
-- inserting some records for each table
insert Students values('David')
insert Students values('Peter')
insert Students values('Sarah')
-- let the students start breeding
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Students(SName) select SName from Students
insert Exams(EName) values('sql')
insert Exams(EName) values('asp')
insert Exams(EName) values('vb')
insert Exams(EName) values('xml')
insert Exams(EName) values('access')
--add exams for each SID,EName pair
insert StudentExam(SID,EName)
select SID, EName
from Students cross join Exams
--erase the SQL exam for 10% of the students
delete studentExam
where EName = 'sql'
and sid%10=0
-- Now suppose that I need to delete the students who have NOT taken
'sql'exam.
--These are two delete statements that i want to choose the perfect one:
set statistics IO ON
print ' QUERY 1'
begin tran
delete Students where Students.sid not in (select
StudentExam.sid from StudentExam where StudentExam.EName='sql')
rollback tran
print ' QUERY 2'
begin tran
delete Students where 'sql' not in (select
StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
rollback tran
/*
QUERY 1
(1228 row(s) affected)
Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
read-ahead reads 0.
Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
read-ahead reads 0.
QUERY 2
(1228 row(s) affected)
Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
read-ahead reads 0.
Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
read-ahead reads 0.
*/|||David,
Thank you for the great example.
I admit that I must do everything right to get the best result, but I cannot
recognize that why a query might have more IO but lower cost(in my example).
Can I count on the cost(relative to the batch) as a criteria for choosing a
query?
Thanks,
Amin
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> > Hugo,
> > Thank you for the tips!
> > Actually this is my case:
> > I have created "Students" table and "StudentExam" to track the exams
that
> > each student has taken. I know that I must have an "Exams" table to keep
> > exam IDs, but I ignore that for this stage.
> You can't ignore that and expect to get any meaningful performance data.
> Here's a more complete example with primary keys, referential integrity a
> whole lot of students, 90% of whom have taken the SQL exam.
> Both queries use exactly the same plan, and have exactly the same cost.
> David
>
> create table Students (
> SID int not null identity primary key,
> SName varchar(10))
> create table Exams(EName varchar(10) not null primary key)
>
> create table StudentExam (
> SID int not null references Students on delete cascade,
> EName varchar(10) not null references Exams on delete cascade,
> constraint pk_StudentExam
> primary key (SID,EName)
> )
> create index IX2 on StudentExam(EName)
> -- inserting some records for each table
> insert Students values('David')
> insert Students values('Peter')
> insert Students values('Sarah')
> -- let the students start breeding
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
> insert Students(SName) select SName from Students
>
> insert Exams(EName) values('sql')
> insert Exams(EName) values('asp')
> insert Exams(EName) values('vb')
> insert Exams(EName) values('xml')
> insert Exams(EName) values('access')
> --add exams for each SID,EName pair
> insert StudentExam(SID,EName)
> select SID, EName
> from Students cross join Exams
> --erase the SQL exam for 10% of the students
> delete studentExam
> where EName = 'sql'
> and sid%10=0
> -- Now suppose that I need to delete the students who have NOT taken
> 'sql'exam.
> --These are two delete statements that i want to choose the perfect one:
> set statistics IO ON
>
> print ' QUERY 1'
> begin tran
> delete Students where Students.sid not in (select
> StudentExam.sid from StudentExam where StudentExam.EName='sql')
> rollback tran
> print ' QUERY 2'
> begin tran
> delete Students where 'sql' not in (select
> StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
> rollback tran
> /*
> QUERY 1
> (1228 row(s) affected)
> Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> read-ahead reads 0.
> Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> read-ahead reads 0.
> QUERY 2
> (1228 row(s) affected)
> Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> read-ahead reads 0.
> Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> read-ahead reads 0.
> */
>|||The query optimizer's effort depends on the estimated cost of the query.
First, the query optimizer will search for any 'obvious' query plans. If
the estimated time of these solutions is no more than 1 or 2 seconds, it
will not look any further, and simply use that plan. These kinds of
query plans can depend on the way the query is written. However, if
there is any performance difference between different query plans, the
difference will be small.
If there is no obvious fast query plan, the full compilation phase is
started. This means (virtually) all possible query plans will be
evaluated. If this phase starts to take too long, then the it is cut
short, and the fastest plan that was found thus far will be used.
This is why you are seeing that in David's example, SQL-Server will use
the same query plan for both queries. I can add another syntax (see
below), which SQL-Server will probably use the same query plan for.
print ' QUERY 3'
begin tran
delete Students where not exists (
select 1
from StudentExam
where Students.sid=StudentExam.sid
and StudentExam.EName='sql'
)
rollback tran
So what I am basically saying, is that the differences you are seeing
are not significant.
Gert-Jan
Amin Sobati wrote:
> David,
> Thank you for the great example.
> I admit that I must do everything right to get the best result, but I cannot
> recognize that why a query might have more IO but lower cost(in my example).
> Can I count on the cost(relative to the batch) as a criteria for choosing a
> query?
> Thanks,
> Amin
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...
> >
> > "Amin Sobati" <amins@.morva.net> wrote in message
> > news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> > > Hugo,
> > > Thank you for the tips!
> > > Actually this is my case:
> > > I have created "Students" table and "StudentExam" to track the exams
> that
> > > each student has taken. I know that I must have an "Exams" table to keep
> > > exam IDs, but I ignore that for this stage.
> >
> > You can't ignore that and expect to get any meaningful performance data.
> >
> > Here's a more complete example with primary keys, referential integrity a
> > whole lot of students, 90% of whom have taken the SQL exam.
> >
> > Both queries use exactly the same plan, and have exactly the same cost.
> >
> > David
> >
> >
> >
> > create table Students (
> > SID int not null identity primary key,
> > SName varchar(10))
> >
> > create table Exams(EName varchar(10) not null primary key)
> >
> >
> > create table StudentExam (
> > SID int not null references Students on delete cascade,
> > EName varchar(10) not null references Exams on delete cascade,
> > constraint pk_StudentExam
> > primary key (SID,EName)
> > )
> > create index IX2 on StudentExam(EName)
> >
> > -- inserting some records for each table
> > insert Students values('David')
> > insert Students values('Peter')
> > insert Students values('Sarah')
> >
> > -- let the students start breeding
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> > insert Students(SName) select SName from Students
> >
> >
> > insert Exams(EName) values('sql')
> > insert Exams(EName) values('asp')
> > insert Exams(EName) values('vb')
> > insert Exams(EName) values('xml')
> > insert Exams(EName) values('access')
> >
> > --add exams for each SID,EName pair
> > insert StudentExam(SID,EName)
> > select SID, EName
> > from Students cross join Exams
> >
> > --erase the SQL exam for 10% of the students
> > delete studentExam
> > where EName = 'sql'
> > and sid%10=0
> >
> > -- Now suppose that I need to delete the students who have NOT taken
> > 'sql'exam.
> > --These are two delete statements that i want to choose the perfect one:
> > set statistics IO ON
> >
> >
> > print ' QUERY 1'
> > begin tran
> > delete Students where Students.sid not in (select
> > StudentExam.sid from StudentExam where StudentExam.EName='sql')
> > rollback tran
> >
> > print ' QUERY 2'
> > begin tran
> > delete Students where 'sql' not in (select
> > StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
> > rollback tran
> >
> > /*
> > QUERY 1
> >
> > (1228 row(s) affected)
> >
> > Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
> 0,
> > read-ahead reads 0.
> > Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> > read-ahead reads 0.
> > Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> > read-ahead reads 0.
> > Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> > read-ahead reads 0.
> > QUERY 2
> >
> > (1228 row(s) affected)
> >
> > Table 'StudentExam'. Scan count 4914, logical reads 34734, physical reads
> 0,
> > read-ahead reads 0.
> > Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads 0,
> > read-ahead reads 0.
> > Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> > read-ahead reads 0.
> > Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> > read-ahead reads 0.
> > */
> >
> >
--
(Please reply only to the newsgroup)|||Thanks Gert-Jan!
Regardless of this particular scenario, I'd like to find the best way for
comparing the performance of two or more queries.
What I've been doing so far, I executed for example two queries and compared
their cost(relative to the batch). Can this cost be the factor that I must
count on?
What made me to start posting this question was that I encountered with a
query that had more IO cost than the other but less Cost. I need to know
that I must choose the first query(with less IO, more cost) or the second
query(with more IO, less cost).
Thanks,
Amin
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:40A2A554.20A1EDEE@.toomuchspamalready.nl...
> The query optimizer's effort depends on the estimated cost of the query.
> First, the query optimizer will search for any 'obvious' query plans. If
> the estimated time of these solutions is no more than 1 or 2 seconds, it
> will not look any further, and simply use that plan. These kinds of
> query plans can depend on the way the query is written. However, if
> there is any performance difference between different query plans, the
> difference will be small.
> If there is no obvious fast query plan, the full compilation phase is
> started. This means (virtually) all possible query plans will be
> evaluated. If this phase starts to take too long, then the it is cut
> short, and the fastest plan that was found thus far will be used.
> This is why you are seeing that in David's example, SQL-Server will use
> the same query plan for both queries. I can add another syntax (see
> below), which SQL-Server will probably use the same query plan for.
> print ' QUERY 3'
> begin tran
> delete Students where not exists (
> select 1
> from StudentExam
> where Students.sid=StudentExam.sid
> and StudentExam.EName='sql'
> )
> rollback tran
> So what I am basically saying, is that the differences you are seeing
> are not significant.
> Gert-Jan
>
> Amin Sobati wrote:
> >
> > David,
> > Thank you for the great example.
> > I admit that I must do everything right to get the best result, but I
cannot
> > recognize that why a query might have more IO but lower cost(in my
example).
> > Can I count on the cost(relative to the batch) as a criteria for
choosing a
> > query?
> >
> > Thanks,
> > Amin
> >
> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> > message news:#k8IHbDOEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > >
> > > "Amin Sobati" <amins@.morva.net> wrote in message
> > > news:Ogbf4BDOEHA.640@.TK2MSFTNGP12.phx.gbl...
> > > > Hugo,
> > > > Thank you for the tips!
> > > > Actually this is my case:
> > > > I have created "Students" table and "StudentExam" to track the exams
> > that
> > > > each student has taken. I know that I must have an "Exams" table to
keep
> > > > exam IDs, but I ignore that for this stage.
> > >
> > > You can't ignore that and expect to get any meaningful performance
data.
> > >
> > > Here's a more complete example with primary keys, referential
integrity a
> > > whole lot of students, 90% of whom have taken the SQL exam.
> > >
> > > Both queries use exactly the same plan, and have exactly the same
cost.
> > >
> > > David
> > >
> > >
> > >
> > > create table Students (
> > > SID int not null identity primary key,
> > > SName varchar(10))
> > >
> > > create table Exams(EName varchar(10) not null primary key)
> > >
> > >
> > > create table StudentExam (
> > > SID int not null references Students on delete cascade,
> > > EName varchar(10) not null references Exams on delete cascade,
> > > constraint pk_StudentExam
> > > primary key (SID,EName)
> > > )
> > > create index IX2 on StudentExam(EName)
> > >
> > > -- inserting some records for each table
> > > insert Students values('David')
> > > insert Students values('Peter')
> > > insert Students values('Sarah')
> > >
> > > -- let the students start breeding
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > > insert Students(SName) select SName from Students
> > >
> > >
> > > insert Exams(EName) values('sql')
> > > insert Exams(EName) values('asp')
> > > insert Exams(EName) values('vb')
> > > insert Exams(EName) values('xml')
> > > insert Exams(EName) values('access')
> > >
> > > --add exams for each SID,EName pair
> > > insert StudentExam(SID,EName)
> > > select SID, EName
> > > from Students cross join Exams
> > >
> > > --erase the SQL exam for 10% of the students
> > > delete studentExam
> > > where EName = 'sql'
> > > and sid%10=0
> > >
> > > -- Now suppose that I need to delete the students who have NOT taken
> > > 'sql'exam.
> > > --These are two delete statements that i want to choose the perfect
one:
> > > set statistics IO ON
> > >
> > >
> > > print ' QUERY 1'
> > > begin tran
> > > delete Students where Students.sid not in (select
> > > StudentExam.sid from StudentExam where StudentExam.EName='sql')
> > > rollback tran
> > >
> > > print ' QUERY 2'
> > > begin tran
> > > delete Students where 'sql' not in (select
> > > StudentExam.EName from StudentExam where Students.sid=StudentExam.sid)
> > > rollback tran
> > >
> > > /*
> > > QUERY 1
> > >
> > > (1228 row(s) affected)
> > >
> > > Table 'StudentExam'. Scan count 4914, logical reads 34734, physical
reads
> > 0,
> > > read-ahead reads 0.
> > > Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads
0,
> > > read-ahead reads 0.
> > > Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> > > read-ahead reads 0.
> > > Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> > > read-ahead reads 0.
> > > QUERY 2
> > >
> > > (1228 row(s) affected)
> > >
> > > Table 'StudentExam'. Scan count 4914, logical reads 34734, physical
reads
> > 0,
> > > read-ahead reads 0.
> > > Table 'Worktable'. Scan count 6140, logical reads 8597, physical reads
0,
> > > read-ahead reads 0.
> > > Table 'Worktable'. Scan count 2, logical reads 1235, physical reads 0,
> > > read-ahead reads 0.
> > > Table 'Students'. Scan count 1, logical reads 4816, physical reads 0,
> > > read-ahead reads 0.
> > > */
> > >
> > >
> --
> (Please reply only to the newsgroup)|||Well, the query plan shows estimates of the execution times. The
estimated subtree cost of the left most element is the estimated time in
seconds for the query. So whichever query plan has the lowest estimated
cost is likely to be the fastest. However, things like locking strategy
can also play a big part on a stressed server. This is not really
expressed or taking into consideration in the query plan.
The I/O count translates into the estimated I/O cost, which is (just
like the estimated CPU cost) just one component in the total estimated
cost.
If you can choose between two query plans with the same estimated cost,
I would choose the plan that uses the least amount of resources.
Gert-Jan
Amin Sobati wrote:
> Thanks Gert-Jan!
> Regardless of this particular scenario, I'd like to find the best way for
> comparing the performance of two or more queries.
> What I've been doing so far, I executed for example two queries and compared
> their cost(relative to the batch). Can this cost be the factor that I must
> count on?
> What made me to start posting this question was that I encountered with a
> query that had more IO cost than the other but less Cost. I need to know
> that I must choose the first query(with less IO, more cost) or the second
> query(with more IO, less cost).
> Thanks,
> Amin

Query Cost - how is it calculated?

Hi,
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
I doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>
|||Hello Wayne,

> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
[vbcol=seagreen]
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
SQL
>

Query Cost - how is it calculated?

Hi,
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.comI doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>|||Hello Wayne,
> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I'm trying to figure out where the value of "Cost" comes from in the
> > Query Analyzer.
> >
> > For example, when running this on Northwind:
> >
> > select * from dbo.[Alphabetical list of products]
> >
> > You get this 3 parts for the Query Plan. The part of
> > "Categories.PK_Categories" has a cost of 24% of the
> > total and a value of 0.0119... Where does this value
> > come from?
> >
> > All I get is "cost for CPU" and "cost for I/O" - but adding
> > these together does not equal that value.
> >
> >
> > Anyone have a clue?
> >
> > --
> > With regards,
> >
> > Martijn Tonies
> > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS
SQL
> > Server
> > Upscene Productions
> > http://www.upscene.com
> >
> >
> >
>

Query Cost - how is it calculated?

Hi,
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.comI doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>|||Hello Wayne,

> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
>

Query Cost - Big diffrence

I have a table called test with five fields of which no is the primary
key
When I run the following query
select * from tt where no not in (23,76)
select * from tt where no !=23 and no!=76
The cost of first query is 0.53% and that of second is 99.47%
Why is this huge difference?
MadhivananIt's hard to offer any help without the exact table definitions and exact
query you are running. Is your table called tt or test?
In any case, the percentages you quote are estimates, and they
may or may not correspond to actual relative costs. My guess
is that the difference is the result of how the literal constants 23 and 76
are typed by the query processor. Assuming the column [no] is of
type INT, try
select * from tt where [no] != cast(23 as int) and [no] != cast(76 as int)
If this doesn't help, please post the CREATE TABLE statements and
the exact query, along with the query plans for each query.
Steve Kass
Drew University
Madhivanan wrote:

>I have a table called test with five fields of which no is the primary
>key
>When I run the following query
>select * from tt where no not in (23,76)
>select * from tt where no !=23 and no!=76
>The cost of first query is 0.53% and that of second is 99.47%
>Why is this huge difference?
>Madhivanan
>
>|||There are a few places in MSDN which make passing mention of the possibility
that SQL Server's query optimizer may not choose indexes efficiently when
the OR operator a negative comparisons (ex: != ) are used in a WHERE clause.
Look closely at the execution plan and see if index selection is different
between the two queries.
http://msdn.microsoft.com/library/d...
etchapt14.asp
http://support.microsoft.com/defaul...=kb;en-us;68470
You can explicitly tell the query processor which index to use by specifying
an index hint.
http://msdn.microsoft.com/library/d...r />
_8upf.asp
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1123770945.884995.22410@.g43g2000cwa.googlegroups.com...
>I have a table called test with five fields of which no is the primary
> key
> When I run the following query
> select * from tt where no not in (23,76)
> select * from tt where no !=23 and no!=76
> The cost of first query is 0.53% and that of second is 99.47%
> Why is this huge difference?
> Madhivanan
>

Query cost

In many cases, I am finding that SQL Server 2005 generates the same plan as SQL Server 2000, but the SQL 2005 costs are orders of magnitude greater than the SQL 2000. Also, the execution time and cpu time shown by SET STATISTICS IO/TIME on are greater in 2005 than they are in 2000. Are the costs not comparable across the two versions?

Sharon

Sharon,

The cost calculation is different in SQL Server 2005.
Cost is now calculated by the number of ticks (based on memory, io and context switch cost). So that may explain the cost difference.

I'll try to see if I'm getting the same behaviour with statistics io and time.

|||

Thanks, Wesley.

I see different patterns in statistics IO and I see significant increases in CPU and elapsed time for SQL Server 2005 vs. SQL Server 2000.

Can you point me to any documentation about a changed costing algorithm?

Sharon

|||

Sharon,

That is very odd indeed.
I'm going to try to find some time today because things like this get my attention :-)

This is a great article that holds some information about the cost calculation.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

hth

Query Cost

What is "Query cost (relative to the batch): 100.00%" mean ?
Thanks.
DXC wrote:
> What is "Query cost (relative to the batch): 100.00%" mean ?
> Thanks.
That statement is consuming the entire cost of executing the batch.
David Gugick
Imceda Software
www.imceda.com

Query Cost

What is "Query cost (relative to the batch): 100.00%" mean ?
Thanks.DXC wrote:
> What is "Query cost (relative to the batch): 100.00%" mean ?
> Thanks.
That statement is consuming the entire cost of executing the batch.
David Gugick
Imceda Software
www.imceda.com

Query Cost

What is "Query cost (relative to the batch): 100.00%" mean ?
Thanks.DXC wrote:
> What is "Query cost (relative to the batch): 100.00%" mean ?
> Thanks.
That statement is consuming the entire cost of executing the batch.
--
David Gugick
Imceda Software
www.imceda.com

Query Analyzer: Execution Plan: Clustered Index Seek: I/O Cost

Can anyone explain how I/O cost is calculated?
My example:
Physical operation: Clustered Index Seek
Logical operation: Clustered Index Seek
Row count: 1
Estimated row size: 76
I/O cost: 0.00632
CPU cost: 0.000080
Number of executes: 1
Cost: 0.006408(100%)
Subtree cost: 0.00640
Estimated row count: 1
Argument: ...
If you really want to know some of the details about query costs, you should
read some of Joe Chang's articles at www.sql-server-performance.com . This
is an excellent site for performance tips, and Joe has several detailed
articles about calculating the cost of a query.
"Dave Owen" <DaveOwen@.discussions.microsoft.com> wrote in message
news:811149F2-25E0-43BA-B3A6-43B17D7014F4@.microsoft.com...
> Can anyone explain how I/O cost is calculated?
> My example:
> Physical operation: Clustered Index Seek
> Logical operation: Clustered Index Seek
> Row count: 1
> Estimated row size: 76
> I/O cost: 0.00632
> CPU cost: 0.000080
> Number of executes: 1
> Cost: 0.006408(100%)
> Subtree cost: 0.00640
> Estimated row count: 1
> Argument: ...