Hi
I recently designed & developed a data warehouse for a client of ours.
The project has been quite a success, but they have since loaded the
database with a substantial amount of (historic) data, and have given
access to the query & reporting system (Business Objects) to many more
users. As a result, the query time on the reports is beginning to
slow.
I need to advise the client on what should be done to improve
performance again.
I'm quite familiar with what can be done on the design side of the
database (i.e. indices, aggregate tables, etc.), but I am not very
sure about the cost/benefits from a hardware perspective. As a
consultancy, the cost of our services (to build aggregate tables, etc)
would be quite expensive. It might be a better option for the client
to improve the spec of the server instead, but I am reluctant to
recommend this option without knowing that there would be guaranteed
benefits. In other words, if they were to double the processing power,
would the query time effectively halve? What about memory - i.e. would
more memory help with multiple-users, but not affect the speed of the
reports? Is 4GB the max memory that Windows 2000 can have?
[I can't remember the exact spec, but the server's got something like
2 Xeon dual-processors, 4GB RAM & Win2k.]
Can anyone recommend a study or white-paper on the performance
improvements in relation to hardware upgrades, or something similar?
Otherwise, what other options do I have? Am I overlooking something
like two load-balanced servers?
Thanks for any help / recommendations!
Sean W.
Sean wrote:
> Hi
> I recently designed & developed a data warehouse for a client of ours.
> The project has been quite a success, but they have since loaded the
> database with a substantial amount of (historic) data, and have given
> access to the query & reporting system (Business Objects) to many more
> users. As a result, the query time on the reports is beginning to
> slow.
> I need to advise the client on what should be done to improve
> performance again.
> I'm quite familiar with what can be done on the design side of the
> database (i.e. indices, aggregate tables, etc.), but I am not very
> sure about the cost/benefits from a hardware perspective. As a
> consultancy, the cost of our services (to build aggregate tables, etc)
> would be quite expensive. It might be a better option for the client
> to improve the spec of the server instead, but I am reluctant to
> recommend this option without knowing that there would be guaranteed
> benefits. In other words, if they were to double the processing power,
> would the query time effectively halve? What about memory - i.e. would
> more memory help with multiple-users, but not affect the speed of the
> reports? Is 4GB the max memory that Windows 2000 can have?
> [I can't remember the exact spec, but the server's got something like
> 2 Xeon dual-processors, 4GB RAM & Win2k.]
> Can anyone recommend a study or white-paper on the performance
> improvements in relation to hardware upgrades, or something similar?
> Otherwise, what other options do I have? Am I overlooking something
> like two load-balanced servers?
> Thanks for any help / recommendations!
> Sean W.
Unfortunately, I think many companies are inclined to add more server
without first trying to tune the database. You've gone ahead and
designed a database for the customer and added the necessary RI and
indexes. But now that you see the SQL executed, someone needs to tune
the queries, revise the indexes if necessary, and verify the reporting
system is executing SQL in an efficient way: For example, is it using
stored procedures? Do users have Ad Hoc access to the database to query
whatever they want? Does the reporting tool bring back large result sets
and filter on the client PC? Do the queries use a READ UNCOMMITTED or
NOLOCK query option to keep locks to a minimum?
My point is that without performance tuning the queries, it's really
impossible to know whether adding more hardware will give you anything
but a temporary reprieve of poor performance.
When I hear about problems like this, I like to remember an old client
that had a couple hundred people hitting a SQL Server database which ran
on a dual-CPU 486 with 84 MB of RAM. I know times have changed, but when
I hear about performance problems on quad-Xeon systems with 3GB RAM (as
an example), I get concerned that a company is considering throwing
another $150K at a new server.
Ad Hoc reporting systems are notoriously bad on performance, especially
if users don't run canned reports and can throw what SQL the end-user
reporting tool can generate at the server. The amount of data queried
can easily move old data out of cache and force the next query to read
that data from disk, which is really slow. Adding more memory is an
option if plan reuse is the issue. And since memory is cheap, it can't
hurt to add all you can. Adding more hardware will help, but may not
really be a long term solution.
However, nothing beats being able to aptly use an index to generate a
result set. Table/Clustered Index Scans are a killer on big tables.
You may want to consider placing query limits on ad hoc queries using
"SET QUERY_GOVERNOR_COST_LIMIT ". You can also use Profiler to track
those queries cosuming excessive CPU and see if the database or the
queries can be tuned.
David Gugick
Imceda Software
www.imceda.com
|||With Business Objects it is worth asking the users to send you copies of
their reports. You can then see if indices need creating on various
tables etc.
Adrian
David G. wrote:
> Sean wrote:
>
> Unfortunately, I think many companies are inclined to add more server
> without first trying to tune the database. You've gone ahead and
> designed a database for the customer and added the necessary RI and
> indexes. But now that you see the SQL executed, someone needs to tune
> the queries, revise the indexes if necessary, and verify the reporting
> system is executing SQL in an efficient way: For example, is it using
> stored procedures? Do users have Ad Hoc access to the database to query
> whatever they want? Does the reporting tool bring back large result sets
> and filter on the client PC? Do the queries use a READ UNCOMMITTED or
> NOLOCK query option to keep locks to a minimum?
> My point is that without performance tuning the queries, it's really
> impossible to know whether adding more hardware will give you anything
> but a temporary reprieve of poor performance.
> When I hear about problems like this, I like to remember an old client
> that had a couple hundred people hitting a SQL Server database which ran
> on a dual-CPU 486 with 84 MB of RAM. I know times have changed, but when
> I hear about performance problems on quad-Xeon systems with 3GB RAM (as
> an example), I get concerned that a company is considering throwing
> another $150K at a new server.
> Ad Hoc reporting systems are notoriously bad on performance, especially
> if users don't run canned reports and can throw what SQL the end-user
> reporting tool can generate at the server. The amount of data queried
> can easily move old data out of cache and force the next query to read
> that data from disk, which is really slow. Adding more memory is an
> option if plan reuse is the issue. And since memory is cheap, it can't
> hurt to add all you can. Adding more hardware will help, but may not
> really be a long term solution.
> However, nothing beats being able to aptly use an index to generate a
> result set. Table/Clustered Index Scans are a killer on big tables.
> You may want to consider placing query limits on ad hoc queries using
> "SET QUERY_GOVERNOR_COST_LIMIT ". You can also use Profiler to track
> those queries cosuming excessive CPU and see if the database or the
> queries can be tuned.
>
|||Adrian Edwards wrote:
> With Business Objects it is worth asking the users to send you copies
> of their reports. You can then see if indices need creating on various
> tables etc.
> Adrian
>
Good point. If you can get your hands on any canned reports or at least
have a process for quickly rolling out new or updated reports, you may
eliminate users creating queries that don't perform well.
You might want to consider having new reports created against a test
server before moving them into production. Not giving users the rights
to create reports on the production data may be difficult to manage.
Managers generally want fast results. If you can't do that, at least
introduce a review process so there's never a time when too many reports
are created without review.
You can do this passively using Profiler or a server-side trace. You
could trap the SQL:BatchCompleted and RPC:Completed events for CPU over
a certain level (say 1,000ms) and review those SQL Statements
periodically.
David Gugick
Imceda Software
www.imceda.com
sql
Showing posts with label design. Show all posts
Showing posts with label design. Show all posts
Friday, March 30, 2012
Performance Improvements: Hardware vs. DB Design
Hi
I recently designed & developed a data warehouse for a client of ours.
The project has been quite a success, but they have since loaded the
database with a substantial amount of (historic) data, and have given
access to the query & reporting system (Business Objects) to many more
users. As a result, the query time on the reports is beginning to
slow.
I need to advise the client on what should be done to improve
performance again.
I'm quite familiar with what can be done on the design side of the
database (i.e. indices, aggregate tables, etc.), but I am not very
sure about the cost/benefits from a hardware perspective. As a
consultancy, the cost of our services (to build aggregate tables, etc)
would be quite expensive. It might be a better option for the client
to improve the spec of the server instead, but I am reluctant to
recommend this option without knowing that there would be guaranteed
benefits. In other words, if they were to double the processing power,
would the query time effectively halve? What about memory - i.e. would
more memory help with multiple-users, but not affect the speed of the
reports? Is 4GB the max memory that Windows 2000 can have?
[I can't remember the exact spec, but the server's got something like
2 Xeon dual-processors, 4GB RAM & Win2k.]
Can anyone recommend a study or white-paper on the performance
improvements in relation to hardware upgrades, or something similar?
Otherwise, what other options do I have? Am I overlooking something
like two load-balanced servers?
Thanks for any help / recommendations!
Sean W.Sean wrote:
> Hi
> I recently designed & developed a data warehouse for a client of ours.
> The project has been quite a success, but they have since loaded the
> database with a substantial amount of (historic) data, and have given
> access to the query & reporting system (Business Objects) to many more
> users. As a result, the query time on the reports is beginning to
> slow.
> I need to advise the client on what should be done to improve
> performance again.
> I'm quite familiar with what can be done on the design side of the
> database (i.e. indices, aggregate tables, etc.), but I am not very
> sure about the cost/benefits from a hardware perspective. As a
> consultancy, the cost of our services (to build aggregate tables, etc)
> would be quite expensive. It might be a better option for the client
> to improve the spec of the server instead, but I am reluctant to
> recommend this option without knowing that there would be guaranteed
> benefits. In other words, if they were to double the processing power,
> would the query time effectively halve? What about memory - i.e. would
> more memory help with multiple-users, but not affect the speed of the
> reports? Is 4GB the max memory that Windows 2000 can have?
> [I can't remember the exact spec, but the server's got something like
> 2 Xeon dual-processors, 4GB RAM & Win2k.]
> Can anyone recommend a study or white-paper on the performance
> improvements in relation to hardware upgrades, or something similar?
> Otherwise, what other options do I have? Am I overlooking something
> like two load-balanced servers?
> Thanks for any help / recommendations!
> Sean W.
Unfortunately, I think many companies are inclined to add more server
without first trying to tune the database. You've gone ahead and
designed a database for the customer and added the necessary RI and
indexes. But now that you see the SQL executed, someone needs to tune
the queries, revise the indexes if necessary, and verify the reporting
system is executing SQL in an efficient way: For example, is it using
stored procedures? Do users have Ad Hoc access to the database to query
whatever they want? Does the reporting tool bring back large result sets
and filter on the client PC? Do the queries use a READ UNCOMMITTED or
NOLOCK query option to keep locks to a minimum?
My point is that without performance tuning the queries, it's really
impossible to know whether adding more hardware will give you anything
but a temporary reprieve of poor performance.
When I hear about problems like this, I like to remember an old client
that had a couple hundred people hitting a SQL Server database which ran
on a dual-CPU 486 with 84 MB of RAM. I know times have changed, but when
I hear about performance problems on quad-Xeon systems with 3GB RAM (as
an example), I get concerned that a company is considering throwing
another $150K at a new server.
Ad Hoc reporting systems are notoriously bad on performance, especially
if users don't run canned reports and can throw what SQL the end-user
reporting tool can generate at the server. The amount of data queried
can easily move old data out of cache and force the next query to read
that data from disk, which is really slow. Adding more memory is an
option if plan reuse is the issue. And since memory is cheap, it can't
hurt to add all you can. Adding more hardware will help, but may not
really be a long term solution.
However, nothing beats being able to aptly use an index to generate a
result set. Table/Clustered Index Scans are a killer on big tables.
You may want to consider placing query limits on ad hoc queries using
"SET QUERY_GOVERNOR_COST_LIMIT ". You can also use Profiler to track
those queries cosuming excessive CPU and see if the database or the
queries can be tuned.
--
David Gugick
Imceda Software
www.imceda.com|||With Business Objects it is worth asking the users to send you copies of
their reports. You can then see if indices need creating on various
tables etc.
Adrian
David G. wrote:
> Sean wrote:
>>Hi
>>I recently designed & developed a data warehouse for a client of ours.
>>The project has been quite a success, but they have since loaded the
>>database with a substantial amount of (historic) data, and have given
>>access to the query & reporting system (Business Objects) to many more
>>users. As a result, the query time on the reports is beginning to
>>slow.
>>I need to advise the client on what should be done to improve
>>performance again.
>>I'm quite familiar with what can be done on the design side of the
>>database (i.e. indices, aggregate tables, etc.), but I am not very
>>sure about the cost/benefits from a hardware perspective. As a
>>consultancy, the cost of our services (to build aggregate tables, etc)
>>would be quite expensive. It might be a better option for the client
>>to improve the spec of the server instead, but I am reluctant to
>>recommend this option without knowing that there would be guaranteed
>>benefits. In other words, if they were to double the processing power,
>>would the query time effectively halve? What about memory - i.e. would
>>more memory help with multiple-users, but not affect the speed of the
>>reports? Is 4GB the max memory that Windows 2000 can have?
>>[I can't remember the exact spec, but the server's got something like
>>2 Xeon dual-processors, 4GB RAM & Win2k.]
>>Can anyone recommend a study or white-paper on the performance
>>improvements in relation to hardware upgrades, or something similar?
>>Otherwise, what other options do I have? Am I overlooking something
>>like two load-balanced servers?
>>Thanks for any help / recommendations!
>>Sean W.
>
> Unfortunately, I think many companies are inclined to add more server
> without first trying to tune the database. You've gone ahead and
> designed a database for the customer and added the necessary RI and
> indexes. But now that you see the SQL executed, someone needs to tune
> the queries, revise the indexes if necessary, and verify the reporting
> system is executing SQL in an efficient way: For example, is it using
> stored procedures? Do users have Ad Hoc access to the database to query
> whatever they want? Does the reporting tool bring back large result sets
> and filter on the client PC? Do the queries use a READ UNCOMMITTED or
> NOLOCK query option to keep locks to a minimum?
> My point is that without performance tuning the queries, it's really
> impossible to know whether adding more hardware will give you anything
> but a temporary reprieve of poor performance.
> When I hear about problems like this, I like to remember an old client
> that had a couple hundred people hitting a SQL Server database which ran
> on a dual-CPU 486 with 84 MB of RAM. I know times have changed, but when
> I hear about performance problems on quad-Xeon systems with 3GB RAM (as
> an example), I get concerned that a company is considering throwing
> another $150K at a new server.
> Ad Hoc reporting systems are notoriously bad on performance, especially
> if users don't run canned reports and can throw what SQL the end-user
> reporting tool can generate at the server. The amount of data queried
> can easily move old data out of cache and force the next query to read
> that data from disk, which is really slow. Adding more memory is an
> option if plan reuse is the issue. And since memory is cheap, it can't
> hurt to add all you can. Adding more hardware will help, but may not
> really be a long term solution.
> However, nothing beats being able to aptly use an index to generate a
> result set. Table/Clustered Index Scans are a killer on big tables.
> You may want to consider placing query limits on ad hoc queries using
> "SET QUERY_GOVERNOR_COST_LIMIT ". You can also use Profiler to track
> those queries cosuming excessive CPU and see if the database or the
> queries can be tuned.
>|||Adrian Edwards wrote:
> With Business Objects it is worth asking the users to send you copies
> of their reports. You can then see if indices need creating on various
> tables etc.
> Adrian
>
Good point. If you can get your hands on any canned reports or at least
have a process for quickly rolling out new or updated reports, you may
eliminate users creating queries that don't perform well.
You might want to consider having new reports created against a test
server before moving them into production. Not giving users the rights
to create reports on the production data may be difficult to manage.
Managers generally want fast results. If you can't do that, at least
introduce a review process so there's never a time when too many reports
are created without review.
You can do this passively using Profiler or a server-side trace. You
could trap the SQL:BatchCompleted and RPC:Completed events for CPU over
a certain level (say 1,000ms) and review those SQL Statements
periodically.
David Gugick
Imceda Software
www.imceda.com
I recently designed & developed a data warehouse for a client of ours.
The project has been quite a success, but they have since loaded the
database with a substantial amount of (historic) data, and have given
access to the query & reporting system (Business Objects) to many more
users. As a result, the query time on the reports is beginning to
slow.
I need to advise the client on what should be done to improve
performance again.
I'm quite familiar with what can be done on the design side of the
database (i.e. indices, aggregate tables, etc.), but I am not very
sure about the cost/benefits from a hardware perspective. As a
consultancy, the cost of our services (to build aggregate tables, etc)
would be quite expensive. It might be a better option for the client
to improve the spec of the server instead, but I am reluctant to
recommend this option without knowing that there would be guaranteed
benefits. In other words, if they were to double the processing power,
would the query time effectively halve? What about memory - i.e. would
more memory help with multiple-users, but not affect the speed of the
reports? Is 4GB the max memory that Windows 2000 can have?
[I can't remember the exact spec, but the server's got something like
2 Xeon dual-processors, 4GB RAM & Win2k.]
Can anyone recommend a study or white-paper on the performance
improvements in relation to hardware upgrades, or something similar?
Otherwise, what other options do I have? Am I overlooking something
like two load-balanced servers?
Thanks for any help / recommendations!
Sean W.Sean wrote:
> Hi
> I recently designed & developed a data warehouse for a client of ours.
> The project has been quite a success, but they have since loaded the
> database with a substantial amount of (historic) data, and have given
> access to the query & reporting system (Business Objects) to many more
> users. As a result, the query time on the reports is beginning to
> slow.
> I need to advise the client on what should be done to improve
> performance again.
> I'm quite familiar with what can be done on the design side of the
> database (i.e. indices, aggregate tables, etc.), but I am not very
> sure about the cost/benefits from a hardware perspective. As a
> consultancy, the cost of our services (to build aggregate tables, etc)
> would be quite expensive. It might be a better option for the client
> to improve the spec of the server instead, but I am reluctant to
> recommend this option without knowing that there would be guaranteed
> benefits. In other words, if they were to double the processing power,
> would the query time effectively halve? What about memory - i.e. would
> more memory help with multiple-users, but not affect the speed of the
> reports? Is 4GB the max memory that Windows 2000 can have?
> [I can't remember the exact spec, but the server's got something like
> 2 Xeon dual-processors, 4GB RAM & Win2k.]
> Can anyone recommend a study or white-paper on the performance
> improvements in relation to hardware upgrades, or something similar?
> Otherwise, what other options do I have? Am I overlooking something
> like two load-balanced servers?
> Thanks for any help / recommendations!
> Sean W.
Unfortunately, I think many companies are inclined to add more server
without first trying to tune the database. You've gone ahead and
designed a database for the customer and added the necessary RI and
indexes. But now that you see the SQL executed, someone needs to tune
the queries, revise the indexes if necessary, and verify the reporting
system is executing SQL in an efficient way: For example, is it using
stored procedures? Do users have Ad Hoc access to the database to query
whatever they want? Does the reporting tool bring back large result sets
and filter on the client PC? Do the queries use a READ UNCOMMITTED or
NOLOCK query option to keep locks to a minimum?
My point is that without performance tuning the queries, it's really
impossible to know whether adding more hardware will give you anything
but a temporary reprieve of poor performance.
When I hear about problems like this, I like to remember an old client
that had a couple hundred people hitting a SQL Server database which ran
on a dual-CPU 486 with 84 MB of RAM. I know times have changed, but when
I hear about performance problems on quad-Xeon systems with 3GB RAM (as
an example), I get concerned that a company is considering throwing
another $150K at a new server.
Ad Hoc reporting systems are notoriously bad on performance, especially
if users don't run canned reports and can throw what SQL the end-user
reporting tool can generate at the server. The amount of data queried
can easily move old data out of cache and force the next query to read
that data from disk, which is really slow. Adding more memory is an
option if plan reuse is the issue. And since memory is cheap, it can't
hurt to add all you can. Adding more hardware will help, but may not
really be a long term solution.
However, nothing beats being able to aptly use an index to generate a
result set. Table/Clustered Index Scans are a killer on big tables.
You may want to consider placing query limits on ad hoc queries using
"SET QUERY_GOVERNOR_COST_LIMIT ". You can also use Profiler to track
those queries cosuming excessive CPU and see if the database or the
queries can be tuned.
--
David Gugick
Imceda Software
www.imceda.com|||With Business Objects it is worth asking the users to send you copies of
their reports. You can then see if indices need creating on various
tables etc.
Adrian
David G. wrote:
> Sean wrote:
>>Hi
>>I recently designed & developed a data warehouse for a client of ours.
>>The project has been quite a success, but they have since loaded the
>>database with a substantial amount of (historic) data, and have given
>>access to the query & reporting system (Business Objects) to many more
>>users. As a result, the query time on the reports is beginning to
>>slow.
>>I need to advise the client on what should be done to improve
>>performance again.
>>I'm quite familiar with what can be done on the design side of the
>>database (i.e. indices, aggregate tables, etc.), but I am not very
>>sure about the cost/benefits from a hardware perspective. As a
>>consultancy, the cost of our services (to build aggregate tables, etc)
>>would be quite expensive. It might be a better option for the client
>>to improve the spec of the server instead, but I am reluctant to
>>recommend this option without knowing that there would be guaranteed
>>benefits. In other words, if they were to double the processing power,
>>would the query time effectively halve? What about memory - i.e. would
>>more memory help with multiple-users, but not affect the speed of the
>>reports? Is 4GB the max memory that Windows 2000 can have?
>>[I can't remember the exact spec, but the server's got something like
>>2 Xeon dual-processors, 4GB RAM & Win2k.]
>>Can anyone recommend a study or white-paper on the performance
>>improvements in relation to hardware upgrades, or something similar?
>>Otherwise, what other options do I have? Am I overlooking something
>>like two load-balanced servers?
>>Thanks for any help / recommendations!
>>Sean W.
>
> Unfortunately, I think many companies are inclined to add more server
> without first trying to tune the database. You've gone ahead and
> designed a database for the customer and added the necessary RI and
> indexes. But now that you see the SQL executed, someone needs to tune
> the queries, revise the indexes if necessary, and verify the reporting
> system is executing SQL in an efficient way: For example, is it using
> stored procedures? Do users have Ad Hoc access to the database to query
> whatever they want? Does the reporting tool bring back large result sets
> and filter on the client PC? Do the queries use a READ UNCOMMITTED or
> NOLOCK query option to keep locks to a minimum?
> My point is that without performance tuning the queries, it's really
> impossible to know whether adding more hardware will give you anything
> but a temporary reprieve of poor performance.
> When I hear about problems like this, I like to remember an old client
> that had a couple hundred people hitting a SQL Server database which ran
> on a dual-CPU 486 with 84 MB of RAM. I know times have changed, but when
> I hear about performance problems on quad-Xeon systems with 3GB RAM (as
> an example), I get concerned that a company is considering throwing
> another $150K at a new server.
> Ad Hoc reporting systems are notoriously bad on performance, especially
> if users don't run canned reports and can throw what SQL the end-user
> reporting tool can generate at the server. The amount of data queried
> can easily move old data out of cache and force the next query to read
> that data from disk, which is really slow. Adding more memory is an
> option if plan reuse is the issue. And since memory is cheap, it can't
> hurt to add all you can. Adding more hardware will help, but may not
> really be a long term solution.
> However, nothing beats being able to aptly use an index to generate a
> result set. Table/Clustered Index Scans are a killer on big tables.
> You may want to consider placing query limits on ad hoc queries using
> "SET QUERY_GOVERNOR_COST_LIMIT ". You can also use Profiler to track
> those queries cosuming excessive CPU and see if the database or the
> queries can be tuned.
>|||Adrian Edwards wrote:
> With Business Objects it is worth asking the users to send you copies
> of their reports. You can then see if indices need creating on various
> tables etc.
> Adrian
>
Good point. If you can get your hands on any canned reports or at least
have a process for quickly rolling out new or updated reports, you may
eliminate users creating queries that don't perform well.
You might want to consider having new reports created against a test
server before moving them into production. Not giving users the rights
to create reports on the production data may be difficult to manage.
Managers generally want fast results. If you can't do that, at least
introduce a review process so there's never a time when too many reports
are created without review.
You can do this passively using Profiler or a server-side trace. You
could trap the SQL:BatchCompleted and RPC:Completed events for CPU over
a certain level (say 1,000ms) and review those SQL Statements
periodically.
David Gugick
Imceda Software
www.imceda.com
Wednesday, March 21, 2012
Performance cost of joins
We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.
Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:
-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC
its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...
for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....
for reports u may use views to avoid repeated use of joins...
so optimal for me = normalized + views
Performance cost of joins
We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.
Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:
-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC
its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...
for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....
for reports u may use views to avoid repeated use of joins...
so optimal for me = normalized + views
Performance cost of joins
We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.
Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:
-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC
its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...
for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....
for reports u may use views to avoid repeated use of joins...
so optimal for me = normalized + views
Monday, March 12, 2012
Performance / Design Question
Hi all,
Ok - I'm building a database around the content of an XML file I've been
supplied with - the process is straight forward enough...
The supplier has provided in the XML file far more data/information than I
actually need at this point in time, however, there's nothing to say that
"some" of this extra information might not be needed in the future...
I have therefore been adding columns like a mad man to incorporate the data
from the beginning, so if requested later on the impact on the system, and
the turn-around will be quicker.
I then wondered "wonder if this will have any side effects?"
Lets say, to make things simple I have a table with 5 columns that I do
need, I have a file with 40 items that could potentially be columns in this
table, I decide to add these now. I run my import process and populate my
table. Lets say the database size would have been 10Mb with the 5 columns,
but now, because of the types of data, and the sizes, its not 350Mb - would
this have any impact on SQL Server's performance when queries are run
against it? Obviously the sizes above are just "figures plucked from the
air" and are not actual sizes of my database, I was just trying to show a
big markup.
I would "guess" that if I wasn't using the other columns in the queries then
it shouldn't make any difference...
Any information would be appreciated - thanks in advance,
Regards
RobRob Meade wrote:
> Hi all,
> Ok - I'm building a database around the content of an XML file I've been
> supplied with - the process is straight forward enough...
> The supplier has provided in the XML file far more data/information than I
> actually need at this point in time, however, there's nothing to say that
> "some" of this extra information might not be needed in the future...
> I have therefore been adding columns like a mad man to incorporate the dat
a
> from the beginning, so if requested later on the impact on the system, and
> the turn-around will be quicker.
> I then wondered "wonder if this will have any side effects?"
> Lets say, to make things simple I have a table with 5 columns that I do
> need, I have a file with 40 items that could potentially be columns in thi
s
> table, I decide to add these now. I run my import process and populate my
> table. Lets say the database size would have been 10Mb with the 5 columns
,
> but now, because of the types of data, and the sizes, its not 350Mb - woul
d
> this have any impact on SQL Server's performance when queries are run
> against it? Obviously the sizes above are just "figures plucked from the
> air" and are not actual sizes of my database, I was just trying to show a
> big markup.
> I would "guess" that if I wasn't using the other columns in the queries th
en
> it shouldn't make any difference...
> Any information would be appreciated - thanks in advance,
> Regards
> Rob
>
Proper indexing and normalization will affect your table performance
more than the number of columns will. I hope you're not just building a
table that mimics the structure of the XML file. For instance, consider
an XML file that contains a list of customers, a billing address for
each customer, and for some customers, an optional shipping address.
Resist the temptation to build a single table that contains two sets of
address fields - one for billing, one for shipping. Instead, build a
"Customers" table that contains identifying information for the
CUSTOMER, such as customer ID, name, account number, etc.. Build an
"Addresses" table that contains information for each ADDRESS, such as an
address ID, street address, city, state, country, etc.. Build a third
table, "CustomerAddresses", that contains maybe four columns; customer
address ID, customer ID, address ID, and address type. This table
creates a 1-to-many link between a customer and its address(es). Even
the address type could be split off into an AddressTypes table,
containing an AddressType ID and a text label describing the AddressType
(billing, shipping, etc).|||"Tracy McKibben" wrote...
> Proper indexing and normalization will affect your table performance more
> than the number of columns will.
okey dokey
> I hope you're not just building a table that mimics the structure of the
> XML file.
Nope...one element "sku" has a number of attributes, these are all in the
same table, however, each sku can also have a number of "objectives",
"topics", "members" and so on...these are currently in separate tables.
I have used the SKUID to link between these tables and the sku table.
It was really more of a question about whether or not the addition (possibly
excessive - as some of it might not be used) data would have an adverse
effect.
Thanks for your reply Tracy.
Regards
Rob|||Rob Meade (ten.bewdoowsgnikNO-SPAM@.edaem.bbor) writes:
> Ok - I'm building a database around the content of an XML file I've been
> supplied with - the process is straight forward enough...
> The supplier has provided in the XML file far more data/information than I
> actually need at this point in time, however, there's nothing to say that
> "some" of this extra information might not be needed in the future...
> I have therefore been adding columns like a mad man to incorporate the
> data from the beginning, so if requested later on the impact on the
> system, and the turn-around will be quicker.
> I then wondered "wonder if this will have any side effects?"
> Lets say, to make things simple I have a table with 5 columns that I do
> need, I have a file with 40 items that could potentially be columns in
> this table, I decide to add these now. I run my import process and
> populate my table. Lets say the database size would have been 10Mb with
> the 5 columns, but now, because of the types of data, and the sizes, its
> not 350Mb - would this have any impact on SQL Server's performance when
> queries are run against it? Obviously the sizes above are just "figures
> plucked from the air" and are not actual sizes of my database, I was
> just trying to show a big markup.
The answer is, as it often does when it comes to performance: it depends.
However, here the "it depends" has a fairly straight-forward qualification.
If you have good indexing, the cost of the extra column is moderate.
The database will grow faster, and you must pay more attention to running
backup of the database and the transaction. It will also be more important
to keep fragmentation in check. But the overall cost for performance
does not have to be more than, say, 10%.
On the other hand, if you are lax about indexing, the cost for the
extra columns can be significant (assuming that they are in the same
tables as the rest). Since the rows are wider, there are fewer rows
per page, and more pages have to be read to scan tables.
Personally, I would consider the strategy of only adding the columns
you need, but add a table to save the XML documents in, so if need
occurs later, you can recover more fields from the XML document. This
is particularly attractive, if you are on SQL 2005 where you can store
the data in column of the xml data type. The performance cost for this
extra table is nil, but of course the increased database size takes
some administrative toll as discussed above.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Rob Meade wrote:
> Nope...one element "sku" has a number of attributes, these are all in the
> same table, however, each sku can also have a number of "objectives",
> "topics", "members" and so on...these are currently in separate tables.
> I have used the SKUID to link between these tables and the sku table.
> It was really more of a question about whether or not the addition (possib
ly
> excessive - as some of it might not be used) data would have an adverse
> effect.
Sounds like you're in good shape then. Make sure you have good indexes,
including covering indexes if appropriate, and you should be fine
performance-wise.
Ok - I'm building a database around the content of an XML file I've been
supplied with - the process is straight forward enough...
The supplier has provided in the XML file far more data/information than I
actually need at this point in time, however, there's nothing to say that
"some" of this extra information might not be needed in the future...
I have therefore been adding columns like a mad man to incorporate the data
from the beginning, so if requested later on the impact on the system, and
the turn-around will be quicker.
I then wondered "wonder if this will have any side effects?"
Lets say, to make things simple I have a table with 5 columns that I do
need, I have a file with 40 items that could potentially be columns in this
table, I decide to add these now. I run my import process and populate my
table. Lets say the database size would have been 10Mb with the 5 columns,
but now, because of the types of data, and the sizes, its not 350Mb - would
this have any impact on SQL Server's performance when queries are run
against it? Obviously the sizes above are just "figures plucked from the
air" and are not actual sizes of my database, I was just trying to show a
big markup.
I would "guess" that if I wasn't using the other columns in the queries then
it shouldn't make any difference...
Any information would be appreciated - thanks in advance,
Regards
RobRob Meade wrote:
> Hi all,
> Ok - I'm building a database around the content of an XML file I've been
> supplied with - the process is straight forward enough...
> The supplier has provided in the XML file far more data/information than I
> actually need at this point in time, however, there's nothing to say that
> "some" of this extra information might not be needed in the future...
> I have therefore been adding columns like a mad man to incorporate the dat
a
> from the beginning, so if requested later on the impact on the system, and
> the turn-around will be quicker.
> I then wondered "wonder if this will have any side effects?"
> Lets say, to make things simple I have a table with 5 columns that I do
> need, I have a file with 40 items that could potentially be columns in thi
s
> table, I decide to add these now. I run my import process and populate my
> table. Lets say the database size would have been 10Mb with the 5 columns
,
> but now, because of the types of data, and the sizes, its not 350Mb - woul
d
> this have any impact on SQL Server's performance when queries are run
> against it? Obviously the sizes above are just "figures plucked from the
> air" and are not actual sizes of my database, I was just trying to show a
> big markup.
> I would "guess" that if I wasn't using the other columns in the queries th
en
> it shouldn't make any difference...
> Any information would be appreciated - thanks in advance,
> Regards
> Rob
>
Proper indexing and normalization will affect your table performance
more than the number of columns will. I hope you're not just building a
table that mimics the structure of the XML file. For instance, consider
an XML file that contains a list of customers, a billing address for
each customer, and for some customers, an optional shipping address.
Resist the temptation to build a single table that contains two sets of
address fields - one for billing, one for shipping. Instead, build a
"Customers" table that contains identifying information for the
CUSTOMER, such as customer ID, name, account number, etc.. Build an
"Addresses" table that contains information for each ADDRESS, such as an
address ID, street address, city, state, country, etc.. Build a third
table, "CustomerAddresses", that contains maybe four columns; customer
address ID, customer ID, address ID, and address type. This table
creates a 1-to-many link between a customer and its address(es). Even
the address type could be split off into an AddressTypes table,
containing an AddressType ID and a text label describing the AddressType
(billing, shipping, etc).|||"Tracy McKibben" wrote...
> Proper indexing and normalization will affect your table performance more
> than the number of columns will.
okey dokey
> I hope you're not just building a table that mimics the structure of the
> XML file.
Nope...one element "sku" has a number of attributes, these are all in the
same table, however, each sku can also have a number of "objectives",
"topics", "members" and so on...these are currently in separate tables.
I have used the SKUID to link between these tables and the sku table.
It was really more of a question about whether or not the addition (possibly
excessive - as some of it might not be used) data would have an adverse
effect.
Thanks for your reply Tracy.
Regards
Rob|||Rob Meade (ten.bewdoowsgnikNO-SPAM@.edaem.bbor) writes:
> Ok - I'm building a database around the content of an XML file I've been
> supplied with - the process is straight forward enough...
> The supplier has provided in the XML file far more data/information than I
> actually need at this point in time, however, there's nothing to say that
> "some" of this extra information might not be needed in the future...
> I have therefore been adding columns like a mad man to incorporate the
> data from the beginning, so if requested later on the impact on the
> system, and the turn-around will be quicker.
> I then wondered "wonder if this will have any side effects?"
> Lets say, to make things simple I have a table with 5 columns that I do
> need, I have a file with 40 items that could potentially be columns in
> this table, I decide to add these now. I run my import process and
> populate my table. Lets say the database size would have been 10Mb with
> the 5 columns, but now, because of the types of data, and the sizes, its
> not 350Mb - would this have any impact on SQL Server's performance when
> queries are run against it? Obviously the sizes above are just "figures
> plucked from the air" and are not actual sizes of my database, I was
> just trying to show a big markup.
The answer is, as it often does when it comes to performance: it depends.
However, here the "it depends" has a fairly straight-forward qualification.
If you have good indexing, the cost of the extra column is moderate.
The database will grow faster, and you must pay more attention to running
backup of the database and the transaction. It will also be more important
to keep fragmentation in check. But the overall cost for performance
does not have to be more than, say, 10%.
On the other hand, if you are lax about indexing, the cost for the
extra columns can be significant (assuming that they are in the same
tables as the rest). Since the rows are wider, there are fewer rows
per page, and more pages have to be read to scan tables.
Personally, I would consider the strategy of only adding the columns
you need, but add a table to save the XML documents in, so if need
occurs later, you can recover more fields from the XML document. This
is particularly attractive, if you are on SQL 2005 where you can store
the data in column of the xml data type. The performance cost for this
extra table is nil, but of course the increased database size takes
some administrative toll as discussed above.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Rob Meade wrote:
> Nope...one element "sku" has a number of attributes, these are all in the
> same table, however, each sku can also have a number of "objectives",
> "topics", "members" and so on...these are currently in separate tables.
> I have used the SKUID to link between these tables and the sku table.
> It was really more of a question about whether or not the addition (possib
ly
> excessive - as some of it might not be used) data would have an adverse
> effect.
Sounds like you're in good shape then. Make sure you have good indexes,
including covering indexes if appropriate, and you should be fine
performance-wise.
Wednesday, March 7, 2012
performance
Hi All:
When I design the report use VB.Net and view the report by using the
Preview tab and take about 1 min to get the result. However, once I deploy
the report and view the report through Report Manager, it take about a hour
to get the result. Why this happen? Anyone can help me on this?
Thanks,
KentKent,
When the report is previewed in the Report Designer the RS web facades are
bypassed. Instead, the Report Designer invokes the Report Server report
rendering functions directly. Perhaps, your performance issue is network
related. Does the Report Manager comes up quickly when you navigate from one
folder to another? If you believe that this is report rendering issue, look
at the Execution Log to find out how much time the Report Server spends in
querying, processing and rendering the report.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"kent kent" <kentkent@.discussions.microsoft.com> wrote in message
news:06DE6559-7DDB-4FD5-B13F-1262466ACC1B@.microsoft.com...
> Hi All:
> When I design the report use VB.Net and view the report by using the
> Preview tab and take about 1 min to get the result. However, once I
deploy
> the report and view the report through Report Manager, it take about a
hour
> to get the result. Why this happen? Anyone can help me on this?
> Thanks,
> Kent|||In addition to what Teo suggested, I think that 1 minute is mightly long.
Many of the tables I report against are between 1 and 10 million rows. Most
of my reports take just a few seconds. How many records are shown in the
final report. How many records are in the base table. Are you using query
parameters or filters. Filters bring all the data over and then filters it.
Query parameters perform the filter at the server and only bring down the
resulting data.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"kent kent" <kentkent@.discussions.microsoft.com> wrote in message
news:06DE6559-7DDB-4FD5-B13F-1262466ACC1B@.microsoft.com...
> Hi All:
> When I design the report use VB.Net and view the report by using the
> Preview tab and take about 1 min to get the result. However, once I
> deploy
> the report and view the report through Report Manager, it take about a
> hour
> to get the result. Why this happen? Anyone can help me on this?
> Thanks,
> Kent
When I design the report use VB.Net and view the report by using the
Preview tab and take about 1 min to get the result. However, once I deploy
the report and view the report through Report Manager, it take about a hour
to get the result. Why this happen? Anyone can help me on this?
Thanks,
KentKent,
When the report is previewed in the Report Designer the RS web facades are
bypassed. Instead, the Report Designer invokes the Report Server report
rendering functions directly. Perhaps, your performance issue is network
related. Does the Report Manager comes up quickly when you navigate from one
folder to another? If you believe that this is report rendering issue, look
at the Execution Log to find out how much time the Report Server spends in
querying, processing and rendering the report.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"kent kent" <kentkent@.discussions.microsoft.com> wrote in message
news:06DE6559-7DDB-4FD5-B13F-1262466ACC1B@.microsoft.com...
> Hi All:
> When I design the report use VB.Net and view the report by using the
> Preview tab and take about 1 min to get the result. However, once I
deploy
> the report and view the report through Report Manager, it take about a
hour
> to get the result. Why this happen? Anyone can help me on this?
> Thanks,
> Kent|||In addition to what Teo suggested, I think that 1 minute is mightly long.
Many of the tables I report against are between 1 and 10 million rows. Most
of my reports take just a few seconds. How many records are shown in the
final report. How many records are in the base table. Are you using query
parameters or filters. Filters bring all the data over and then filters it.
Query parameters perform the filter at the server and only bring down the
resulting data.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"kent kent" <kentkent@.discussions.microsoft.com> wrote in message
news:06DE6559-7DDB-4FD5-B13F-1262466ACC1B@.microsoft.com...
> Hi All:
> When I design the report use VB.Net and view the report by using the
> Preview tab and take about 1 min to get the result. However, once I
> deploy
> the report and view the report through Report Manager, it take about a
> hour
> to get the result. Why this happen? Anyone can help me on this?
> Thanks,
> Kent
Saturday, February 25, 2012
Perform an experssion in desing mode
Hi all,
SSIS, I would like to perform/validate an experssion in design mode. In particular, i would like to see the value of the varibles before running the whole package. I tried several time in Command Windows or Immedite Window unsuccessfully. Do those windows can only be used in running mode? Or how can I do that in other way?
Thanks
Where are you performing the expression? In the Control Flow or Data Flow.
This BOL entry describes how to evaluate an expression at design time in the Control Flow: http://msdn2.microsoft.com/en-us/library/ms141698.aspx
Expressions in the data flow are validated automatically at design time and any errors will be raised - but they can not be evaluated at design time.
Debug windows are only available when the package is running in the debugger.
Donald Farmer
|||That's mean I must create varibles in Control flow or date flow even though i just want to see the result of 1+1? Can't it like in VB or C# IDE?
|||
I'm still not clear where you are using the expression, so it's difficult to help in detail. You can evaluate Property Expressions, but not expressions in the data flow.
Donald
Perfomance Enhancement through proper database designing
Dear Reader
I am trying to design a database. How can I make best Judgement that Indexing (which I am trying to fix during Diagram Desingning process)is ok.
I am able to identify the best candidate for the indexing.
Below is the details I want to understand:
Area
ZIP
City
County
District
State/Province
Country
Now I want the data retrival optimization through Index. (you can suggest another idea, also)
Entities Area,..., Country have independent tables.
Example:
Area_Table
AreaID (PK)
Area
They have relationship- one to many- if you go from Country to Area.
There is one more table:
Location_Table (PK)
LocationID
AreaID
ZIPID
CityID
CountyID
DistrictID
State/ProvinceID
CountryID
(Location_ID is further related to the Address of the contact.)
GUI has a single form to enter these details.On a save command details in all the tables -Area to Country- (individually) being inserted.
& simultaniously Location_Table is also being inserted with the details.
Following is the situation of being queried these tables:
(1) GUI user can select an Area than the related details of ZIP .., ..., ...upto Country etc. should be loaded automatically (id it is previously stored by the user entry in the database.)
(2) Contacts have to be retrived on the basis of Area, ZIP, ....County. (Necessary Groupings are required )
Example:
If Contacts are queried Country Wise then the Display should be
Country1
State1
District1
County1
City1
ZIP1
Area1
Area2
ZIP2
City2
County2
District2
Country2
Please Guide.
SuryaPrakash
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...0255a1765491f15
*****************************************SuryaPrakash Patel via SQLMonster.com (forum@.SQLMonster.com) writes:
> I am trying to design a database. How can I make best Judgement that
> Indexing (which I am trying to fix during Diagram Desingning process)is
> ok.
I was not really able to understand exactly what the queries would look
like. While a trained database designer certainly puts indexes already
in the early design stage, it's better to focus to get the data model
right to support the functional requirements first. Once we have the
queries and the tables, it may be eaesier to say what would be the
best indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 09 Nov 2004 04:32:02 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:
>Dear Reader
>I am trying to design a database.
(snip)
Hi SuryaPrakash,
Further to Erlands's comments: you should also not think about GUI or
report formats when designing a database. Database design should be driven
by the structure of the data only.
Only when you have a properly normalized database that will store all data
the application needs and that will reject all modifications that would
hurt data integrity comes the time to think about the user interface (both
for in- and output screens and for reports) and about adding extra indexes
for performance enhancement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Dear Erland & Hugo
Thanks
I got your suggestions.
I have specific needs to get the certain ways of output. So it is obvious that I incorporate the needs while doing the database design.
But nowonwards I will try to stick the database structure, only. And leave the GUI part and Indexing part for later stages of development.
Point to be noted is that I will not have any chance to deploy any index after the software has been installed at user's end. Any comments.
Thanks again
SuryaPrakash
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...sql-server/5093
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...4931f7dbd3d74e1
*****************************************|||On Wed, 10 Nov 2004 00:59:23 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:
>Point to be noted is that I will not have any chance to deploy any index after the software has been installed at user's end. Any comments.
Hi SuryaPrakash,
That's a logical and quite common scenario. That's why you should only
install the software when it's completely finished. I'd recommend to build
the software first, then test and debug until it works as desired, then
start doing performance test and tweaking things (adding indexes,
rewriting queries, etc) until the speed is as desired. Then do a final
test to check that performance tweaking didn't break functionality.
Installing at the user's end should be postponed until all these stages
are done and the product is completely finished.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||SuryaPrakash Patel via SQLMonster.com (forum@.SQLMonster.com) writes:
> Point to be noted is that I will not have any chance to deploy any index
> after the software has been installed at user's end. Any comments.
So you need to test carefully with real-world data before you ship.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)