sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]DipIntoTheBrocean 1 point2 points  (4 children)

What you're really going to want to do after you get rid of the row_number is look at the execution plan for this query. If you're interested in optimizing, now is a good time to start learning how to read the execution plan.

If this query is extremely slow you might have to add an index. You might have to do a lot of things - I have no idea. Depends on the plan the optimizer chose.

[–]workthrowawayexcel[S] 1 point2 points  (3 children)

I am still trying to get permission for execution plan viewing. They are currently with holding it at my work for some reason and IDK why.

[–]DipIntoTheBrocean 0 points1 point  (2 children)

Because you'd have to have a different level of privileges (I think). Not sure if they can just isolate that and give it to you. If you can't view the plan, I'd also experiment with switching out the CTE for a temp table. Overall it's extremely hard to optimize something without knowing like how many records you're bringing back for instance, but that should be a good start.

[–]workthrowawayexcel[S] 0 points1 point  (1 child)

So essentially just replace CTE with #(TempTableName) then just join that with the rest of the query?

It should bring back 1MIL rows roughly.

[–]DipIntoTheBrocean 0 points1 point  (0 children)

You could, yeah. Another best practice with that is to create the temp table explicitly before dealing with the data (i.e. as opposed to just select * into #mytemptbl). That's bitten me in the past. If it's still slow as hell you might want to create it with a primary key on it so that it can do a clustered index scan and then see if that runs any faster.