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 2005 Forums
 Transact-SQL (2005)
 Strange behaviour of T-SQL

Author  Topic 

rikleo2001
Posting Yak Master

185 Posts
Posted - 2010-09-22 : 09:25:26
Hi

I am running a query like that.

Insert into Table a
Select distict b10.* from Table a b10
and Lots of union to other databases and tables.

If I run it like below, response time is 3 Sec
Select distict b10.* from Table a b10
and Lots of union to other databases and tables.

If I run it below, response time is hours...I had to stop it.

Insert into Table a
Select distict b10.* from Table a b10
and Lots of union to other databases and tables.


I tried it differant ways which works (3 sec)
Select distict b10.* INTO testtable from Table a b10
and Lots of union to other databases and tables.
Insert into Table a SELECT * from testtbale


Question is why below query goes into endless state?

Insert into Table a
Select distict b10.* from Table a b10
and Lots of union to other databases and tables.


Please help

Thanks


SKR

jh_sql
Starting Member

24 Posts
Posted - 2010-09-22 : 09:41:34
You insert data from table a to table a? Maybe u get endless loop if something in query gets the select query refressed, never really tested this kinda query, so just wild guess.
Go to Top of Page

rikleo2001
Posting Yak Master

185 Posts
Posted - 2010-09-22 : 09:44:29
Any help please, my production server is running slow and consuming 100% CPU.

Just to let you know that I recently migrated my database across to SQL Server 2005. And this behaviour is on SQL Server 2005.

SKR
Go to Top of Page

rikleo2001
Posting Yak Master

185 Posts
Posted - 2010-09-22 : 09:45:47
Thanks for that, but I am using query with (nolock), any more ideas?

SKR
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-09-22 : 10:14:26
This isn't a question that can be solved given the information provided.

Any keys? Any indexes? What size of data? What isolation level are you on? Data structure of the tables? What does the execution plan show you for different queries?

Are you REALLY doing

INSERT <table>
SELECT *
FROM xyz....

Because that's horrible. SELECT * in production and INSERT without a column list..... BLURGH

Any concurrent problems

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

rikleo2001
Posting Yak Master

185 Posts
Posted - 2010-09-22 : 10:39:54
Table A has only 50K rows and have clustered index.

Select query which returns a recordset is the same amount of fields as required by Table A hence I am writing this query

Insert into DatabaseA.dbo.LB10
Select Distinct B10.* From [DatabaseA].dbo.LB10 B10 (nolock)
Where Field_C = 'GK' And so on.......

Table DatabaseA.dbo.LB10 has clustered index
ALTER TABLE [dbo].[LB10] ADD CONSTRAINT [XPKMFSLB10] PRIMARY KEY CLUSTERED
(
[F1] ASC,
[F2] ASC,
[F3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]


And has keys which are in clustered index

That's all

As I said if I genenerate the select output to Temp table and insert into the same table from the temp table, it works in 3 sec, but when I join it togather with the problem statements, it goes into endless loop...

Any more guidance?








SKR
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2010-09-22 : 10:56:39
Charlie is saying "SELECT * FROM..." in ANY production environment is VERY BAD practice.

If you can do the insert into a temp table, then the other table with no problem, then do it that way. I would guess that the final target table has some kind of trigger or constraint connected to your other DB that is putting you into an endless loop.

If you follow the "How to ask" link, and supply all the data it asks for, a much better answer can be given to you.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-09-22 : 11:14:21
If you don't know / aren't aware of any triggers or constraints that could be slowing this down then my first recommendation would be:

1) Run Profiler while doing a small, small insert of this type. Trace the execution plan and have a look at locks / io etc.

Just to illustrate how horrible INSERT table SELECT * FROM .... can get....

What happens when the ordinal position of the columns changes? what if you have to modify the table (Say the collation on one of the columns) and you aren't careful about rearranging all the columns back into the same ordinal order?

Best case scenario -- the INSERT will fail with a type mismatch.

Worse case scenario -- it'll work but the columns will all be wrong.

If I was your DBA I'd never let production code do an insert without it being in the form

INSERT xyz (
    [col1]
    , [col2]
    , [col3]
    , ......
    )
SELECT
    'abc'
    , 232
    , [col23]
    , [colz]
    .....
FROM
    ....


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-09-22 : 11:58:25
"If I run it below, response time is hours...I had to stop it.

Insert into Table a
Select distict b10.* from Table a b10
and Lots of union to other databases and tables."

Is it improved if you do:

Insert into Table a
SELECT *
FROM
(
Select distict b10.* from Table a b10
and Lots of union to other databases and tables.
) AS X

??

If not then create a #TEMP table and repeat the test:

SELECT *
INTO #TEMP
FROM
(
Select distict b10.* from Table a b10
and Lots of union to other databases and tables.
) AS X
WHERE 1=0 -- i.e. do NOT insert any rows

Then test the duration of:

Insert into #TEMP
SELECT *
FROM
(
Select distict b10.* from Table a b10
and Lots of union to other databases and tables.
) AS X

if that is fast then the problem is indexes or triggers or fragmentation or somesuch on TableA
Go to Top of Page

rikleo2001
Posting Yak Master

185 Posts
Posted - 2010-09-23 : 03:28:34
Charlie, Thank you so much, I do understand this and I replaced as per your instructions, but it doesn't make any differance. But at least I told the application team with these recommendations.

Kristen
Yes, as I mentioned in my first post, I did tried it via temp table and work like a charm. Now I am really not sure which triggers are stopping this, what I will do to run trace with single insert statement and see what are the results, any other trace I need to look for while I run single insert statement?

Just to let you know, I already tired 1 insert statements with hardcoded values and it take only milliseconds, it is when I join it with self nested query.




SKR
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-09-23 : 04:24:38
Can you please post the actual query used?

Including all the 'and Lots of union to other databases and tables.'

You mention you are performing a self join at some point?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

rikleo2001
Posting Yak Master

185 Posts
Posted - 2010-09-23 : 04:32:31
Insert into dbo.LB10 (MKT_C, LANG_ISO_C, USC_C, USC_DESC_X, SEQ_R, UPDATE_S, UPDATE_USER_N, CUST_DESC_X, GRP_C)

Select Distinct MKT_C, LANG_ISO_C, USC_C, USC_DESC_X, SEQ_R, UPDATE_S, UPDATE_USER_N, CUST_DESC_X, GRP_C From [_STAG_].dbo.LB10 B10 (nolock)
Where MKT_C = 'GK'
And USC_C In
( --frist
select USC_C from
( -- second
select distinct listkey
,
coalesce( case tag when 'modln_usc_c' then modln_usc_c end
,case tag when 'modyr_usc_c' then modyr_usc_c end
,case tag when 'body_usc_c' then body_usc_c end
,case tag when 'drv_derivative_c' then drv_derivative_c end
,case tag when 'svp_usc_c' then svp_usc_c end
,case tag when 'eng_usc_c' then eng_usc_c end
,case tag when 'trans_usc_c' then trans_usc_c end
,case tag when 'colour_usc_c' then colour_usc_c end
,case tag when 'trim_usc_c' then trim_usc_c end) as usc_c
from
(select mkt_c + modln_usc_c + modyr_usc_c AS listkey
,modln_usc_c
,modyr_usc_c
,body_usc_c
,drv_derivative_c
,svp_usc_c
,eng_usc_c
,trans_usc_c
,colour_usc_c
,trim_usc_c
from [_STAG_].dbo.MF_ENT
where mkt_c = 'GK') B12
cross join
(select 'modln_usc_c' as tag
union
select 'modyr_usc_c' as tag
union
select 'body_usc_c' as tag
union
select 'drv_derivative_c' as tag
union
select 'svp_usc_c' as tag
union
select 'eng_usc_c' as tag
union
select 'trans_usc_c' as tag
union
select 'colour_usc_c' as tag
union
select 'trim_usc_c' as tag) tags
where listkey In (select * from wept.dbo.fnsplit(1,X,X,X,X',',')) --Dummy values
union
select distinct mkt_c+modln_usc_c+modyr_usc_c as listkey,
option_usc_c as usc_c
from [_STAG_].dbo.ent_OPT eo
where eo.mkt_c = 'GK'
and mkt_c+modln_usc_c+modyr_usc_c In (select * from wept.dbo.fnsplit('1,X,X,X,X',',')) --Dummy values
) as ul) OPTION (MAXDOP 1)



SKR
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-09-23 : 04:52:17
Wow. That's some really, really horrible code. Nested IN city and a cross join to a mass of unions......

I've tried to format it 


Insert into dbo.LB10 (MKT_C, LANG_ISO_C, USC_C, USC_DESC_X, SEQ_R, UPDATE_S, UPDATE_USER_N, CUST_DESC_X, GRP_C)

Select Distinct MKT_C, LANG_ISO_C, USC_C, USC_DESC_X, SEQ_R, UPDATE_S, UPDATE_USER_N, CUST_DESC_X, GRP_C

From
	[_STAG_].dbo.LB10  B10 (nolock)

Where
	MKT_C = 'GK'

	And	USC_C In

	( --frist
	select USC_C from 
  
		 ( -- second
		select  distinct
			listkey
			, coalesce( case tag when 'modln_usc_c' then modln_usc_c end
				,case tag when 'modyr_usc_c' then modyr_usc_c end
				,case tag when 'body_usc_c' then body_usc_c end
				,case tag when 'drv_derivative_c' then drv_derivative_c end
				,case tag when 'svp_usc_c' then svp_usc_c end
				,case tag when 'eng_usc_c' then eng_usc_c end
				,case tag when 'trans_usc_c' then trans_usc_c end
				,case tag when 'colour_usc_c' then colour_usc_c end
				,case tag when 'trim_usc_c' then trim_usc_c end
			) as usc_c
		from

			(
			select
				mkt_c + modln_usc_c + modyr_usc_c AS listkey
				,modln_usc_c
				,modyr_usc_c
				,body_usc_c
				,drv_derivative_c
				,svp_usc_c
				,eng_usc_c
				,trans_usc_c
				,colour_usc_c
				,trim_usc_c
			from
				[_STAG_].dbo.MF_ENT
			where
				mkt_c = 'GK') B12

				cross join (
					select 'modln_usc_c' as tag
					union select 'modyr_usc_c' as tag
					union select 'body_usc_c' as tag
					union select 'drv_derivative_c' as tag
					union select 'svp_usc_c' as tag
					union select 'eng_usc_c' as tag
					union select 'trans_usc_c' as tag
					union select 'colour_usc_c' as tag
					union select 'trim_usc_c' as tag) tags
					where
						listkey In (
							select *
							from wept.dbo.fnsplit('1,X,X,X,X',',')
					) --Dummy values
					union select distinct
						mkt_c+modln_usc_c+modyr_usc_c as listkey
						, option_usc_c as usc_c
					from
						[_STAG_].dbo.ent_OPT eo
					where
						eo.mkt_c = 'GK'
						and mkt_c+modln_usc_c+modyr_usc_c In (
							select *
							from wept.dbo.fnsplit('1,X,X,X,X',','))  --Dummy values
					)
					as ul
			) OPTION (MAXDOP 1)

Don't think I've done too good a job on it.

So what are you *actually* trying to do?

You are inserting into a table some data that is modified from data that is already in the table?

I think you might want to start again. Start from what you have (data wise now) -- so the structure of the three tables.

Can you give some sample data and the required output?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

rikleo2001
Posting Yak Master

185 Posts
Posted - 2010-09-23 : 05:19:12
I was shocked too when I saw this query.
Select query with all horrible joins returns 324 rows in 3 sec. (This retireve information from differant database on the same server)
But as soon as I execute it with insert it is where it goes into endless loop.

Now I tried to run trace, no triggers are being fired.

What I did then as suggested by Kristen, select into some temp table, then insert into from temp table, that works in < 5 sec. Same index are being utilized as above, why this approch is faster and other approch I adopted is going into lock mode?

But my point it what is causing this whole query with insert to halt?








SKR
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-09-23 : 05:25:01
I think what's happening is the query is causing a race condition with itself. You are trying to read from and write to the same table and during this query you are doing stuff that the engine just won't be able to plan for.

What isolation setting are you using?

If you are on READ COMMITTED you could try changing to READ COMMITTED SNAPSHOT. see if that makes any difference.

My advice though -- try and rewrite this completely. If left it's just going to lurk and someday cause a problem and the poor person that has to look at it just won't understand what the hell is going on.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-09-23 : 05:26:31
if you've got profiler up can yo see any locking or lock escalation when you run the query?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2010-09-23 : 06:37:39
Simplify!
SELECT DISTINCT	b10.MKT_C,
		b10.LANG_ISO_C,
		b10.USC_C,
		b10.USC_DESC_X,
		b10.SEQ_R,
		b10.UPDATE_S,
		b10.UPDATE_USER_N,
		b10.CUST_DESC_X,
		b10.GRP_C
FROM		[_STAG_].dbo.LB10 AS b10
INNER JOIN	(
				SELECT		CASE
							WHEN x.modln_usc_c IS NOT NULL THEN x.modln_usc_c
							WHEN x.modyr_usc_c IS NOT NULL THEN x.modyr_usc_c
							WHEN x.body_usc_c IS NOT NULL THEN x.body_usc_c
							WHEN x.drv_derivative_c IS NOT NULL THEN x.drv_derivative_c
							WHEN x.svp_usc_c IS NOT NULL THEN x.svp_usc_c
							WHEN x.eng_usc_c IS NOT NULL THEN x.eng_usc_c
							WHEN x.trans_usc_c IS NOT NULL THEN x.trans_usc_c
							WHEN x.colour_usc_c IS NOT NULL THEN x.colour_usc_c
							WHEN x.trim_usc_c IS NOT NULL THEN x.trim_usc_c
						END AS usc_c
				FROM		[_STAG_].dbo.MF_ENT AS x
				CROSS APPLY	wept.dbo.fnsplit('1,X,X,X,X', ',') AS f(g)
				WHERE		x.mkt_c = 'GK'
						AND x.mkt_c + x.modln_usc_c + x.modyr_usc_c = f.g

				UNION

				SELECT		x.option_usc_c AS usc_c
				FROM		[_STAG_].dbo.ent_OPT AS x
				CROSS APPLY	wept.dbo.fnsplit('1,X,X,X,X', ',') AS f(g)
				WHERE		x.mkt_c = 'GK'
						AND x.mkt_c + x.modln_usc_c + x.modyr_usc_c = f.g
		) AS b12 ON b12.usc_c = b10.usc_c
WHERE		b10.MKT_C = 'GK'


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

rikleo2001
Posting Yak Master

185 Posts
Posted - 2010-09-23 : 06:53:16
Hi Peso
Thanks for simplifying this, but query itself isn't a problem, query running currently is fast enough returning results in 3 sec, and I am sure with your simplied version it would be < 1 sec, but issue is around when inserting it into the same table using this query.. any ideas why this is happening?

Charlie

I am running it without any transaction isolation as simple query, so no islation level applies, is that may be a problem? also trace didn't find any locks at all.

Thanks Again

SKR
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-09-23 : 07:04:09
Did you try my insert-from-wrapper-SELECT suggestion?
Go to Top of Page

rikleo2001
Posting Yak Master

185 Posts
Posted - 2010-09-23 : 07:38:16
Yes, I tried it, didn't make any difference though.

SKR
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2010-09-23 : 07:40:43
Note

Your use of UNION introduces an implicit DISTINCT to remove duplicates from the overall SELECT resultset. If you are guaranteed to have no duplication across (or within) each union segment, then switch to UNION ALL to remove a wasted SORT/UNIQUE step.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site