Fastest method to move data between two remote SQL servers?

Need to move approximately 2-5 GB of data between two servers located
in different states but on same WAN. What is the fastest method with
least intrusion on daily updates to file? (SQL Server 2000)
1. "Copy Server Object" task in DTS (any special options to enable?)
2. Use ODBC/Transformation within DTS.
3. Replication?
4. DROP / Select into via linked server?
5. Other options?P.S. I'm testing this out, but it appears step 1 and 2 are taking 6-8
hours each, so any advance help would be appreciated...|||2 options for a one-off
1 - backup and copy/restore on target server
2 - detach/copy/attach again on both servers.
But if you want to do this regularly then maybe 1 with jobs running scripts.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>|||I'm assuming the SQL Servers are reasonably fast, the bottle neck is the WAN
pipe, and the only scheduler available is SQL Agent.
Create a linked server from the source to the destination
On the source server have a job that does the following steps
Write a DTS package to export the data to a file
xp_cmdshell or operating system command within a job step: use gzip or
some other compression software to do reasonable compression. Gzip has
different compression levels.
xp_cmdshell or operating system command within a job step: copy the file
to the destination server (FTP is a little faster but less managable)
Use the linked server to start a job on the destination server or just
call a stored procedure
On the destination server, the job or stored procedure should
xp_cmdshell or operating system command within a job step: decompress the
file
bcp, bulk insert, or DTS (bulk loader) into SQL Server.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>|||Interesting! It'll take a bit to test and coordinate schedule times,
but I'll give it a shot. I believe you are correct on all your
assumptions too, althought I also suspect a little Disk IO competition.
Thanks!
FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
Option 1 did run during the day, when Option 2 had nighttime/early
morning.|||This is the time I usually get on my soap box about an enterprise batch
scheduler and preach to management.
If you have your source server start a job on the destination server after
the copy then you shouldn't have to worry about schedule times between the
servers.
Good luck.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129132709.159007.124640@.g49g2000cwa.googlegroups.com...
> Interesting! It'll take a bit to test and coordinate schedule times,
> but I'll give it a shot. I believe you are correct on all your
> assumptions too, althought I also suspect a little Disk IO competition.
>
> Thanks!
>
> FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
> Option 1 did run during the day, when Option 2 had nighttime/early
> morning.
>

Fastest method to move data between two remote SQL servers?

Need to move approximately 2-5 GB of data between two servers located
in different states but on same WAN. What is the fastest method with
least intrusion on daily updates to file? (SQL Server 2000)
1. "Copy Server Object" task in DTS (any special options to enable?)
2. Use ODBC/Transformation within DTS.
3. Replication?
4. DROP / Select into via linked server?
5. Other options?
P.S. I'm testing this out, but it appears step 1 and 2 are taking 6-8
hours each, so any advance help would be appreciated...
|||2 options for a one-off
1 - backup and copy/restore on target server
2 - detach/copy/attach again on both servers.
But if you want to do this regularly then maybe 1 with jobs running scripts.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegr oups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>
|||I'm assuming the SQL Servers are reasonably fast, the bottle neck is the WAN
pipe, and the only scheduler available is SQL Agent.
Create a linked server from the source to the destination
On the source server have a job that does the following steps
Write a DTS package to export the data to a file
xp_cmdshell or operating system command within a job step: use gzip or
some other compression software to do reasonable compression. Gzip has
different compression levels.
xp_cmdshell or operating system command within a job step: copy the file
to the destination server (FTP is a little faster but less managable)
Use the linked server to start a job on the destination server or just
call a stored procedure
On the destination server, the job or stored procedure should
xp_cmdshell or operating system command within a job step: decompress the
file
bcp, bulk insert, or DTS (bulk loader) into SQL Server.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegr oups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>
|||Interesting! It'll take a bit to test and coordinate schedule times,
but I'll give it a shot. I believe you are correct on all your
assumptions too, althought I also suspect a little Disk IO competition.
Thanks!
FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
Option 1 did run during the day, when Option 2 had nighttime/early
morning.
|||This is the time I usually get on my soap box about an enterprise batch
scheduler and preach to management.
If you have your source server start a job on the destination server after
the copy then you shouldn't have to worry about schedule times between the
servers.
Good luck.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129132709.159007.124640@.g49g2000cwa.googlegr oups.com...
> Interesting! It'll take a bit to test and coordinate schedule times,
> but I'll give it a shot. I believe you are correct on all your
> assumptions too, althought I also suspect a little Disk IO competition.
>
> Thanks!
>
> FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
> Option 1 did run during the day, when Option 2 had nighttime/early
> morning.
>

Fastest method to move data between two remote SQL servers?

Need to move approximately 2-5 GB of data between two servers located
in different states but on same WAN. What is the fastest method with
least intrusion on daily updates to file? (SQL Server 2000)
1. "Copy Server Object" task in DTS (any special options to enable?)
2. Use ODBC/Transformation within DTS.
3. Replication?
4. DROP / Select into via linked server?
5. Other options?P.S. I'm testing this out, but it appears step 1 and 2 are taking 6-8
hours each, so any advance help would be appreciated...|||2 options for a one-off
1 - backup and copy/restore on target server
2 - detach/copy/attach again on both servers.
But if you want to do this regularly then maybe 1 with jobs running scripts.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>|||I'm assuming the SQL Servers are reasonably fast, the bottle neck is the WAN
pipe, and the only scheduler available is SQL Agent.
Create a linked server from the source to the destination
On the source server have a job that does the following steps
Write a DTS package to export the data to a file
xp_cmdshell or operating system command within a job step: use gzip or
some other compression software to do reasonable compression. Gzip has
different compression levels.
xp_cmdshell or operating system command within a job step: copy the file
to the destination server (FTP is a little faster but less managable)
Use the linked server to start a job on the destination server or just
call a stored procedure
On the destination server, the job or stored procedure should
xp_cmdshell or operating system command within a job step: decompress the
file
bcp, bulk insert, or DTS (bulk loader) into SQL Server.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>|||Interesting! It'll take a bit to test and coordinate schedule times,
but I'll give it a shot. I believe you are correct on all your
assumptions too, althought I also suspect a little Disk IO competition.
Thanks!
FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
Option 1 did run during the day, when Option 2 had nighttime/early
morning.|||This is the time I usually get on my soap box about an enterprise batch
scheduler and preach to management.
If you have your source server start a job on the destination server after
the copy then you shouldn't have to worry about schedule times between the
servers.
Good luck.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129132709.159007.124640@.g49g2000cwa.googlegroups.com...
> Interesting! It'll take a bit to test and coordinate schedule times,
> but I'll give it a shot. I believe you are correct on all your
> assumptions too, althought I also suspect a little Disk IO competition.
>
> Thanks!
>
> FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
> Option 1 did run during the day, when Option 2 had nighttime/early
> morning.
>

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

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 minutes
Hi
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,kb s.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

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.LemmaModificationD
ate 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' )an
d
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.NNIndicationTypeI
d
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