fast query execution

i have a query which is taking 15 minutes to execute.The databse is very
large with a size of 20 gb approximately.the curruspong .ldf file to the
table is around 13gb.can any one help me to make my query execution fast.max
of 2 or 3 minutesHi
Send the DML and DDL. We can't help if we don't see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dileep" <Dileep@.discussions.microsoft.com> wrote in message
news:4C57EC53-CE00-4AA0-B938-86545EFBF088@.microsoft.com...
>i have a query which is taking 15 minutes to execute.The databse is very
> large with a size of 20 gb approximately.the curruspong .ldf file to the
> table is around 13gb.can any one help me to make my query execution
> fast.max
> of 2 or 3 minutes|||Hi
this is my query
SELECT l.LemmaId, l.BaseString, l.LanguageISODesc,l.LemmaMemo,k.NNClassName
,k.Semantic,l.ProductName, l.LemmaCreationDate,l.LemmaModificationDate
from (SELECT kbs.Lemma.LemmaId, kbs.BaseString.BaseString,
kbs.Country.CountryDescription,kbs.[Language].LanguageISODesc,
kbs.Product.ProductName
kbs.Lemma.LemmaMemo,kbs.Lemma.LemmaCreationDate,kbs.Lemma.LemmaModificationDate FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language] ON kbs.Lemma.LanguageId =kbs.[Language].LanguageId INNER JOIN kbs.Product ON kbs.Lemma.ProductId =kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%')
as l, (select i.LemmaId,i.NNClassName,j.Semantic from (SELECT DISTINCT
kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId INNER JOIN kbs.NNClass ON
kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId where LemmaId
in(SELECT kbs.Lemma.LemmaId FROM kbs.Lemma INNER JOIN kbs.BaseString ON
kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId where CountryISONr
=250 and kbs.Lemma.TableTypeId in ('2' ) and kbs.Lemma.ProductId in ('3' )and
kbs.BaseString.BaseString like 'a%') and kbs.NNClass.NNMetaClassId =3 ) as
i, (SELECT DISTINCT kbs.NNIndication.LemmaId, kbs.NNClass.NNClassName as
Semantic FROM kbs.NNIndication INNER JOIN kbs.NNIndicationType ON
kbs.NNIndication.NNIndicationTypeId = kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass ON kbs.NNIndicationType.NNClassId =kbs.NNClass.NNClassId where LemmaId in(SELECT kbs.Lemma.LemmaId FROM
kbs.Lemma INNER JOIN
kbs.BaseString ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250 and kbs.Lemma.TableTypeId in ('2' ) and
kbs.Lemma.ProductId in ('3' )and kbs.BaseString.BaseString like 'a%') and
kbs.NNClass.NNMetaClassId =4 )as j where i.LemmaId=j.LemmaId )as k
where k.LemmaId=l.LemmaId order by BaseString
thanks and regards|||Dileep wrote:
> Hi
> this is my query
>
[snip]
Hi Dileep,
For anyone that would like to analyse this query further, I have
reformatted the query (see bottom of the post). It is best to keep the
query in a human readable format. This makes debugging easier and also
performance analysis.
Please post the accompanying DDL (simplyfied CREATE TABLE statements,
all keys, constraints and indexes, and some sample data). Please also
post estimates of the number of rows in each table and the number of
rows in the resultset. Without all this information there is not much
anyone can say.
Based on the query you posted, I only have general advice:
- Make sure you index all primary and foreign keys (all join criteria)
- Add some compound indexes, check the query plan and keep the indexes
that are used (remove the other indexes that you added). For example,
try adding an index on kbs.Lemma(TableTypeId, ProductId, BaseStringId)
and on kbs.Lemma(BaseStringId, TableTypeId, ProductId) and check which
(if any) index is used for table kbs.Lemma. Another example: add indexes
on kbs.BaseString(BaseString, BaseStringId) and on
kbs.BaseString(BaseStringId, BaseString). It is best to add indexes to
all tables before determining which are used and which are useless
- Remove the DISTINCT keywords if they are not necessary
- Remove the virtual table constructs. The current main query is
something like "SELECT <columns> FROM (<subquery1>) AS l, (<subquery2>)
AS k WHERE l.<key>=k.<key>". The main query and the 2 subqueries can be
merged into one query.
HTH,
Gert-Jan
SELECT l.LemmaId
, l.BaseString
, l.LanguageISODesc
, l.LemmaMemo
, k.NNClassName
, k.Semantic
, l.ProductName
, l.LemmaCreationDate
, l.LemmaModificationDate
from (
SELECT
kbs.Lemma.LemmaId
, kbs.BaseString.BaseString
, kbs.Country.CountryDescription
, kbs.[Language].LanguageISODesc
, kbs.Product.ProductName
, kbs.Lemma.LemmaMemo
, kbs.Lemma.LemmaCreationDate
, kbs.Lemma.LemmaModificationDate
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
INNER JOIN kbs.Country
ON kbs.Lemma.CountryISONr = kbs.Country.CountryISONr
INNER JOIN kbs.[Language]
ON kbs.Lemma.LanguageId = kbs.[Language].LanguageId
INNER JOIN kbs.Product
ON kbs.Lemma.ProductId = kbs.Product.ProductId
where kbs.Lemma.CountryISONr = 250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
) as l
, (
select
i.LemmaId
, i.NNClassName
, j.Semantic from (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in(
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr=250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =3
) as i
, (
SELECT DISTINCT
kbs.NNIndication.LemmaId
, kbs.NNClass.NNClassName as Semantic
FROM kbs.NNIndication
INNER JOIN kbs.NNIndicationType
ON kbs.NNIndication.NNIndicationTypeId =kbs.NNIndicationType.NNIndicationTypeId
INNER JOIN kbs.NNClass
ON kbs.NNIndicationType.NNClassId = kbs.NNClass.NNClassId
where LemmaId in (
SELECT kbs.Lemma.LemmaId
FROM kbs.Lemma
INNER JOIN kbs.BaseString
ON kbs.Lemma.BaseStringId = kbs.BaseString.BaseStringId
where CountryISONr =250
and kbs.Lemma.TableTypeId in ('2')
and kbs.Lemma.ProductId in ('3')
and kbs.BaseString.BaseString like 'a%'
)
and kbs.NNClass.NNMetaClassId =4
)as j
where i.LemmaId=j.LemmaId
)as k
where k.LemmaId=l.LemmaId
order by BaseString