Query whith Linked Server

Hi gruoup
I have to make a query from SQL Server tables joined with Visual FoxPro
xbase tables. I created a Linked Server with Microrosft OLE DB Provider for
Visual FoxPro provider, whth VFPOLEDB.1 string provider. Everything runs ok
within a Business Intelligence Development Studio environment, but when I
implement my report in SQL Reporting Server this one reports an error
because it can't create the object VFPOLEDB. I proved any combination of
Linked Server security tab page without to solve the problem.
Any help about this error I'll thank very much.I want to add some coments to clarify the problem.
The report runs at a right way from Internet Explorer 7 browser when I
generate it in a machine at which runs the Report Server, but I have
problems when I run it under Mozilla Fire Fox at local server machine or it
fails too from any browser at any intranet machine.
Thanks.
<tiempotecnologia@.newsgroup.nospam> escribió en el mensaje
news:Ou2%23o5kPHHA.1380@.TK2MSFTNGP05.phx.gbl...
> Hi gruoup
> I have to make a query from SQL Server tables joined with Visual FoxPro
> xbase tables. I created a Linked Server with Microrosft OLE DB Provider
> for Visual FoxPro provider, whth VFPOLEDB.1 string provider. Everything
> runs ok within a Business Intelligence Development Studio environment, but
> when I implement my report in SQL Reporting Server this one reports an
> error because it can't create the object VFPOLEDB. I proved any
> combination of Linked Server security tab page without to solve the
> problem.
> Any help about this error I'll thank very much.
>|||Hello Tiem
My understanding of this issue is that: You have a linked server in the sql
server and you wants to use it in the reporting services.
I would like to know what's the credential you supplied in the datasource
for the sql server.
If you use a sa account, did this report could be accessed?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei, thanks for your time!
I configured the database with RS Configuration tool under SQL credentials
using sa account and the report and its queries run correctly within
Business Intelligence Dev. Studio, i.e. the report preview, the execute of
query in data tab page, etc, everything runs ok, but from another intranet
machine, where I must authenticate with the account under run RS web
services, Report Manager opens the initial parameters view, I supply them
and then an error occurs because VFPOLEDB data provider object can`t create.
The physical Visual FoxPro tables are stored in an intranet machine within a
shared folder with read permission for anyone. Today I made a test moving
this tables to RS server local folder and I created a linked server ponting
to it but the same resulted. I tested with Domain\Useraccount credentials
for RS database too and the same resulted.
I'm waiting for help. Thanks in advance.
Arturo Carrión
artcarrion@.yahoo.com.ar
at Tiempo Hard SA
Mendoza, Argentina
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:POsb5hrPHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Tiem
> My understanding of this issue is that: You have a linked server in the
> sql
> server and you wants to use it in the reporting services.
> I would like to know what's the credential you supplied in the datasource
> for the sql server.
> If you use a sa account, did this report could be accessed?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Arturo,
When I mentioned credentials, I mean the credentials you use in the report.
You could connect to the report manager, find the report and then provide
the credential information.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello, Wei
When you mentioned credentials, did you refer to ones to access SQL Server
or the Web Services ?
If I've a good understanding and you refered to SQL Server credentials, when
I generate a report I define its data source providing a connect string with
RS server name and initial catalog, but I don't define credentials. These
ones are defined at RS database configuration, not at a single report.
Please, correct me if I'm wrong.
I think that the problem seems to be related with some VFPOLEDB Provider
Security settings, do you believe it ?
Thank you very much.
Arturo Carrión
.Net/SQL Server Developer at
Tiempo Hard SA
artcarrion@.yahoo.com.ar
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Wei.
I'm so sorry!. Definitively I'm wrong. When I define a data source I must
provide the server and credentials. I defined them as sa account and its
password, test button has no problem.
Sincerely yours,
Arturo Carrión
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Great Wei !
I solve the problem following this 3 steps:
1. SQL Server Windows Service, Browser and RS all must run under domain
account or net service account.
2. VFPOLEDB Provider Setting "Allow Inprocess" must be checked.
3. DataSource at each report must have sql credentials (your suggestion), in
our case, sa account.
Thank you very much for your help.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hello Arturo,
Thanks for the update and glad to hear you resolved this issue.
You need to pass the credential in the datasource of the report so other
client could use this credential to connect to the linked server.
Otherwise, they will access denied.
If you have any questions, please feel free to let me know.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql

Query tuning

I have the query below:
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
The fields RxSource and RxTarget have nonclustered indexes.
Density for the indexes is near E-5.
Moreover there are another indexes for the table.
The optimizer does not want to use the indexes and it uses the primary key.
If I force to use the indexes by hints, then it raise the indexes, but it does not select the needed rows.
select *
from
CDO_RXLINK T_CDO_RXLINK (index(rdb$foreign411, rdb$foreign412))
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Plan:
|--Compute Scalar(DEFINE[T_CDO_RXLINK].[TEXTBLOB]=[T_CDO_RXLINK].TEXTBLOB]))
|--Filter(WHERE(('Mar 29 2004 12:00AM'>=[T_CDO_RXLINK].[CREATED] AND 'Mar 29 2004 12:00AM'<=[T_CDO_RXLINK].[DELETED]) AND ([T_CDO_RXLINK].[CID]=100043 OR [T_CDO_RXLINK].[CID]=100041)) AND ([T_CDO_RXLINK].[RXSOURCE]=23921 OR [T_CDO_RXLINK].[RXTARGE
|--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[Vlad43].[dbo].[CDO_RXLINK] AS [T_CDO_RXLINK]))
|--Hash Match(Inner Join, HASH[T_CDO_RXLINK].[OID])=([T_CDO_RXLINK].[OID]))
|--Index Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK])) Index Scan OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK]), FORCEDINDEX
|--Index Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK])) Index Scan OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK]), FORCEDINDEX [T_CDO_RXLINK].[OID]
If I make one compound index for the two fields then it works well, and time of executing in 10 time better.
There are causes that I can not change the indexes.
How can I force the Server to make good plan?
Victor,
Are your statistics up to date? Running Update Stats might help the optimizer here. Failing that you might want to use index hints (with caution). Have you got an index on the correct columns? Posting a repro script here would help us to debug it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi Victor,
This query is too complex for the optimizer for the following reasons:
- It can't use any index on T_CDO_RXLINK.CID because of the IN clause.
- It won't use the index on T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget
because of the or clause
Because of this, the optimizer will estimate that a cluster index seek or a
table scan is probably the best execution plan.
You best bet (I think) is a combined index on T_CDO_RXLINK.Created and
T_CDO_RXLINK.Deleted .
Also replace the 'SELECT *' with only the column names you really need. If
this is a limited set, add them together with the T_CDO_RXLINK.CID,
T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget columns to the to make it a
covering index.
Karl Gram, BSc, MBA
http://www.gramonline.com
"Victor Kozel" <victor_kozel@.tut.by> wrote in message
news:egxlDkjFEHA.2976@.TK2MSFTNGP10.phx.gbl...
I have the query below:
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
The fields RxSource and RxTarget have nonclustered indexes.
Density for the indexes is near E-5.
Moreover there are another indexes for the table.
The optimizer does not want to use the indexes and it uses the primary key.
If I force to use the indexes by hints, then it raise the indexes, but it
does not select the needed rows.
select *
from
CDO_RXLINK T_CDO_RXLINK (index(rdb$foreign411, rdb$foreign412))
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Plan:
|--Compute
Scalar(DEFINE[T_CDO_RXLINK].[TEXTBLOB]=[T_CDO_RXLINK].TEXTBLOB]))
|--Filter(WHERE(('Mar 29 2004 12:00AM'>=[T_CDO_RXLINK].[CREATED] AND
'Mar 29 2004 12:00AM'<=[T_CDO_RXLINK].[DELETED]) AND
([T_CDO_RXLINK].[CID]=100043 OR [T_CDO_RXLINK].[CID]=100041)) AND
([T_CDO_RXLINK].[RXSOURCE]=23921 OR [T_CDO_RXLINK].[RXTARGE
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[Vlad43].[dbo].[CDO_RXLINK] AS [T_CDO_RXLINK]))
|--Hash Match(Inner Join,
HASH[T_CDO_RXLINK].[OID])=([T_CDO_RXLINK].[OID]))
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK]),
FORCEDINDEX
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK]),
FORCEDINDEX [T_CDO_RXLINK].[OID]
If I make one compound index for the two fields then it works well, and time
of executing in 10 time better.
There are causes that I can not change the indexes.
How can I force the Server to make good plan?
|||Hi Karl
If I remove the "Between" and "IN" conditions then the plan is OK.
The fields "CID" and "CREATED" have own indexes too, but if I disable their for optimizer It does not help.
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Also "*" I inserted for readability. This is a list of fields really
I divided the Query to two different queries and it works speedly.
I think the poor controllability of the sql server is a serious problem.
Thanks
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message news:OwSrV4kFEHA.2408@.TK2MSFTNGP10.phx.gbl...
Hi Victor,
This query is too complex for the optimizer for the following reasons:
- It can't use any index on T_CDO_RXLINK.CID because of the IN clause.
- It won't use the index on T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget
because of the or clause
Because of this, the optimizer will estimate that a cluster index seek or a
table scan is probably the best execution plan.
You best bet (I think) is a combined index on T_CDO_RXLINK.Created and
T_CDO_RXLINK.Deleted .
Also replace the 'SELECT *' with only the column names you really need. If
this is a limited set, add them together with the T_CDO_RXLINK.CID,
T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget columns to the to make it a
covering index.
Karl Gram, BSc, MBA
http://www.gramonline.com
"Victor Kozel" <victor_kozel@.tut.by> wrote in message
news:egxlDkjFEHA.2976@.TK2MSFTNGP10.phx.gbl...
I have the query below:
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
The fields RxSource and RxTarget have nonclustered indexes.
Density for the indexes is near E-5.
Moreover there are another indexes for the table.
The optimizer does not want to use the indexes and it uses the primary key.
If I force to use the indexes by hints, then it raise the indexes, but it
does not select the needed rows.
select *
from
CDO_RXLINK T_CDO_RXLINK (index(rdb$foreign411, rdb$foreign412))
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Plan:
|--Compute
Scalar(DEFINE[T_CDO_RXLINK].[TEXTBLOB]=[T_CDO_RXLINK].TEXTBLOB]))
|--Filter(WHERE(('Mar 29 2004 12:00AM'>=[T_CDO_RXLINK].[CREATED] AND
'Mar 29 2004 12:00AM'<=[T_CDO_RXLINK].[DELETED]) AND
([T_CDO_RXLINK].[CID]=100043 OR [T_CDO_RXLINK].[CID]=100041)) AND
([T_CDO_RXLINK].[RXSOURCE]=23921 OR [T_CDO_RXLINK].[RXTARGE
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[Vlad43].[dbo].[CDO_RXLINK] AS [T_CDO_RXLINK]))
|--Hash Match(Inner Join,
HASH[T_CDO_RXLINK].[OID])=([T_CDO_RXLINK].[OID]))
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK]),
FORCEDINDEX
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK]),
FORCEDINDEX [T_CDO_RXLINK].[OID]
If I make one compound index for the two fields then it works well, and time
of executing in 10 time better.
There are causes that I can not change the indexes.
How can I force the Server to make good plan?
|||Hi Mark,
Yes, I made updating for the statistic.
I have tried to use the query on Borland Interbase (the same db structure and data) It made good plan.
I think it does not have a sense to debug without data and the table have more than 150000 rows.
Thanks
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message news:E22497EA-36C8-4C1E-BA4E-86CA08578D6B@.microsoft.com...
Victor,
Are your statistics up to date? Running Update Stats might help the optimizer here. Failing that you might want to use index hints (with caution). Have you got an index on the correct columns? Posting a repro script here would help us to debug it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||thanks All,
This is the solution
select *
from
CDO_RXLINK T_CDO_RXLINK, CDO_RXLINK T_CDO_RXLINK1
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK1.RxSource = 23921 or T_CDO_RXLINK1.RxTarget = 23921)
and T_CDO_RXLINK1.OID = T_CDO_RXLINK.OID /*primary key*/
"Victor Kozel" <victor_kozel@.tut.by> wrote in message news:uJRJ2xvFEHA.3912@.TK2MSFTNGP10.phx.gbl...
Hi Karl
If I remove the "Between" and "IN" conditions then the plan is OK.
The fields "CID" and "CREATED" have own indexes too, but if I disable their for optimizer It does not help.
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Also "*" I inserted for readability. This is a list of fields really
I divided the Query to two different queries and it works speedly.
I think the poor controllability of the sql server is a serious problem.
Thanks
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message news:OwSrV4kFEHA.2408@.TK2MSFTNGP10.phx.gbl...
Hi Victor,
This query is too complex for the optimizer for the following reasons:
- It can't use any index on T_CDO_RXLINK.CID because of the IN clause.
- It won't use the index on T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget
because of the or clause
Because of this, the optimizer will estimate that a cluster index seek or a
table scan is probably the best execution plan.
You best bet (I think) is a combined index on T_CDO_RXLINK.Created and
T_CDO_RXLINK.Deleted .
Also replace the 'SELECT *' with only the column names you really need. If
this is a limited set, add them together with the T_CDO_RXLINK.CID,
T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget columns to the to make it a
covering index.
Karl Gram, BSc, MBA
http://www.gramonline.com
"Victor Kozel" <victor_kozel@.tut.by> wrote in message
news:egxlDkjFEHA.2976@.TK2MSFTNGP10.phx.gbl...
I have the query below:
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
The fields RxSource and RxTarget have nonclustered indexes.
Density for the indexes is near E-5.
Moreover there are another indexes for the table.
The optimizer does not want to use the indexes and it uses the primary key.
If I force to use the indexes by hints, then it raise the indexes, but it
does not select the needed rows.
select *
from
CDO_RXLINK T_CDO_RXLINK (index(rdb$foreign411, rdb$foreign412))
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Plan:
|--Compute
Scalar(DEFINE[T_CDO_RXLINK].[TEXTBLOB]=[T_CDO_RXLINK].TEXTBLOB]))
|--Filter(WHERE(('Mar 29 2004 12:00AM'>=[T_CDO_RXLINK].[CREATED] AND
'Mar 29 2004 12:00AM'<=[T_CDO_RXLINK].[DELETED]) AND
([T_CDO_RXLINK].[CID]=100043 OR [T_CDO_RXLINK].[CID]=100041)) AND
([T_CDO_RXLINK].[RXSOURCE]=23921 OR [T_CDO_RXLINK].[RXTARGE
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[Vlad43].[dbo].[CDO_RXLINK] AS [T_CDO_RXLINK]))
|--Hash Match(Inner Join,
HASH[T_CDO_RXLINK].[OID])=([T_CDO_RXLINK].[OID]))
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK]),
FORCEDINDEX
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK]),
FORCEDINDEX [T_CDO_RXLINK].[OID]
If I make one compound index for the two fields then it works well, and time
of executing in 10 time better.
There are causes that I can not change the indexes.
How can I force the Server to make good plan?

query to Linksed server very slow!

Hello,

i have created an RPC from my SQL server which queries a database of a linked server (remote server).
the query result is very very slow. (it is a mis-size query, with many JOINs on tables with many entries). Let's say it takes about two minutes to get about 3000 results.
The query uses five tables in the database of the linked server, runs a few (let's about 5-8) JOIN clauses and selects the entries. except for two tables (out of 8), each table has about 1000-2000 entries. the two have about 40,000 entries.
Is this normal?!
Is there anyway i can optimize my query?
i also tested my query and asked for only 100 results as opposed to all of the 3000. there was only 2-3 second difference in getting the results back, which indicates that it is not the remote connection but the query itself which is slow.

any help would be greatly appreciated!!How about the speed when you connect to your linked server directly and test your query? You can use SQL Server Management Studio to issue the same query to the linked server directly. SQL Server 2005 Database Tuning Advisor can help to you to optimize the query.

Query timeouts

I've created a report in RS 2006 that uses an XML data source. The source
uses a web service that takes sufficiently long to return that my query times
out.
can anyone tell me how can I increase the timeout value of the query?Click on the ... of the dataset. The timeout is on that page.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ekkis" <ekkis@.discussions.microsoft.com> wrote in message
news:CC99FCCF-D3B5-4AB6-AD31-BE35A002748E@.microsoft.com...
> I've created a report in RS 2006 that uses an XML data source. The source
> uses a web service that takes sufficiently long to return that my query
> times
> out.
> can anyone tell me how can I increase the timeout value of the query?
>

query timeout expired

Using VB, I am running a bulk insert query from csv file into a newly created table. It works fine on small test files; but when I try it on the production data, I get a "query timeout expired" message and processing ends. The text files contain several hundred thousand lines.

How can I resolve this problem. I have several hundred of these csv files and more coming.

Here's the code:

Dim sSQL As String
sSQL = "BULK INSERT " & TableName & " "
sSQL = sSQL & "FROM '" & DataPath & "' WITH "
sSQL = sSQL & "(FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2)"

DbConn.Execute sSQL

I commented out the DbConn.Execute sSQL and discovered a different error which is probably causing the timeout. Prior to the bulk insert, I check if the table exists, drop it if it does and then do the bulk insert. So now I'm getting the message:

Invalid object name 'XLDS_ds1'

The XLDS_ds1 is the table name. I was able to create the table originally so how can it be invalid? Here's the SQL code assigned to a string

sSQLExists = "IF OBJECT_ID(N'ImEx.dbo." & TableName & "', N'U') IS NOT NULL"
sSQLExists = sSQLExists & " DROP TABLE ImEx.dbo." & TableName & ";"

|||

Still can't get past the invalid object name. Since the table does not currently exist (apparently it was dropped at some point), I commented out the sSQLExists code and tried executing just the sSQL code in the original post. Still getting query timeout expired.

BTW, I ran the following query in MSSMS and got a success message but the table was NOT dropped. Anybody have a clue as to what is happenning?

IF OBJECT_ID (N'ImEx.dbo.XLDS_ds1', N'U') IS NOT NULL DROP TABLE ImEx.dbo.XLDS_ds1;

Command(s) completed successfully.

Query takes 3 mins to execute

Hi all, Iam new to DBA. I have tables with created indexes on all most all of
the columns. millions of records exists in this table. when i run the below
query, it is taking nearly 3 minutes to execute. How can i minimise the query
time ?
SELECT TOP 100 count(*) as cntDuplicatePat, FIRST_NAME, SURNAME, ADDRESS_1,
POSTCODE, DATE_OF_BIRTH
From TBL_Patient
where Merged_Into_ID is NULL AND inactive='N' and
PATIENT_ID NOT IN
(SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
HAVING (COUNT(*) >=2)
Also i would like to know how a query time can be minimized (on what factors
the query time depends?)On Wed, 2 Aug 2006 06:02:02 -0700, Vikas
<Vikas@.discussions.microsoft.com> wrote:
>Hi all, Iam new to DBA. I have tables with created indexes on all most all of
>the columns. millions of records exists in this table. when i run the below
>query, it is taking nearly 3 minutes to execute. How can i minimise the query
>time ?
Creating indexes on most of the columns is not usually the answer.
Creating them on the right columns, and on the right sets of columns
(with the columns in the right order) requires understanding of what
queries will be doing.
>SELECT TOP 100 count(*) as cntDuplicatePat, FIRST_NAME, SURNAME, ADDRESS_1,
>POSTCODE, DATE_OF_BIRTH
> From TBL_Patient
> where Merged_Into_ID is NULL AND inactive='N' and
> PATIENT_ID NOT IN
> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
> GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
> HAVING (COUNT(*) >=2)
>Also i would like to know how a query time can be minimized (on what factors
>the query time depends?)
Using TOP without ORDER BY makes little sense. I would either drop
the TOP 100, or add "ORDER BY cntDuplicatePat DESC".
I would not expect indexing of TBL_Patient to help much with this
query. The only way it might is if a very small percentage of
patients were active, or a very small percentage satisfied the "
Merged_Into_ID is NULL" test.
However, indexing of TBL_PATIENT_DUPLICATES is another matter. How
many rows are in TBL_PATIENT_DUPLICATES? How many columns? How many
satisfy the test STATUS <> 2? Does it have an index on PATIENT_ID_1?
You might try a non-clustered index on the column pair (PATIENT_ID_1,
STATUS).
It probably will not perform any differently, but you could also try a
NOT EXISTS test in place of the IN.
SELECT count(*) as cntDuplicatePat,
FIRST_NAME, SURNAME, ADDRESS_1, POSTCODE, DATE_OF_BIRTH
FROM TBL_Patient as P
WHERE Merged_Into_ID is NULL
AND inactive='N'
AND NOT EXISTS
(SELECT * FROM TBL_PATIENT_DUPLICATES as D
WHERE P.PATIENT_ID = D.PATIENT_ID_1
AND STATUS <> 2)
GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
Roy Harvey
Beacon Falls, CT|||Thanks Roy!
I could get some stuff from your side. I also want to know how to
design a database so that query time is minimized and also the updates are
faster. can you prefer any e-book or any single url please.
Vikas
"Roy Harvey" wrote:
> On Wed, 2 Aug 2006 06:02:02 -0700, Vikas
> <Vikas@.discussions.microsoft.com> wrote:
> >Hi all, Iam new to DBA. I have tables with created indexes on all most all of
> >the columns. millions of records exists in this table. when i run the below
> >query, it is taking nearly 3 minutes to execute. How can i minimise the query
> >time ?
> Creating indexes on most of the columns is not usually the answer.
> Creating them on the right columns, and on the right sets of columns
> (with the columns in the right order) requires understanding of what
> queries will be doing.
> >SELECT TOP 100 count(*) as cntDuplicatePat, FIRST_NAME, SURNAME, ADDRESS_1,
> >POSTCODE, DATE_OF_BIRTH
> > From TBL_Patient
> > where Merged_Into_ID is NULL AND inactive='N' and
> > PATIENT_ID NOT IN
> > (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
> > GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
> > HAVING (COUNT(*) >=2)
> >
> >Also i would like to know how a query time can be minimized (on what factors
> >the query time depends?)
> Using TOP without ORDER BY makes little sense. I would either drop
> the TOP 100, or add "ORDER BY cntDuplicatePat DESC".
> I would not expect indexing of TBL_Patient to help much with this
> query. The only way it might is if a very small percentage of
> patients were active, or a very small percentage satisfied the "
> Merged_Into_ID is NULL" test.
> However, indexing of TBL_PATIENT_DUPLICATES is another matter. How
> many rows are in TBL_PATIENT_DUPLICATES? How many columns? How many
> satisfy the test STATUS <> 2? Does it have an index on PATIENT_ID_1?
> You might try a non-clustered index on the column pair (PATIENT_ID_1,
> STATUS).
> It probably will not perform any differently, but you could also try a
> NOT EXISTS test in place of the IN.
> SELECT count(*) as cntDuplicatePat,
> FIRST_NAME, SURNAME, ADDRESS_1, POSTCODE, DATE_OF_BIRTH
> FROM TBL_Patient as P
> WHERE Merged_Into_ID is NULL
> AND inactive='N'
> AND NOT EXISTS
> (SELECT * FROM TBL_PATIENT_DUPLICATES as D
> WHERE P.PATIENT_ID = D.PATIENT_ID_1
> AND STATUS <> 2)
> GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
> Roy Harvey
> Beacon Falls, CT
>|||Sorry, there are lots of participants here with a catalog of links to
articles and books, but I'm not one of them.
The place to start in database design is normalization. Until you
understand that, and follow it, and do it by reflex, any other attempt
to design for minimized query time is probably going to create more
problems than it answers. Once you have that, then most of it is
proper indexing and queries.
Good luck!
Roy
On Wed, 2 Aug 2006 07:48:01 -0700, Vikas
<Vikas@.discussions.microsoft.com> wrote:
>Thanks Roy!
> I could get some stuff from your side. I also want to know how to
>design a database so that query time is minimized and also the updates are
>faster. can you prefer any e-book or any single url please.
>Vikas|||Vikas wrote:
> Hi all, Iam new to DBA. I have tables with created indexes on all most all of
> the columns. millions of records exists in this table. when i run the below
> query, it is taking nearly 3 minutes to execute. How can i minimise the query
> time ?
> SELECT TOP 100 count(*) as cntDuplicatePat, FIRST_NAME, SURNAME, ADDRESS_1,
> POSTCODE, DATE_OF_BIRTH
> From TBL_Patient
> where Merged_Into_ID is NULL AND inactive='N' and
> PATIENT_ID NOT IN
> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
> GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
> HAVING (COUNT(*) >=2)
> Also i would like to know how a query time can be minimized (on what factors
> the query time depends?)
I'm curious about one part of your WHERE clause:
PATIENT_ID NOT IN
(SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
Isn't this the same as:
PATIENT_ID IN
(SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS = 2)
Written the first way, you're most likely looking at an index or table
scan, whereas the second method will, assuming "status = 2" is selective
enough, use an index seek.
Also, if the second method is true, you might consider doing this as an
INNER JOIN instead of an IN.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Thu, 03 Aug 2006 08:55:52 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>I'm curious about one part of your WHERE clause:
> PATIENT_ID NOT IN
> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
>Isn't this the same as:
> PATIENT_ID IN
> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS = 2)
>Written the first way, you're most likely looking at an index or table
>scan, whereas the second method will, assuming "status = 2" is selective
>enough, use an index seek.
>Also, if the second method is true, you might consider doing this as an
>INNER JOIN instead of an IN.
Suppose there is NO row in TBL_PATIENT_DUPLICATES with a specific
PATIEND_ID. With the original version the NOT IN will be satisfied.
With the alternate version it will not be satisfied.
Roy Harvey
BeacoN Falls, CT|||Roy Harvey wrote:
> On Thu, 03 Aug 2006 08:55:52 -0500, Tracy McKibben
> <tracy@.realsqlguy.com> wrote:
>> I'm curious about one part of your WHERE clause:
>> PATIENT_ID NOT IN
>> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
>> Isn't this the same as:
>> PATIENT_ID IN
>> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS = 2)
>> Written the first way, you're most likely looking at an index or table
>> scan, whereas the second method will, assuming "status = 2" is selective
>> enough, use an index seek.
>> Also, if the second method is true, you might consider doing this as an
>> INNER JOIN instead of an IN.
> Suppose there is NO row in TBL_PATIENT_DUPLICATES with a specific
> PATIEND_ID. With the original version the NOT IN will be satisfied.
> With the alternate version it will not be satisfied.
> Roy Harvey
> BeacoN Falls, CT
Doh! Makes perfect sense, thanks...
Tracy McKibben
MCDBA
http://www.realsqlguy.com