SQL Query taking long time

Question

SQL Query taking long time

Best_boy26

SSC-Addicted

Points: 457
More actions
March 19, 2011 at 8:03 am

#237013

Hi Experts,
I have a SQL Table.
And I have written againest of it lot of math Calculations and for each calculations I created views and finally I joined all these views as single view.
When I run this final view which has internally about 10 views and taking lot of time and not getting output. I can see my CPU time is taking 100%.
1) Now can anyone explain me the best way of getting the data without disturbing the database performance?
2) Looking for the SQL database performance on when we write the SQL queries.
3) If I change these all quires to run from SQL Reporting services, then SQL query will run without any issues?
4) Experts talks about indexing will indexing concept will do any favor for me since I have only one sql table?
Thanks in Advance...
trying to learn SQL Query World

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738673 More actions · Answer 1

You'll have to show some code and execution plans here, but in general, you want to create indexes on the fields that are being joined and potentially the aggregates.

However the view of view problem can easily be an issue. Typically we don't want to see views nested too much as you can mask performance issues.

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 2

Best_boy26 (3/19/2011)
Hi Experts,
I have a SQL Table.
And I have written againest of it lot of math Calculations and for each calculations I created views and finally I joined all these views as single view.
When I run this final view which has internally about 10 views and taking lot of time and not getting output. I can see my CPU time is taking 100%.
1) Now can anyone explain me the best way of getting the data without disturbing the database performance?
2) Looking for the SQL database performance on when we write the SQL queries.
3) If I change these all quires to run from SQL Reporting services, then SQL query will run without any issues?
4) Experts talks about indexing will indexing concept will do any favor for me since I have only one sql table?

If you have views of views that contain aggregates and they are joined on those aggregates, you have likeky created a lot of many-to-many joins and maybe even the occasional many-to-many-to-many join. These are also called "accidental Cross-Joins" and, depending on the data involved, they may never resolve because of the billions of internal rows that will be generated in an attempt to resolve all the views in the joins.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

Just to state it as clearly as possible, nesting views is generally a bad idea.

Same goes for nesting functions.

The query optimizer is smart and effective, but it will throw up it's hands as things get too complicated and this will result in you getting really bad execution plans.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Simple solution: do ALL of your math in a SINGLE PASS over the table. Do not have umpteen hits on the table (views or no views).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 5

TheSQLGuru (3/21/2011)
Simple solution: do ALL of your math in a SINGLE PASS over the table. Do not have umpteen hits on the table (views or no views).

Gosh, Kevin. I'll have to say "It Depends" here. I've seen way too many people join way too many tables trying to do just what you said only to be solved by some good ol' fashioned "Divide'n'Conquer" methods that I know I've seen you do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Jeff Moden (3/22/2011)
TheSQLGuru (3/21/2011)
Simple solution: do ALL of your math in a SINGLE PASS over the table. Do not have umpteen hits on the table (views or no views).
Gosh, Kevin. I'll have to say "It Depends" here. I've seen way too many people join way too many tables trying to do just what you said only to be solved by some good ol' fashioned "Divide'n'Conquer" methods that I know I've seen you do.

Note that I specifically said "... the table". That means just ONE table. Going all the way back to characteristic functions doing complex mathematical manipulations on data in a single stream pass over data is the right thing to do.

My take on the OP was that each view was on the same table thus my response.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service