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:
>
> 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

Performance and Scalability of SqlXml

Hi
I have noticed 2 issues with SqlXml, which I am kind of worried about,
since I have already implemented some Business-Logic on SqlXml and am
now considering reverting back to other techniques:
1st) The Framework objects Microsoft.Data.SqlXml seem to be very slow,
compared to retrieving the same data using SqlDataAdapter.
Here my results for retrieving a rather complex schema involving data
from 9 tables (I ran the test twice):
TEST 1
through MS.Net Framework)
SqlCommand object)
is returned)
TEST 2
Now this tells me that IIS is almost always faster, because the
Connection to the DB is aready open (?). Calling this test from my
workstation across the network shows slow-downs of the MS.Net query of
up to 200% versus the IIS query.
Loading the same data into a DataSet is 3x faster!! (See Schema and
SQL statements below)
2nd) Once SqlXml through IIS looses connection to the DB, it seems to
hang and not try to reconnect. I have a proxy running to which I point
all db-connections and it decides to which SQL-Server the request is
sent, but this only works for direct calls within my code, IIS seems
to not retry to connect.
Here the Schema for the test:
-- cut --
<?xml version="1.0"?>
<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:ms="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<ms:relationship name="Pr_Hr" parent="ShopView_Produkte"
parent-key="Hersteller_ID" child="ShopView_Hersteller" child-key="ID"
/>
<ms:relationship name="Pr_Pri" parent="ShopView_Produkte"
parent-key="ID" child="ShopView_Kundengruppen_Preise"
child-key="Produkte_ID" />
<ms:relationship name="Pr_StP" parent="ShopView_Produkte"
parent-key="ArtikelNr" child="ShopView_Stacks" child-key="stackParent"
/>
<ms:relationship name="Pr_StC" parent="ShopView_Produkte"
parent-key="ArtikelNr" child="ShopView_Stacks" child-key="stackChild"
/>
<ms:relationship name="Pr_Op" parent="ShopView_Produkte"
parent-key="ID" child="ShopView_Optionen_Map" child-key="Produkte_ID"
/>
<ms:relationship name="OP_MAP" parent="ShopView_Optionen_Map"
parent-key="ID" child="ShopView_Optionen_Data" child-key="MAP_ID" />
<ms:relationship name="Pr_IE" parent="ShopView_Produkte"
parent-key="Inhalt_Einheit_ID" child="ShopView_Inhalt_Einheit_DATA"
child-key="Map_ID" />
<ms:relationship name="Pr_Xt" parent="ShopView_Produkte"
parent-key="ArtikelNr" child="ShopView_Produkte_Extras"
child-key="ArtikelNr" />
<ms:relationship name="Pr_Be" parent="ShopView_Produkte"
parent-key="ID" child="ShopView_Produktbeschreibung_MAP"
child-key="Produkte_ID" />
<ms:relationship name="Be_MAP"
parent="ShopView_Produktbeschreibung_MAP" parent-key="ID"
child="ShopView_Produktbeschreibung_Data" child-key="MAP_ID" />
<ms:relationship name="Pr_EG" parent="ShopView_Produkte"
parent-key="ID" child="ShopView_ProdESGruppen" child-key="Produkte_ID"
/>
<ms:relationship name="EG_Es" parent="ShopView_ProdESGruppen"
parent-key="ID Sprachen_ID" child="ShopView_ProdEigenschaften"
child-key="Gruppe Sprachen_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Produkt" ms:relation="ShopView_Produkte"
ms:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="Extras" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Extra"
ms:relation="ShopView_Produkte_Extras" ms:relationship="Pr_Xt"
ms:key-fields="ArtikelNr">
<xs:complexType>
<xs:attribute name="Typ" ms:field="Type"/>
<xs:attribute name="Daten" ms:field="Data"/>
<xs:attribute name="Erstellt" ms:field="DC"/>
<xs:attribute name="Positionen" ms:field="Positions"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="StackKinder" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Produkt" ms:relation="ShopView_Stacks"
ms:relationship="Pr_StP" ms:key-fields="stackParent">
<xs:complexType>
<xs:attribute name="Name" ms:field="PName_Child"/>
<xs:attribute name="ArtikelNr" ms:field="stackChild"/>
<xs:attribute name="Einstellungen"
ms:field="settings"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="InStacks" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Produkt" ms:relation="ShopView_Stacks"
ms:relationship="Pr_StC" ms:key-fields="stackChild">
<xs:complexType>
<xs:attribute name="Name" ms:field="PName_Parent"/>
<xs:attribute name="ArtikelNr" ms:field="stackParent"/>
<xs:attribute name="Einstellungen"
ms:field="settings"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Preise" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Preis"
ms:relation="ShopView_Kundengruppen_Preise" ms:relationship="Pr_Pri"
ms:key-fields="Kundengruppen_ID Produkte_ID">
<xs:complexType>
<xs:attribute name="Gruppe"
ms:field="Kundengruppen_ID"/>
<xs:attribute name="Preis" ms:field="Preis_EURO"/>
<xs:attribute name="USt" ms:field="USt_Satz"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Texte"
ms:relation="ShopView_Produktbeschreibung_MAP" ms:relationship="Pr_Be"
ms:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="Text"
ms:relation="ShopView_Produktbeschreibung_Data"
ms:relationship="Be_MAP" ms:key-fields="MAP_ID">
<xs:complexType>
<!--
<xs:attribute name="Kurz" ms:field="Kurzbeschreibung"/>
-->
<xs:attribute name="Lang" ms:field="Beschreibung"/>
<xs:attribute name="Einnahme" ms:field="Einnahme"/>
<xs:attribute name="Warenhinweise"
ms:field="Warenhinweise"/>
<xs:attribute name="Sprache" ms:field="Sprachen_ID"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Optionen" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Option"
ms:relation="ShopView_Optionen_Map" ms:relationship="Pr_Op"
ms:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="Item"
ms:relation="ShopView_Optionen_Data" ms:relationship="OP_MAP"
ms:key-fields="MAP_ID">
<xs:complexType>
<xs:attribute name="Name"
ms:field="Bezeichnung"/>
<xs:attribute name="Sprache"
ms:field="Sprachen_ID"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ID" ms:field="ID"/>
<xs:attribute name="ArtikelNR" ms:field="ArtikelNR"/>
<xs:attribute name="Bestellbar"
ms:field="istBestellbar"/>
<xs:attribute name="AutoLager"
ms:field="Lagerstand_Auto"/>
<xs:attribute name="Lagerstand" ms:field="ImLager"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="EGruppen"
ms:relation="ShopView_ProdESGruppen" ms:relationship="Pr_EG"
ms:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="Eigenschaft"
ms:relation="ShopView_ProdEigenschaften" ms:relationship="EG_Es">
<xs:complexType>
<xs:attribute name="Name" ms:field="Eigenschaft"/>
<xs:attribute name="Wert1" ms:field="Wert1"/>
<xs:attribute name="Wert2" ms:field="Wert2"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="Name" ms:field="Name"/>
<xs:attribute name="Spalte1" ms:field="Beschreibung_Wert1"/>
<xs:attribute name="Spalte2" ms:field="Beschreibung_Wert2"/>
<xs:attribute name="Sprache" ms:field="Sprachen_ID"/>
<xs:attribute name="Schlusstext" ms:field="Schlusstext"/>
</xs:complexType>
</xs:element>
<xs:element name="Einheit" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Einheit_Item"
ms:relation="ShopView_Inhalt_Einheit_DATA" ms:relationship="Pr_IE"
ms:key-fields="Map_ID">
<xs:complexType>
<xs:attribute name="Name" ms:field="Beschreibung"/>
<xs:attribute name="Sprache" ms:field="Sprachen_ID"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="Hersteller" ms:field="Name"
ms:relation="ShopView_Hersteller" ms:relationship="Pr_Hr" />
<xs:attribute name="ID" type="xs:decimal" ms:field="ID" />
<xs:attribute name="Name" ms:field="Name"/>
<xs:attribute name="Menge" ms:field="Inhalt_Menge"/>
<xs:attribute name="ArtikelNR" ms:field="ArtikelNR"/>
<xs:attribute name="GewichtKG" ms:field="GewichtKG"/>
<xs:attribute name="Lagerstand" ms:field="ImLager"/>
<xs:attribute name="Bestellbar" ms:field="IstBestellbar"/>
<xs:attribute name="Aktiv" ms:field="IstAktiv"/>
<xs:attribute name="NichtBestText" ms:field="NichtBestText"/>
<xs:attribute name="Highlight" ms:field="Highlighted"/>
<xs:attribute name="Rabattfaehig" ms:field="Rabattfhig"/>
</xs:complexType>
</xs:element>
</xs:schema>
-- cut --
And here the SQL Statements equivilent to the schema above
-- cut --
"SELECT "+
"H.Name, P.ID, P.Name, P.Inhalt_Menge, P.ArtikelNR, P.GewichtKG,
P.ImLager, P.IstBestellbar, P.IstAktiv, P.NichtBestText,
P.Highlighted, P.Rabattfhig "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Hersteller H ON P.Hersteller_ID = H.ID "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"X.Type, X.Data, X.DC, X.Positions "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Produkte_Extras X ON P.ArtikelNr = X.ArtikelNr "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"StP.PName_Child, StP.stackChild, StP.settings "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Stacks StP ON P.ArtikelNr = StP.stackParent "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"StC.PName_Parent, StC.stackParent, StC.settings "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Stacks StC ON P.ArtikelNr = StC.stackChild "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"Pr.Kundengruppen_ID, Pr.Preis_EURO, Pr.USt_Satz "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Kundengruppen_Preise Pr ON P.ID = Pr.Produkte_ID
"+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"TXD.Beschreibung, TXD.Einnahme, TXD.Warenhinweise, TXD.Sprachen_ID "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Produktbeschreibung_MAP TXM ON P.ID =
TXM.Produkte_ID "+
"LEFT JOIN ShopView_Produktbeschreibung_Data TXD ON TXM.ID =
TXD.MAP_ID "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"ED.Beschreibung, ED.Sprachen_ID "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Inhalt_Einheit_DATA ED ON P.Inhalt_Einheit_ID =
ED.MAP_ID "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"OD.Bezeichnung, OD.Sprachen_ID, "+
"OM.ArtikelNR, OM.istBestellbar, OM.Lagerstand_Auto, OM.ImLager "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Optionen_Map OM ON P.ID = OM.Produkte_ID "+
"LEFT JOIN ShopView_Optionen_Data OD ON OM.ID = OD.MAP_ID "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"Gr.Beschreibung_Wert1, Gr.Beschreibung_Wert2, Gr.Sprachen_ID,
Gr.Schlusstext, "+
"ES.Eigenschaft, ES.Wert1, ES.Wert2 "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_ProdESGruppen Gr ON P.ID = Gr.Produkte_ID "+
"LEFT JOIN ShopView_ProdEigenschaften ES ON Gr.ID = ES.Gruppe AND
Gr.Sprachen_ID = ES.Sprachen_ID "+
"WHERE (P.ArtikelNR = @.ArtNr);"
-- cut --
regards, Tilli
Noone has anything to say? Noone tested performance?
Any replies would be apprechiated!
regards, Tilli

Performance and Execution Plan

Performance !!!

Hi!

I am trying to execute an stored procedured that have already been used in another server (the test server ) but it takes 25 hours and in the another server takes just 40 minutes. !!

I have reviewed the indexes, statistics an so on, I have exactly the same quantity of data but cannot explain why does it takes so long in the server that is supposed to be the better one.

When I show de Execution Plan in the Query Analyzer in one server I get:

- Select (0%)

- Compute scalar (0%)

- Remote Query (100 %)

When I executed in the another one I get:

- Select

- Compute Scalar

- Nested Loops Left Outer Join (80%)

- Merge Join / Right

- Remote Query

- Sort

- Hash Match Right …

Even if I make a simple select by a certain period, in one server takes so long and in the another one just a few minutes.

Coul anybody please help me?

Sure... How much memory is on the test server versus the "other" server? How much CPU? 32 bit for both of them? Same operating systems? More data?|||By looking at the execution plan, they don't look like the same query.|||Can you please post some sample queries? If it is distributed queries then it is possible that you are getting a plan that is retrieving large number of rows from remote data source. But this depends on the statisitcs on any local tables that you are joining with and the query. There are ways to force joins to happen on the remote side. But anyway, you need to give more details before I can suggest any solution.|||

There is a chance that on server A you are querying it's local tables

where on server B you are querying remote tables (on server A).

If this is the case, try not to commit huge transactions over the network.

Also the source code for the query and the exact location of the tables would help define the problem

|||

Thank you for all your answers, let me give a little more of detail to clarify.

In my real environment I have this:

I have the problematic stored procedure in one server (let me say "Server A") and this procedure gets
data from Itself and server B (the linked server).

Basically the procedure makes a select from remote tables into a temporary table with
just a "where" of dates and another inserts in local tables.

To simplify the case I extract just the main select I use and it is exactly the same:
takes hours and hours to get the results of the "select".
But this "select" when is executed in my Test environment takes just a few minutes. (I am talking of 25 Hours vs. 40 Minutes aprox.)

What I have already checked is to have the same indexes, same statistics created
in both servers of both environments (server A, B, X, Y)

I also may say, the databases that I have in my Real Environment are just a Restore of the Databases that I have in my Test Environment.

The Hardware...
Real Environment:
Server A: 4 GB RAM,
Server B: 4 GM RAM (The linked Server)

Test Environment:
Server X: 4 GM RAM
Server Y: 1 GB RAM (The linked Server)

The Query...

For this query I checked the next points after executed in both environments:

Results of Execution Plan:
The results between Test an Real are very different, because while in the Real Environment there are a lot of
Logical and Physical Operators that describe the steps followed.

In terms of Costs the most expensive is "Nested Loops/Left Outer Join" with
80% of total execution plan. (this step just does not appear when I use my test environment).

In the Test Environment the only steps that i can see in the execution plan are:

Select 0%,

Compute Scalar 0% and

Remote Query 100%|||

For starters, I want to suggest that you create a view on the linked server with the query and use it instead remotely. This will provide the best performance and the plan will be local to that server in most cases. This provides better maintainability also and more tuning options. Also, please make sure that you are running the same version + service pack of SQL Server in your environment for comparison purposes.

Having said this, since all of the tables in the query are remote tables I don't see why we should even be trying to do any operations locally. It is possible that your test queries are little bit different or you are using different parameters. I am assuming that you are comparing your SP call with the same parameters between the two environments.

|||

Well, the thing here is that the stored procedured that I have to use have more statements, some of them need to access local tables. The queries that i used in both environments are exactly the same and over the same quantity of data (just a copy of one into the another).

As the main cost that i have is in the extract of the stored procedure that I show here, I have also tried to make the select step by step. I mean adding table by table in order to find any reason for the excessive time spend. I have noticed that after adding the table that have an “inner join”the process gets worst. So please, if you have any other idea let me know.

Performance - Joins vs Filters

Hi
I reckon this is a "how long's a piece of string"-type of question but I'll
try it anyway. If you could provide any pointers, even if it is not a direct
answer then I'd be really grateful.
I've written an app generates SQL. I'm joining many tables and it's stable.
However, I now need to enhance it some more and link in another table. I
have the option of extending the WHERE clause instead of modifying the
joining mechanism in the FROM clause. Extending the WHERE clause means
adding a subselect and the way to do this is *far* easier to implement than
to rework the joining mechanisms to include an extra table - most especially
for Left Joins.
So my preference would be just extend the filter but I'm not sure about the
impact on performance. Will left joining from the additional table (and
extending the filter) be significantly faster than SubSelecting from it and
using an IN?
Thanks
Simonit depends. You need to do your own benchmarks|||Recently, I developed a data warehouse and in terms of SQL performance tips
etc. I was a complete novice. I did alot of investigation and research into
the fastest way to query and I found that subselects in general were a big
performance hit. I found by creating intermediate tables that I could join
into other queries that performance was greatly enhanced. Of course this is
subjective to the scenario and I imagine there are plenty of exceptions, why
dont you try both and find out?
"Simon Woods" wrote:

> Hi
> I reckon this is a "how long's a piece of string"-type of question but I'l
l
> try it anyway. If you could provide any pointers, even if it is not a dire
ct
> answer then I'd be really grateful.
> I've written an app generates SQL. I'm joining many tables and it's stable
.
> However, I now need to enhance it some more and link in another table. I
> have the option of extending the WHERE clause instead of modifying the
> joining mechanism in the FROM clause. Extending the WHERE clause means
> adding a subselect and the way to do this is *far* easier to implement tha
n
> to rework the joining mechanisms to include an extra table - most especial
ly
> for Left Joins.
> So my preference would be just extend the filter but I'm not sure about th
e
> impact on performance. Will left joining from the additional table (and
> extending the filter) be significantly faster than SubSelecting from it an
d
> using an IN?
> Thanks
> Simon
>
>|||Simon,
Why don't you find out instead of guess? Type the two
possible queries you're considering into query analyzer
and either compare their execution plans or test them on
sample data.
If for some reason you can't test the queries you're considering,
and want more advice here, you'll have better luck if you
post specific queries along with the relevant CREATE TABLE
statements and some sample data.
Steve Kass
Drew University
Simon Woods wrote:

>Hi
>I reckon this is a "how long's a piece of string"-type of question but I'll
>try it anyway. If you could provide any pointers, even if it is not a direc
t
>answer then I'd be really grateful.
>I've written an app generates SQL. I'm joining many tables and it's stable.
>However, I now need to enhance it some more and link in another table. I
>have the option of extending the WHERE clause instead of modifying the
>joining mechanism in the FROM clause. Extending the WHERE clause means
>adding a subselect and the way to do this is *far* easier to implement than
>to rework the joining mechanisms to include an extra table - most especiall
y
>for Left Joins.
>So my preference would be just extend the filter but I'm not sure about the
>impact on performance. Will left joining from the additional table (and
>extending the filter) be significantly faster than SubSelecting from it and
>using an IN?
>Thanks
>Simon
>
>|||Well, first question in my mind is "do you need any of this data for
output?" If yes, then join, if no, then where clause. If it is too slow,
then optimize.
If you are just filtering data, then an exists in the where should be
faster, it will certainly express what you are trying to do in a more
correct manner.
And as everyone else has stated, test it out :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Simon Woods" <simonSPAMMENOT.woods@.virginNOTMESPAM.net> wrote in message
news:eQ94X$tEGHA.4036@.TK2MSFTNGP09.phx.gbl...
> Hi
> I reckon this is a "how long's a piece of string"-type of question but
> I'll try it anyway. If you could provide any pointers, even if it is not a
> direct answer then I'd be really grateful.
> I've written an app generates SQL. I'm joining many tables and it's
> stable. However, I now need to enhance it some more and link in another
> table. I have the option of extending the WHERE clause instead of
> modifying the joining mechanism in the FROM clause. Extending the WHERE
> clause means adding a subselect and the way to do this is *far* easier to
> implement than to rework the joining mechanisms to include an extra
> table - most especially for Left Joins.
> So my preference would be just extend the filter but I'm not sure about
> the impact on performance. Will left joining from the additional table
> (and extending the filter) be significantly faster than SubSelecting from
> it and using an IN?
> Thanks
> Simon
>|||On Fri, 6 Jan 2006 16:40:02 -0000, "Simon Woods"
<simonSPAMMENOT.woods@.virginNOTMESPAM.net> wrote:
>So my preference would be just extend the filter but I'm not sure about the
>impact on performance. Will left joining from the additional table (and
>extending the filter) be significantly faster than SubSelecting from it and
>using an IN?
If you're very lucky, the optimizer will turn out exactly the same
code for any of the top three or four ways to code it.
Actually, it's pretty common to see that.
J.|||Hi Simon
can't argue with Alexander and Steve's recommendation of "try both and
find out which is better"!
However, my experience (10 years) is that subselects are almost always
less efficient than joins. I never use them now, and I'm pleasantly
surprised again and again at how SQL Server can gobble up the most
evil-looking multiple join operations and flip the results back in
seconds.
The divantage of joins as you say is that they can take quite a bit
of work to get right.
try out the subselect with some (sufficiently large set of) sample
data, I reckon.
cheers
Seb

Performance

Hi
I have a sql server 2000 running with SP4.
My goal is the improvement of server prformance! Can i install server
performance advisor and get about SQL?
Regards
Chico
Start with
http://www.sql-server-performance.com/
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>
|||Run profiler for 20 minutes or so during a period of high activity. Store
the trace in a SQL table. Index the trace table by cpu, then duration, then
reads, then writes - ie 4 seperate indexes.
Then do queries like this
select top 1000 textdata from tracetable order by duration desc
GO
select top 1000 textdata from tracetable order by writes desc
GO
select top 1000 textdata from tracetable order by Reads desc
Go
select top 1000 textdata from tracetable order by CPU desc
Now you know the most expensive queries or stored procedures. From here on
end its a matter of improving the performance of the queries, procs
identified above. Study the execution plans and see if you can't improve the
code itself, or improve the performance by well placed indexes.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>

Performance

Hi
I have a sql server 2000 running with SP4.
My goal is the improvement of server prformance! Can i install server
performance advisor and get about SQL?
RegardsChico
Start with
http://www.sql-server-performance.com/
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>|||Run profiler for 20 minutes or so during a period of high activity. Store
the trace in a SQL table. Index the trace table by cpu, then duration, then
reads, then writes - ie 4 seperate indexes.
Then do queries like this
select top 1000 textdata from tracetable order by duration desc
GO
select top 1000 textdata from tracetable order by writes desc
GO
select top 1000 textdata from tracetable order by Reads desc
Go
select top 1000 textdata from tracetable order by CPU desc
Now you know the most expensive queries or stored procedures. From here on
end its a matter of improving the performance of the queries, procs
identified above. Study the execution plans and see if you can't improve the
code itself, or improve the performance by well placed indexes.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>