July 29, 2015 at 6:51 am
Hi,
I came upon an odd issue today:
I have a query that runs fine and at an acceptable speed (a few seconds). It looks like this:
select count(row_id) From table1 v inner join
(select name,max(somedate) as correct_date From table1 group by name ) z
on v.name = z.name and correct_date != somedate
This query is a part of a stored procedure where it is used as a condition of an IF statement. That part of the SP is extremely slow.
Below is a query that ran for an hour until i stopped it:
if (
(
select count(row_id) From table1 v inner join
(select name,max(somedate) as correct_date From table1 group by name ) z
on v.name = z.name and correct_date != somedate
)
) > 0
begin
select 1
end
Can anyone suggest an explanation for this behaviour?
I used a local variable to calculate the result of the query before the IF statement and that solved the problem, though i feel like this might me sidestepping the real issue.
July 29, 2015 at 7:10 am
tomullus 69522 (7/29/2015)
Hi,I came upon an odd issue today:
I have a query that runs fine and at an acceptable speed (a few seconds). It looks like this:
select count(row_id) From table1 v inner join
(select name,max(somedate) as correct_date From table1 group by name ) z
on v.name = z.name and correct_date != somedate
This query is a part of a stored procedure where it is used as a condition of an IF statement. That part of the SP is extremely slow.
Below is a query that ran for an hour until i stopped it:
if (
(
select count(dex_row_id) From akint10001 v (nolock) ,
(select idfaktury,max(dataexportu)as poprawna_dataexportu From akint10001 (nolock) group by idfaktury ) z
where v.idfaktury = z.idfaktury and poprawna_dataexportu != dataexportu
)
) > 0
begin
select 1
end
Can anyone suggest an explanation for this behaviour?
I used a local variable to calculate the result of the query before the IF statement and that solved the problem, though i feel like this might me sidestepping the real issue.
The IF statement doesn't match the question. Your IF statement uses a cartesian join (full join) on idfaktury and the WHERE clause then limits it. If the Query Analyzer doesn't catch it properly and the table is large, you could have lots of problems. If you change the IF to be the select from your original question at the top, it will likely run faster.
July 29, 2015 at 7:20 am
Thanks for noticing. That was actually an error on my part. I copied into the post an older version of the query from when I was troubleshooting. Edited and corrected.
July 29, 2015 at 7:40 am
tomullus 69522 (7/29/2015)
Hi,I came upon an odd issue today:
I have a query that runs fine and at an acceptable speed (a few seconds). It looks like this:
select count(row_id) From table1 v inner join
(select name,max(somedate) as correct_date From table1 group by name ) z
on v.name = z.name and correct_date != somedate
This query is a part of a stored procedure where it is used as a condition of an IF statement. That part of the SP is extremely slow.
Below is a query that ran for an hour until i stopped it:
if (
(
select count(row_id) From table1 v inner join
(select name,max(somedate) as correct_date From table1 group by name ) z
on v.name = z.name and correct_date != somedate
)
) > 0
begin
select 1
end
Can anyone suggest an explanation for this behaviour?
I used a local variable to calculate the result of the query before the IF statement and that solved the problem, though i feel like this might me sidestepping the real issue.
It seems you have changed the posted query based on the first quoted response above. Your query has a number of challenges from a performance perspective. First is the inequality in the where predicate. This forces a scan no matter what indexing you have in place. Another issue is that the whole query seems to be flawed conceptually. It is totally out of context here but it looks like you should be using an EXISTS.
Also, it seems that one attempt you tried using NOLOCK. That hint is NOT a performance enhancing query hint. It carries a LOT of baggage that people seem to ignore. Things like missing and/or duplicate rows are one of them.
If you really want some help with this we need a lot more information posted. Tables structures along with index definitions. An actual execution plan. Some sample data. Take a look at this article for more details about how to post performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2015 at 8:09 am
Try this query, should give you the same result
SELECT COUNT(a.dex_row_id)
FROM (SELECT dex_row_id, ROW_NUMBER() OVER (PARTITION BY idfaktury ORDER BY dataexportu DESC) AS RowNo
FROM akint10001) AS a
WHERE RowNo > 1
but as already mentioned we need much more to determine how to optimise the query
July 29, 2015 at 8:41 am
I think i was misunderstood. I made a mistake in the original post by copying the wrong query into it. The current post (after my edit) is the actual issue. My original intention was for the query inside the IF statement be identical to the first one.
I'm well aware of the concerns when using NOLOCK and a WHERE instead of a JOIN, but it isn't the core of the issue. I'd like to know how is it that a query that runs reasonably fast takes forever when put inside a simple IF statement.
To be perfectly clear:
This query takes a couple of seconds:
select count(row_id) From table1 v inner join
(select name,max(somedate) as correct_date From table1 group by name ) z
on v.name = z.name and correct_date != somedate
This query takes at least an hour:
if (
(
select count(row_id) From table1 v inner join
(select name,max(somedate) as correct_date From table1 group by name ) z
on v.name = z.name and correct_date != somedate
)
) > 0
begin
select 1
end
How is this possible when the only thing changed is the query being put into a simple IF statement.
Additional info:
-The table is not indexed.
-The table:
CREATE TABLE [dbo].[table1](
[name] [char](30) NOT NULL,
[otherstuff1] [smallint] NOT NULL,
[otherstuff2] [datetime] NOT NULL,
[otherstuff3] [datetime] NOT NULL,
[otherstuff4] [datetime] NULL,
[otherstuff5] [char](20) NOT NULL,
[otherstuff6] [char](13) NOT NULL,
[otherstuff7] [datetime] NOT NULL,
[otherstuff8] [char](3) NOT NULL,
[otherstuff9] [numeric](12, 2) NOT NULL,
[otherstuff10] [numeric](12, 2) NOT NULL,
[otherstuff11] [numeric](12, 2) NOT NULL,
[otherstuff12] [char](30) NOT NULL,
[otherstuff13] [char](100) NOT NULL,
[otherstuff14] [char](100) NOT NULL,
[otherstuff15] [char](100) NOT NULL,
[otherstuff16] [char](30) NOT NULL,
[otherstuff17] [varchar](32) NULL,
[otherstuff18] [char](255) NULL,
[somedate] [datetime] NOT NULL,
[otherstuff19] [varchar](128) NULL,
[otherstuff20] [varchar](50) NULL,
[otherstuff21] [varchar](50) NULL,
[otherstuff22] [varchar](50) NULL,
[otherstuff23] [varchar](50) NULL,
[row_id] [int] IDENTITY(1,1) NOT NULL,
[otherstuff24] [varchar](128) NULL,
[otherstuff25] [varchar](128) NULL,
[otherstuff26] [varchar](128) NULL,
[otherstuff27] [varchar](128) NULL,
[otherstuff28] [varchar](50) NULL
) ON [PRIMARY]
-The execution plan of the IF statement:
|--Compute Scalar(DEFINE: ([Expr1010]=CASE WHEN [Expr1011] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE: ([Expr1011] = [PROBE VALUE]))
|--Constant Scan
|--Nested Loops(Inner Join, WHERE: ([userdb].[dbo].[table1].[name] as [v].[name]=[userdb].[dbo].[table1].[name] AND [Expr1007]<>[userdb].[dbo].[table1].[somedate] as [v].[somedate]))
|--Table Scan(OBJECT: ([userdb].[dbo].[table1] AS [v]))
|--Table Spool
|--Hash Match(Aggregate, HASH: ([userdb].[dbo].[table1].[name]), RESIDUAL: ([userdb].[dbo].[table1].[name] = [userdb].[dbo].[table1].[name]) DEFINE: ([Expr1007]=MAX([userdb].[dbo].[table1].[somedate])))
|--Table Scan(OBJECT: ([userdb].[dbo].[table1]))
July 29, 2015 at 8:56 am
tomullus 69522 (7/29/2015)
I think i was misunderstood. I made a mistake in the original post by copying the wrong query into it. The current post (after my edit) is the actual issue. My original intention was for the query inside the IF statement be identical to the first one.I'm well aware of the concerns when using NOLOCK and a WHERE instead of a JOIN, but it isn't the core of the issue. I'd like to know how is it that a query that runs reasonably fast takes forever when put inside a simple IF statement.
Well your table has no indexing. Depending on the volume of data I would be surprised if anything ran acceptably fast.
I would suggest completely reworking that query. The <> is nonSARGable but you can use a < which is SARGable. Also, using EXISTS instead of COUNT makes this a lot clearer what you are trying to do. See if something like doesn't help.
if EXISTS
(
select *
From table1 v
inner join
(
select name
, max(somedate) as correct_date
From table1
group by name
) z on v.name = z.name and correct_date < somedate
)
select 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2015 at 10:29 pm
What's the execution plan without the IF statement look like?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2015 at 2:36 am
Jeff Moden (7/29/2015)
What's the execution plan without the IF statement look like?
Here:
|--Compute Scalar(DEFINE: ([Expr1008]=CONVERT_IMPLICIT(int,[Expr1013],0)))
|--Stream Aggregate(DEFINE: ([Expr1013]=Count(*)))
|--Nested Loops(Inner Join)
|--Table Spool
| |--Segment
| |--Sort(ORDER BY: ([v].[name] ASC))
| |--Table Scan(OBJECT: ([userdb].[dbo].[table1] AS [v]))
|--Nested Loops(Inner Join, WHERE: ([Expr1007]<>[userdb].[dbo].[table1].[somedate] as [v].[somedate]))
|--Stream Aggregate(DEFINE: ([Expr1007]=MAX([userdb].[dbo].[table1].[somedate] as [v].[somedate])))
| |--Table Spool
|--Table Spool
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply