Query Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Query Performance

Author

Topic

ramaiah
Starting Member

27 Posts

Posted - 2010-03-12 : 18:08:03

Hi,
I have a table (example table01) having 10 million records and 10 columns. I have written a below query it is taking 22 hours for running this. i have created a non clustured index on clumns(c2,c3).
I have updated the statistics. defragmentain on this table is Avg.Page per extent(8), scan density(>90) and avg.page density(>90%).
In query execution plan : Hash Match(Inner join) has taken 80%.

Query:
SELECT A.pkid ,A.c1 ,A.c2--(DATEADD column) ,A.c3 ,A.c4,..
FROM table01 AS A WITH (NOLOCK) INNER JOIN
(SELECT c2,MAX(c3) AS UpdatedDate FROM table01 WITH (NOLOCK) GROUP BY c2) AS B
ON A.c2 = B.c2 AND A.c3 = B.c3

Please help me on this.
Thanks
Ram

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-12 : 19:45:36

Check for blocking.

Perhaps you should have c2,c3 as a clustered index if you are returning all of the columns in the SELECT. Just depends on what other queries you have that would need your current clustered index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-12 : 19:46:30

Why don't you have a WHERE clause on the query? How many rows does the derived table return and how many rows does the query return after it completes in 22 hours?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-12 : 23:53:37

Your query looks strange ....

SELECT A.pkid ,A.c1 ,A.c2--(DATEADD column) ,A.c3 ,A.c4,..
FROM table01 AS A WITH (NOLOCK) INNER JOIN
(SELECT c2,MAX(c3) AS UpdatedDate FROM table01 WITH (NOLOCK) GROUP BY c2) AS B
ON A.c2 = B.c2 AND A.c3 = B.c3

ramaiah
Starting Member

27 Posts

Posted - 2010-03-16 : 06:48:27

C2 having the order id, c3 having order date. with above query i am trying to fetch all the recent orderd items information(i want the all the items which are ordered on max(order_date)). 1)using derived table, first i am fetching the order_id, max(order_date). 2) i am giving this order id, order_date into the outer query(using inner join).

ramaiah
Starting Member

27 Posts

Posted - 2010-03-16 : 06:49:59

on this server, there is no blocking/locking.

pphillips001
Starting Member

23 Posts

Posted - 2010-03-23 : 21:34:52

Couldn't you do something like:

SELECT
A.pkid,
A.c1,
A.c2
FROM
table01
GROUP BY
A.c2
HAVING
a.c3 = max(a.c3)

The other way I thought of was to create a temp table just feeding in c2 and MAX(c3) and use this in the query. The main query wouldn't have to calculate the MAX (which is SSLLOOWW) for each and every line.

Just a thought.

===========================
There is no reality - only perspective.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-24 : 03:29:07

I think does the same thing you want, only quite a bit more efficient:

SELECT A.pkid ,A.c1 ,A.c2
FROM table01 AS A WITH (NOLOCK) 
WHERE ROW_NUMBER() OVER (PARTITION BY c2 ORDER BY c3 DESC) = 1

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 03:44:44

"Couldn't you do something like"

All columns in the SELECT have to be in the GROUP BY

@ramaiah : Are you using SQL2005 or later? Using ROW_NUMBER() and OVER (introduced in SQL2005) will be best way to get the Row with the MAX value of C3

If SQL2005 I would try putting the SUB SELECT into a Temp Table first, and then JOIN that to the main query. That sometimes works better.

The use of NOLOCK will give you problems in a live server. Again, if you are using SQL2005 or later use READ_COMMITTED_SNAPSHOT. If you don;t know what issues I am talking about regarding NOLOCK then you DEFINITELY should not be using it (IME it is normally the case that DEVs don;t know the side effects of NOLOCK and use it to make Blocking / Deadlock problems go away - its the wrong tool for that job)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources