How to make query faster - takes 49 mins to run

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

How to make query faster - takes 49 mins to run

Author

Topic

SQLkitty
Starting Member

4 Posts

Posted - 2015-04-20 : 19:24:53

With A as (
SELECT Name, Number, ROW_NUMBER() over (order by Name) as rownumber
FROM Table M
GROUP BY Name, Number
)

Select *
From A A1
left join A A2 on A1.rownumber=A2.rownumber -1
left join A A3 on A1.rownumber=A3.rownumber +1
where A1.Number =1 and a2.Number >1 and a3.Number > 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-20 : 19:38:19

Depending on the size of the CTE, you may want to dump it into a temp table that has an index on the rownumber column. What indexes do you have on the A table?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-21 : 01:39:53

have you analysed the execution plan and doublechecked all the basics such as statistics are maintained properly. Follow the steps in this post to investigate a slow running query :http://www.sqlserver-dba.com/2012/11/sql-server-how-to-troubleshoot-a-slow-running-query.html

Break down the query into smaller parts. How long is the query within the CTE taking?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Kristen
Test

22859 Posts

Posted - 2015-04-21 : 05:42:34

I would have done:


CREATE TABLE #TempTable
(
    [T_ID] int IDENTITY(1, 1) NOT NULL,
    [Name] ...,
    [Number] ...,
    PRIMARY KEY
    (
        T_ID
    )
)

INSERT INTO #TempTable
SELECT DISTINCT Name, Number
ORDER BY Name -- NOTE: This sort order is NOT repeatable, it would be better to include [Number]

Select *
From #TempTable AS A1
left join #TempTable AS A2 ...

Kristen
Test

22859 Posts

Posted - 2015-04-21 : 05:44:20

Also note that you have LEFT JOIN but then WHERE clause contains:

and a2.Number >1 and a3.Number > 1

which will force the JOINs to be INNER joins, so the resultset will not include any rows where there is no preceding/following row

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-21 : 09:55:49

If you are on SQL 2012 or later, you can use LAG and LEAD window functions to get the same results without having to use the CTE and the multiple joins. That would be considerably more efficient.

SQLkitty
Starting Member

4 Posts

Posted - 2015-04-21 : 14:23:11

Kristen - how can I fix the where clause so that the joins are not forced to be inner joins, but remain left joins?

Kristen
Test

22859 Posts

Posted - 2015-04-22 : 06:52:01

Put the condition in the JOIN clause and not in the WHERE clause (so you will only be left with "where A1.Number =1" in your WHERE clause)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-22 : 07:32:24

What is your objective? To find first gap?
There are other methods for this.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SQLkitty
Starting Member

4 Posts

Posted - 2015-04-22 : 12:32:14

Thank you Kristen, but I tried the following and it just resulted in a bunch of NULL fields. Am I missing something?

With A as (
SELECT Name, Number, ROW_NUMBER() over (order by Name) as rownumber
FROM Table M
GROUP BY Name, Number
)

Select *
From A A1
left join A A2 on A1.rownumber=A2.rownumber -1 and a2.Number >1
left join A A3 on A1.rownumber=A3.rownumber +1 and a3.Number > 1
where A1.Number =1

My objective is to order the table by name and then find names with numbers that equal 1 where the preceding and following numbers are greater than 1.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-22 : 14:33:00

Which version of SQL Server are you using?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Kristen
Test

22859 Posts

Posted - 2015-04-23 : 03:04:57

quote:
Originally posted by SQLkitty

it just resulted in a bunch of NULL fields. Am I missing something?

Those are presumably rows that have no Preceding / Following row (matching the criteria). A LEFT JOIN will include all the rows from Alias A1 but not A2 or A3 if they don't match the criteria.

Given that you say "My objective is to order the table by name and then find names with numbers that equal 1 where the preceding and following numbers are greater than 1." perhaps you ONLY want rows where there IS a matching Preceding AND a Following row? If that is the case then change the LEFT JOINs to an INNER JOIN (which will be equivalent to your original syntax, although, personally, I would never use a LEFT JOIN with some condition in the WHERE clause that then forced an INNER JOIN as I think it would be confusing to someone coming along later who thought "Was this supposed to be a LEFT JOIN and the WHERE clause is wrong, or is the WHERE correct and it should have been an INNER JOIN - just like I'm doing now!!)

SQLkitty
Starting Member

4 Posts

Posted - 2015-04-23 : 18:04:49

Thanks Kristen, that makes sense. I changed the joins to INNER JOINS.

Also, thank you tkizer for the suggestion. I created a temp table that has an index on rownumber and it now only takes 7 seconds to run. Yay!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources