SQL Performance

[gbaughma]

I'm looking at some of our "legacy" SQL queries. They're painful.

One of the things that is being done is a RBAR copy of a subset of fields into a temporary table. Part of the reason it's doing the copy to the temp table is because it renames columns... Needless to say, that is very slow (and it's happening a LOT in this routine.. EOM.)

So, I was thinking to myself, and I said "Self, would a VIEW of that subset be faster? What are the costs of maintaining a view that would only be used once a month?"

Then I thought of you fine folks... and figured you probably knew the answer for that.

Thanks in advance!


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[fredericofonseca]

views tend to behave badly in many cases - so avoid them.
with regards to your particular code - renaming columns should not require temp tables - this rename can be done on the fly within the main sql - so if you need help on this please post the full code (and a actual explain plan as well) so we can see what is causing the slowness and advise on how/what to change

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Chris Miller]

What are the costs of maintaining a view that would only be used once a month?

Edited:
Depends on whether you define a view just as the query it is or create a materialized view that's thereyby stored like a table or an indexed view that stores these new indexes, a bnormal query view just stores the query and indeed is the simplest way to repeatedly do a query that aliases some column names without needing more storage in SQL Server than is necessary for the query itself, so it's not a burden to SQL Server at all. The only burden you have spacewise is with materialized and indexed views, also in terms of time necessary to react to any changes in underlying tables.
:Edited

See

https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views

and

https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-indexed-views/

Chriss

 

[Andrzejek]

I would take one of the 'subset' and compare its performance to the 'view' approach.
And if renaming columns is the only 'issue', why not just use aliases for columns in 'regular' Select based on your tables?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[gmmastros]

Greg,

I think you're looking at the details instead of the bigger picture. I can probably count on one hand the number of times I've needed to rename a column within a table. I encourage you to step back and think about why you want to rename a column. I suspect this is to facilitate reporting because there is a column in a report that holds summary data. If this is the case, the problem you should *probably* solve is the overall method.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chris Miller]

One thing that irritates me is that you talk o your legacy queries, but then talk of copying columns to temp tables. That's not ever part of an SQL Select query, that reqires a script, doesn't it?

If these scripts only would copy columns into a temp table just to rename columns, that's like installing a gas driven generator to power your electric oven and still be able to say you're "cooking with gas".

Because, as Andrzejek said you can SELECT column1 as othername, column2 as yetanothername, etc. The renaming of columns is poossible on the fly from the original source and so you don't need the step to copy into at all. There also is no need for a view to have these aliased column names, you just have replace a * for all fields to the full list of columns and, whenever necessary, with the as clause.

A materiallized view will cause storage of the view result as if it is its own table, an indexed view is storing the index, but a normal view is nothing but the view query, it is no burden to the database, a temp table you create as a global temp table with ## prefix is stored just like a table is. If you do this so a report can refer to the temp table, you better learn that a report cannot only be fed by a single table but by any query as complex as you like. You don't make a report much faster if data is already existing as a temporary table before the report is executed, It makes alsmost no difference if the report reads from th temp database or from the source table, you don't gain performance that way, you just waste the double space.

I don't know what's not clear about that, but you also said:

Part of the reason it's doing the copy to the temp table is because it renames columns

What's the opther part? I think you're executing a script, or a table valued function/procedure. Can you post what you do? Then we could recommend what to do instead. If you create the temp table for a report right at the time you need the report, then you're wasting double time on the data aaccess alone as you first query one or more original tables, then store that result into a temp table and then read that into a report. The only way a temp table could accelerate a report generation is, if the data would already be there in the formt he report needs it, in advance. For example, created within the precvious night. If not, you're just wasting time, i(o and cpu for all that shoveling of data.

Chriss

 

[Chris Miller]

Can you post what you do?

I mean, can you post one example of such an RBAR copy and how it's finally used for things like reports?

Chriss

 

[Chris Miller]

gbaughma,

did you solve that and have no need for further advice?

Chriss

 

[gbaughma]

I'm just now catching up.

These queries are part of our EOM reports and calculations. They're gruesome. One of the things I've learned about SQL is to think in terms of data sets, not rows... evidently the original programmer didn't have that mindset.

The whole routine is DOZENS of SQL scripts... it would take me probably 3 months to map out everything that they're doing. I know I could re-write it all to be a LOT more efficient... I was just trying to think of ways that I could improve performance (closing the month takes about 3 days of a lot of manual script triggering and waiting).

I'd love to post some of the code, but I think that my employer would frown on that. They're pretty protective.

Meanwhile, there's talk about "2.0" of our system (it's all in house, currently front-ended with Access... and 2.0 is supposed to be all browser-based... but I don't think any code has been started for it).

My boss will probably tell me "We'll address all of this in 2.0, so don't put any effort into rewriting anything".


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[SamBones]

My boss will probably tell me "We'll address all of this in 2.0, so don't put any effort into rewriting anything".

In other words, "Don't fix the broken foundation, go ahead and build the house".

It will only get worse.

 

[Andrzejek]

The house foundation is not only broken – it is falling apart, your walls are propped up with 2x4’s, windows and doors are held with a duck type, bubble gum and band aide. And your boss is saying: “Let’s build a second floor!”

Talking from personal experience, unfortunately. :-(


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[pjw001]

I think you meant "duct tape", not "duck type".