|
Home > Archive > MS SQL Server > March 2006 > Huge select costs performance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Huge select costs performance
|
|
| fantasimus via SQLMonster.com 2006-03-05, 8:24 pm |
| I have a select on 8 views (OLAP, Facts, Dimensions) with a select
statement wich takes about 30 minutes ?
the select statement uses the tempdb file.. which hast about 7mb reads and
7mb writes /sec
on a SCSI 320 Hard Disk with RPM.
Does anybody know how to solve the problem . so that the query will be faster
?
--
Message posted via http://www.webservertalk.com
| |
| David Gugick 2006-03-05, 8:24 pm |
| fantasimus via webservertalk.com wrote:
> I have a select on 8 views (OLAP, Facts, Dimensions) with a select
> statement wich takes about 30 minutes ?
>
> the select statement uses the tempdb file.. which hast about 7mb
> reads and 7mb writes /sec
> on a SCSI 320 Hard Disk with RPM.
>
> Does anybody know how to solve the problem . so that the query will
> be faster ?
8 views. Presumably most of those views either have conditions and/or
use multiple tables. You did not post any information about these views,
the underlying tables, the indexes you have in place, or the final
query, so it's difficult to guess what's going on. So I'll guess anyway.
There are too many tables in your query. First, make sure you have the
right indexes in place. If you don't need the views, then access the
underlying tables directly to avoid accessing and unnecessary tables.
And do this in a stored procedure in parts if needed. Figure out how to
divide the query into steps that minimize data access. Use temp tables
with indexes to speed the queries if that helps. Watch your execution
plans and avoid any table scans or clustered index scans.
--
David Gugick - SQL Server MVP
Quest Software
|
|
|
|
|