Query performance problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query performance problem

Author

Topic

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-30 : 04:55:56

I had query as

SELECT DISTINCT S_PARTY, P_NAME, S_DATE, S_CONTRACT
FROM SAUDA ,PARTY
WHERE S_PARTY = P_CODE AND S_DATE BETWEEN '09-Apr-2006' AND '11-Apr-2006' AND S_CITY = 'Z' AND S_MKT_TYPE = 'N' AND P_CAT != 'DZ' AND P_CAT != 'Dz'
ORDER BY S_DATE, S_PARTY

Takes 45 Second to complete. When I check the Execution plan, it shows the “Table Scan, Cost 91%” for the Table SAUDA. Actually I should pick the index scan for the Index “XS_DATE”
Which is defined as

CREATE UNIQUE INDEX [XS_DATE] ON [dbo].[SAUDA]([S_DATE], [S_NO]) ON [PRIMARY]

If I try with HINT as

SELECT DISTINCT S_PARTY, P_NAME, S_DATE, S_CONTRACT
FROM PARTY , SAUDA WITH(INDEX(XS_DATE))
WHERE S_PARTY = P_CODE AND S_DATE BETWEEN '09-Apr-2006' AND '11-Apr-2006' AND S_CITY = 'Z' AND S_MKT_TYPE = 'N' AND P_CAT != 'DZ' AND P_CAT != 'Dz'
ORDER BY S_DATE, S_PARTY

Result takes less then 2 seconds.

My question is why SQL Server took the decision to have Table Scan not the Index scan (using XS_DATE), for this query.

We had converted the same database to Oracle 10g same query takes less than 2 second & uses the XS_DATE index.
What happen to the SQL Server?

Kind Regards,
Thanks.
Gurpreet S. Gill

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-30 : 05:09:21

Try using ISO dateformat:

S_DATE BETWEEN '20060409' AND '20060411'

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 05:11:58

Might be that the statistics for the table are stale?

It may also be that the indexes available for the JOIN (or perhaps even the ABSENCE of suitable indexes for the JOIN!!) are causing a table scan to look preferable.

It also might help if you used the JOIN syntax instead - I suppose its possible that without an Explicit JOIN statement SQL server is making a dumb choice.


SELECT DISTINCT S_PARTY, P_NAME, S_DATE, S_CONTRACT
FROM SAUDA
    JOIN PARTY 
        ON P_CODE = S_PARTY
WHERE     S_DATE BETWEEN '09-Apr-2006' AND '11-Apr-2006' 
      AND S_CITY = 'Z' 
      AND S_MKT_TYPE = 'N' 
      AND P_CAT != 'DZ' 
      AND P_CAT != 'Dz'
ORDER BY S_DATE, S_PARTY

and further more assuming that the "P_" columns are in PARTY table and "S_" ones in SAUDA I would do:


SELECT DISTINCT S_PARTY, P_NAME, S_DATE, S_CONTRACT
FROM SAUDA
    JOIN PARTY 
        ON P_CODE = S_PARTY
       AND P_CAT != 'DZ' 
       AND P_CAT != 'Dz'
WHERE     S_DATE BETWEEN '09-Apr-2006' AND '11-Apr-2006' 
      AND S_CITY = 'Z' 
      AND S_MKT_TYPE = 'N' 
ORDER BY S_DATE, S_PARTY

and I would also change the dates to ISO format (what you have won't work if, for example, the Locale of the server changes to a non-English language):

S_DATE BETWEEN '20060409' AND '20060411'

plus I would put "dbo." before each of the two tables - to increase the chance of the query being cached.

Kristen

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-30 : 05:45:52

After the suggestions by Mr. Kristen & harsh_athalye my query become as
SELECT DISTINCT S_PARTY, P_NAME, S_DATE, S_CONTRACT
FROM dbo.SAUDA
JOIN dbo.PARTY
ON P_CODE = S_PARTY
AND P_CAT != 'DZ'
AND P_CAT != 'Dz'
WHERE
S_DATE BETWEEN '20060409' AND '20060411'AND
S_CITY = 'Z' AND S_MKT_TYPE = 'N'
ORDER BY S_DATE, S_PARTY

but still the same time it takes (45 Seconds).

Kristen you got right P_ belongs to PARTY & S_ belongs to SAUDA table.

I seem that my existing Indexes are creating the problem, could this be the case? (if so then why ORACLE does the best?)

Kind Regards,
Thanks.
Gurpreet S. Gill

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 05:51:21

Maybe your statistics are not up to date? The query might in the cache in ORACLE and not SQL?
You have double checks for P_CODE and S_PARTY. First in the join and later in the WHERE.

SELECT DISTINCT	s.S_PARTY,
		p.P_NAME,
		s.S_DATE,
		s.S_CONTRACT
FROM		dbo.SAUDA s
INNER JOIN	dbo.PARTY p ON p.P_CODE = s.S_PARTY AND p.P_CAT NOT IN ('Dz', 'DZ')
WHERE		s.S_DATE BETWEEN '20060409' AND '20060411'
		AND s.S_CITY = 'Z'
		AND s.S_MKT_TYPE = 'N' 
ORDER BY	s.S_DATE,
		s.S_PARTY

Peter Larsson
Helsingborg, Sweden

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-30 : 06:03:30

Hi Peter Larsson
my mistake of double check of P_CODE and S_PARTY (I had changed). I execute the query given by you.But still same time cost.

quote:

SELECT DISTINCT	s.S_PARTY,
		p.P_NAME,
		s.S_DATE,
		s.S_CONTRACT
FROM		dbo.SAUDA s
INNER JOIN	dbo.PARTY p ON p.P_CODE = s.S_PARTY AND p.P_CAT NOT IN ('Dz', 'DZ')
WHERE		s.S_DATE BETWEEN '20060409' AND '20060411'
		AND s.S_CITY = 'Z'
		AND s.S_MKT_TYPE = 'N' 
ORDER BY	s.S_DATE,
		s.S_PARTY

Any Other solution?

Kind Regards,
Thanks.
Gurpreet S. Gill

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 06:04:45

"You have double checks for P_CODE and S_PARTY"

Can you clarify that Peso? I don't follow it ...

gsgill76:

Check your statistics are up to date, or just refresh them and rerun your test:

UPDATE STATISTICS dbo.PARTY WITH FULLSCAN
UPDATE STATISTICS dbo.SAUDA WITH FULLSCAN

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 06:09:50

I am not sure the query optimizer is clever enought to only check for P_CODE = S_PARTY once.
It certainly does it for the JOIN operation but I think the optimizer checks again later when parsing the WHERE clause even though all records meet this criteria.

When I copied OP code to QA, I got

SELECT DISTINCT S_PARTY, P_NAME, S_DATE, S_CONTRACT
FROM dbo.SAUDA
JOIN dbo.PARTY 
ON P_CODE = S_PARTY 
AND P_CAT != 'DZ' 
AND P_CAT != 'Dz' 
WHERE 
S_DATE BETWEEN '20060409' AND '20060411'AND 
P_CODE = S_PARTY AND
S_CITY = 'Z' AND S_MKT_TYPE = 'N' 
ORDER BY S_DATE, S_PARTY

Peter Larsson
Helsingborg, Sweden

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-30 : 06:10:48

My Auto UPDATE STATISTICS option is TRUE for this Database.

Gurpreet S. Gill

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-30 : 07:04:55

Hi
Peter Larsson,
i had check & changed my double check of P_CODE = S_PARTY, that was my mistake.
I am still with same time cost.

Should I post my all the Indexes of SAUDA & PARTY Tables? (that could help you people to analize).

Kind Regards,
Thanks.
Gurpreet S. Gill

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 08:05:05

"My Auto UPDATE STATISTICS option is TRUE for this Database."

No harm running the UPDATE STATS commands manually. Otherwise you won't know if it makes a difference

- although it might not of course!

Kristen

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-12-01 : 05:00:13

Hi Kristen,
I did execute UPDATE STATS commands manually, but no change in the response time.

Regards,
Thanks.
Gurpreet S. Gill

Kristen
Test

22859 Posts

Posted - 2006-12-01 : 06:18:33

Shame

Need to see the Query Plan then please (please post both sets of output):


SET SHOWPLAN_TEXT ON
GO
... your query here ...
GO
SET SET SHOWPLAN_TEXT OFF
GO

and repeat for

SET STATISTICS IO ON; SET STATISTICS TIME ON
GO
... your query here ...
GO
SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO

Kristen

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-12-01 : 07:00:09

SET SHOWPLAN_TEXT ON
GO
SELECT DISTINCT s.S_PARTY,
p.P_NAME,
s.S_DATE,
s.S_CONTRACT
FROM dbo.SAUDA s
INNER JOIN dbo.PARTY p ON p.P_CODE = s.S_PARTY AND p.P_CAT NOT IN ('Dz', 'DZ')
WHERE s.S_DATE BETWEEN '20060409' AND '20060411'
AND s.S_CITY = 'Z'
AND s.S_MKT_TYPE = 'N'
ORDER BY s.S_DATE,
s.S_PARTY
GO
SET SHOWPLAN_TEXT OFF
GO

I got the TWO output.
First output:

SELECT DISTINCT s.S_PARTY, p.P_NAME, s.S_DATE, s.S_CONTRACT FROM dbo.SAUDA s INNER JOIN dbo.PARTY p ON p.P_CODE = s.S_PARTY AND p.P_CAT NOT IN ('Dz', 'DZ') WHERE s.S_DATE BETWEEN '20060409' AND '20060411' AND s.S_CITY = 'Z' AND s.S_MKT_TYPE = 'N' ORDER BY s.S_DATE, s.S_PARTY

Second output:
|--Sort(DISTINCT ORDER BY:([s].[S_DATE] ASC, [s].[S_PARTY] ASC, [s].[S_CONTRACT] ASC))
|--Hash Match(Inner Join, HASH:([s].[S_PARTY])=([p].[P_CODE]), RESIDUAL:([TZEN2007].[dbo].[PARTY].[P_CODE] as [p].[P_CODE]=[TZEN2007].[dbo].[SAUDA].[S_PARTY] as [s].[S_PARTY]))
|--Table Scan(OBJECT:([TZEN2007].[dbo].[SAUDA] AS [s]), WHERE:([TZEN2007].[dbo].[SAUDA].[S_DATE] as [s].[S_DATE]>='2006-04-09 00:00:00.000' AND [TZEN2007].[dbo].[SAUDA].[S_DATE] as [s].[S_DATE]<='2006-04-11 00:00:00.000' AND [TZEN2007].[dbo].[SAUDA].[S_CITY] as [s].[S_CITY]='Z' AND [TZEN2007].[dbo].[SAUDA].[S_MKT_TYPE] as [s].[S_MKT_TYPE]='N'))
|--Hash Match(Inner Join, HASH:([Bmk1003])=([Bmk1003]), RESIDUAL:([Bmk1003] = [Bmk1003]))
|--Hash Match(Inner Join, HASH:([Bmk1003])=([Bmk1003]), RESIDUAL:([Bmk1003] = [Bmk1003]))
| |--Index Seek(OBJECT:([TZEN2007].[dbo].[PARTY].[X_PCAT] AS [p]), SEEK:([p].[P_CAT] < 'Dz' OR [p].[P_CAT] > 'Dz'), WHERE:([TZEN2007].[dbo].[PARTY].[P_CAT] as [p].[P_CAT]<'DZ' OR [TZEN2007].[dbo].[PARTY].[P_CAT] as [p].[P_CAT]>'DZ') ORDERED FORWARD)
| |--Index Scan(OBJECT:([TZEN2007].[dbo].[PARTY].[QST8839135] AS [p]))
|--Index Scan(OBJECT:([TZEN2007].[dbo].[PARTY].[X_PNAME] AS [p]))

------------------------------------------------------------------------------------------
SET STATISTICS IO ON; SET STATISTICS TIME ON
GO
SELECT DISTINCT s.S_PARTY,
p.P_NAME,
s.S_DATE,
s.S_CONTRACT
FROM dbo.SAUDA s
INNER JOIN dbo.PARTY p ON p.P_CODE = s.S_PARTY AND p.P_CAT NOT IN ('Dz', 'DZ')
WHERE s.S_DATE BETWEEN '20060409' AND '20060411'
AND s.S_CITY = 'Z'
AND s.S_MKT_TYPE = 'N'
ORDER BY s.S_DATE,
s.S_PARTY
GO
SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO

Output:
CPU time = 0 ms, elapsed time = 1 ms.

(1494 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PARTY'. Scan count 4, logical reads 382, physical reads 3, read-ahead reads 373, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SAUDA'. Scan count 1, logical reads 133821, physical reads 299, read-ahead reads 125994, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1640 ms, elapsed time = 34453 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

------------------------------------------------------------------------------------------
Kind regards,
Gurpreet S. Gill

Kristen
Test

22859 Posts

Posted - 2006-12-01 : 07:17:44

Well I'm sorry, but it makes no sense to me why it is not choosing to use the Date index.

You don't appear to have a clustered index on SAUDA - but I don't think that would make a difference to its choice.

I can see why its taking a while though, 'coz its a lot of work:

Table 'SAUDA'. Scan count 1, logical reads 133821

in fact even:

Table 'PARTY'. Scan count 4, logical reads 382

is quite a lot of work.

Perhaps someone else can see why the query plan is so poor.

Otherwise you will have to resort to using the HINT.

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-01 : 07:22:29

How about this?

SELECT DISTINCT S.S_PARTY, P.P_NAME, S.S_DATE, S.S_CONTRACT
FROM 
(
	SELECT S_PARTY,  S_DATE, S_CONTRACT
	FROM dbo.SAUDA
	WHERE 
	S_DATE BETWEEN '20060409' AND '20060411' AND 
	S_CITY = 'Z' AND S_MKT_TYPE = 'N' 
) S
JOIN
(
	SELECT P_NAME
	FROM dbo.PARTY
	WHERE P_CAT != 'DZ' AND
	P_CAT != 'Dz'
) P
ON P.P_CODE = S.S_PARTY 
ORDER BY S.S_DATE, S.S_PARTY

Harsh Athalye
India.
"Nothing is Impossible"

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-12-01 : 07:33:46

Dear Kristen
Thanks for the help.
It seem that at this particular case the SQL Server makes a BAD CHOICE.Here the ORACLE win the race(i too test the same on the SQL Base, Gupta/Centura product, its again the fast).So,i thing the only way out is "HINTS".

[Comments]
For me this particular behavior can effect the overall performance of our Project(this will really bad part of our project)
[/Comments]

Kind Regards,
Thanks.
Gurpreet S. Gill

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-12-01 : 07:38:37

quote:

Originally posted by harsh_athalye

How about this?

SELECT DISTINCT S.S_PARTY, P.P_NAME, S.S_DATE, S.S_CONTRACT
FROM 
(
	SELECT S_PARTY,  S_DATE, S_CONTRACT
	FROM dbo.SAUDA
	WHERE 
	S_DATE BETWEEN '20060409' AND '20060411' AND 
	S_CITY = 'Z' AND S_MKT_TYPE = 'N' 
) S
JOIN
(
	SELECT P_NAME
	FROM dbo.PARTY
	WHERE P_CAT != 'DZ' AND
	P_CAT != 'Dz'
) P
ON P.P_CODE = S.S_PARTY 
ORDER BY S.S_DATE, S.S_PARTY

Harsh Athalye
India.
"Nothing is Impossible"

Sorry Harsh Athalye

This throws error @ ON P.P_CODE = S.S_PARTY

Msg 207, Level 16, State 1, Line 17
Invalid column name 'P_CODE'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-01 : 07:41:17

Oh, my mistake!

SELECT DISTINCT S.S_PARTY, P.P_NAME, S.S_DATE, S.S_CONTRACT
FROM 
(
	SELECT S_PARTY,  S_DATE, S_CONTRACT
	FROM dbo.SAUDA
	WHERE 
	S_DATE BETWEEN '20060409' AND '20060411' AND 
	S_CITY = 'Z' AND S_MKT_TYPE = 'N' 
) S
JOIN
(
	SELECT P_CODE, P_NAME
	FROM dbo.PARTY
	WHERE P_CAT != 'DZ' AND
	P_CAT != 'Dz'
) P
ON P.P_CODE = S.S_PARTY 
ORDER BY S.S_DATE, S.S_PARTY

Harsh Athalye
India.
"Nothing is Impossible"

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-12-01 : 07:44:05

I change your(Mr.harsh_athalye) codes as


SELECT DISTINCT S.S_PARTY, P.P_NAME, S.S_DATE, S.S_CONTRACT
FROM 
(
	SELECT S_PARTY,  S_DATE, S_CONTRACT
	FROM dbo.SAUDA
	WHERE 
	S_DATE BETWEEN '20060409' AND '20060411' AND 
	S_CITY = 'Z' AND S_MKT_TYPE = 'N' 
) S
JOIN
(
	SELECT P_NAME,P_CODE
	FROM dbo.PARTY
	WHERE P_CAT != 'DZ' AND
	P_CAT != 'Dz'
) P
ON P.P_CODE = S.S_PARTY 
ORDER BY S.S_DATE, S.S_PARTY

But same TIME.

Gurpreet S. Gill

Kristen
Test

22859 Posts

Posted - 2006-12-01 : 08:24:05

Harsh: The index is only covering [S_DATE], [S_NO] - so I don't think any of these approaches will use the index, without a clustered index too lean on from, say, S_NO (or the Clustered Index key that would be in the date index too)

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources