Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Mistery never ending query Expand / Collapse
Author
Message
Posted Monday, June 3, 2013 8:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:38 PM
Points: 139, Visits: 541
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?

Post #1459518
Posted Tuesday, June 4, 2013 1:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 5,107, Visits: 11,911
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1459582
Posted Tuesday, June 4, 2013 1:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 6,750, Visits: 13,898
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
Post #1459585
Posted Tuesday, June 4, 2013 5:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1459682
Posted Tuesday, June 4, 2013 5:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 5,107, Visits: 11,911
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1459690
Posted Tuesday, June 4, 2013 6:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:38 PM
Points: 139, Visits: 541
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 #1459692
Posted Tuesday, June 4, 2013 6:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 6,750, Visits: 13,898
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
Post #1459697
Posted Tuesday, June 4, 2013 6:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1459698
Posted Tuesday, June 4, 2013 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:38 PM
Points: 139, Visits: 541
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
Post #1459728
Posted Tuesday, June 4, 2013 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 6,750, Visits: 13,898
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
Post #1459730
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse