Need to Tune Query

Question

Post reply

Need to Tune Query

shrikant-279400

SSCommitted

Points: 1651
More actions
June 16, 2006 at 7:25 am

#118416

Hi
I need to tune a inner join query.
SELECT IP.F2796 AS F2796, IP.F2798 AS F2798, IP.F2800 AS F2800, IP.F2802 AS F2802, IP.F2804 AS F2804,IP.F2827 AS F2827, cast('false' AS bit) AS F2820
INTO TABLEA
FROM T5718_1900_1 IP INNER JOIN T2888_1900_1 OP ON
(IP.F2796 = OP.F2796 ) AND
(IP.F2800 = OP.F2800 ) AND
(IP.F2804 = OP.F2804 ) AND
(IP.F2808 = OP.F2808 ) AND
(IP.F2812 = OP.F2812 ) AND
(IP.F2824 = OP.F2824 )

query takes 1:20 min for inserting 100 rows in the temp table. I need this query to work on 1ML table, but the query hangs (i tried for an hour before killing process manually )

Afa, Indexing T2888_1900_1 table has cluster index on the ROW_ID column.

Regards
Shrikant Kulkarni

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

The design of your tables is terribly wrong.

But anyway it would be useful at least to have some indexes on columns mentioned in the query.

_____________
Code for TallyGenerator

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 2

Try creating non-clustered index for table T2888_1900_1 on which your joning condition works.

------------
Prakash Sawant

http://psawant.blogspot.com

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 3

Yes, I know..sorry for not giving all information. We have an application where user create the tables and they are named as T434_....

The above query is puting matching rows between two into temp table, for further processing.

I observed another thing. I am using SQL 2005. when I execute query in SQL 2000. it creates different execution plan. SQL 2005 has "Lazy spool" which is killing the query.

|--Compute Scalar(DEFINE[Expr1006]=(0), [Expr1007]=(0), [Expr1008]=(1), [Expr1009]=(1))) |--Compute Scalar(DEFINE[Expr1006]=(0), [Expr1007]=(0), [Expr1008]=(1), [Expr1009]=(1)))

|--Top(TOP EXPRESSION(10)))

|--Parallelism(Gather Streams)

|--Nested Loops(Left Outer Join, WHERE([UDAY_CLIENTDATA].[dbo].[T5718_1900_1].[F2796]=[UDAY_CLIENTDATA].[dbo].[T2888_1900_1].[F2796] ) …….all join conditions

|--Table Scan(OBJECT[UDAY_CLIENTDATA].[dbo].[T5718_1900_1]))

|--Table Spool

|--Clustered Index Scan(OBJECT[UDAY_CLIENTDATA].[dbo].[T2888_1900_1].[PK__T2888_1900_1__47919582] AS [OP]))

Regards
Shrikant Kulkarni

stax68 SSChampion Points: 11711 More actions · Answer 4

Both tables being scanned, one repeatedly (the lazy spool.) Put a compound index on all join columns on both tables, in the same order on both tables. Add any remaining columns in the SELECT list to the end of the relevant index, to make a covering index (or use INCLUDE in v9)

The query is also using parallelism which the 'optimiser' often makes a bit of a hash of (excuse the pun). Try using the maxdop = 1 query hint or setting the 'max degree of parallelism' db option to 1.

Your plan also shows a left join, which doesn't occur in the posted query...?

And I agree with Sergiy that the design of your tables is a total wrong'un (and I don't mean a doosra).

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 5

Hi Tim

Thanks a lot for the help. I got the isuue, it was with MAXDOP. some more info about the problem..

"The sys process info tells that, the status of process is suspended and waittype is CXPACKET and PAGEIOLATCH_EX.

CXPACKET shows up in parallel query execution. That is, when a single query is parallelized by the server and run on multiple threads. The threads have to exchange data, and CXPACKET indicates that one thread is waiting for data to be sent from another thread"

sp_configure 'show advanced options', 1;GORECONFIGURE WITH OVERRIDE;GOsp_configure 'max degree of parallelism', 1;GORECONFIGURE WITH OVERRIDE;GO

Regards
Shrikant Kulkarni

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Shrikant, don't try to spoil your server, just create required indexes.

That's all.

_____________
Code for TallyGenerator

stax68 SSChampion Points: 11711 More actions · Answer 7

I have found that parallelism very often seems to spoil otherwise impeccable queries. Maybe it's better in v9, but I doubt it. MS just keep adding more 'features' and don't seem to have time to fix bugs.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 8

Hi Tim, I am 100% agree that making MAXDOP = 1 is not the solution to be implemented and indexes on the columns participating in join should have non-clustered indexes.

I am facing two isuues...I have transaction that

1. creates temp table by fetching reqd rows from input

2. perform some validations

3. Update/Insert or U+A in the destination table.

Query i mentiones was the first step..

1. creating index on the columns in join clause in dest table (in the apllication these are key columns in the T- table, which is created/Altered by an user) would definately made query faster but the at the same time size of index should not be crossed 900 B. The T- table can have 1 key col or can be more that 10/20...( unfortunately this is in hand of end user.. in the application ).

2. Another thing I need to take care is of Locking and concurrency. one of feature MS v9 have is Snapshot isolation (readers don't block writers.) but some how I can't make use of this feature. ( I have some table alteration during the transaction ) .so when this process updates destination table it has to lock entire table as where cluase in update is performing table scan, and i am putting lock on entire table though I might update 10 rows out of 10000. which is again not a good design ...

Regards
Shrikant Kulkarni