Help Optimize my Query

that takes 20 seconds may however maximally 5 last
how am I to provide the indices? fill factor?
SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
Massnahme,
dbo.IHLEAktionenStamm.Name AS Aktion,
dbo.IHLEAdressenStamm.Name1 AS KundenName,
dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
dbo.IHLEAktionenPlan.GueltigBis,
dbo.IHLEAktionenPlan.Wiedervorlage, dbo.IHLEAktionenPlan.WiedervorlageText,
dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
dbo.IHLEMarketingParameter.StatusKennzeichen AS
StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
LeistungsAngebote,
dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
LeistungsAngebotPruefung,
dbo.IHLEMarketingParameter.Kommunikationsart AS
KommunikationsArt,
dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
KommunikationsArtPruefung
FROM dbo.IHLEAktionenPlan INNER JOIN
dbo.IHLEMassnahmeStamm ON
dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID INNER
JOIN
dbo.IHLEAktionenStamm ON dbo.IHLEAktionenPlan.AktionID
= dbo.IHLEAktionenStamm.AktionID INNER JOIN
dbo.IHLEMassnahmePlan ON
dbo.IHLEAktionenPlan.MassnahmePlanID = dbo.IHLEMassnahmePlan.MassnahmePlanID
INNER JOIN
dbo.IHLEAdressenStamm ON dbo.IHLEAktionenPlan.AdressID
= dbo.IHLEAdressenStamm.AdressID INNER JOIN
dbo.IHLEMarketingParameter ON
dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
(dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
(dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM) AND
(dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
dbo.IHLEAktionenPlan.GeplanteAusfuehrungHi
Have you looked at an execution plan? Does an optimizer available to use
indexes defined on the tables?
"mPiccoli" <mPiccoli@.discussions.microsoft.com> wrote in message
news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
> that takes 20 seconds may however maximally 5 last
> how am I to provide the indices? fill factor?
> SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
> dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
> Massnahme,
> dbo.IHLEAktionenStamm.Name AS Aktion,
> dbo.IHLEAdressenStamm.Name1 AS KundenName,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
> dbo.IHLEAktionenPlan.GueltigBis,
> dbo.IHLEAktionenPlan.Wiedervorlage,
dbo.IHLEAktionenPlan.WiedervorlageText,
> dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
> dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
> dbo.IHLEMarketingParameter.StatusKennzeichen AS
> StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
> LeistungsAngebote,
> dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
> LeistungsAngebotPruefung,
> dbo.IHLEMarketingParameter.Kommunikationsart AS
> KommunikationsArt,
> dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
> KommunikationsArtPruefung
> FROM dbo.IHLEAktionenPlan INNER JOIN
> dbo.IHLEMassnahmeStamm ON
> dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID
INNER
> JOIN
> dbo.IHLEAktionenStamm ON
dbo.IHLEAktionenPlan.AktionID
> = dbo.IHLEAktionenStamm.AktionID INNER JOIN
> dbo.IHLEMassnahmePlan ON
> dbo.IHLEAktionenPlan.MassnahmePlanID =
dbo.IHLEMassnahmePlan.MassnahmePlanID
> INNER JOIN
> dbo.IHLEAdressenStamm ON
dbo.IHLEAktionenPlan.AdressID
> = dbo.IHLEAdressenStamm.AdressID INNER JOIN
> dbo.IHLEMarketingParameter ON
> dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
> WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
> (dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM)
AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
> ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung|||Did you look at the joined and the condition columns, are they indexed ?
Thats would be the main improment.
Jens SUessmeyer.
"mPiccoli" <mPiccoli@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
> that takes 20 seconds may however maximally 5 last
> how am I to provide the indices? fill factor?
> SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
> dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
> Massnahme,
> dbo.IHLEAktionenStamm.Name AS Aktion,
> dbo.IHLEAdressenStamm.Name1 AS KundenName,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
> dbo.IHLEAktionenPlan.GueltigBis,
> dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.WiedervorlageText,
> dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
> dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
> dbo.IHLEMarketingParameter.StatusKennzeichen AS
> StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
> LeistungsAngebote,
> dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
> LeistungsAngebotPruefung,
> dbo.IHLEMarketingParameter.Kommunikationsart AS
> KommunikationsArt,
> dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
> KommunikationsArtPruefung
> FROM dbo.IHLEAktionenPlan INNER JOIN
> dbo.IHLEMassnahmeStamm ON
> dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID
> INNER
> JOIN
> dbo.IHLEAktionenStamm ON
> dbo.IHLEAktionenPlan.AktionID
> = dbo.IHLEAktionenStamm.AktionID INNER JOIN
> dbo.IHLEMassnahmePlan ON
> dbo.IHLEAktionenPlan.MassnahmePlanID =
> dbo.IHLEMassnahmePlan.MassnahmePlanID
> INNER JOIN
> dbo.IHLEAdressenStamm ON
> dbo.IHLEAktionenPlan.AdressID
> = dbo.IHLEAdressenStamm.AdressID INNER JOIN
> dbo.IHLEMarketingParameter ON
> dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
> WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
> (dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
> ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung|||First of all, When you have a query this long, with table and column names a
s
long as this, use aliases as much as possible to improve readability.
Example: Your
"mPiccoli" wrote:

> that takes 20 seconds may however maximally 5 last
> how am I to provide the indices? fill factor?
> SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
> dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
> Massnahme,
> dbo.IHLEAktionenStamm.Name AS Aktion,
> dbo.IHLEAdressenStamm.Name1 AS KundenName,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
> dbo.IHLEAktionenPlan.GueltigBis,
> dbo.IHLEAktionenPlan.Wiedervorlage, dbo.IHLEAktionenPlan.WiedervorlageText
,
> dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
> dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
> dbo.IHLEMarketingParameter.StatusKennzeichen AS
> StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
> LeistungsAngebote,
> dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
> LeistungsAngebotPruefung,
> dbo.IHLEMarketingParameter.Kommunikationsart AS
> KommunikationsArt,
> dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
> KommunikationsArtPruefung
> FROM dbo.IHLEAktionenPlan INNER JOIN
> dbo.IHLEMassnahmeStamm ON
> dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID INNE
R
> JOIN
> dbo.IHLEAktionenStamm ON dbo.IHLEAktionenPlan.Aktion
ID
> = dbo.IHLEAktionenStamm.AktionID INNER JOIN
> dbo.IHLEMassnahmePlan ON
> dbo.IHLEAktionenPlan.MassnahmePlanID = dbo.IHLEMassnahmePlan.MassnahmePlan
ID
> INNER JOIN
> dbo.IHLEAdressenStamm ON dbo.IHLEAktionenPlan.Adress
ID
> = dbo.IHLEAdressenStamm.AdressID INNER JOIN
> dbo.IHLEMarketingParameter ON
> dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
> WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
> (dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM) AN
D
> (dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
> ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung|||First of all, to suggest what indices should exist, it would be good to know
the structure of the tables involved, and the DRI (Declared Referential
Integrity) constraints on the tables... Post the DDL...
Next, When you have a query this long, with table and column names as
long as this, use aliases as much as possible, and indent the major clauses
of teh SQL, to improve readability.
for Example:
The following is identical to your query
SELECT TOP 250 Ap.AktionenPlanID,
Ap.AdressID, MS.Bezeichnung Massnahme,
Ak.Name Aktion, Ad.Name1 KundenName,
Ap.GeplanteAusfuehrung, Ap.GueltigBis,
Ap.Wiedervorlage, Ap.WiedervorlageText,
Ap.NachbearbeitungsCodeID,
Mp.Statuspruefung StatusPruefung,
Mk.StatusKennzeichen StatusKennzeichen,
Mk.Leistungsangebote LeistungsAngebote,
Mp.LeistungsAngebotPruefung LeistungsAngebotPruefung,
Mk.Kommunikationsart KommunikationsArt,
Mp.KommunikationsArtPruefung KommunikationsArtPruefung
FROM dbo.IHLEAktionenPlan Ap
JOIN dbo.IHLEMassnahmeStamm Ms
ON Ms.MassnahmeID = Ap.MassnahmeID
JOIN dbo.IHLEAktionenStamm Ak
ON Ak.AktionID = Ap.AktionID
JOIN dbo.IHLEAktionenStamm Mp
ON Mp.MassnahmePlanID = Ap.MassnahmePlanID
JOIN dbo.IHLEAdressenStamm Ad
ON Ad.AdressID = Ap.AdressID
JOIN dbo.IHLEMarketingParameter Mk
ON Mk.AdressID = Ap.AdressID =
WHERE Ap.AusgefuehrtAm IS NOT NULL
AND MS.LandID = @.LANDID
AND Ap.AusgefuehrtAm >= @.DATEFROM
AND Ap.AusgefuehrtAm <= @.DATETO
ORDER BY Ap.Wiedervorlage, Ap.GeplanteAusfuehrung
"mPiccoli" wrote:

> that takes 20 seconds may however maximally 5 last
> how am I to provide the indices? fill factor?
> SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
> dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
> Massnahme,
> dbo.IHLEAktionenStamm.Name AS Aktion,
> dbo.IHLEAdressenStamm.Name1 AS KundenName,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
> dbo.IHLEAktionenPlan.GueltigBis,
> dbo.IHLEAktionenPlan.Wiedervorlage, dbo.IHLEAktionenPlan.WiedervorlageText
,
> dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
> dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
> dbo.IHLEMarketingParameter.StatusKennzeichen AS
> StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
> LeistungsAngebote,
> dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
> LeistungsAngebotPruefung,
> dbo.IHLEMarketingParameter.Kommunikationsart AS
> KommunikationsArt,
> dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
> KommunikationsArtPruefung
> FROM dbo.IHLEAktionenPlan INNER JOIN
> dbo.IHLEMassnahmeStamm ON
> dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID INNE
R
> JOIN
> dbo.IHLEAktionenStamm ON dbo.IHLEAktionenPlan.Aktion
ID
> = dbo.IHLEAktionenStamm.AktionID INNER JOIN
> dbo.IHLEMassnahmePlan ON
> dbo.IHLEAktionenPlan.MassnahmePlanID = dbo.IHLEMassnahmePlan.MassnahmePlan
ID
> INNER JOIN
> dbo.IHLEAdressenStamm ON dbo.IHLEAktionenPlan.Adress
ID
> = dbo.IHLEAdressenStamm.AdressID INNER JOIN
> dbo.IHLEMarketingParameter ON
> dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
> WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
> (dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM) AN
D
> (dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
> ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung|||my indices:
PK – AktionenPlanID
IX – AdressID
IX1 – MassnahmeID,AktionID
IX2 – LandID,AusgefuehrtAm
IX3 – Wiedervorlage, GeplanteAusfuerhung
the table contains 1.500.000 data records
if I order by cluse do not use then takes only 4 seconds
can someone explain to me why?
"Jens Sü?meyer" wrote:

> Did you look at the joined and the condition columns, are they indexed ?
> Thats would be the main improment.
> Jens SUessmeyer.
>
> "mPiccoli" <mPiccoli@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
>
>|||my indices:
PK – AktionenPlanID
IX – AdressID
IX1 – MassnahmeID,AktionID
IX2 – LandID,AusgefuehrtAm
IX3 – Wiedervorlage, GeplanteAusfuerhung
the table contains 1.500.000 data records
if I order by cluse do not use then takes only 4 seconds
can someone explain to me why?
"Jens Sü?meyer" wrote:

> Did you look at the joined and the condition columns, are they indexed ?
> Thats would be the main improment.
> Jens SUessmeyer.
>
> "mPiccoli" <mPiccoli@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
>
>|||my indices:
PK – AktionenPlanID
IX – AdressID
IX1 – MassnahmeID,AktionID
IX2 – LandID,AusgefuehrtAm
IX3 – Wiedervorlage, GeplanteAusfuerhung
the table contains 1.500.000 data records
if I order by cluse do not use then takes only 4 seconds
can someone explain to me why?
"Uri Dimant" wrote:

> Hi
> Have you looked at an execution plan? Does an optimizer available to use
> indexes defined on the tables?
> "mPiccoli" <mPiccoli@.discussions.microsoft.com> wrote in message
> news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
> dbo.IHLEAktionenPlan.WiedervorlageText,
> INNER
> dbo.IHLEAktionenPlan.AktionID
> dbo.IHLEMassnahmePlan.MassnahmePlanID
> dbo.IHLEAktionenPlan.AdressID
> AND
>
>|||my indices:
PK – AktionenPlanID
IX – AdressID
IX1 – MassnahmeID,AktionID
IX2 – LandID,AusgefuehrtAm
IX3 – Wiedervorlage, GeplanteAusfuerhung
the table contains 1.500.000 data records
if I order by cluse do not use then takes only 4 seconds
can someone explain to me why?|||On Tue, 26 Apr 2005 05:36:03 -0700, mPiccoli wrote:

>my indices:
>PK AktionenPlanID
>IX AdressID
>IX1 MassnahmeID,AktionID
>IX2 LandID,AusgefuehrtAm
>IX3 Wiedervorlage, GeplanteAusfuerhung
>the table contains 1.500.000 data records
>if I order by cluse do not use then takes only 4 seconds
>can someone explain to me why?
Hi mPiccoli,
If you leave out the ORDER BY, then SQL Server can return the rows in any
way it sees fit. With the ORDER BY, it has to do extra work.
If you comare execution plans, I guess you'll see one of the following
scenarios:
1. With ORDER BY, the index IX3 is used; without ORDER BY a different
index is used. Apparently, the other index can find the rows you need more
quickly; to satisfy the ORDER BY, the optimizer decides to use the index
that is already in the required order, even if it means that the rest of
the query takes some more work.
2. With ORDER BY, an extra sorting step is added, but the execution plan
doesn't change otherwise. Apparently, the optimizer estimates that getting
the rows in the wrong order first, then reordering them is better than
using a more expensive method to retrieve the rows.
Since I know nothing about your table structure or data, and the query
itself is quite hard to read as well (please do take Bretana's advice to
heart!) this is about all I can say. If you need more advice, you'll have
to provide more information. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

help on indices

are there any thumb rules to follow to set up indices on tables and to set the fill factor. can somebody suggest any link where I can read about this.
thanks in advance.
RohitSQL Server Central has some real good articles. Unfortunately, I haven't found any standard guidlines for creating indexes and setting fill factors. It realy depends how your system is designed and what you're trying to do. I found testing is the key and learning simply by trial and error.

Heer's the link:
http://www.sqlservercentral.com/|||The biggest factor I have found is the placement of the clustered index. I typically place the clustered index on the identity column (if I have one) so I don't have to worry about pagesplitting as new data always falls at the bottom of the leaf level. You're big thing with fill factors is you are paying a price in storage to reduce or eliminate page splits during operations. Kalen Delaney's Inside SQL Server 2000 is good for researching indexing and page splits.

HTH|||Page splitting is really what can eat time. But that only happens when a new key-value is inserted or changed "at random" or when a record-delete occurs. It really depends on your data and your application. In an awful lot of applications, deletions and even key-changes occur "almost never."

I'd suggest that you make no changes at all unless, after quite some time of running the database, you can establish that insert/delete/change-key activity is really creating a "human noticeable" performance problem. Establish that; don't "fiddle and tweak and guess."

Another thing to really bear in mind is that every index you place on the database ought to have a clear purpose for being there. It's real easy to have too many, and I think that's what really munchie-munchies the time away for any database. Some indexes might need to exist only for certain purposes, e.g. once-a-month, in which case it might be profitable to drop or deactivate them except when they're needed. (A computer can rebuild an index "from scratch" uncommonly quickly. Obviously if you are dealing with "billions and billions" of records, you might not... etc.)

An index will never be "optimal." Not at any time; not if it's being used. :p They don't need to be reorganized frequently... they will maintain themselves. Let them do that! This means that they will always have fairly-empty pages, somewhat lopsided record-counts in the tree... and "who cares." You should really only plan to take action when, and if, you factually establish the presence of unacceptable pain in the current status quo.|||The reason you would care about the amount of empty space in your indices are that it makes your queries have higher logical IO than neccessary.

To see if you have page splits issues, run perfmon and check your page splits/sec. To check your fragmentation level of your tables run dbcc showcontig, to see your logical IO and how you can tune your queries, use "set statistics IO on"

HTH|||Now I know where to start from, thank you all for the inputs.
Rohit