Executing query batch - 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 Programming

Executing query batch

Author

Topic

Ads
Starting Member

23 Posts

Posted - 2011-10-10 : 04:55:01

Hi,

When running the following code, it's constantly in a state of Executing query batch... If I reset the server the code will run once but then enter the same state again, why is this?

USE efacdb

DECLARE @MyWebTable Table( webpartnum char(22), webpdrawing char(30), webprice float, webstock float, webweight float)
DECLARE @MyWebTable2 Table( webpartnum char(22), webpdrawing char(30), webprice float, webstock float, webweight float)

/*Extract webdata 'asis' from EFACS*/
INSERT INTO @MyWebTable
SELECT [partmaster].[partnum], [partmaster].[pdrawing],
([pricelist].[plunitprice] * 100) AS PriceF, [stock].[stquant] AS Unallocated, [partmaster].[weight]
FROM
[partmaster], [pricelist], [stock]
WHERE
[pricelist].[pltype] = 'PLUK' AND [pricelist].[plbrkqty] = 1 AND
[pricelist].[plpart] = [partmaster].[partnum] AND
[partmaster].[partnum] = [stock].[stocknum]
ORDER BY [partmaster].[partnum]

/*Update stock levels for all parts with allocations*/
USE ActinicholdingDB
UPDATE @MyWebTable
SET webstock = a.astunalloc
from AllocTable a
where a.apartnum = webpartnum

/*Update Stock levels for the multi-rev parts with allocations*/
Update @MyWebTable Set webstock = a.astlevelout FROM @MyWebTable, AllocTableOut a
WHERE substring(webpartnum,0,len(webpartnum)-1) = a.astpartnumout or webpartnum = a.astpartnumout

insert into @MyWebTable2 select * from @MyWebTable

/*Remove neg stock values*/
Update @MyWebTable Set webstock = 0 FROM @MyWebTable WHERE webstock < 0

/*Update MultiRev Parts - remove rev postfix*/
Update @MyWebTable
Set webpartnum = substring(webpartnum,0,len(webpartnum)-1)
FROM @MyWebTable, AllocTableOut a
WHERE substring(webpartnum,0,len(webpartnum)-1) = a.astpartnumout
and substring(webpartnum,0,len(webpartnum)-1) not in
(select t1.webpartnum from @MyWebTable t1, @MyWebTable2 t2 where t1.webpartnum = substring(t2.webpartnum,0,len(t2.webpartnum)-1))

/*Select final web table data*/
/*Select final web table data*/
USE ActinicholdingDB
DROP Table WebTable
Create Table Webtable
(apartnum varchar(30), apdrawing varchar(30), aprice integer, astock integer, aweight float)

Insert into WebTable
Select rtrim(webpartnum), rtrim( webpdrawing), Round(webprice,0), webstock, webweight FROM @MyWebTable

delete from webtable
where apartnum = 'RWD-MIFARE-'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 05:09:50

why are you trying to execute multiple db scripts together? why not put them separately into different batches in each db and execute
Also whats the amount of data you're trying to populate to table variables? if its considerably large try to use # tables instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-10 : 05:17:43

Sorry, I do not agree with visakh16,
Table variable shows significant load performance improvements over the use of a local temp table especially when used in a transaction. Further, performance of the global temp table exceeds the local temp table
Refer: http://sqlserverperformance.idera.com/uncategorized/performance-comparison-temp-tables-table-variables-sql-server/

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 05:41:09

You missed my point. I've specifically told that in case of large data especially when you want to take advantage of parallel plans you cant use table variables. see the below link

http://sqlblog.com/blogs/peter_larsson/archive/2009/10/15/performance-consideration-when-using-a-table-variable.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 05:42:19

quote:
Originally posted by jassi.singh

Sorry, I do not agree with visakh16,
Table variable shows significant load performance improvements over the use of a local temp table especially when used in a transaction. Further, performance of the global temp table exceeds the local temp table
Refer: http://sqlserverperformance.idera.com/uncategorized/performance-comparison-temp-tables-table-variables-sql-server/

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Glad that finally you posted something on your own rather than copying previous suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ads
Starting Member

23 Posts

Posted - 2011-10-10 : 06:09:38

Unfortunately I'm new to SQL. So a little slow in picking things up. The code has been run for a number of years uploading prices and stock for our website, we have 300 products or so, why would it suddenly change, I would understand if it takes a few minutes to update, however even after 3 days of executing it still hasnt created the table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 06:51:07

was there any blocking happening on the server? run
sp_who2 'active' when the execution time is going too long in db and see whether there's any blocking happening (look for BlkBy column with non empty value)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ads
Starting Member

23 Posts

Posted - 2011-10-10 : 09:27:41

SPID 68 has 182 in BLKBY with a CPUtime of 7843 and DISKIO of 2 with the command DROP TABLE if that helps.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 14:35:25

quote:
Originally posted by Ads

SPID 68 has 182 in BLKBY with a CPUtime of 7843 and DISKIO of 2 with the command DROP TABLE if that helps.

Thanks

so that was the reason

the process 182 was blocking this process. I think its due to fact the processes both here working on same set of objects and one had put a lock on them preventing other from accessing it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ads
Starting Member

23 Posts

Posted - 2011-10-11 : 04:08:38

How do I then un-block this for the job to run? do I need to change the code at all?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 04:13:37

you need kill one them or have to wait till one process gets over.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ads
Starting Member

23 Posts

Posted - 2011-10-11 : 05:16:01

Whats the best command to kill? when using kill 68 it says please choose a number between 1 and 32176? I just need to kill all processes from RFS/administrator

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 05:24:22

it should be KILL 182

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ads
Starting Member

23 Posts

Posted - 2011-10-11 : 07:33:10

Unfortunately it wont kill the process, it stats a wait time of 523453857?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources