SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mistery never ending query


Mistery never ending query

Author
Message
ricardo_chicas
ricardo_chicas
SSC-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 Visits: 694
Hello all
I have a big machine with 192gb, 16 cpus, and sql server 2012 RTM in it

I am trying to run this query

Select top 1
t.field1,
isnull( t.field2,'nothing'),
a.field3,
count(1) as total--,
FROM temptable2 t WITH(NOLOCK)
inner join temptable a WITH(NOLOCK) on t.field1 = a.field1
GROUP BY t.field1,a.field3,isnull( t.field2,'nothing')

NOthing fancy about it, temptable has 300k rows, temptable2 has 110k rows

the thing is that it never ends, none is blocking the process, the join is done using the clustered index

the fields are like this
field1 varchar(23, not null)
field2 datetime
field3 varchar(255)

I even recrete the tables in a different database with the same result, if I remove field 2 from the equation it will work just fine

Any ideas?
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18700 Visits: 20451
If field2 is a datetime, trying to return 'nothing' if it is null will be giving the database engine a headache in terms of mismatching data types.

Instead, maybe try returning '1900-01-01'.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16430 Visits: 19554
ricardo_chicas (6/3/2013)
Hello all
I have a big machine with 192gb, 16 cpus, and sql server 2012 RTM in it

I am trying to run this query

Select top 1
t.field1,
isnull( t.field2,'nothing'),
a.field3,
count(1) as total--,
FROM temptable2 t WITH(NOLOCK)
inner join temptable a WITH(NOLOCK) on t.field1 = a.field1
GROUP BY t.field1,a.field3,isnull( t.field2,'nothing')

NOthing fancy about it, temptable has 300k rows, temptable2 has 110k rows

the thing is that it never ends, none is blocking the process, the join is done using the clustered index

the fields are like this
field1 varchar(23, not null)
field2 datetime
field3 varchar(255)

I even recrete the tables in a different database with the same result, if I remove field 2 from the equation it will work just fine

Any ideas?



It's a completely pointless query. TOP 1 by what? Car colour? Why do all the aggregation work then return a single random aggregated row? Nevertheless, folks will be curious. Can you post the estimated plan as a .sqlplan attachment?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28439 Visits: 39963
Besides NOLOCK being a bad idea in general, as it can lead to bad thinks like missing data, duplicate data and more, the NOLOCKS on a temp table are pointless.

if you created the #temptable yourself, and noone else can use it(since it's session specific to this set of queries) , why would you need nolock? a bad habit you haven't broken yet?

some of my peers can point you to the link that has a great explanation on the hazards of nolock,

i think the actual query, instead of pseudo code might get you some better answers; the actual execution plan would show us what is going wrong int he query itself, if you can post that as an attached .sqlplan file.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18700 Visits: 20451
Here is a small piece of code to back up my assertion that this is a datatype problem:

if object_id('tempdb..#ex', 'U') is not null 
drop table #ex

create table #ex
(
someDate datetime null
)

insert #ex
(someDate)
select '20130101'

select isnull(someDate, 'nothing')
from #ex
group by isnull(someDate, 'nothing')



Msg 241, Level 16, State 1, Line 13
Conversion failed when converting date and/or time from character string.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ricardo_chicas
ricardo_chicas
SSC-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 Visits: 694
All
Thanks but you are missing the point. The original query was something much bigger
I used a top 1 just to show you that there is something wrong with one of the tables

Even a select field2 from temptable
Never ends, it is a really small table,
The engine just can't resolve it, surely a bug or something
I am just asking what can it be?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16430 Visits: 19554
ricardo_chicas (6/4/2013)
All
Thanks but you are missing the point. The original query was something much bigger
I used a top 1 just to show you that there is something wrong with one of the tables

Even a select field2 from temptable
Never ends, it is a really small table,
The engine just can't resolve it, surely a bug or something
I am just asking what can it be?


Post the estimated plan for the original query, it's the first (and best) point of call for this type of problem. Without it, folks can only guess.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28439 Visits: 39963
if you have a specific performance problem, we really would want to see at least the estimated execution plan, and the actual query.

when you abstract it out, you end up cutting out critical details;
All of us are volunteers, but with a lot of experience and willingness to help.

help us help you!
provide better details, datatypes, etc.

post a .sqlplan for the query, that would show us a lot, for example we could see if out of date statistics are really affecting the query.



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ricardo_chicas
ricardo_chicas
SSC-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 Visits: 694
All
I can't even generate the exec plan. It never ends, and none is using that table since it is a copy of the original
And none is blocking that spod either
This is only happenning to me with that specific query
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16430 Visits: 19554
ricardo_chicas (6/4/2013)
All
I can't even generate the exec plan. It never ends, and none is using that table since it is a copy of the original
And none is blocking that spod either
This is only happenning to me with that specific query


Not even an estimated plan?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search