Workflow question

[mercwrought]

Hi all, got a stupid question here that I want to confirm the answer to.

In a DTS package, inside of an SQL Task if there are multiple queries does server try to execute them concurrently?

I was informed that id tries unless you put a go after each query.

Thanks for your answers.

 

[EdwinGene]

They are executed in the order they are written.

 

[mercwrought]

EdwinGene thx for the reply. Here is what I found on my own. Statements that are not separated by a “GO” command are batched together. Could the batch be responsible for this, executing them at the same time not understanding that they need to be executed in that order. There are 2 simple SQL statements that are in the SQL task in DTS package that is not acting properly when it executes.

 insert into XXX
	select
        M.recordid,
        'active',
        r2.last,
        r2.ccode,
        r2.fcode,
        NULL,
        NULL
        FROM   MTOM M inner join pass r1 on m.recordid = r1.idnumber inner join cacct c on m.recordid=c.idnumber inner join pass r2 on r2.idnumber = c.pnumber
        where c.pnumber <>0

update pass set ccode = null, fcode = null where idnumber between 1230000 and 3230000

It keeps giving rows that should have a ccode and an fcode a null. I have tried to replicate the problem but have not been very successful. Could it be bad memory in the server? Any other ideas?

 

[dbomrrsm]

try it with a GO in between and if that sorts the problem then you know they were previously being executed at the same time.

I think it may be that if there is no go then it will parse and execute them as if two seperate queries had hit the DB at once.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[mercwrought]

dbomrrsm I agree and I have inserted the GO in the all of my SQL tasks but I won’t be able to find out if it worked till I run the next scheduled test run of the conversion. This will be in 1.5 to 2 weeks. I’ve done some testing with the GO this morning and it seems to resolve the problem but then again I did not have much success in duplicating the problem. Thx.

 

[donutman]

I wonder if the Update is causing the query to timeout. If the where clause is allowing 10,000 rows to be updated, then you should be doing the Update in batches.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[yksvaan]

I find it really hard to believe that statements would be running in parallel. It is probably something like donutman suggests. Funny thing is that Exec SQL Task doesn't necessarily give any kind of error even if the statements would give an error in Query Analyzer.

My suggestion is to put all sql statements as possible into sql stored procedures and use Exec SQL Task just to execute the procedures. And you have no GO word in middle of a procedure.

You have checked that you have usable disk space in/for db log file ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[yksvaan]

Have to add that if there is no other activity during the load window, you should use table locking to avoid unnecessary overhead

insert into XXX WITH (TABLOCKX)
FROM MTOM M WITH (TABLOCK)inner join pass r1 WITH (TABLOCK) on m.recordid = r1.idnumber inner join cacct c WITH (TABLOCK) on m.recordid=c.idnumber inner join pass WITH (TABLOCK) r2 on r2.idnumber = c.pnumber

update pass WITH (TABLOCKX) set ...

You may get markable performance gain with this.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[mercwrought]

Thx yksvaan. I will look into what you advise. There is sufficient disk space. The SQL task updates about 100,000 rows. Now that you mention it TABLOCK would be probably increase my speed. On another note I have noticed while running another part of the conversation that the tasks in an extremely large SQL task are definitely executing out of order. I have not been able to catch it executing queries right beside each other out of order but I have caught it executing command at the bottom of the list before several at the top. Not sure right now why but It is.

 

[yksvaan]

KISS: if it's not a transactional database, use the recovery model of Simple and backup the whole db every time.

I won't belive statements in a single Execute SQL Task are not executing in order before I see evidence of it. Whole other thing is that if you have several Execute SQL Tasks. DTS will try to run ANY kind of tasks in parallel, if they are not precedenced exclusively in the workflow and depending on some settings. A mistake is easy to make because the arrows in dts designer might be interpreted wrongly, happened to me, after which I started to check precedence from workflow properties. A task and a task's workflow position are two different things in dts package.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]