Can one tell if a query is taking too long?

[micang]

SQL 2000

Hi All,

I am running an update query joining one table to another.

The query has been going on for 3 hours now. Is there a way of finding out if the query will go on and on until it either falls over or it has to be stopped?

Many Thanks

Michael

 

[bborissov]

3 hours?
How many records?
Did you try the same query on your development DataBase and use execution plan to see what happens?
Did you run SQL Profiler before that query to see if there is a Dead Locks?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[micang]

Hi Borislav,

It is a development database. We are new to SQL, so it's a bit of hit and miss/trial and error.

Firstly this is run on a desktop pc (1 gig ram, p4).

I would not call it a "database" as such..it's just tables for now. I have a table 36million rows. 30 or so columns. I have to update 5 of these columns from a another table that has 45000 rows.

This is what I have tried to run.

update tbDVLACensus_Anonymised
set tbDVLACensus_Anonymised.Segment = decode.strSegment , 
    tbDVLACensus_Anonymised.Revised_EngineCapacity = decode.intCCVal,
    tbDVLACensus_Anonymised.MakeDescription = decode.MakeDescription,
    tbDVLACensus_Anonymised.Model_Description = decode.ModelDescription,
    tbDVLACensus_Anonymised.MakeModelRange_Description = decode.MakeModelRange_Description,
    tbDVLACensus_Anonymised.ModelRange_Description = decode.ModelRange_Description,
    tbDVLACensus_Anonymised.Revised_FuelType = decode.strFuel,
    tbDVLACensus_Anonymised.Transmission = decode.strTransmission
  from tbDVLACensus_Anonymised join Decode on (tbDVLACensus_Anonymised.Revised_DVLA_MakeCode = Decode.strMakeCode)
				and (tbDVLACensus_Anonymised.[Revised_DVLA_ModelCode] = Decode.[strModelCode])
				and (convert(int,tbDVLACensus_Anonymised.strEngineCapacity) between Decode.intCCmin and Decode.intccmax)
where intDVLACensus_AnonymisedID < 20000000

It works fine if I do example 50 000 rows a time (and they I do this is by putting a where between clause on a unique id!) I tried 20 million as u see above, and I think it will never run!

I have indexes on the following fields:
Teh 36 milloin row table: tbDVLACensus_Anonymised - Revised_DVLA_MakeCode,
Revised_DVLA_ModelCode,
strEngineCapacity,
intDVLACensus_AnonymisedID(unique)

On the other table 45 0000 rows: decode -
strMakeCode,
strModelCode,
intCCmin,
intCCmax


I have looked at the execution plan, but I don;t understand it enough to know what it's telling me. As for the SQL profile, it's the first I here of it.

I know this is messy, but I have not been able to update this for months, and it's driving me insane!!

ANY help will be appreciated immensely.

Thank you

Michael

PS - I have killed it and now ready try another route. Otherwise I think I will have to run like 30 odd queries doing 1 million rows at a time. Like I said I am a novice and this is way over my knowledge.

 

[bborissov]

Did you have index on HEY what is your WHERE clause?

WHERE intDVLACensus_AnonymisedID < 2000000
BUT that is your table name isn't it?
I suppose you have a typo here, so did you have index in the intDVLACensus_AnonymisedID based on the field you testing in the WHERE clause?
If not create it.
Also can you post the execution plan here?
(try first on the small database, there is no need to wait 3-10 hours to get the execution plan )

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[micang]

The table name is tbDVLACensus_Anonymised
the unique id index is on column:intDVLACensus_AnonymisedID so the where clause is on this column
(WHERE intDVLACensus_AnonymisedID < 2000000)

Um, stoopid question! How do I get the execution plan pasted in here? I assume there is a function that exports teh plan to file, like txt etc?

Michael

 

[SQLDenis]

Here is first problem

and (convert(int,tbDVLACensus_Anonymised.strEngineCapacity) between Decode.intCCmin and Decode.intccmax)


the convert will cause a scan instead of a seek

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[micang]

Hi Denis,

"and (convert(int,tbDVLACensus_Anonymised.strEngineCapacity) between Decode.intCCmin and Decode.intccmax)


the convert will cause a scan instead of a seek "

Does this mean I should convert the whole column [strEngineCapacity] to an integer type?

Many thanks.

Michael

 

[SQLDenis]

>>Um, stoopid question! How do I get the execution plan pasted in here

SET SHOWPLAN_TEXT ON


>>Does this mean I should convert the whole column [strEngineCapacity] to an integer type?


yes if you store only integers there is no point making the column a varchar



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[micang]

Hi Denis,

Thank you. I will convert to integer, but before I do I would like to show the exec plan.
If I run example:

SET SHOWPLAN_TEXT ON
select col1
from blah
where col1 =1

I get an error.

Michael

 

[micang]

Hi,

Here is the query and the exec plan below.

SET SHOWPLAN_TEXT ON
update tbDVLACensus_Anonymised
set tbDVLACensus_Anonymised.Segment = decode.strSegment, 
    tbDVLACensus_Anonymised.Revised_EngineCapacity = decode.intCCVal,
    tbDVLACensus_Anonymised.MakeDescription = decode.MakeDescription,
    tbDVLACensus_Anonymised.Model_Description = decode.ModelDescription,
    tbDVLACensus_Anonymised.MakeModelRange_Description = decode.MakeModelRange_Description,
    tbDVLACensus_Anonymised.ModelRange_Description = decode.ModelRange_Description,
    tbDVLACensus_Anonymised.Revised_FuelType = decode.strFuel,
    tbDVLACensus_Anonymised.Transmission = decode.strTransmission
  from tbDVLACensus_Anonymised inner join Decode on (tbDVLACensus_Anonymised.Revised_DVLA_MakeCode = Decode.strMakeCode)
				and (tbDVLACensus_Anonymised.[Revised_DVLA_ModelCode] = Decode.[strModelCode])
				and (convert(int,tbDVLACensus_Anonymised.strEngineCapacity) between Decode.intCCmin and Decode.intccmax)
where intDVLACensus_AnonymisedID between 1 AND 50
SET SHOWPLAN_TEXT OFF

Exec plan of above code:

update tbDVLACensus_Anonymised
set tbDVLACensus_Anonymised.Segment = decode.strSegment,
tbDVLACensus_Anonymised.Revised_EngineCapacity = decode.intCCVal,
tbDVLACensus_Anonymised.MakeDescription = decode.MakeDescription,
tbDVLACensus_Anonym

(1 row(s) affected)

|--Clustered Index Update(OBJECT[Anon].[dbo].[tbDVLACensus_Anonymised].[PK_tbDVLACensus_Anonymised_intDVLACensus_AnonymisedID]), SET[tbDVLACensus_Anonymised].[MakeDescription]=[Decode].[MakeDescription], [tbDVLACensus_Anonymised].[Model_Description]=
|--Compute Scalar(DEFINE[Expr1006]=Convert([Decode].[intCCVal])))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY[Bmk1000] ASC))
|--Hash Match(Inner Join, HASH[tbDVLACensus_Anonymised].[Revised_DVLA_MakeCode], [tbDVLACensus_Anonymised].[Revised_DVLA_ModelCode])=([Decode].[strMakeCode], [Decode].[strModelCode]), RESIDUAL(([Decode].[strMakeCode]=[tbDVLACensus
|--Clustered Index Seek(OBJECT[Anon].[dbo].[tbDVLACensus_Anonymised].[PK_tbDVLACensus_Anonymised_intDVLACensus_AnonymisedID]), SEEK[tbDVLACensus_Anonymised].[intDVLACensus_AnonymisedID] >= 1 AND [tbDVLACensus_Anonymised].[int
|--Table Scan(OBJECT[Anon].[dbo].[Decode]))

(7 row(s) affected)

I can't make sense of it!

Many thanks

Michael

 

[zhavic]

Hi,

for me this looks like the source of the problem:

Clustered Index Update(OBJECT:([Anon].[dbo].[tbDVLACensus_Anonymised].[PK_tbDVLACensus_Anonymised_intDVLACensus_AnonymisedID])

Are you updating columns that are used in clustered index ?

On that table with 36 milions rows it can take long time to update clustered index, because rows needs to be sorted and stored physicaly in database in given order.

If you can, try to change that index to non clustered or remove that index - for test.

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[micang]

Hi Zhavic,

Yes, it does have a clustered index. And i think you are correct, I noted that it seems the table scan with the clustered index is the major problem.

Thank you, I will try removing it and see how much it speeds up things.

Michael