Performance question.

Hello Everybody,
I have a table which has arround 30 millions rows.
Table structure is as following..
CREATE TABLE TestTable
(Id INT, --which is PK,
EmpId INT, --There is a non cluster index on it.
DeptName VARCHAR(50),
Hours NUMERIC(5,2),
Tdate DATETIME,
ProjectNumber smallint,
.and few more columns
.
.
)
And i have following query, which is taking arround 1 minute 10 sec to run.
SELECT
DeptName,
EmpId,
SUM(CASE WHEN ProjectNumber = 11 THEN Hours ELSE 0
END) AS FinHours,
SUM(CASE WHEN ProjectId = 12 THEN Hours ELSE 0
END) AS HrHours,
SUM(CASE WHEN ProjectId = 13 THEN Hours ELSE 0
END) AS TaxHours,
FROM TestTable WHERE Tdate between @.Date1 and @.Date2
GROUP BY
DeptName,
EmpId
I do not have index on ProjectNumber column because this column will have
only
200 distinct values.
If i create index on Group by Columns, would it improve performance ?
Pls let me know, how can i imporve performance ?
Thanks.I don't know about the rest of your queries or your usage patterns, but the
most obvious choice in this case is to make the PK nonclustered and create a
clustered index on the Tdate column to support your WHERE clause.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:569B7FD3-452C-4B8F-9056-06798A32B3EA@.microsoft.com...
> Hello Everybody,
> I have a table which has arround 30 millions rows.
> Table structure is as following..
> CREATE TABLE TestTable
> (Id INT, --which is PK,
> EmpId INT, --There is a non cluster index on it.
> DeptName VARCHAR(50),
> Hours NUMERIC(5,2),
> Tdate DATETIME,
> ProjectNumber smallint,
> .and few more columns
> .
> .
> )
> And i have following query, which is taking arround 1 minute 10 sec to
> run.
>
> SELECT
> DeptName,
> EmpId,
> SUM(CASE WHEN ProjectNumber = 11 THEN Hours ELSE 0
> END) AS FinHours,
> SUM(CASE WHEN ProjectId = 12 THEN Hours ELSE 0
> END) AS HrHours,
> SUM(CASE WHEN ProjectId = 13 THEN Hours ELSE 0
> END) AS TaxHours,
> FROM TestTable WHERE Tdate between @.Date1 and @.Date2
> GROUP BY
> DeptName,
> EmpId
> I do not have index on ProjectNumber column because this column will have
> only
> 200 distinct values.
> If i create index on Group by Columns, would it improve performance ?
> Pls let me know, how can i imporve performance ?
>
> Thanks.|||also you may try an index on all the columns involved in the query,
Tdate first if the interval is narrow, DeptName, EmpId first if the
interval is wide|||Your query indicates that you are using the following columns: DeptId,
EmpId, ProjectNumber, ProjectId, TDate. This means that SQL Server will have
to look at all of the rows being returned regardless of whether an index
exists on your grouped columns. This will be the case unless you were to
create a covering index for all of the columns being returned. In your case,
that's a lot of columns so I don't recommend it.
According to your DDL, I don't see an index on Tdate. I would actually start
with that. However, depending on the number of rows that are being returned
from your query, the optimizer may or may not even choose to use that index
(due to the expense of bookmark lookup). However, I would attempt a
non-clustered index on Tdate first.
Assuming you're not using the data for anything else (or much else), Adam's
method could be the best route. However, this would result in larger indexes
for all of the nonclustered indexes on this table.
Since all nonclustered indexes also include the clustered index key, and
your key is going from a 4-byte data type to an 8-byte data type and add up
114MB to each index on your table. Combined with the fact that a
"uniquifier" is applied to all non-unique clustered indexes, could add
another 4 bytes to your rows and bring each nonclustered index up to 228 MB.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%237v%239uXBGHA.892@.TK2MSFTNGP12.phx.gbl...
>I don't know about the rest of your queries or your usage patterns, but the
>most obvious choice in this case is to make the PK nonclustered and create
>a clustered index on the Tdate column to support your WHERE clause.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:569B7FD3-452C-4B8F-9056-06798A32B3EA@.microsoft.com...
>|||"Jared Ko" <JaredKo05@.sanbeiji.removethispart.com> wrote in message
news:uPOE8AZBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Since all nonclustered indexes also include the clustered index key, and
> your key is going from a 4-byte data type to an 8-byte data type and add
> up 114MB to each index on your table. Combined with the fact that a
> "uniquifier" is applied to all non-unique clustered indexes, could add
> another 4 bytes to your rows and bring each nonclustered index up to 228
> MB.
Slight correction: The uniquifier is only added to non-unique rows, not
every row. So if the majority are unique (which we might expect from a
DATETIME column), the uniquifier will add very little overhead.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thanks Adam. I was frantically searching for that information while I was
writing my response. I was thinking that was the case but a couple of web
sites I hit suggested otherwise.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OybGaZZBGHA.2356@.tk2msftngp13.phx.gbl...
> "Jared Ko" <JaredKo05@.sanbeiji.removethispart.com> wrote in message
> news:uPOE8AZBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Slight correction: The uniquifier is only added to non-unique rows, not
> every row. So if the majority are unique (which we might expect from a
> DATETIME column), the uniquifier will add very little overhead.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||Best is to test for yourself. Have to similar tables, populate them with bun
ch of rows. One unique,
the other all with same value. Check size of the index. that is how I conclu
ded that uniqifier is
only added for the duplicates.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jared Ko" <JaredKo05@.sanbeiji.removethispart.com> wrote in message
news:uyOPGjZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Thanks Adam. I was frantically searching for that information while I was
writing my response. I
> was thinking that was the case but a couple of web sites I hit suggested o
therwise.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OybGaZZBGHA.2356@.tk2msftngp13.phx.gbl...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OK1JSoZBGHA.3472@.TK2MSFTNGP09.phx.gbl...
> Best is to test for yourself. Have to similar tables, populate them with
> bunch of rows. One unique, the other all with same value. Check size of
> the index. that is how I concluded that uniqifier is only added for the
> duplicates.
I took the lazy way out. _Inside SQL Server 2000_, page 412:
"If your clustered index was not created with the UNIQUE property, SQL
Server adds a 4-byte field when necessary to make each key unique."
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Just a question - wouldn't horizontal partitioning be useful in this case?
of course apart from index on the Tdate field.
Peter

Performance Question

Hello Everybody.
Not sure how sql server query optimizer works ?
if i have a query like following.
select * from table where field1 = 'A' or field2 = 'B' or field2 = 'C'
so how the above query will execute ? if field1 = 'A' is true, the other
part of the query field2 = 'B' or field3 = 'C' will execute or not ?
because i have a very complecated query with the same concept.
Pls let me knowIn this case if any OR expression is true it will stop processing the list
as that satisfies the WHERE clause. But you can not guarantee which order
the expressions are evaluated. It is up to the engine to determine that at
run time and as such any of the columns can be addressed first.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F3BC8CDD-E914-45FD-8A74-74E3BBD922C0@.microsoft.com...
> Hello Everybody.
> Not sure how sql server query optimizer works ?
> if i have a query like following.
> select * from table where field1 = 'A' or field2 = 'B' or field2 = 'C'
> so how the above query will execute ? if field1 = 'A' is true, the other
> part of the query field2 = 'B' or field3 = 'C' will execute or not ?
> because i have a very complecated query with the same concept.
> Pls let me know
>|||On Fri, 16 Dec 2005 11:53:02 -0800, mvp wrote:

>Hello Everybody.
>Not sure how sql server query optimizer works ?
>if i have a query like following.
>select * from table where field1 = 'A' or field2 = 'B' or field2 = 'C'
>so how the above query will execute ? if field1 = 'A' is true, the other
>part of the query field2 = 'B' or field3 = 'C' will execute or not ?
>because i have a very complecated query with the same concept.
>Pls let me know
Hi mvp,
What Andrew says is correct, *IF* the optimizer decides to use a table
scan or an index scan to satisfy the query. However, there are also
completely different techniques that the optimizer might choose.
For instance, if both field1 and field2 are indexed, and both indexes
are sufficiently selective, the optimizer might decide to do individual
index lookups for each of the three comparisons, then combine the
results to get the end result. To see an example of this, run the
following query against Northwind and check the execution plan:
SELECT *
FROM Orders
WHERE ShippedDate = '19980506'
OR OrderID = 10550
OR OrderID = 11067
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Performance Question

Hello Everybody,
I do have a question abt performance of one of my report store procedure.
We have a reporing application using Microsoft Reporting Services a Report
Front End and SQL SERVER 2000 as a DB. I have written one report store proc.
That report store proc is taking arround 30 sec to run in Query Analyzer. I
had opened 10 query analyzer windows and ran that report at same time from
each window and it is taking arround 35-40 sec to run. But if 10 people
access that same report from Report Server at same time, it is taking very
long time.. arround 8-10 minutes...
so really wondering...what would be the reason. I have proper indexes
created on all the appropriate columns..
So pls let me know what i can do ?
ThanksGoing from 30 seconds to 10 minutes sounds like blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/defaul...kb;en-us;271509
aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F3C00A9E-9D21-4B58-98B5-C2A2A042BEE7@.microsoft.com...
> Hello Everybody,
> I do have a question abt performance of one of my report store procedure.
> We have a reporing application using Microsoft Reporting Services a Report
> Front End and SQL SERVER 2000 as a DB. I have written one report store
> proc.
> That report store proc is taking arround 30 sec to run in Query Analyzer.
> I
> had opened 10 query analyzer windows and ran that report at same time from
> each window and it is taking arround 35-40 sec to run. But if 10 people
> access that same report from Report Server at same time, it is taking very
> long time.. arround 8-10 minutes...
> so really wondering...what would be the reason. I have proper indexes
> created on all the appropriate columns..
> So pls let me know what i can do ?
> Thanks

Performance question

Hello Everybody,
There is an unexplained yet situation with
performance/time to return query results on 3 queries.
Queries must be identical and are executed from SQL S 2000
via Linked server to an Oracle database.
End table is the same (events).
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'FLORIDA'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'POWER2'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'APCEPKS'
Last query always takes ~5 sec to return results (
to 'APCEPKS'); there are much more records than others.
First and second queries take more than a minute!!!
The order of execution doesn't matter.
Execution plan shows no differences.
My question is:
What could cause performance difference like this?
Any suggestions are highly appreciated!
Thx,
DobbyAdd these commands after each query and see how does it affect ?
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>|||Basically, you are submitting the queries to an Oracle database, via
SQL-Server. So I would look at the Oracle side. If you submit the
queries directly on the Oracle database, do they behave the same?
Gert-Jan
Dobromir Rizov wrote:
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby|||One thing I can think of -- not familiar with Oracle data/index structure --
is that Servername is non-cluster indexed, and most of the records for
Servername = 'APCEPKS' are located in a small range of data pages, whereas
the records for the other two servername values are spread wide.
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>|||Same all queries executed directly in Oracle take less
than a second to return result.
Dobromir
>--Original Message--
>Basically, you are submitting the queries to an Oracle
database, via
>SQL-Server. So I would look at the Oracle side. If you
submit the
>queries directly on the Oracle database, do they behave
the same?
>Gert-Jan
>
>Dobromir Rizov wrote:
>> Hello Everybody,
>> There is an unexplained yet situation with
>> performance/time to return query results on 3 queries.
>> Queries must be identical and are executed from SQL S
2000
>> via Linked server to an Oracle database.
>> End table is the same (events).
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'FLORIDA'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'POWER2'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'APCEPKS'
>> Last query always takes ~5 sec to return results (
>> to 'APCEPKS'); there are much more records than others.
>> First and second queries take more than a minute!!!
>> The order of execution doesn't matter.
>> Execution plan shows no differences.
>> My question is:
>> What could cause performance difference like this?
>> Any suggestions are highly appreciated!
>> Thx,
>> Dobby
>.
>|||In that case, I would consider moving to an OPENQUERY syntax:
SET QUOTED_IDENTIFIER OFF
SELECT MaxID FROM OPENQUERY(PHDT,
"SELECT MAX(ID) AS MaxID
FROM PHDT..PS_USER.EVENTS
WHERE SERVERNAME='FLORIDA'
")
I realize however, that this may not meet your requirement...
Gert-Jan
Dobromir Rizov wrote:
> Same all queries executed directly in Oracle take less
> than a second to return result.
> Dobromir
> >--Original Message--
> >Basically, you are submitting the queries to an Oracle
> database, via
> >SQL-Server. So I would look at the Oracle side. If you
> submit the
> >queries directly on the Oracle database, do they behave
> the same?
> >
> >Gert-Jan
> >
> >
> >Dobromir Rizov wrote:
> >>
> >> Hello Everybody,
> >>
> >> There is an unexplained yet situation with
> >> performance/time to return query results on 3 queries.
> >>
> >> Queries must be identical and are executed from SQL S
> 2000
> >> via Linked server to an Oracle database.
> >> End table is the same (events).
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'FLORIDA'
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'POWER2'
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'APCEPKS'
> >>
> >> Last query always takes ~5 sec to return results (
> >> to 'APCEPKS'); there are much more records than others.
> >>
> >> First and second queries take more than a minute!!!
> >>
> >> The order of execution doesn't matter.
> >>
> >> Execution plan shows no differences.
> >>
> >> My question is:
> >>
> >> What could cause performance difference like this?
> >>
> >> Any suggestions are highly appreciated!
> >>
> >> Thx,
> >>
> >> Dobby
> >.
> >|||The execution plan shows:
Row count 1 on the shortest query and full table scans on
others.
My understanding is that indexes are used only (but
always) with the query on 'APCEPKS', but never with any
other queries.
What makes queries to differ?
Is there a way to force a query to use a particular index?
Thx,
Dobby
>--Original Message--
>Hello Everybody,
>There is an unexplained yet situation with
>performance/time to return query results on 3 queries.
>Queries must be identical and are executed from SQL S
2000
>via Linked server to an Oracle database.
>End table is the same (events).
>
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'FLORIDA'
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'POWER2'
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'APCEPKS'
>Last query always takes ~5 sec to return results (
>to 'APCEPKS'); there are much more records than others.
>First and second queries take more than a minute!!!
>The order of execution doesn't matter.
>Execution plan shows no differences.
>My question is:
>What could cause performance difference like this?
>Any suggestions are highly appreciated!
>Thx,
>Dobby
>
>
>.
>|||Directly in SQL*Plus queries retutn instant results.
There are indexes in place.
Dobby
>--Original Message--
>Basically, you are submitting the queries to an Oracle
database, via
>SQL-Server. So I would look at the Oracle side. If you
submit the
>queries directly on the Oracle database, do they behave
the same?
>Gert-Jan
>
>Dobromir Rizov wrote:
>> Hello Everybody,
>> There is an unexplained yet situation with
>> performance/time to return query results on 3 queries.
>> Queries must be identical and are executed from SQL S
2000
>> via Linked server to an Oracle database.
>> End table is the same (events).
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'FLORIDA'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'POWER2'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'APCEPKS'
>> Last query always takes ~5 sec to return results (
>> to 'APCEPKS'); there are much more records than others.
>> First and second queries take more than a minute!!!
>> The order of execution doesn't matter.
>> Execution plan shows no differences.
>> My question is:
>> What could cause performance difference like this?
>> Any suggestions are highly appreciated!
>> Thx,
>> Dobby
>.
>sql

Performance question

Hello Everybody,
There is an unexplained yet situation with
performance/time to return query results on 3 queries.
Queries must be identical and are executed from SQL S 2000
via Linked server to an Oracle database.
End table is the same (events).
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'FLORIDA'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'POWER2'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'APCEPKS'
Last query always takes ~5 sec to return results (
to 'APCEPKS'); there are much more records than others.
First and second queries take more than a minute!!!
The order of execution doesn't matter.
Execution plan shows no differences.
My question is:
What could cause performance difference like this?
Any suggestions are highly appreciated!
Thx,
DobbyAdd these commands after each query and see how does it affect ?
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Basically, you are submitting the queries to an Oracle database, via
SQL-Server. So I would look at the Oracle side. If you submit the
queries directly on the Oracle database, do they behave the same?
Gert-Jan
Dobromir Rizov wrote:

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby

---

|||One thing I can think of -- not familiar with Oracle data/index structure --
is that Servername is non-cluster indexed, and most of the records for
Servername = 'APCEPKS' are located in a small range of data pages, whereas
the records for the other two servername values are spread wide.
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Same all queries executed directly in Oracle take less
than a second to return result.
Dobromir

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

|||In that case, I would consider moving to an OPENQUERY syntax:
SET QUOTED_IDENTIFIER OFF
SELECT MaxID FROM OPENQUERY(PHDT,
"SELECT MAX(ID) AS MaxID
FROM PHDT..PS_USER.EVENTS
WHERE SERVERNAME='FLORIDA'
")
I realize however, that this may not meet your requirement...
Gert-Jan
Dobromir Rizov wrote:[QUOTE]
> Same all queries executed directly in Oracle take less
> than a second to return result.
> Dobromir
>
> database, via
> submit the
> the same?
> 2000
> SERVERNAME
> SERVERNAME
> SERVERNAME|||Directly in SQL*Plus queries retutn instant results.
There are indexes in place.
Dobby

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

sql

Performance problem

Hello everybody,
I need to detect wich database is getting more resources on my database
machine.
I have to use SQL Profiler.
Wich parameter i should analyze?
Thanks
Leandro Loureiro dos SantosLeandro,
You might want to trace to a file and then load this trace file into a table
(or just trace to a table).
Events to monitor:
Defaults are fine.
Columns to monitor:
Add DatabaseID to the defaults.
Run profiler for a typical workload on your database, and then query your tr
ace table and run the following pseudo queries:
SUM(Duration) GROUP BY DatabaseID, ORDER BY 1 -- will give you database tha
t have the most time taken up running queries
SUM(CPU) GROUP BY DatabaseID, ORDER BY 1 -- will give you most CPU intensi
ve database
SUM(Reads) GROUP BY DatabaseID, ORDER BY 1 -- will give you most Read I/O i
ntensive database
SUM (Writes) GROUP BY DatabaseID, ORDER BY 1 -- will give you most Write I/
O intensive database
You might also want to find the top 10 intensive queries across databases. H
ave a play, there's a wealth of information to be found in a profiler trace
table.
Mark Allison, SQL Server MVP